Re: What index do I need for this query ?

From: <krislioe_at_gmail.com>
Date: Tue, 24 Mar 2009 18:19:06 -0700 (PDT)
Message-ID: <8e42cf6e-0c37-4388-8081-26b62504b20b_at_v1g2000prd.googlegroups.com>

Hi Mr,

Below is the plan.

Thank you,
xtanto

Plan hash value: 3690974330

---

| Id  | Operation                               |
Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)|
Time     |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT
|                            |    12 |  5916 |       |    81   (5)|

00:00:01 |
| 1 | NESTED LOOPS

|                            |    12 |  5916 |       |    81   (5)|

00:00:01 |
|* 2 | HASH JOIN

|                            |    12 |  5484 |       |    69   (6)|

00:00:01 |
|* 3 | HASH JOIN

|                            |    12 |  4620 |       |    65   (5)|
00:00:01 |
|   4 |     NESTED LOOPS
|                            |    12 |  4260 |       |    62   (5)|
00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID        |
LOCATION_GROUPS            |     1 |   117 |       |     1   (0)|
00:00:01 |


|*  6 |       INDEX UNIQUE SCAN                 |

LOCATION_GROUPS_PK         |     1 |       |       |     0   (0)|
00:00:01 |
|   7 |      VIEW
|                            |    12 |  2856 |       |    61   (5)|
00:00:01 |
|   8 |       TEMP TABLE TRANSFORMATION
|                            |       |       |       |
|          |
|   9 |        LOAD AS SELECT                   |
LOCATIONS                  |       |       |       |
|          |


|* 10 |         TABLE ACCESS BY INDEX ROWID     |

INVOICE_H                  |     1 |    92 |       |    11  (19)|
00:00:01 |
|  11 |          BITMAP CONVERSION TO ROWIDS
|                            |       |       |       |
|          |
|  12 |           BITMAP AND
|                            |       |       |       |
|          |
|  13 |            BITMAP CONVERSION FROM
ROWIDS|                            |       |       |
|            |          |
|  14 |             SORT ORDER BY
|                            |       |       |       |
|          |


|* 15 |              INDEX RANGE SCAN           |

INVOICE_H_IDX3             |   679 |       |       |     4   (0)|
00:00:01 |
|  16 |            BITMAP CONVERSION FROM
ROWIDS|                            |       |       |
|            |          |
|  17 |             SORT ORDER BY
|                            |       |       |       |
|          |


|* 18 |              INDEX RANGE SCAN           |

INVOICE_H_IDX2             |   679 |       |       |     5   (0)|
00:00:01 |
|  19 |        LOAD AS SELECT                   |
LOCATIONS                  |       |       |       |
|          |
|  20 |         HASH GROUP BY
|                            | 12585 |   319K|  1000K|   126   (3)|
00:00:02 |
|  21 |          TABLE ACCESS FULL              |
INVOICE_B                  | 12585 |   319K|       |    29   (4)|
00:00:01 |
|  22 |        UNION-ALL
|                            |       |       |       |
|          |


|* 23 |         HASH JOIN OUTER

|                            |     8 |  2240 |       |    27   (8)|
00:00:01 |


|* 24 |          HASH JOIN

|                            |     8 |  1760 |       |    14   (8)|
00:00:01 |
|  25 |           TABLE ACCESS BY INDEX ROWID   |
INVOICE_D                  |     8 |   328 |       |     4   (0)|
00:00:01 |
|  26 |            NESTED LOOPS
|                            |     8 |  1440 |       |     6   (0)|
00:00:01 |
|  27 |             VIEW
|                            |     1 |   139 |       |     2   (0)|
00:00:01 |
|  28 |              TABLE ACCESS FULL          |
SYS_TEMP_0FD9D66E6_463BB9B |     1 |    92 |       |     2   (0)|
00:00:01 |


|* 29 |             INDEX RANGE SCAN            |

INVOICE_D_FKH              |     8 |       |       |     2   (0)|
00:00:01 |
|  30 |           TABLE ACCESS FULL             |
PRODUCTS                   |   997 | 39880 |       |     7   (0)|
00:00:01 |
|  31 |          VIEW
|                            | 12585 |   737K|       |    12   (0)|
00:00:01 |
|  32 |           TABLE ACCESS FULL             |
SYS_TEMP_0FD9D66E7_463BB9B | 12585 |   319K|       |    12   (0)|
00:00:01 |


|* 33 |         FILTER

|                            |       |       |       |
|          |
|  34 |          NESTED LOOPS OUTER
|                            |     3 |   771 |       |    30   (4)|
00:00:01 |
|  35 |           NESTED LOOPS
|                            |     3 |   717 |       |    18   (6)|
00:00:01 |


|* 36 |            HASH JOIN

|                            |     3 |   597 |       |    15   (7)|
00:00:01 |
|  37 |             VIEW
|                            |     1 |   139 |       |     2   (0)|
00:00:01 |
|  38 |              TABLE ACCESS FULL          |
SYS_TEMP_0FD9D66E6_463BB9B |     1 |    92 |       |     2   (0)|
00:00:01 |
|  39 |             VIEW
|                            | 12585 |   737K|       |    12   (0)|
00:00:01 |
|  40 |              TABLE ACCESS FULL          |
SYS_TEMP_0FD9D66E7_463BB9B | 12585 |   319K|       |    12   (0)|
00:00:01 |
|  41 |            TABLE ACCESS BY INDEX ROWID  |
PRODUCTS                   |     1 |    40 |       |     1   (0)|
00:00:01 |


|* 42 |             INDEX UNIQUE SCAN           |

PROD_PK1                   |     1 |       |       |     0   (0)|
00:00:01 |


|* 43 |           TABLE ACCESS BY INDEX ROWID   |

INVOICE_D                  |     1 |    18 |       |     4   (0)|
00:00:01 |


|* 44 |            INDEX RANGE SCAN             |

INVOICE_D_FKH              |     8 |       |       |     2   (0)|
00:00:01 |


|* 45 |         FILTER

|                            |       |       |       |
|          |
|  46 |          NESTED LOOPS OUTER
|                            |     1 |   116 |       |     4   (0)|
00:00:01 |
|  47 |           NESTED LOOPS
|                            |     1 |    92 |       |     3   (0)|
00:00:01 |


|* 48 |            TABLE ACCESS BY INDEX ROWID  |

RMEMO_H                    |     1 |    75 |       |     2   (0)|
00:00:01 |


|* 49 |             INDEX RANGE SCAN            |

RMEMO_H_IDX                |     1 |       |       |     1   (0)|
00:00:01 |
|  50 |            TABLE ACCESS BY INDEX ROWID  |
RMEMO_D                    |     1 |    17 |       |     1   (0)|
00:00:01 |


|* 51 |             INDEX RANGE SCAN            |

RMEMO_D_FKH                |     1 |       |       |     0   (0)|
00:00:01 |
|  52 |           TABLE ACCESS BY INDEX ROWID   |
INEXP                      |     1 |    24 |       |     1   (0)|
00:00:01 |


|* 53 |            INDEX UNIQUE SCAN            |

INEXP_PK                   |     1 |       |       |     0   (0)|
00:00:01 |
|  54 |     TABLE ACCESS FULL                   |
LOCATION_GROUPS            |    21 |   630 |       |     3   (0)|
00:00:01 |
|  55 |    TABLE ACCESS FULL                    |
LOCATIONS                  |    24 |  1728 |       |     3   (0)|
00:00:01 |
|  56 |   TABLE ACCESS BY INDEX ROWID           |
CUSTOMERS                  |     1 |    36 |       |     1   (0)|
00:00:01 |


|* 57 |    INDEX UNIQUE SCAN                    |

CUSTOMERS_PK               |     1 |       |       |     0   (0)|

00:00:01 |

---

Predicate Information (identified by operation id):

---

   2 - access("LOC"."LOC_CODE"="T"."LOC_CODE")
   3 - access("LG"."LOCGRP_CODE"="T"."LOCGRP_CODE")
   6 - access("LG1"."LOCGRP_CODE"='01')
  10 - filter("INVH"."CUST_CODE">=:CUSTFR AND
"INVH"."CUST_CODE"<=:CUSTTO AND
              "INVH"."TAXNO">=TRIM(SUBSTR(:TAXNOFR,3)) AND

"INVH"."TAXNO"<=TRIM(SUBSTR(:TAXNOTO,3)) AND "INVH"."TAXCODE"<>'GAB' AND
              NVL("INVH"."TAXNO",' ')<>' ' AND "INVH"."REGS_CODE">=:REGSFR AND "INVH"."REGS_CODE"<=:REGSTO)

  15 - access("INVH"."TAXDATE">=TO_DATE(:TAXDATEFR,'DD-MM-YYYY') AND
              "INVH"."TAXDATE"<=TO_DATE(:TAXDATETO,'DD-MM-YYYY'))
  18 - access("INVH"."INV_NO">=:INVOICENUMBER AND
"INVH"."INV_NO"<=:INVOICENUMBERUPTO)
  23 - access("SOB"."UOM"(+)="SOD"."UOM" AND "SOB"."PRODUCT_B"(+)
="SOD"."PRODUCT" AND
              "SOB"."INVH_ID"(+)="SOD"."INV_ID_HDR")
  24 - access("PRD"."PRODUCT"="SOD"."PRODUCT")
  29 - access("INVH"."INV_ID"="SOD"."INV_ID_HDR")
  33 - filter("SOD"."PRODUCT" IS NULL)
  36 - access("INVH"."INV_ID"="SOB"."INVH_ID")

  42 - access("PRD"."PRODUCT"="SOB"."PRODUCT_B")   43 - filter("SOB"."UOM"="SOD"."UOM"(+) AND "SOD"."PRODUCT"(+) ="SOB"."PRODUCT_B")
  44 - access("SOD"."INV_ID_HDR"(+)="SOB"."INVH_ID")

  45 - filter(:REGSFR<=:REGSTO AND :CUSTFR<=:CUSTTO AND TO_DATE
(:TAXDATEFR,'DD-MM-YYYY')<=TO_DATE(:TAXDATETO,'DD-MM-YY
              YY') AND TRIM(SUBSTR(:TAXNOFR,3))<=TRIM(SUBSTR(:TAXNOTO,

3)) AND :INVOICENUMBER<=:INVOICENUMBERUPTO)   48 - filter("H"."BILLING_CODE">=:CUSTFR AND "H"."BILLING_CODE"<=:CUSTTO AND "H"."REGS_CODE">=:REGSFR AND

              "H"."REGS_CODE"<=:REGSTO AND "H"."TAXNO">=TRIM(SUBSTR
(:TAXNOFR,3)) AND "H"."TAXNO"<=TRIM(SUBSTR(:TAXNOTO,3)) AND
              "H"."TAXDATE">=TO_DATE(:TAXDATEFR,'DD-MM-YYYY') AND
"H"."TAXDATE"<=TO_DATE(:TAXDATETO,'DD-MM-YYYY') AND
              NVL("H"."TAXNO",' ')<>' ')

  49 - access("H"."TAXTYPE"=1 AND "DOC_CODE"||' '||"RM_NO">=:INVOICENUMBER AND "DOC_CODE"||'               '||"RM_NO"<=:INVOICENUMBERUPTO)

  51 - access("H"."RMH_ID"="D"."RMH_ID")
  53 - access("I"."IE_CODE"(+)="D"."IE_CODE")
  57 - access("C"."CUST_CODE"="T"."CUST_CODE" AND

"C"."LOC_CODE"="T"."LOC_CODE") Received on Tue Mar 24 2009 - 20:19:06 CDT