8.7 Special Cases

 < Day Day Up > 



There are some special cases. The ones of interest for discussion involve concatenation, IN list operators, and the UNION set operator.

8.7.1 Concatenation

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 

8.7.2 The IN List Operator

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.

8.7.3 UNION, MINUS, and INTERSECT

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 > 



Oracle High Performance Tuning for 9i and 10g
Oracle High Performance Tuning for 9i and 10g
ISBN: 1555583059
EAN: 2147483647
Year: 2003
Pages: 164

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net