Re: Optimize Index - help needed

Sybrand Bakker wrote:

> 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

In addition, if you don't need the ORDER BY dump it.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)