Re: Need SQL optimization help

From: Ivan K. <ivan_521521_at_yahoo.com>
Date: Wed, 20 Jul 2005 10:33:55 -0500
Message-ID: <42DE6EE3.7020200@yahoo.com>

Niall Litchfield wrote:
> "Ivan K." <ivan_521521_at_yahoo.com> wrote in message
> news:42DD5B06.1050208_at_yahoo.com...
>

>>...

>
> well you don't give the explain plan, stats etc.
>
> never the less.
>
> do you really need all those distincts in the subquerys? as opposed to at
> the end?
> are stats current?
> optimiser_mode/goal?
> oracle version?
> indexes on the various XXX_id columns ans subsubitems.score?
>
> some of these will be answered by posting an explain plan.
>

The Oracle version is 9.2.0.5.0. The plan is attached. Thank you for taking a look!

PLAN_TABLE_OUTPUT

---

---

| Id  | Operation                             |  Name              | 

Rows | Bytes |TempSpc| Cost |

---

|   0 | SELECT STATEMENT                      |                    | 
77255 |  5054K|       |   209K|
|*  1 |  HASH JOIN OUTER                      |                    | 
77255 |  5054K|  3792K|   209K|
|   2 |   VIEW                                |                    | 
76030 |  2895K|       |   166K|
|   3 |    SORT UNIQUE                        |                    | 
76030 |    15M|    40M|   166K|
|*  4 |     HASH JOIN OUTER                   |                    | 
76030 |    15M|  8472K|   158K|
|*  5 |      FILTER                           |                    | 
    |       |       |       |
|*  6 |       HASH JOIN OUTER                 |                    | 
    |       |       |       |
|*  7 |        HASH JOIN                      |                    | 
76030 |  4009K|       | 72326 |
|   8 |         INLIST ITERATOR               |                    | 
    |       |       |       |
|   9 |          TABLE ACCESS BY INDEX ROWID  | KEYWORD            | 
  4 |   100 |       |     2 |
|* 10 |           INDEX RANGE SCAN            | INJ_KEYWORD_IND02  | 
  1 |       |       |     1 |
|* 11 |         HASH JOIN                     |                    | 
1767K|    48M|    37M| 72315 |
|  12 |          TABLE ACCESS FULL            | ITEMKEYWORD        | 
1767K|    16M|       |   889 |
|* 13 |          TABLE ACCESS FULL            | ITEM_LIST_IMP      | 
5646K|   102M|       | 65224 |
|  14 |        VIEW                           |                    | 
13440 |   630K|       | 71414 |
|  15 |         SORT UNIQUE                   |                    | 
13440 |   354K|  1168K| 71414 |
|  16 |          VIEW                         |                    | 
19007 |   501K|       | 71310 |
|* 17 |           HASH JOIN                   |                    | 
19007 |   946K|       | 71310 |
|  18 |            TABLE ACCESS BY INDEX ROWID| KEYWORD            | 
  1 |    25 |       |     2 |
|* 19 |             INDEX RANGE SCAN          | INJ_KEYWORD_IND02  | 
  1 |       |       |     1 |
|  20 |            VIEW                       |                    | 
1767K|    43M|       | 71299 |
|* 21 |             HASH JOIN                 |                    | 
1767K|    38M|    37M| 71299 |
|  22 |              TABLE ACCESS FULL        | ITEMKEYWORD        | 
1767K|    16M|       |   889 |
|  23 |              VIEW                     | ITEM_LIST          | 
5646K|    70M|       | 65224 |
|* 24 |               TABLE ACCESS FULL       | ITEM_LIST_IMP      | 
5646K|   102M|       | 65224 |
|  25 |      TABLE ACCESS BY INDEX ROWID      | SUB_ITEM           | 
779K|    81M|       | 12442 |
|* 26 |       INDEX RANGE SCAN                | SUB_ITEM_IND05     | 
17 |       |       |  1748 |
|* 27 |   TABLE ACCESS FULL                   | SUBSUBITEMS        | 
2261K|    60M|       | 38489 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

    1 - access("$vm_view"."SUB_ITEM_ID"="SUBSUBITEMS"."SUB_ITEM_ID"(+))
    4 - access("ITEM_LIST_IMP"."ITEM_ID"="SUBITEM"."QUERY_ID"(+))
    5 - filter("ITEM_EPASS02"."EP02_KEYWORD" IS NULL)
    6 - access("ITEM_LIST_IMP"."ITEM_ID"="ITEM_EPASS02"."ITEM_ID"(+))
    7 - 

access("ITEM_IPASS01_NSK"."KEYWORD_ID"="ITEM_IPASS01_K"."KEYWORD_ID")

PLAN_TABLE_OUTPUT

---

   10 - access("ITEM_IPASS01_K"."KEYWORD"='FLAG01' OR 
"ITEM_IPASS01_K"."KEYWORD"='FLAG02' OR
               "ITEM_IPASS01_K"."KEYWORD"='FLAG03' OR 
"ITEM_IPASS01_K"."KEYWORD"='FLAG04'
   11 - access("ITEM_LIST_IMP"."ITEM_ID"="ITEM_IPASS01_NSK"."ITEM_ID")
   13 - filter("ITEM_LIST_IMP"."SUBCLASS_VIEW"='Item_List')

   17 -
access("from$_subquery$_011"."KEYWORD_ID_38"="ITEM_IPASS02_K"."KEYWORD_ID")

   19 - access("ITEM_IPASS02_K"."KEYWORD"='FLAG05')
   21 - access("ITEM_IPASS02"."ITEM_ID"="ITEM_IPASS02_NSK"."ITEM_ID")
   24 - filter("ITEM_LIST_IMP"."SUBCLASS_VIEW"='Item_List')
   26 - access("SUBITEM"."GROUP_ID"(+)=146)
   27 - 
filter(ROUND("SUBSUBITEMS"."PVALUE_MANT"(+)*POWER(10.0,"SUBSUBITEMS"."PVALUE_EXP"(+)),40)<.000
               0000001 AND "SUBSUBITEMS"."SCORE"(+)>40.0)

Note: cpu costing is off

56 rows selected. Received on Wed Jul 20 2005 - 10:33:55 CDT