高效SQL语句必杀技

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(LNNVL("OBJECT_ID"=69450))
   5 - access("OWNER"='SYSTEM')
        
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         46  consistent gets
          0  physical reads
          0  redo size
      11383  bytes sent via SQL*Net to client
        712  bytes received via SQL*Net from client
         22  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        301  rows processed

scott@CNMMBO> select * from t6
  2  where object_id=69450
  3  union
  4  select * from t6
  5  where owner='SYSTEM';

301 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 370530636
------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |   301 |  7224 |     7  (72)| 00:00:01 |
|   1 |  SORT UNIQUE                  |                |   301 |  7224 |     7  (72)| 00:00:01 |
|   2 |   UNION-ALL                   |                |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T6             |     1 |    24 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | I_T6_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T6             |   300 |  7200 |     3   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | I_T6_OWNER     |   300 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("OBJECT_ID"=69450)
   6 - access("OWNER"='SYSTEM')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
      11383  bytes sent via SQL*Net to client
        712  bytes received via SQL*Net from client
         22  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        301  rows processed

　　-->从上面的统计信息可知，consistent gets由46下降为7，故当where子句中谓词上存在索引时，使用union替换or更高效

　　-->即使当列object_id与owner上不存在索引时，使用union仍然比or更高效（在Oracle 10g R2与Oracle 11g R2测试）

　　4）避免索引列上使用函数

　　-->下面是一个来自实际生产环境的例子

　　-->表acc_pos_int_tbl上business_date列存在索引，由于使用了SUBSTR函数，此时索引失效，使用全表扫描

SELECT acc_num
     ， curr_cd
     ， DECODE( '20110728'
             ， ( SELECT TO_CHAR( LAST_DAY( TO_DATE( '20110728'， 'YYYYMMDD' ) )， 'YYYYMMDD' ) FROM dual )， 0
             ，   adj_credit_int_lv1_amt
               + adj_credit_int_lv2_amt
               - adj_debit_int_lv1_amt
               - adj_debit_int_lv2_amt )
          AS interest
FROM   acc_pos_int_tbl
WHERE  SUBSTR( business_date， 1， 6 ) = SUBSTR( '20110728'， 1， 6 ) AND business_date <= '20110728';

　　-->改进的办法

SELECT acc_num
     ， curr_cd
     ， DECODE( '20110728'
             ， ( SELECT TO_CHAR( LAST_DAY( TO_DATE( '20110728'， 'YYYYMMDD' ) )， 'YYYYMMDD' ) FROM dual )， 0
             ，   adj_credit_int_lv1_amt
               + adj_credit_int_lv2_amt
               - adj_debit_int_lv1_amt
               - adj_debit_int_lv2_amt )
          AS interest
FROM   acc_pos_int_tbl acc_pos_int_tbl
WHERE  business_date >= TO_CHAR( LAST_DAY( ADD_MONTHS( TO_DATE( '20110728'， 'yyyymmdd' )， -1 ) )
                                + 1， 'yyyymmdd' )
       AND business_date <= '20110728';

　　-->下面的例子虽然没有使用函数，但字符串连接同样导致索引失效

　　-->低效：

SELECT account_name， amount
FROM   transaction
WHERE  account_name
       || account_type = 'AMEXA';

　　-->高效：

SELECT account_name， amount
FROM   transaction
WHERE  account_name = 'AMEX' AND account_type = 'A';