Hi Grant,

Another one would be to create a compound index on spare_part, stk_item_ind.

Also if you have OEM (9i version) you could look to use SQL*Analyse and use the index recommendations / virtual index tuning wizard to determine if the new indexes are valid or that there are other indexes that may be of use.

If you have too many indexes then it is likely that updates will slow down through index maintenance.

Cheers

Keith

-----Original Message-----
From: Krystian Zieja
[mailto:oracledba-ezmlmshield-x65036691.[Email address protected]
Sent: Monday, 1 August 2005 8:38 PM
To: LazyDBA Discussion
Subject: Re: Index help.....


What indexes you have on your table?? How many rows your table contains?
I will sugest you adding index on stk_item_ind. Try to check sort
statistics on your instance because when you use distinct it involves
sorts - if you don't have suffiecient free memory you may sort on disk
and this can be an issue in your database
Best Regards
Krystian Zieja / mob
> Hello all!
>
> I see a lot of posts here asking for help on indexes, so I hope that I'm
> not gonna drive you crazy with another question:
> I have the following select that takes ages to run. Any hints on an
> index or indexes that may help.
>
> I guess that not knowing the actual data, may make it impossible to
> make a suggestion - but here it is anyway:
>
> CURSOR c_update_pacer_inv_items IS
> SELECT DISTINCT spare_part, ref_desc_50, DECODE(stk_item_ind, '0', '0',
> 'Y', '0','1') sii, uom, unit_price,
> end_date_active
> FROM xx_pacer_invumi;
>
> Many thanks
> Grant.
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these
> terms:http://www.lazydba.com/legal.html
>
>




--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html



Oracle LazyDBA home page