Indexes on temp tables
- From: "Ram Raman" <veeeraman@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 15 Jan 2008 12:07:03 -0600
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: