Indexes on temp tables

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.

Other related posts: