Re: Tuning question!

Hi Tapan
Could you specify which columns belong to each index. Currently there is no method to determine why the optimizer decides in favor of full table scans.

Regards,

Sybrand Bakker, Oracle DBA

ttrivedi <ttrivedi_at_deja.com> schreef in berichtnieuws 3937D61A.7882F666_at_deja.com...
> Hey Gurus,
> I have a query which is bugging me. I got it down to a point where I
> have it under acceptable timings
> but it still is giving me a lot of full table scans . Is there any way
> to avoid the same ? Any ideas, suggestions,
> outbursts or epiphanies will be appreciated. Please let me know if you
> need something else to solve this
> puzzle.
>
> Here is the query with the explain plan (I apologize in advance for lost
>
> formatting ,if any, I can see this perfectly but some of us might
> lose the formatting) Core Product has 2752120 records and product map
> has 5626772 records.
> sql> select INDEX_NAME , INDEX_TYPE , STATUS from user_indexes where
> table_name = 'PRODUCT_MAP';
> INDEX_NAME INDEX_TYPE STATUS
> ---------------- --------------------- ------
> PRODUCT_MAP_IDX1 NORMAL VALID
> PRODUCT_MAP_IDX3 FUNCTION-BASED NORMAL VALID
> SYS_C001900 NORMAL VALID
> 3 rows affected.
> sql> select INDEX_NAME , INDEX_TYPE , STATUS from user_indexes where
> table_name = 'CORE_PRODUCT';
> INDEX_NAME INDEX_TYPE STATUS
> ----------------- ---------- ------
> CORE_PRODUCT_IDX2 NORMAL VALID
> CORE_PRODUCT_IDX3 NORMAL VALID
> CORE_PRODUCT_IDX4 NORMAL VALID
> SYS_C001800 NORMAL VALID
> 4 rows affected.
> sql> x select distinct
> sql> victim_cp.cpid,
> sql> survivor_cp.cpid
> sql> from
> sql> product_group pg1,
> sql> product_group pg2,
> sql> normalized_product_map npm1,
> sql> core_product victim_cp,
> sql> core_product survivor_cp
> sql> where
> sql> pg2.p_group_equiv_id = pg1.p_group_equiv_id
> sql> and pg2.p_group_id <> pg1.p_group_id
> sql> and npm1.p_code_type_id = pg1.p_code_type_id
> sql> and npm1.p_group = pg1.p_group
> sql> and npm2.p_code_type_id = pg2.p_code_type_id
> sql> and npm2.p_group = pg2.p_group
> sql> and npm1.normalized_p_code = npm2.normalized_p_code
> sql> and npm1.cpid != npm2.cpid
> sql> and victim_cp.cpid = npm1.cpid
> sql> and victim_cp.state = 6
> sql> and survivor_cp.cpid = npm2.cpid
> sql> and survivor_cp.state in (1, 4)
> sql> ;
> Plan
> ------------------------------------------------------------------
> SELECT STATEMENT Cost = 1054479
> SORT (UNIQUE)
> NESTED LOOPS
> MERGE JOIN
> SORT (JOIN)
> HASH JOIN
> MERGE JOIN
> SORT (JOIN)
> MERGE JOIN
> SORT (JOIN)
> TABLE ACCESS (FULL) DEJANEWS.CORE_PRODUCT (5)
> SORT (JOIN)
> TABLE ACCESS (FULL) DEJANEWS.PRODUCT_MAP (8)
> SORT (JOIN)
> TABLE ACCESS (FULL) DEJANEWS.PRODUCT_GROUP (1)
> TABLE ACCESS (FULL) DEJANEWS.PRODUCT_GROUP (2)
> FILTER
> SORT (JOIN)
> TABLE ACCESS (FULL) DEJANEWS.PRODUCT_MAP (7)
> TABLE ACCESS (BY INDEX ROWID) DEJANEWS.CORE_PRODUCT (6)
> INDEX (UNIQUE SCAN) UNIQUE DEJANEWS.SYS_C001800
> ------------------------------------------------------------------
> Plan
> 21 rows affected.
> sql>
>
>
>
>
>
>
Received on Fri Jun 02 2000 - 00:00:00 CDT