All the AND ( OR IS NULL) combinations are poison.
The only predicates useful for an index are nsc_model_code = 100217 AND
and locale = 'es_ES'
Unless you have a really good filtering on these columns and the matching
index I'd expect DB2 to do a table scan (check the db2exmt!).
You may be able to improve things somewhat by creating a covering index
across ALL predicates.
The complete index scan, followed by a ridsort and table fetch could be
cheaper (depends on the stats).

Cheers
Serge

PS: What's this "+ 0 days" stuff?

"Elumalai Sholan "
<db2udbdba-ezmlmshield-x47845229.[Email address protected] wrote on
07/27/2005 03:47:12 PM:

> Dear All,
>
> Could anyone help me to optimize this query, its taking long
> time to execute.,
>
>
>
> Select nsc_model_code, grade_code, body_code, engine_code,
> interior_code, equipment_code, is_accessory, is_port_fit,
>
> name, image, image_alt_text, description, disclaimer,
> locale, summary, slogan, raw_price, raw_price_ex_vat,
>
> i18n_cur_price, i18n_cur_price_ex_vat, is_core_feature,
> status, eqp_class_code, class_sort_order, sort_order,
>
> class_name
>
> from cb5_vehicle_options_v
>
> where nsc_model_code = 100217
>
> and (body_code = 100219 OR body_code is null)
>
> and (engine_code = 100182 OR engine_code is null)
>
> and (grade_code = 100141 OR grade_code is null)
>
> and (interior_code = 100101 OR interior_code is null)
>
> and locale = 'es_ES'
>
> AND COALESCE(VEHEQP_AVAILABLE_FROM, current date + 0 days) <= current
> date + 0 days
>
> AND COALESCE(VEHEQP_AVAILABLE_TO, current date + 0 days) >= current
> date + 0 days
>
> AND COALESCE(PRICE_START_DATE, current date + 0 days) <= current date +
> 0 days
>
> AND COALESCE(PRICE_END_DATE, current date + 0 days) >= current date + 0
> days ;
>
>
>
> Regards
>
>
>
> Sholan
>
>
>
>
***********************************************************************************************************

> CONFIDENTIALITY NOTICE
>
> This message is for the named person's use only. It may contain
> confidential, proprietary or legally privileged information.
>
> If you receive this message in error, please immediately delete it
> and all copies of it from your system, destroy any hard copies of it
> and notify us by email to email.[Email address protected] with a
> copy of this message. You must not, directly or indirectly, use,
> disclose, distribute, print or copy any part of this message if you
> are not the intended recipient. NISSAN EUROPE and any of its
> subsidiaries each reserves the right to monitor all e-mail
> communications through its networks.
>
> NISSAN EUROPE is neither liable for the proper, complete
> transmission of the information contained in this communication nor
> any delay in its receipt. This email was scanned for the presence of
> computer viruses. In the unfortunate event of infection NISSAN
> EUROPE does not accept liability.
>
> Any views expressed in this message are those of the individual
> sender, except where the message states otherwise and the sender is
> authorised to state them.
>
>
***********************************************************************************************************

>
>
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>


DB2 & UDB email list listserv db2-l LazyDBA home page