RE: Index help

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Fri, 23 Jul 2004 10:41:25 -0400
Message-ID: <564DE4477544D411AD2C00508BDF0B6A2133DE24@usahm018.exmi01.exch.eds.com>

In order to identify the best indexes for this query column statistics for number of distinct values and the total number of rows would be helpful.

A multicolumn index on idA3A5 and codeC5 looks like a candidate to be considered. Since the schedultetime date column is only an upper bound it may not be very selective but again it might make a fine 3rd column in a multicolumn index. What other queries do you have against this table? Indexes should be designed for the entire query load and not just one query unless that query is a critical query.

HTH -- Mark D Powell --

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of M.Godlewski Sent: Friday, July 23, 2004 10:18 AM
To: oracle-l_at_freelists.org
Subject: Index help

I ran tkprof on a trace file, and saw the SQL was doing full table scans. I added an index and now the SQL is using the index, but it still seems like it is using a lot of Oracle I/O.

Was wondering if anyone had ideas or HELP for index/indexes they would add to the following SQL statements?

SELECT 'wt.queue.ScheduleQueueEntry',A0.args,A0.classnamekeydomainRef, A0.idA3domainRef,TO_CHAR(A0.endExec,'dd mm yyyy hh24:mi:ss'),A0.entryNumber,
A0.entryOwnerIsNull,A0.classnamekeyB5,A0.idA3B5,A0.failureCount, A0.inheritedDomain,TO_CHAR(A0.datelock,'dd mm yyyy hh24:mi:ss'),

A0.classnamekeyA2lock,A0.idA3A2lock,A0.notelock,A0.classnamekeyA6,A0.idA3A6,   A0.classnamekeyA5,A0.idA3A5,TO_CHAR(A0.scheduleTime,'dd mm yyyy hh24:mi:ss')
  ,TO_CHAR(A0.startExec,'dd mm yyyy hh24:mi:ss'),A0.statusInfoIsNull,   A0.codeC5,A0.messageC5,TO_CHAR(A0.rescheduleTimeC5,'dd mm yyyy hh24:mi:ss'),
  A0.targetClass,A0.targetMethod,TO_CHAR(A0.createStampA2,'dd mm yyyy   hh24:mi:ss'),TO_CHAR(A0.modifyStampA2,'dd mm yyyy hh24:mi:ss'),A0.idA2A2,   A0.updateCountA2,TO_CHAR(A0.updateStampA2,'dd mm yyyy hh24:mi:ss') FROM
ScheduleQueueEntry A0 WHERE (A0.idA3A5 = :1) AND (A0.scheduleTime <=   TO_DATE(:2,:3)) AND ((A0.codeC5 = :4) OR (A0.codeC5 = :5))

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 5 0.00 0.00 0 0 0 0
Execute 29 0.00 0.00 0 0 0 0
Fetch 29 5.42 5.60 17240 50464 0 4
------- ------ -------- ---------- ---------- ---------- ----------

total 63 5.42 5.62 17240 50464 0 4

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 75 (PL626)

Rows Row Source Operation
------- ---------------------------------------------------

0 TABLE ACCESS FULL SCHEDULEQUEUEENTRY

Do you Yahoo!?
Vote for the stars of Yahoo!'s next ad campaign!

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Fri Jul 23 2004 - 09:38:41 CDT