Tuning question!

From: ttrivedi <ttrivedi_at_deja.com>
Date: 2000/06/02
Message-ID: <3937D61A.7882F666@deja.com>#1/1

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