| Query difference [message #20357] |
Thu, 16 May 2002 04:27  |
K. Prakash babu
Messages: 46
Registered: July 2001
|
Member |
|
|
hi
In my query i am using 2 tables(let us say T1 & T2).
There are some codes in T2. Based on codes i am trying to retrieve the records. The records pertaining to codes are given below..
code1 - 20 records
code2 - 30 records
code3 - 300000 records
code4 - 400000 records.
my tables are having indexes indexes. I am using hints to force the index on codes and other columns.
Surprisingly I am able to retrieve records in 200 milliseconds for code3 and code4. But for code1 and code2 the query is performing very bad as it is taking more than 6 seconds.
pls let me know the fact immdly.
thanx in advance
|
|
|
|
| Re: Query difference [message #20359 is a reply to message #20357] |
Thu, 16 May 2002 06:46  |
oraboy
Messages: 97
Registered: October 2001
|
Member |
|
|
I guess it comes down skewness issue on index..
As you know, the optimizer looks into the index first and depending upon the value, it decided whether to use index or full table scan(example: in case if you have retrieve more than 70% rows from table)
So I guess, fr code3 and code4 since it has huge no of rows, it uses the index appro'tly whereas fr code1 and code 2 its doing full table scan..Chk out EXPLAIN PLAN to see how query is executed.
Also check out for queries tables and indexes are properly analysed .
Good luck
Oraboy
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
