• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3197
  • Last Modified:

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
0
ooiewe
Asked:
ooiewe
1 Solution
 
dbalaskiCommented:
Need a little more info -- please post the actual select statement...
Also --  do you have a current explain plan on the query

dB
0
 
dbalaskiCommented:
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
0
 
ooieweAuthor Commented:
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
shikhadhCommented:
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
0
 
wlangCommented:
If you can change col1 to number instead of char, it helps. Or you can create function index on col1 (oracle 8i)
0
 
dbalaskiCommented:
That is not too bad --  the only thing I can think of is to avoid the sort ...

One way you can do this:
build a composite index on   (COL1,COL2)
this would produce an index range scan
maybe you could then apply an INDEX_FFS
hint..

The only thing I can think of at the moment...

dB
0
 
dbalaskiCommented:
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
0
 
ooieweAuthor Commented:
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.
0
 
ooieweAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now