Optimize Index - help needed

From: RJ <rjwerning_at_yahoo.com>
Date: 18 Jun 2003 07:53:48 -0700
Message-ID: <570dd773.0306180653.1e8a7273@posting.google.com>

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?
Is there a way to improve performance?
How can I figure this information out for myself?
Any suggestions on a really good book to help me better understand issues of this nature? 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 Received on Wed Jun 18 2003 - 09:53:48 CDT