Re: Query tuning help, please

smithsummit_at_yahoo.com (Rob) wrote in message news:<fd4d5483.0310161411.369a47c6_at_posting.google.com>...
> HI,
>
> Query from a 9i db. Suggestions? Explain plan cost is 296787. Ouch.
> Any help would be much appreciated.
>
> SELECT WWLINK_THA_DATASET.FX_ID, WWLINK_DIE.UPDATE_DM,
> WWLINK_DIE.DIAT_ORAT_DT, WWLINK_DIE.FYAT_ID,
> WWLINK_DIE.OL_ID, WWLINK_DIE.OS_NR,
> WWLINK_DIE.OR_ROW_NR, WWLINK_DIE.OR_COL_NR,
> WWLINK_THA_DATASET.OL_ID, WWLINK_THA_DATASET.OS_NR,
> WWLINK_THA_DATASET.OR_ROW_NR, WWLINK_THA_DATASET.OR_COL_NR,
> WWLINK_THA_DATASET.DIAT_ORAT_DT, WWLINK_THA_DATASET.FYAT_ID,
> WWLINK_THA_DATASET.UPDATE_DM FROM WWLINK_DIE,
> WWLINK_THA_DATASET
> WHERE ( WWLINK_DIE.UPDATE_DM >= to_date('10/08/2003 05:23:25')
> OR WWLINK_THA_DATASET.UPDATE_DM >= to_date('10/15/2003
> 02:02:22'))
> AND WWLINK_THA_DATASET.WF_ID = WWLINK_DIE.WF_ID
> AND WWLINK_THA_DATASET.DI_ROW_NR = WWLINK_DIE.DI_ROW_NR
> AND WWLINK_THA_DATASET.DI_COL_NR = WWLINK_DIE.DI_COL_NR
> AND WWLINK_THA_DATASET.WF_ID is not null
> AND WWLINK_THA_DATASET.DI_ROW_NR is not null
> AND WWLINK_THA_DATASET.DI_COL_NR is not null
>
> Cheers,
>
>
> Rob
> rroot -at criadvantage -dot com

cost is completely irrelevant. Ignore it. posting the explain plan and the number of records in each table is far more helpful.

some quick points:

WHERE ( WWLINK_DIE.UPDATE_DM >= to_date('10/08/2003 05:23:25')
> OR WWLINK_THA_DATASET.UPDATE_DM >= to_date('10/15/2003
> 02:02:22'))

forces a full table scan. do you want an index scan? then you need a function based index.

i cant give you any more info than that. i need more information. Received on Fri Oct 17 2003 - 18:11:04 CEST