Oracle Performance and Engineering
Random notes on database features, code, practice, optimization and techniques.Wednesday, October 04, 2006
Do you really need a UNION - more example of bad coding!
Today I was reviewing a code for a materialized view (part of) that looked like this -
Now, look at that code carefully. All the UNION parts are bringing basically the same data (same WHERE clause etc) except that each part merges the data with a different month, there being 12 UNIONed parts for 12 months in a year.
The plan for this SQL is horrendous (actual SQL was far worse, being different UNION parts for different request types as well!) -
Look at how many times the large table KCRT_REQUEST_DETAILS is full-scanned. Each UNIONed part has FOUR full-table scans for that table and there are total of 12 UNION-ed parts. - totalling a whooping 48 full-scans of KCRT_REQUEST_DETAILS tables.
Now, let's create a "static" data table that will hold the months in a year.
Now, rewrite the SQL as -
The explain plan of which is much more friendly than before -
So, from 48 full-scan we're now back to 4! The response time, predictably, is about 15 times faster!
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'January' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD4.VISIBLE_PARAMETER17 V0,
RD4.VISIBLE_PARAMETER20 D0,
RD5.VISIBLE_PARAMETER48 V1,
RD6.VISIBLE_PARAMETER1 D1,
RD7.VISIBLE_PARAMETER26 V2,
RD7.VISIBLE_PARAMETER29 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'February' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD4.VISIBLE_PARAMETER23 V0,
RD4.VISIBLE_PARAMETER25 D0,
RD6.VISIBLE_PARAMETER4 V1,
RD6.VISIBLE_PARAMETER7 D1,
RD7.VISIBLE_PARAMETER32 V2,
RD7.VISIBLE_PARAMETER35 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'March' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD4.VISIBLE_PARAMETER28 V0,
RD4.VISIBLE_PARAMETER31 D0,
RD6.VISIBLE_PARAMETER10 V1,
RD6.VISIBLE_PARAMETER13 D1,
RD7.VISIBLE_PARAMETER38 V2,
RD7.VISIBLE_PARAMETER41 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'April' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD4.VISIBLE_PARAMETER34 V0,
RD4.VISIBLE_PARAMETER37 D0,
RD6.VISIBLE_PARAMETER16 V1,
RD6.VISIBLE_PARAMETER19 D1,
RD7.VISIBLE_PARAMETER44 V2,
RD7.VISIBLE_PARAMETER47 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'May' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD4.VISIBLE_PARAMETER40 V0,
RD4.VISIBLE_PARAMETER46 D0,
RD6.VISIBLE_PARAMETER22 V1,
RD6.VISIBLE_PARAMETER25 D1,
RD7.VISIBLE_PARAMETER50 V2,
RD8.VISIBLE_PARAMETER3 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'June' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD4.VISIBLE_PARAMETER49 V0,
RD5.VISIBLE_PARAMETER2 D0,
RD6.VISIBLE_PARAMETER28 V1,
RD6.VISIBLE_PARAMETER31 D1,
RD8.VISIBLE_PARAMETER6 V2,
RD8.VISIBLE_PARAMETER9 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'July' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD5.VISIBLE_PARAMETER5 V0,
RD5.VISIBLE_PARAMETER8 D0,
RD6.VISIBLE_PARAMETER34 V1,
RD6.VISIBLE_PARAMETER37 D1,
RD8.VISIBLE_PARAMETER12 V2,
RD8.VISIBLE_PARAMETER15 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'August' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD5.VISIBLE_PARAMETER11 V0,
RD5.VISIBLE_PARAMETER14 D0,
RD6.VISIBLE_PARAMETER40 V1,
RD6.VISIBLE_PARAMETER43 D1,
RD8.VISIBLE_PARAMETER18 V2,
RD8.VISIBLE_PARAMETER21 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'September' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD5.VISIBLE_PARAMETER17 V0,
RD5.VISIBLE_PARAMETER20 D0,
RD6.VISIBLE_PARAMETER46 V1,
RD6.VISIBLE_PARAMETER49 D1,
RD8.VISIBLE_PARAMETER24 V2,
RD8.VISIBLE_PARAMETER27 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'October' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD5.VISIBLE_PARAMETER23 V0,
RD5.VISIBLE_PARAMETER26 D0,
RD7.VISIBLE_PARAMETER2 V1,
RD7.VISIBLE_PARAMETER5 D1,
RD8.VISIBLE_PARAMETER30 V2,
RD8.VISIBLE_PARAMETER33 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'November' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD5.VISIBLE_PARAMETER29 V0,
RD5.VISIBLE_PARAMETER32 D0,
RD7.VISIBLE_PARAMETER8 V1,
RD7.VISIBLE_PARAMETER11 D1,
RD8.VISIBLE_PARAMETER36 V2,
RD8.VISIBLE_PARAMETER39 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
UNION
SELECT R.REQUEST_ID "REQUEST_ID",
'Opportunity' "PROMPT",
'December' MONTH,
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD5.VISIBLE_PARAMETER35 V0,
RD5.VISIBLE_PARAMETER38 D0,
RD7.VISIBLE_PARAMETER14 V1,
RD7.VISIBLE_PARAMETER17 D1,
RD8.VISIBLE_PARAMETER42 V2,
RD8.VISIBLE_PARAMETER45 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (select request_type_id from kcrt_request_types where request_type_name = 'MKTG - IB Request')
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
Now, look at that code carefully. All the UNION parts are bringing basically the same data (same WHERE clause etc) except that each part merges the data with a different month, there being 12 UNIONed parts for 12 months in a year.
The plan for this SQL is horrendous (actual SQL was far worse, being different UNION parts for different request types as well!) -
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 76656 | 37M| 19864 |
| 1 | SORT UNIQUE | | 76656 | 37M| 19864 |
| 2 | UNION-ALL | | | | |
| 3 | HASH JOIN | | 6388 | 3493K| 1324 |
| 4 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 5 | HASH JOIN | | 5750 | 3099K| 1279 |
| 6 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 7 | HASH JOIN | | 5175 | 2749K| 1236 |
| 8 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 661K| 177 |
| 9 | HASH JOIN | | 4657 | 2392K| 1029 |
| 10 | HASH JOIN | | 4192 | 1408K| 760 |
| 11 | HASH JOIN | | 3773 | 854K| 544 |
| 12 | HASH JOIN | | 3395 | 397K| 348 |
| 13 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 14 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 15 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 16 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 17 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 18 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 19 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 20 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 21 | HASH JOIN | | 6388 | 2838K| 1095 |
| 22 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 23 | HASH JOIN | | 5750 | 2510K| 1053 |
| 24 | HASH JOIN | | 5175 | 1339K| 788 |
| 25 | HASH JOIN | | 4657 | 704K| 579 |
| 26 | HASH JOIN | | 4192 | 176K| 392 |
| 27 | HASH JOIN | | 3773 | 98098 | 212 |
| 28 | HASH JOIN | | 3395 | 61110 | 189 |
| 29 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 30 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 31 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 32 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 33 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 34 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 35 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 625K| 177 |
| 36 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 37 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 38 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 39 | HASH JOIN | | 6388 | 3892K| 1203 |
| 40 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 41 | HASH JOIN | | 5750 | 3458K| 1156 |
| 42 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 43 | HASH JOIN | | 5175 | 3072K| 1111 |
| 44 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 45 | HASH JOIN | | 4657 | 2728K| 1068 |
| 46 | HASH JOIN | | 4192 | 1711K| 780 |
| 47 | HASH JOIN | | 3773 | 869K| 545 |
| 48 | HASH JOIN | | 3395 | 397K| 348 |
| 49 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 50 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 51 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 52 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 53 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 54 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4265K| 177 |
| 55 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 56 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 57 | HASH JOIN | | 6388 | 3867K| 1199 |
| 58 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 59 | HASH JOIN | | 5750 | 3436K| 1152 |
| 60 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 61 | HASH JOIN | | 5175 | 3052K| 1107 |
| 62 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 63 | HASH JOIN | | 4657 | 2710K| 1064 |
| 64 | HASH JOIN | | 4192 | 1694K| 778 |
| 65 | HASH JOIN | | 3773 | 854K| 544 |
| 66 | HASH JOIN | | 3395 | 397K| 348 |
| 67 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 68 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 69 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 70 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 71 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 72 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 73 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 74 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 75 | HASH JOIN | | 6388 | 3880K| 1357 |
| 76 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 77 | HASH JOIN | | 5750 | 3447K| 1310 |
| 78 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 367K| 177 |
| 79 | HASH JOIN | | 5175 | 3052K| 1107 |
| 80 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 81 | HASH JOIN | | 4657 | 2710K| 1064 |
| 82 | HASH JOIN | | 4192 | 1694K| 778 |
| 83 | HASH JOIN | | 3773 | 854K| 544 |
| 84 | HASH JOIN | | 3395 | 397K| 348 |
| 85 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 86 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 87 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 88 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 89 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 90 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 91 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 92 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 93 | HASH JOIN | | 6388 | 3337K| 1296 |
| 94 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 588K| 177 |
| 95 | HASH JOIN | | 5750 | 2914K| 1087 |
| 96 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 97 | HASH JOIN | | 5175 | 2582K| 1045 |
| 98 | HASH JOIN | | 4657 | 1496K| 771 |
| 99 | HASH JOIN | | 4192 | 601K| 553 |
| 100 | HASH JOIN | | 3773 | 136K| 368 |
| 101 | HASH JOIN | | 3395 | 61110 | 189 |
| 102 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 103 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 104 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 105 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 106 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 107 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 698K| 177 |
| 108 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 109 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 110 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 111 | HASH JOIN | | 6388 | 2857K| 1098 |
| 112 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 113 | HASH JOIN | | 5750 | 2526K| 1056 |
| 114 | HASH JOIN | | 5175 | 1354K| 790 |
| 115 | HASH JOIN | | 4657 | 718K| 580 |
| 116 | HASH JOIN | | 4192 | 188K| 392 |
| 117 | HASH JOIN | | 3773 | 98098 | 212 |
| 118 | HASH JOIN | | 3395 | 61110 | 189 |
| 119 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 120 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 121 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 122 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 123 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 124 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 125 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 735K| 177 |
| 126 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 127 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 128 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 129 | HASH JOIN | | 6388 | 3268K| 1132 |
| 130 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 131 | HASH JOIN | | 5750 | 2897K| 1088 |
| 132 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 133 | HASH JOIN | | 5175 | 2567K| 1046 |
| 134 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 588K| 177 |
| 135 | HASH JOIN | | 4657 | 2237K| 842 |
| 136 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 137 | HASH JOIN | | 4192 | 1981K| 803 |
| 138 | HASH JOIN | | 3773 | 1112K| 553 |
| 139 | HASH JOIN | | 3395 | 397K| 348 |
| 140 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 141 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 142 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 143 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 144 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 145 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 146 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 147 | HASH JOIN | | 6388 | 3431K| 1158 |
| 148 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 149 | HASH JOIN | | 5750 | 3043K| 1113 |
| 150 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 151 | HASH JOIN | | 5175 | 2698K| 1070 |
| 152 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 153 | HASH JOIN | | 4657 | 2392K| 1029 |
| 154 | HASH JOIN | | 4192 | 1408K| 760 |
| 155 | HASH JOIN | | 3773 | 854K| 544 |
| 156 | HASH JOIN | | 3395 | 397K| 348 |
| 157 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 158 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 159 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 160 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 161 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 162 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 163 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 164 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 165 | HASH JOIN | | 6388 | 2850K| 1097 |
| 166 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 167 | HASH JOIN | | 5750 | 2521K| 1055 |
| 168 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 169 | HASH JOIN | | 5175 | 2228K| 1015 |
| 170 | HASH JOIN | | 4657 | 1505K| 772 |
| 171 | HASH JOIN | | 4192 | 609K| 553 |
| 172 | HASH JOIN | | 3773 | 136K| 368 |
| 173 | HASH JOIN | | 3395 | 61110 | 189 |
| 174 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 175 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 176 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 177 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 178 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 179 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 698K| 177 |
| 180 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 181 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 182 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 183 | HASH JOIN | | 6388 | 2252K| 1038 |
| 184 | HASH JOIN | | 5750 | 1005K| 793 |
| 185 | HASH JOIN | | 5175 | 348K| 598 |
| 186 | HASH JOIN | | 4657 | 209K| 416 |
| 187 | HASH JOIN | | 4192 | 155K| 392 |
| 188 | HASH JOIN | | 3773 | 110K| 368 |
| 189 | HASH JOIN | | 3395 | 61110 | 189 |
| 190 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 191 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 192 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 193 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 194 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 195 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 441K| 177 |
| 196 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 197 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 198 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 845K| 177 |
| 199 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 200 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 201 | HASH JOIN | | 6388 | 2807K| 1091 |
| 202 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 203 | HASH JOIN | | 5750 | 2481K| 1049 |
| 204 | HASH JOIN | | 5175 | 1313K| 786 |
| 205 | HASH JOIN | | 4657 | 673K| 578 |
| 206 | HASH JOIN | | 4192 | 155K| 392 |
| 207 | HASH JOIN | | 3773 | 110K| 368 |
| 208 | HASH JOIN | | 3395 | 61110 | 189 |
| 209 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 210 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 211 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 212 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 213 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 214 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 441K| 177 |
| 215 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 216 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
| 217 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 218 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
---------------------------------------------------------------------------------------------------
Look at how many times the large table KCRT_REQUEST_DETAILS is full-scanned. Each UNIONed part has FOUR full-table scans for that table and there are total of 12 UNION-ed parts. - totalling a whooping 48 full-scans of KCRT_REQUEST_DETAILS tables.
Now, let's create a "static" data table that will hold the months in a year.
CREATE TABLE stat_months(month_name VARCHAR2(20));
INSERT INTO stat_months (SELECT DISTINCT(TO_CHAR(creation_date,'MONTH')) FROM kcrt_requests);
-- check if ITG_MONTHS have 12 months. If not, manually insert them.
Now, rewrite the SQL as -
SELECT R.REQUEST_ID "REQUEST_ID",
'Proposal' "PROMPT",
month_name "MONTH",
trim(RD3.parameter23) YEAR0,
trim(RD3.parameter23+1) YEAR1,
trim(RD3.parameter23+2) YEAR2,
RD4.VISIBLE_PARAMETER17 V0,
RD4.VISIBLE_PARAMETER20 D0,
RD5.VISIBLE_PARAMETER48 V1,
RD6.VISIBLE_PARAMETER1 D1,
RD7.VISIBLE_PARAMETER26 V2,
RD7.VISIBLE_PARAMETER29 D2
FROM KCRT_REQUEST_DETAILS RD3, KCRT_REQUEST_DETAILS RD4 , KCRT_REQUEST_DETAILS RD5 , KCRT_REQUEST_DETAILS RD6 , KCRT_REQUEST_DETAILS RD7 , KCRT_REQUEST_DETAILS RD8 , KCRT_REQUEST_DETAILS RD9 ,
STAT_MONTHS itg_m,
KCRT_REQUESTS R
WHERE R.REQUEST_TYPE_ID = (SELECT request_type_id FROM kcrt_request_types WHERE request_type_name IN ('MKTG - IB Request'))
AND R.REQUEST_ID = RD4.REQUEST_ID AND RD4.BATCH_NUMBER = 4
AND R.REQUEST_ID = RD5.REQUEST_ID AND RD5.BATCH_NUMBER = 5
AND R.REQUEST_ID = RD6.REQUEST_ID AND RD6.BATCH_NUMBER = 6
AND R.REQUEST_ID = RD7.REQUEST_ID AND RD7.BATCH_NUMBER = 7
AND R.REQUEST_ID = RD8.REQUEST_ID AND RD8.BATCH_NUMBER = 8
AND R.REQUEST_ID = RD9.REQUEST_ID AND RD9.BATCH_NUMBER = 9
AND R.REQUEST_ID = RD3.REQUEST_ID AND RD3.BATCH_NUMBER = 3
The explain plan of which is much more friendly than before -
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 76661 | 41M| 4227 |
| 1 | HASH JOIN | | 76661 | 41M| 4227 |
| 2 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 6691K| 177 |
| 3 | HASH JOIN | | 68996 | 25M| 3520 |
| 4 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 5 | HASH JOIN | | 62097 | 16M| 3001 |
| 6 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4117K| 177 |
| 7 | HASH JOIN | | 55888 | 8896K| 2604 |
| 8 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 9 | HASH JOIN | | 50300 | 7613K| 2531 |
| 10 | INDEX FAST FULL SCAN | KCRT_REQUEST_DETAILS_N1 | 37650 | 294K| 21 |
| 11 | HASH JOIN | | 45271 | 6498K| 2464 |
| 12 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 661K| 177 |
| 13 | HASH JOIN | | 40744 | 5132K| 2230 |
| 14 | MERGE JOIN CARTESIAN | | 36670 | 680K| 2006 |
| 15 | TABLE ACCESS FULL | STAT_MONTHS | 12 | 108 | 2 |
| 16 | BUFFER SORT | | 3056 | 30560 | 2004 |
| 17 | TABLE ACCESS BY INDEX ROWID | KCRT_REQUESTS | 3056 | 30560 | 167 |
| 18 | INDEX RANGE SCAN | KCRT_REQUESTS_N1 | 3056 | | 7 |
| 19 | TABLE ACCESS BY INDEX ROWID| KCRT_REQUEST_TYPES | 1 | 20 | 1 |
| 20 | INDEX UNIQUE SCAN | KCRT_REQUEST_TYPES_U2 | 1 | | |
| 21 | TABLE ACCESS FULL | KCRT_REQUEST_DETAILS | 37650 | 4044K| 177 |
---------------------------------------------------------------------------------------------------
So, from 48 full-scan we're now back to 4! The response time, predictably, is about 15 times faster!
Archives
January 2005 February 2005 March 2005 April 2005 May 2005 March 2006 April 2006 August 2006 September 2006 October 2006 January 2007 May 2008
