|
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'; |
