Re: SQL performance tuning

From: Oleg Fedorov <OFederov_at_hds-mckhboc.com>
Date: 14 Jun 2001 11:44:53 -0700
Message-ID: <1b18fb6a.0106141044.46566ec3@posting.google.com>

Hi James,

If you are looking for SO.DT_SO_CMPLT is NULL, indexes will not help you on SERV_ORD table (Oracle will not use any indexes).

I hope you have already created the following indexes: 1)
CREATE INDEX index_name
 ON PREMISE

  ( ky_so_no,
    dt_so_pend,
    ky_prem_no  )

2)
CREATE INDEX index_name
 ON STREET_NAME

  ( ky_so_no,
    dt_so_pend,
    ky_str_nm  )

P.S. I don't know the logic of your application, but if SERV_ORD has a lot of records and just a few of them is NULL try to avoid is NULL expression.

Have fun!

Good luck,
Oleg  

> Oleg,
>
> I made your suggested changes. Get the same results.. I am about to
> look at some of the parms in the init.ora pertaining to the optimizer.
>
>
> For grins and giggles I have added indexs to all columns except
> CD_SPEC_DT which only has 4 distinct values. Let me know if you guys
> see anthing else.
>
> Thanks!
>
> >Hi,
> >
> >First of all,
> >
> >SO.DT_SO_CMPLT is NULL or
> >SO.DT_SO_CMPLT>to_date('20010501','yyymmdd')
> >
> >looks better(Oracle do not need to convert SO.DT_SO_CMPLT to char for
> >each record and can use index for this field) then
> >
> >to_char(SO.DT_SO_CMPLT,'yyyymmdd') > '20010501' or
> >to_char(SO.DT_SO_CMPLT,'yyyymmdd') is null
> >
> >If that's not enough let us know.
> >
> >Good luck,
> >Oleg
> >
> >
Received on Thu Jun 14 2001 - 13:44:53 CDT