Solved

SELECT MAX(TO_NUMBER(COL1)) FROM TABLE1 WHERE COL2=&VAL2;

Posted on 2001-07-05
9
3,044 Views
Last Modified: 2010-08-05
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
Comment
Question by:ooiewe
9 Comments
 
LVL 9

Expert Comment

by:dbalaski
ID: 6258351
Need a little more info -- please post the actual select statement...
Also --  do you have a current explain plan on the query

dB
0
 
LVL 9

Expert Comment

by:dbalaski
ID: 6258359
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
 

Author Comment

by:ooiewe
ID: 6258438
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
 

Expert Comment

by:shikhadh
ID: 6258568
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Expert Comment

by:wlang
ID: 6260196
If you can change col1 to number instead of char, it helps. Or you can create function index on col1 (oracle 8i)
0
 
LVL 9

Accepted Solution

by:
dbalaski earned 200 total points
ID: 6260291
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
 
LVL 9

Expert Comment

by:dbalaski
ID: 6260383
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
 

Author Comment

by:ooiewe
ID: 6261420
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
 

Author Comment

by:ooiewe
ID: 6411896
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Format Number Field 10 39
Generate HTML charts from SQL 4 51
Need a replacement data type in Oracle 6 29
sql query 9 21
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now