Search

Top 60 Oracle Blogs

Recent comments

Hinting Sub-Queries on Oracle

This posting is a purely Oracle RDBMS discussion about how to correctly apply hints to sub-queries. However, it is particularly relevant to PeopleSoft, which it makes extensive use of correlated sub-queries. The point of this story is not the particular hints that I applied, but where I placed the hints, and how I scoped them to the sub-queries.

The following SQL extract is from the delivered Global Payroll GPGB_EDI process (although I have already made some minor changes, and PS_GP_RSLT_PIN is partitioned). Notice that each sub-query joins two tables together. PS_GP_RSLT_PIN is second largest of Global Payroll result tables. PS_GP_PIN is a look-up table, and the criterion on PIN_CODE will only return a single row.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
UPDATE Table(GPGB_EDIE_TMP) X
SET X.GPGB_WK53_IND = (
SELECT %TrimSubstr(%Sql(FUNCLIB_HR_CHAR,A.CALC_RSLT_VAL),1,2)
FROM PS_GP_RSLT_PIN A
,PS_GP_PIN B

WHERE A.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID
...
AND A.PIN_NUM = B.PIN_NUM 
AND B.PIN_CODE = 'TAX VR PERIOD GBR'
AND A.SLICE_BGN_DT = (
SELECT MAX(D.SLICE_BGN_DT)
FROM PS_GP_RSLT_PIN D
WHERE D.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID
...
AND D.INSTANCE = A.INSTANCE
AND D.PIN_NUM = B.PIN_NUM)
...
)
WHERE EXISTS (
...
)
AND PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

This is part of the initial SQL execution plan. The problem is that the sub-query starts by scanning through the PS_GP_RSLT_PIN table, 4 times because there are three correlated sub-queries, and only at the very end does it look up the PIN_CODE by the PIN_NUM.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart|
---------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 65 | 113M (93)| 39:44:51 | |
| 1 | UPDATE | PS_GPGB_EDIE_TMP4 | | | | | |
|* 2 | FILTER | | | | | | |
|* 3 | TABLE ACCESS FULL | PS_GPGB_EDIE_TMP4 | 673K| 41M| 9967 (6)| 00:00:13 |
| 4 | NESTED LOOPS | | 1 | 108 | 4 (0)| 00:00:01 | |
| 5 | PARTITION RANGE SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 6 | PARTITION LIST SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
|* 7 | INDEX RANGE SCAN | PS_GP_RSLT_PIN | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 8 | SORT AGGREGATE | | 1 | 72 | | | |
| 9 | PARTITION RANGE SINGLE | | 1 | 72 | 3 (0)| 00:00:01 | KEY |
| 10 | PARTITION LIST SINGLE | | 1 | 72 | 3 (0)| 00:00:01 | KEY |
|* 11 | INDEX RANGE SCAN | PS_GP_RSLT_PIN | 1 | 72 | 3 (0)| 00:00:01 | KEY |
| 12 | SORT AGGREGATE | | 1 | 83 | | | |
| 13 | PARTITION RANGE SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 14 | PARTITION LIST SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 15 | FIRST ROW | | 1 | 83 | 3 (0)| 00:00:01 | |
|* 16 | INDEX RANGE SCAN (MIN/MAX) | PS_GP_RSLT_PIN | 1 | 83 | 3 (0)| 00:00:01 |
| 17 | SORT AGGREGATE | | 1 | 83 | | | |
| 18 | PARTITION RANGE SINGLE | | 1 | 83 | 158 (99)| 00:00:01 | KEY |
| 19 | PARTITION LIST SINGLE | | 1 | 83 | 158 (99)| 00:00:01 | KEY |
| 20 | FIRST ROW | | 1 | 83 | 158 (99)| 00:00:01 | |
|* 21 | INDEX RANGE SCAN (MIN/MAX)| PS_GP_RSLT_PIN | 1 | 83 | 158 (99)| 00:00:01 | KEY |
|* 22 | INDEX RANGE SCAN | PSAGP_PIN | 1 | 25 | 1 (0)| 00:00:01 | |
...
---------------------------------------------------------------------------------------------------------------------------

It would be much better if we started with the PS_GP_PIN table, looked up the PIN_NUM with the PIN_CODE (there is a suitable index on this column) and used the PIN_NUM value as a part of the lookup on PS_GP_RSLT_PIN (again PIN_CODE is in the unique index on that table).

It is tempting to add LEADING hints to the sub-queries, but such a hint does not work because the hint is not scoped to the sub-query and is considered to be invalid because the entire query cannot start at this point.

The only supported place in the query to add a LEADING hint would be the first query, in this case after the UPDATE keyword.

In this case, I have named the query blocks in the sub-queries with the QB_NAME hint. It is valid to put this hint into the sub-query. Then I added LEADING hints for each sub-query after the UPDATE keyword, but I specified their scope using the name of the sub-query specified in the QB_NAME hint. Each sub-query must now start with the PS_GP_PIN table.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
UPDATE /*+LEADING(@SUB1 B@SUB1) LEADING(@SUB2 B@SUB2)*/ %Table(GPGB_EDIE_TMP) X
SET X.GPGB_WK53_IND = (
SELECT /*+QB_NAME(SUB1)*/ %TrimSubstr(%Sql(FUNCLIB_HR_CHAR,A.CALC_RSLT_VAL),1,2)
FROM PS_GP_RSLT_PIN A
,PS_GP_PIN B
WHERE A.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID
...
AND A.PIN_NUM = B.PIN_NUM
AND B.PIN_CODE = 'TAX VR PERIOD GBR'
AND A.SLICE_BGN_DT = (
SELECT MAX(D.SLICE_BGN_DT)
FROM PS_GP_RSLT_PIN D
WHERE D.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID
...
AND D.INSTANCE = A.INSTANCE
AND D.PIN_NUM = B.PIN_NUM)
...
)
WHERE EXISTS (
SELECT /*+QB_NAME(SUB2)*/ 'X'
FROM PS_GP_RSLT_PIN A1
,PS_GP_PIN B1
WHERE A1.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID
...
AND A1.PIN_NUM = B1.PIN_NUM
AND B1.PIN_CODE = 'TAX VR PERIOD GBR'
...
)
AND PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

This is the new execution plan. The sub-query starts with an access of index PSAGP_PIN (which leads on PIN_CODE) on PS_GP_PIN, and then does the lookups on PS_GP_RSLT_PIN. The cost is the same, but the execution time was considerably reduced.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart|
----------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 65 | 113M (93)| 39:44:51 | |
| 1 | UPDATE | PS_GPGB_EDIE_TMP4 | | | | | |
|* 2 | FILTER | | | | | | |
|* 3 | TABLE ACCESS FULL | PS_GPGB_EDIE_TMP4 | 673K| 41M| 9967 (6)| 00:00:13 | |
| 4 | NESTED LOOPS | | 1 | 108 | 4 (0)| 00:00:01 | |
|* 5 | INDEX RANGE SCAN | PSAGP_PIN | 1 | 25 | 2 (0)| 00:00:01 | |
| 6 | PARTITION RANGE SINGLE | | 1 | 83 | 2 (0)| 00:00:01 | KEY |
| 7 | PARTITION LIST SINGLE | | 1 | 83 | 2 (0)| 00:00:01 | KEY |
|* 8 | INDEX RANGE SCAN | PS_GP_RSLT_PIN | 1 | 83 | 2 (0)| 00:00:01 | KEY |
| 9 | SORT AGGREGATE | | 1 | 72 | | | |
| 10 | PARTITION RANGE SINGLE | | 1 | 72 | 3 (0)| 00:00:01 | KEY |
| 11 | PARTITION LIST SINGLE | | 1 | 72 | 3 (0)| 00:00:01 | KEY |
|* 12 | INDEX RANGE SCAN | PS_GP_RSLT_PIN | 1 | 72 | 3 (0)| 00:00:01 | KEY |
| 13 | SORT AGGREGATE | | 1 | 83 | | | |
| 14 | PARTITION RANGE SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 15 | PARTITION LIST SINGLE | | 1 | 83 | 3 (0)| 00:00:01 | KEY |
| 16 | FIRST ROW | | 1 | 83 | 3 (0)| 00:00:01 | |
|* 17 | INDEX RANGE SCAN (MIN/MAX) | PS_GP_RSLT_PIN | 1 | 83 | 3 (0)| 00:00:01 | |
| 18 | SORT AGGREGATE | | 1 | 83 | | | |
| 19 | PARTITION RANGE SINGLE | | 1 | 83 | 158 (99)| 00:00:01 | KEY |
| 20 | PARTITION LIST SINGLE | | 1 | 83 | 158 (99)| 00:00:01 | KEY |
| 21 | FIRST ROW | | 1 | 83 | 158 (99)| 00:00:01 | |
|* 22 | INDEX RANGE SCAN (MIN/MAX)| PS_GP_RSLT_PIN | 1 | 83 | 158 (99)| 00:00:01 | KEY |
| 23 | TABLE ACCESS BY LOCAL INDEX ROWID | PS_GP_RSLT_PIN | 1 | 86 | 3 (0)| 00:00:01 | |
...
---------------------------------------------------------------------------------------------------------------------------