|
Hi Scubapro,
Thanks for the reply.
But this is not making any difference in the rankings. But we found that the current ranking is somehow ok for us.
Thanks for you efforts
Regards
jophin
|
|
|
|
|
Being a SQL Server developer, I am not familiar with Oracle.
We have a third party product using Oracle 10g that uses a dropdown to show the unique values from a field in a table of 2.5 million rows, but with only 50 unique values. There is no index on the field and it takes a minute to run the query 'select distinct field from table order by 1' used to populate the dropdown.
Adding an index on that field should allow the optimiser to scan the index rather than the table, and as the indexed field is 1/50th of the table’s row length I would expect to see a similar decrease in the time taken to run the query. However, it made no difference to the performance.
I have been told that Oracle will ALWAYS do a table scan if there is no where clause in the query. If this is the case without access to the source to change the query, I seem to have no way to optimise this.
Is what I heve been told true?
If not how can I get the optimiser to use the index without being able to chnage the SQL in the query?
|
|
|
|
|
From my experience, I would say, yes, Oracle will do a table scan if there is no where clause.
However, adding and index to the table may not solve the problem either, the entire table may be 2.5 million rows yet there are only 50 unique values, this means that each unique values a has about 50,000 index entries. Oracle would have to read 50,000 index records to satisfy the query. The optimizer may choose to perform a table scan rather then read the index.
Let me contact a friend of mine who is a true Oracle expert and see if there is anything that can be done. (He worked for Oracle for 18 years, now is a consultant specializing in Performance Tuning)
I'm afraid you may have to live with a poorly designed application.
|
|
|
|
|
The only way you will know if there is a table scan or not is to look into the plan generated for the SQL. If a where clause is an absolute necessity, which I doubt, then you can always add something like where 1 = 1. But without access to modify the source for the SQL you are in trouble.
Also are the indexes in separate tablespaces and files from the data? If not that may be why you don't see any difference. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Explain Query in PLSQL Developer shows:
SELECT STATEMENT.GOAL=ALL_ROWS
SORT UNIQUE
TABLE ACCESS FULL
The TABLE ACCESS FULL has cardinality of 2.5 milion, the other two 50, so I guess that is a full table scan
|
|
|
|
|
In PLSQL Developer, try adding theses different where clauses and see if a TABLE ACCESS FULL still shows up.
where 1 = 1
or
where nvl(field,field) = field
The last one has the the field you are selecting in the where clause and may force the use of the index.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Explain Plan with - where field <> 'imposibleValue'
SELECT STATEMENT.GOAL=FIRST_ROWS
SORT UNIQUE NOSORT
INDEX FULL SCAN
and the cost has reduced from 22,089 to 344
However, it only helps my understanding, not the problem!
|
|
|
|
|
It's always good to be able to understand a problem. At least now you have really good evidence to provide to the 3rd party vendor with a request for a change. Whether it is accepted or not is unfortunately an entirely different situation. Good luck with it though.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
You are right - adding a 'null' where clause such as where field <> 'imposibleValue' reduces the execution time from 1 minute to 1 second, so that is obviously forcing the use of an index.
Shame I can't change the sql 
|
|
|
|
|
Wow! This thread just tought me that I had really good luck when I designed the generation of the sql queries in our application: I do often start the WHERE clause with a "WHERE (1=1) " so that I can add further conditions simply with " AND " (instead of checking if the WHERE clause is still empty or not) - I did so because of that laziness...
|
|
|
|
|
See, it pays off to be lazy sometimes.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Hello everyone,
I have used sql2000 but sql2005 is also installed. I have created a select query that select the data opening a xml. while executing this query it generates an error:
Procedure sp_xml_preparedocument,
Failed to load Msxml2.dll.
please help me.
thank you in advance.
|
|
|
|
|
|
I have installed the msxml2.dll, but the problem is still there.
I have downloaded the file msxml2.dll.
Where should i store that file on computer, and how do we register that dll.
|
|
|
|
|
kaasir_2 wrote: Where should i store that file on computer
C:\Windows\System32
kaasir_2 wrote: and how do we register that dll.
regsvr32 C:\Windows\System32\msxml2.dll
I are Troll 
|
|
|
|
|
I am using windows 7, while i try to register the msxml2.dll.
It can not register the msxml2.dll rather is throws an error:
(The module "c:\windows\System32\msxml2.dll"
failed to load
Make sure the binary is stored at the specified path or debug it to check for problems with the binary or dependent .dll files
A dynamic link library (dll) initialization routine failed.)
|
|
|
|
|
kaasir_2 wrote: I am using windows 7
You were logged in as an administrator while registering the library?
This[^] kb-article suggests that it might be caused by the wrong MDAC version, an update is available here[^]. That update should also replace the MSXML2.DLL library, without the need to register it by hand.
Hope this helps
I are Troll
|
|
|
|
|
|
Hey guys, I've got 4 tables
Table A
ID
B_ID
Table B
ID
Name
C_ID
D_ID
Table C
ID
Name
Table D
ID
Name
Now I want to make this selection:
Select A.ID, B.Name, C.Name, D.Name
Getting B.Name is simple by using a inner join but I', confused about how to get C.Name and D.Name based on B.ID (If the selection was one record It would be easier but sometimes it's multi-records)
regards,
|
|
|
|
|
Im not exactely sure what your issue is
getting C.Name and D.Name should be easy, but thats a seperate problem from your suggestion
mrkeivan wrote: but sometimes it's multi-records
which implies that you may be looking for distinct rows - the data is what the data is - if you get multiple rows and you only want single rows, then you could have a fundamental constraint issue in your database design, or you're going to have to use distinct/unique keywords and hope its what you need
why dont you post the sql you think you'd use - its also not obvious to the rest of us wether the relationship between the tables are 1:1 or 1:m for example...
'g'
|
|
|
|
|
Me thinks you need to do some reading on sql queries, this is a very simple problem.
A is linked to B via BID
B is linked to C via CID
B is linked to D via DID
So do the joins and select your fields.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
you gave him the answer - I was going to make him work for it, then again, Ive been at work on a long weekend so Im a grumpy b@stard today (my team would say Im a grumpy b@stard everyday, but thats different  )
I was more interested in his multiple rows issues - I tend to find bad database design everywhere, forcing people to use distinct, max, min etc to get unique rows when it could have been prevented (and its a pet hate of mine if someone doesnt know why they are using such a qualifier)
'g'
|
|
|
|
|
Garth J Lancaster wrote: Ive been at work on a long weekend so Im a grumpy b@stard today
So at least your getting paid to faff around with code today, I'm just bored at the moment.
Garth J Lancaster wrote: you gave him the answer
The answer, yes, the code not a chance. He still needs to do some reseach into the exact syntax. Desperately needs to do some study if this has stumped him.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Let me clarify what I just said,
using a simple select I get BID,
Select BID FROM A WHERE ID = x
If I was sure I had one record returned I would write a function to do the job but most of the time I get more than one BID, No for each BID I get I have to Fetch C.Name ON B.ID = C.B_ID
I Can't use inner join cause I have to have all the Fkeys in the main table A.
regards,
|
|
|
|
|
I still don't quite understand your problem, I must be missing something.
You select B_ID from A.
Then you use this to join to B to get all matching C_ID and D_ID values.
Then you use these to join to C to get C.Name and to D to get D.Name.
If there will be any missing rows or null values anywhere along the line, use an outer join instead of an inner join.
You can do all of this in one single SELECT statement. No need for functions, no need for FETCH or cursors or anything like that. If there are any N:M relationships along the chain, you may end up with more than one row for any given value of A.ID value. By using outer joins, you can ensure that you get at least one row for every A.ID value.
I think I must be missing something in your problem because it seems quite straightforward to me.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
