Indexes on temp tables

From: Ram Raman <veeeraman_at_gmail.com>
Date: Tue, 15 Jan 2008 12:07:03 -0600
Message-ID: <effc058d0801151007n3d98445etfb1ca2f43807986b@mail.gmail.com>

Hi,

Oracle: 10.2.

We have a query that has been running slow. After researching on the query I found out that that it was not using an index on one of the temporary tables involved. I used a hint to force the query to use the index, and the query's perfomance improved very well. But now I learn from the developers that the query may use any of a range of temp tables when it runs - there seem to be about 24 of them. I have the option of adding hints with the names of all indexes for all the temp tables. I tested the query in this way and it works fine. This solution works, but is there a better way.

PS. Even though they are called temporary tables, they are in permanent tablespace.

Qry:

SELECT /*+ INDEX(D PS_CM_ITEM_MTH_T7) INDEX(D PS_CM_ITEM_MTH_T4) INDEX(D PS_CM_ITEM_MTH_T7) */

   X.PROCESS_INSTANCE
 , A.BUSINESS_UNIT
 , A.INV_ITEM_ID, A.CM_BOOK, A.DT_TIMESTAMP, A.SEQ_NBR, A.CM_DT_TIMESTAMP ,

A.CM_SEQ_NBR, B.CM_SEQ_COST
 , B.COST_ELEMENT, 0, 0 , 0 , B.CM_UNIT_COST_VO , 0 , 0 FROM PS_CM_DEPLETE A, PS_CM_RMATCH_S_VW B, PS_TRANSACTION_INV C,    PS_CM_ITEM_MTH_T4 D, PS_CM_CSTACCTG X

 WHERE X.PROCESS_INSTANCE = 2981824
   AND X.REQUEST_ID = 'AB301'
   AND X.BUSINESS_UNIT = A.BUSINESS_UNIT
   AND X.CM_BOOK = A.CM_BOOK
   AND B.BUSINESS_UNIT_RECV = C.BUSINESS_UNIT_PO
   AND B.RTV_ID = C.RTV_ID
   AND B.RTV_LN_NBR = C.RTV_LN_NBR
   AND B.RTV_DISTRIB_NUM = C.RTV_DISTRIB_NUM
   AND C.BUSINESS_UNIT = A.BUSINESS_UNIT
   AND C.INV_ITEM_ID = A.INV_ITEM_ID
   AND C.DT_TIMESTAMP = A.DT_TIMESTAMP
   AND C.SEQ_NBR = A.SEQ_NBR
   AND A.POSTED_FLAG = 'N'
   AND A.CM_DUMMY_FLG ='N'
   AND D.PROCESS_INSTANCE = X.PROCESS_INSTANCE
   AND D.BUSINESS_UNIT = A.BUSINESS_UNIT
   AND D.INV_ITEM_ID = A.INV_ITEM_ID
   AND D.CM_BOOK = A.CM_BOOK

   AND (D.CM_METHOD NOT IN ('6','9') OR D.CM_COST_ELEMNT_OPT <> '1')    AND EXISTS (
 SELECT 'X'
  FROM PS_SET_CNTRL_REC
  , PS_CM_ELEMENT
 WHERE A.BUSINESS_UNIT = PS_SET_CNTRL_REC.SETCNTRLVALUE

   AND PS_SET_CNTRL_REC.RECNAME = 'CM_ELEMENT'
   AND PS_CM_ELEMENT.SETID = PS_SET_CNTRL_REC.SETID
   AND PS_CM_ELEMENT.COST_ELEMENT = B.COST_ELEMENT
   AND PS_CM_ELEMENT.COST_CATEGORY = 'MAT' )

Plan:

17:14:47 SQL> select * from table(dbms_xplan.display); more..

PLAN_TABLE_OUTPUT

---

---

| Id  | Operation                         | Name               | Rows  |
Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                    |
|       |    21 (100)|          |
|   1 |  HASH GROUP BY                    |                    |     1 |
259 |    21  (20)| 00:00:01 |
|   2 |   NESTED LOOPS                    |                    |     1 |
259 |    18  (17)| 00:00:01 |
|   3 |    NESTED LOOPS                   |                    |     1 |
206 |    17  (18)| 00:00:01 |
|   4 |     MERGE JOIN CARTESIAN          |                    |     1 |
139 |    14  (22)| 00:00:01 |
|   5 |      MERGE JOIN CARTESIAN         |                    |     1 |
78 |    11  (28)| 00:00:01 |


|*  6 |       TABLE ACCESS FULL           | PS_CM_ITEM_MTH_T4  |     1 |

38 |    10  (30)| 00:00:01 |
|   7 |       BUFFER SORT                 |                    |     1 |
40 |     1   (0)| 00:00:01 |


|*  8 |        INDEX RANGE SCAN           | PSBCM_CSTACCTG     |     1 |

40 |     1   (0)| 00:00:01 |
|   9 |      BUFFER SORT                  |                    |    93 |
5673 |    13  (24)| 00:00:01 |


|* 10 |       TABLE ACCESS FULL           | PS_CM_RMATCH_COST  |    93 |

5673 |     3   (0)| 00:00:01 |


|* 11 |     TABLE ACCESS BY INDEX ROWID   | PS_CM_DEPLETE      |     1 |

67 |     3   (0)| 00:00:01 |


|* 12 |      INDEX RANGE SCAN             | PSACM_DEPLETE      |    18

|       |     2   (0)| 00:00:01 |
|  13 |       NESTED LOOPS                |                    |     1 |
40 |     2   (0)| 00:00:01 |


|* 14 |        TABLE ACCESS BY INDEX ROWID| PS_CM_ELEMENT      |     1 |

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


|* 15 |         INDEX SKIP SCAN           | PS_CM_ELEMENT      |     1

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


|* 16 |        INDEX UNIQUE SCAN          | PSASET_CNTRL_REC   |     1 |

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


|* 17 |    TABLE ACCESS BY INDEX ROWID    | PS_TRANSACTION_INV |     1 |

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


|* 18 |     INDEX UNIQUE SCAN             | PS_TRANSACTION_INV |     1

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

Thanks.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 15 2008 - 12:07:03 CST