Re: optimization required

From: sybrandb <sybrandb_at_gmail.com>
Date: Tue, 26 Feb 2008 03:04:06 -0800 (PST)
Message-ID: <dac75e01-a6f0-4436-9b7f-6d9cc9f36846@64g2000hsw.googlegroups.com>

On Feb 26, 6:35 am, Nicsmart <nishantchandres..._at_gmail.com> wrote:
> I executed the following query in toad
>
> SELECT DISTINCT a.prv_cod
>            FROM iw_rle_prv a, iw_prv_mst b
>           WHERE a.rle_cod IN ('CEO')
>             AND a.prv_cod = b.prv_cod
>             AND (b.prv_cre_status = 'N' OR b.prv_cre_status IS NULL)
>             AND a.del_ind = 'N'
>             AND b.del_ind = 'N'
>
> Explain plan for the above query is
> SELECT STATEMENT Optimizer Mode=ALL_ROWS68              6.55350208921878
>   HASH JOIN                             68      2 K     6.55350208921878
>     TABLE ACCESS FULL   IWISEPRDN.IW_RLE_PRV    73      1 K     3.03524459882865
>     TABLE ACCESS FULL   IWISEPRDN.IW_PRV_MST    199     2 K     3.01541637294416
>
> number of records in table iw_rle_prv  is 991
> and  iw_prv_mst  is 251
>
> whats the reason for the above query to show such high cost?Any
> suggestions to reduce it

Please do not post queries
without

-specifying the version (4 digits): we can not guess it
- the DDL for tables and indices involved
- information about statistiscs being generated and current
- proper optimizer_index_cost_adj and optimizer_index_caching

parameters or system statistics being present. - information about predicate cardinality

Right now one can only assume the cardinality for columns like del_ind is high, and for prev_cre_status is unknown. The optimizer doesn't think there are suitable indices, so it is setting up a hash join.
This might be the fastest route.
You can force to use a NESTED LOOPS join, by using the /*+ USE_NL(a) */ hint.

--
Sybrand Bakker
Senior Oracle DBA

Received on Tue Feb 26 2008 - 05:04:06 CST