ooiewe
asked on
SELECT MAX(TO_NUMBER(COL1)) FROM TABLE1 WHERE COL2=&VAL2;
How to optimize the select statement?
COL1 is of char(15).
TABLE1 is hash-partitioned on COL2.
Hash-partitioned non-unique index on COL2.
Oracle version 8.1.7.1
COL1 is of char(15).
TABLE1 is hash-partitioned on COL2.
Hash-partitioned non-unique index on COL2.
Oracle version 8.1.7.1
never mind the query -- I just noticed it was the title of the message
still the current explain plan would be helpful --
what is the apx size of the table ? # of partitions? apx distribution on col2??
dB
still the current explain plan would be helpful --
what is the apx size of the table ? # of partitions? apx distribution on col2??
dB
ASKER
SELECT STATEMENT, COST=1, CARDINALITY=1
SORT AGGREGATE, COST=1, CARDINALITY=1
TABLE ACCESS BY LOCAL INDEX ROWID TABLE1, COST=1,CARDINALITY = 2605
INDEX RANGE SCAN IDX_COL2_ON_TABLE1, COST=1, CARDINALITY=2605
15 millions records
# of partitions = 4
0.1% (1 in 1000 records)
Additional info :
AVG_ROW_LEN=103
SORT AGGREGATE, COST=1, CARDINALITY=1
TABLE ACCESS BY LOCAL INDEX ROWID TABLE1, COST=1,CARDINALITY = 2605
INDEX RANGE SCAN IDX_COL2_ON_TABLE1, COST=1, CARDINALITY=2605
15 millions records
# of partitions = 4
0.1% (1 in 1000 records)
Additional info :
AVG_ROW_LEN=103
probably something wrong inthe memory.memory optimisation needs to be done.i would suggest u to use tkprof instaed of explan plan and then see what is wrong where
If you can change col1 to number instead of char, it helps. Or you can create function index on col1 (oracle 8i)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This went in as a proposed answer -- it was supposed to be a comment... must be a glitch, I know comment was checked off.
dB
dB
ASKER
Function index is possible in general to speed up similar query, but it will slow down DML.
Give me some time to try out the proposed indexing.
Give me some time to try out the proposed indexing.
ASKER
You have answered my question correctly. For your info, Oracle is using INDEX_FFS even though it is not specified as a hint. Thanks alot.
Also -- do you have a current explain plan on the query
dB