| < Day Day Up > |
|
There are some special cases. The ones of interest for discussion involve concatenation, IN list operators, and the UNION set operator.
Concatenation is used when OR separated conditions do not occur on the same column. The following query uses a hint to force concatenation, making the cost of the query slightly higher. The cost is higher because the PeriodSum table is small and a full table scan is quicker as can be seen in the second example.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT /*+ USE_CONCAT */ * FROM periodsum WHERE year = 2001 OR period = 10 OR coa# = '60001'; Query Cost Rows Bytes Pos -------------------------- ---- ---- ------ --- 1. SELECT STATEMENT on 6 11 165 0 2. CONCATENATION on 1 3. TABLE ACCESS BY INDEX ROWID on PE 2 1 15 1 4. INDEX RANGE SCAN on XFK_PS_COA 1 1 1 3. TABLE ACCESS FULL on PERIODSUM 2 1 15 2 3. TABLE ACCESS BY INDEX ROWID on PE 2 1 15 3 4. INDEX RANGE SCAN on XPK_PERIODSU 1 1 1 EXPLAIN PLAN SET statement_id='TEST' FOR SELECT * FROM periodsum WHERE year = 2001 OR period = 10 OR coa# = '60001'; Query Cost Rows Bytes Pos -------------------------- ---- ---- ------ --- 1. SELECT STATEMENT on 2 31 465 0 2. TABLE ACCESS FULL on PERIODSUM 2 31 465 1
Concatenation only becomes a performance advantage when row sets are extremely large. The next two examples use a very large query where the cost is decreased substantially by application of the USE_CONCAT hint. In this case the Optimizer fails to find the lowest cost option. However, timing tests executed on these two queries make the query with the hint slower so the Optimizer does not fail. Timing is shown after the queries. On the contrary, the query plan cost estimate fails because the cost values are spurious.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT * FROM transactions t JOIN transactionsline tl USING(transaction_id) JOIN stockmovement sm USING(stockmovement_id) JOIN stock USING(stock_id) WHERE t.type = 'S' OR tl.amount < 100 OR sm.qty = 0; Query Cost Rows Bytes Pos -------------------------- ---- ---- ------ --- 1. SELECT STATEMENT on 71985 1020650 ######### 0 2. HASH JOIN on 71985 1020650 ######### 1 3. TABLE ACCESS FULL on STOCK 2 118 27612 1 3. MERGE JOIN on 67130 1020650 76548750 2 4. SORT JOIN on 60057 1027251 40062789 1 5. MERGE JOIN on 25616 1027251 40062789 1 6. SORT JOIN on 6619 1043460 23999580 1 7. TABLE ACCESS FULL on STOCKMOV 6619 1043460 23999580 1 6. SORT JOIN on 18997 1027251 16436016 2 7. TABLE ACCESS FULL on TRANSACT 800 1027251 16436016 1 4. FILTER on 2 5. SORT JOIN on 1 6. TABLE ACCESS FULL on TRANSACTI 390 242584 8733024 1 EXPLAIN PLAN SET statement_id='TEST' FOR SELECT /*+ USE_CONCAT */ * FROM transactions t JOIN transactionsline tl USING(transaction_id) JOIN stockmovement sm USING(stockmovement_id) JOIN stock USING(stock_id) WHERE t.type = 'S' OR tl.amount < 100 OR sm.qty = 0; Query Cost Rows Bytes Pos -------------------------- ---- ---- ------ --- 1. SELECT STATEMENT on 20861 104406 32261454 0 2. CONCATENATION on 1 3. NESTED LOOPS on 6885 51363 3852225 1 4. HASH JOIN on 7134 51363 15871167 1 5. TABLE ACCESS FULL on STOCK 2 118 27612 1 5. MERGE JOIN on 2587 51363 2003157 2 6. TABLE ACCESS BY INDEX ROWID on 826 51363 821808 1 7. INDEX FULL SCAN on XFK_TL_STO 26 1027251 1 6. SORT JOIN on 1761 52173 1199979 2 7. TABLE ACCESS FULL on STOCKMOV 1032 52173 1199979 1 4. TABLE ACCESS BY INDEX ROWID on T 390 21292 4366512 2 5. INDEX UNIQUE SCAN on XPK_TRANSA 2 1 3. HASH JOIN on 7134 51363 15871167 2 4. TABLE ACCESS FULL on STOCK 2 118 27612 1 4. MERGE JOIN on 6885 51363 3852225 2 5. SORT JOIN on 3646 51363 2003157 1 6. MERGE JOIN on 2587 51363 2003157 1 7. SORT JOIN on 826 51363 821808 1 8. TABLE ACCESS FULL on TRANSAC 826 51363 821808 1 7. SORT JOIN on 1761 52173 1199979 2 8. TABLE ACCESS FULL on STOCKMO 1032 52173 1199979 1 5. SORT JOIN on 3239 121292 4366512 2 6. TABLE ACCESS FULL on TRANSACTI 390 121292 4366512 1 3. HASH JOIN on 7134 51363 15871167 3 4. TABLE ACCESS FULL on STOCK 2 118 27612 1 4. MERGE JOIN on 6885 51363 3852225 2 5. SORT JOIN on 3646 51363 2003157 1 6. MERGE JOIN on 2587 51363 2003157 1 7. SORT JOIN on 826 51363 821808 1 8. TABLE ACCESS FULL on TRANSAC 826 51363 821808 1 7. SORT JOIN on 1761 52173 1199979 2 8. TABLE ACCESS FULL on STOCKMO 1032 52173 1199979 1 5. SORT JOIN on 3239 121292 4366512 2 6. TABLE ACCESS FULL on TRANSACTI 390 121292 4366512 1
These are the timing tests.
SQL> SELECT COUNT(*) FROM( 2 SELECT * FROM transactions t 3 JOIN transactionsline tl USING(transaction_id) 4 JOIN stockmovement sm USING(stockmovement_id) 5 JOIN stock USING(stock_id) 6 WHERE t.type = 'S' OR tl.amount < 100 OR sm.qty = 0); COUNT(*) ----------- 4989625 Elapsed: 00:22:08.08 SQL> SELECT COUNT(*) FROM( 2 SELECT /*+ USE_CONCAT */ * FROM transactions t 3 JOIN transactionsline tl USING(transaction_id) 4 JOIN stockmovement sm USING(stockmovement_id) 5 JOIN stock USING(stock_id) 6 WHERE t.type = 'S' OR tl.amount < 100 OR sm.qty = 0); COUNT(*) ----------- 4989625 Elapsed: 00:28:23.09
Concatenation is used to merge OR conditions on different columns. The IN List operator is used to merge conditions on the same column as shown in the following query.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT * FROM coa NATURAL JOIN generalledger WHERE coa.type = 'A' OR coa.type = 'L' OR coa.type = 'E' OR coa.type = 'I'; Query Cost Rows Bytes Pos -------------------------- ---- ------ ------- --- 1. SELECT STATEMENT on 1642 1068929 50239663 0 2. HASH JOIN on 1642 1068929 50239663 1 3. INLIST ITERATOR on 1 4. TABLE ACCESS BY INDEX ROWID on C 2 55 1320 1 5. INDEX RANGE SCAN on XFK_COA_TYP 1 55 1 3. TABLE ACCESS FULL on GENERALLEDGE 1128 1068929 24585367 2
The following query is a variation on the previous query, forcing concatenation by using a hint. It is much higher in cost.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT /*+ USE_CONCAT */ * FROM coa NATURAL JOIN generalledger WHERE coa.type = 'A' OR coa.type = 'L' OR coa.type = 'E' OR coa.type = 'I';
Each of four joins for COA.TYPE column values are concatenated together.
Query Cost Rows Bytes Pos -------------------------- ---- ------ --------- --- 1. SELECT STATEMENT on 6568 4199364 ######### 0 2. CONCATENATION on 1 3. HASH JOIN on 1642 1049841 49342527 1 4. TABLE ACCESS BY INDEX ROWID on C 2 14 336 1 5. INDEX RANGE SCAN on XFK_COA_TYP 1 14 1 4. TABLE ACCESS FULL on GENERALLEDG 1128 1068929 24585367 2 3. HASH JOIN on 1642 1049841 49342527 2 4. TABLE ACCESS BY INDEX ROWID on C 2 14 336 1 5. INDEX RANGE SCAN on XFK_COA_TYP 1 14 1 4. TABLE ACCESS FULL on GENERALLEDG 1128 1068929 24585367 2 3. HASH JOIN on 1642 1049841 49342527 3 4. TABLE ACCESS BY INDEX ROWID on C 2 14 336 1 5. INDEX RANGE SCAN on XFK_COA_TYP 1 14 1 4. TABLE ACCESS FULL on GENERALLEDG 1128 1068929 24585367 2 3. HASH JOIN on 1642 1049841 49342527 4 4. TABLE ACCESS BY INDEX ROWID on C 2 14 336 1 5. INDEX RANGE SCAN on XFK_COA_TYP 1 14 1 4. TABLE ACCESS FULL on GENERALLEDG 1128 1068929 24585367 2
The previous query plan shows a concatenation of four separate joins. This leads us to the UNION set operator.
The following UNION set operator concatenated query is the same query as the previous query, which used the USE_CONCAT hint. Concatenation is faster than using UNION.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT * FROM coa NATURAL JOIN generalledger WHERE coa.type = 'A' UNION SELECT * FROM coa NATURAL JOIN generalledger WHERE coa.type = 'L' UNION SELECT * FROM coa NATURAL JOIN generalledger WHERE coa.type = 'E' UNION SELECT * FROM coa NATURAL JOIN generalledger WHERE coa.type = 'I';
Note the enormously higher cost as a result of using UNION rather than concatenation.
Query Cost Rows Bytes Pos -------------------------- ------ ------- --------- --- 1. SELECT STATEMENT on 171272 4199364 ######### 0 2. SORT UNIQUE on 171272 4199364 ######### 1 3. UNION-ALL on 1 4. HASH JOIN on 1642 1049841 49342527 1 5. TABLE ACCESS BY INDEX ROWID on 2 14 336 1 6. INDEX RANGE SCAN on XFK_COA_TY 1 14 1 5. TABLE ACCESS FULL on GENERALLED 1128 1068929 24585367 2 4. HASH JOIN on 1642 1049841 49342527 2 5. TABLE ACCESS BY INDEX ROWID on 2 14 336 1 6. INDEX RANGE SCAN on XFK_COA_TY 1 14 1 5. TABLE ACCESS FULL on GENERALLED 1128 1068929 24585367 2 4. HASH JOIN on 1642 1049841 49342527 3 5. TABLE ACCESS BY INDEX ROWID on 2 14 336 1 6. INDEX RANGE SCAN on XFK_COA_TY 1 14 1 5. TABLE ACCESS FULL on GENERALLED 1128 1068929 24585367 2 4. HASH JOIN on 1642 1049841 49342527 4 5. TABLE ACCESS BY INDEX ROWID on 2 14 336 1 6. INDEX RANGE SCAN on XFK_COA_TY 1 14 1 5. TABLE ACCESS FULL on GENERALLED 1128 1068929 24585367 2
UNION is useful in a situation such as the following query where ranges of values are split and displayed, each with a different concatenated string. Using UNION generally creates very poorly performing SQL code.
SELECT 'OUT OF STOCK: '||text FROM stock WHERE qtyonhand <=0 UNION SELECT 'UNDER STOCKED: ' ||text FROM stock WHERE qtyonhand BETWEEN 1 AND min-1 UNION SELECT 'STOCKED: ' ||text FROM stock WHERE qtyonhand BETWEEN min AND max UNION SELECT 'OVER STOCKED: ' ||text FROM stock WHERE qtyonhand > max; 'OUTOFSTOCK:'||TEXT ------------------- OUT OF STOCK: iRock MP3 Digital Audio Player by First International OVER STOCKED: Compaq Presario 6016US Minitower OVER STOCKED: Epson Perfection 2450 Photo Color Scanner OVER STOCKED: Internet Design Shop XL by Boomerang Software OVER STOCKED: Memorex 24X CD-R Media,700MB/80Min, Spindle, 30 Pack OVER STOCKED: Palm m505 Handheld OVER STOCKED: Sonnet Technologies HARMONi G3 Processor Upgrade/FireWire STOCKED: Corex CardScan Executive (600c/V6) Business Card Scanner STOCKED: Epson Perfection 1250 Color Flatbed Scanner STOCKED: Epson Perfection 1650 Color Flatbed Scanner STOCKED: FrontPage 2002 by Microsoft STOCKED: Hewlett-Packard Photo Scanner 1000 Color Scanner STOCKED: Imation 32x Neon CD-R 700 MB/80 Min 50-pk Spindle STOCKED: Logitech Cordless Freedom Optical Keyboard/Mouse STOCKED: Logitech Internet Navigator Keyboard STOCKED: Memorex 4.7GB DVD+R Media, 3-pack STOCKED: TDK DVD-R Media, 4.7GB, 10 Pack UNDER STOCKED: Apple Studio 17-inch TFT LCD Monitor UNDER STOCKED: Envision Peripherals EN-5100 15-inch LCD Monitor UNDER STOCKED: Hewlett-Packard ScanJet 7400c Flatbed Scanner
The MINUS and INTERSECT operators behave similarly to that of UNION and also have very high cost. Avoid using UNION, UNION ALL, MINUS, and INTERSECT if possible. First let's show the MINUS operator.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT * FROM coa NATURAL JOIN generalledger MINUS SELECT * FROM coa NATURAL JOIN generalledger WHERE coa.type IN('E','I'); Query Cost Rows Bytes ---------------------------- ------ ------- --------- 1. SELECT STATEMENT on 143333 1577577 ######### 2. MINUS on 3. SORT UNIQUE on 72290 1577577 74146119 4. HASH JOIN on 2434 1577577 74146119 5. TABLE ACCESS FULL on COA 2 55 1320 5. TABLE ACCESS FULL on GENERALLEDGER 1677 1577577 36284271 3. SORT UNIQUE on 71043 1549406 72822082 4. HASH JOIN on 2434 1549406 72822082 5. INLIST ITERATOR on 6. TABLE ACCESS BY INDEX ROWID on COA 2 28 672 7. INDEX RANGE SCAN on XFK_COA_TYPE 1 28 5. TABLE ACCESS FULL on GENERALLEDGER 1677 1577577 36284271
Now the INTERSECT operator.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT * FROM coa NATURAL JOIN generalledger WHERE coa.type = 'A' INTERSECT SELECT * FROM coa NATURAL JOIN generalledger WHERE coa.type = 'L'; Query Cost Rows Bytes ---------------------------- ------ ------- --------- 1. SELECT STATEMENT on 142086 1549406 ######### 2. INTERSECTION on 3. SORT UNIQUE on 71043 1549406 72822082 4. HASH JOIN on 2434 1549406 72822082 5. TABLE ACCESS BY INDEX ROWID on COA 2 14 336 6. INDEX RANGE SCAN on XFK_COA_TYPE 1 14 5. TABLE ACCESS FULL on GENERALLEDGER 1677 1577577 36284271 3. SORT UNIQUE on 71043 1549406 72822082 4. HASH JOIN on 2434 1549406 72822082 5. TABLE ACCESS BY INDEX ROWID on COA 2 14 336 6. INDEX RANGE SCAN on XFK_COA_TYPE 1 14 5. TABLE ACCESS FULL on GENERALLEDGER 1677 1577577 36284271
| < Day Day Up > |
|