Re: SQL performance tuning

On 14 Jun 2001 11:44:53 -0700, OFederov_at_hds-mckhboc.com (Oleg Fedorov) wrote:

Yes, I touched base with the developers and removed the NULL and went from 2 minutes to 2 seconds.

>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 - 16:16:10 CDT