Re: Optimize Index - help needed

On 18 Jun 2003 07:53:48 -0700, rjwerning_at_yahoo.com (RJ) wrote:

Answers embedded

>Sorry for having to bug others for help, but I'm still fighting my way
>through some of the DBA tasks since our DBA quit (he has yet to be
>replaced).
>
>We're using Oracle 8.1.7
>
>Select statement that is running and takes around 3 min to process:
>
>select * from G_CONTRACT
>where (project not in (select project from G_PROJECT_ID_REL where
>(loginid =
>'test1') and (business_unit = 'DEM')))
>and (R_STATUS = 'A')
>and BUSINESS_UNIT = 'DEM'
>order by CONTRACT
>
>
>Table G_CONTRACT has 1.5 million records
>
>UNIQUE INDEX G_CONTRACT001 ON G_CONTRACT (
> PROJECT, BUSINESS_UNIT)
>INDEX G_CONTRACT002 ON G_CONTRACT (
> CONTRACT, BUSINESS_UNIT)
>
>
>Table G_PROJECT_ID_REL has 800,000 records
>
>UNIQUE INDEX GPROJECTIDREL001 ON G_PROJECT_ID_REL (
> LOGINID, PROJECT, BUSINESS_UNIT)
>
>
>What I would like to know is:
>
>- Are these index's fine for the select statement?

The index on G_CONTRACTS will not be used in this statement, neither in CBO nor in RBO. Using CBO you could try to use a /*+ HASH_AJ */ hint in the subquery, other than that you should rewrite your not in as a not exists

>
>- Is there a way to improve performance?
>

replace the not in subquery by not exists
>- How can I figure this information out for myself?

Read the 'Designing and tuning for performance manual'
>
>- Any suggestions on a really good book to help me better understand
>issues of this nature?

Oracle High Performance Tuning by Guy Harrison.

>I'm always interested in learning more, and not afraid to dig in and
>get my hands dirty, but this issue is a hot one so I don't have a lot
>of time to resolve it. Long term I need to improve my skills so I
>don't have to beg others for help.
>
>
>Thanks a ton,
> Rich Werning

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Jun 18 2003 - 12:26:35 CDT