Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning question!

Re: Tuning question!

From: S Bunker <bunkers_at_bigfoot.com>
Date: 2000/06/02
Message-ID: <cP0_4.607$Ko2.16880@wormhole.dimensional.com>#1/1

You can probably do better that this -- but more information is needed ...

"ttrivedi" <ttrivedi_at_deja.com> wrote in message news: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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US