qileilove
          

          blog已經轉移至github,大家請訪問 http://qaseven.github.io/
          
			
          
				
					
	
          
		
          
			SQL優化中索引列使用函數之靈異事件
		
          
		
          　在SQL優化內容中有一種說法說的是避免在索引列上使用函數、運算等操作，否則Oracle優化器將不使用索引而使用全表掃描，但是也有一些例外的情況，今天我們就來看看該靈異事件。
          　　一般而言，以下情況都會使Oracle的優化器走全表掃描，舉例:
          　　1.         substr(hbs_bh,1,4)=’5400’，優化處理：hbs_bh like ‘5400%’
          　　2.         trunc(sk_rq)=trunc(sysdate)， 優化處理：sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)
          　　3.         進行了顯式或隱式的運算的字段不能進行索引，如：
          　　ss_df+20>50，優化處理：ss_df>30
          　　'X' || hbs_bh>’X5400021452’，優化處理：hbs_bh>'5400021542'
          　　sk_rq+5=sysdate，優化處理：sk_rq=sysdate-5
          　　4.         條件內包括了多個本表的字段運算時不能進行索引，如：ys_df>cx_df，無法進行優化
          　　qc_bh || kh_bh='5400250000'，優化處理：qc_bh='5400' and kh_bh='250000'
          　　5.  避免出現隱式類型轉化
          　　hbs_bh=5401002554，優化處理：hbs_bh='5401002554'，注：此條件對hbs_bh 進行隱式的to_number轉換，因為hbs_bh字段是字符型。
          　　有一些其它的例外情況，如果select 后邊只有索引列且where查詢中的索引列含有非空約束的時候，以上規則不適用，如下示例：
          　　先給出所有腳本及結論：
          　　drop table t  purge;
          　　Create Table t  nologging As select *  from    dba_objects d ;
          　　create   index ind_objectname on  t(object_name);
          　　select t.object_name from t where t.object_name ='T';        --走索引
          　　select t.object_name from t where UPPER(t.object_name) ='T';       --不走索引
          　　select t.object_name from t where UPPER(t.object_name) ='T' and t.object_name IS NOT NULL ;           --走索引  (INDEX FAST FULL SCAN)
          　　select t.object_name from t where UPPER(t.object_name) ||'AAA' ='T'||'AAA' and t.object_name IS NOT NULL ;     --走索引  (INDEX FAST FULL SCAN)
          　　select t.object_name,t.owner from t where UPPER(t.object_name) ||'AAA' ='T'||'AAA' and t.object_name IS NOT NULL ;     --不走索引
          　　測試代碼：
          C:\Users\華榮>sqlplus lhr/lhr@orclasm
          SQL*Plus: Release 11.2.0.1.0 Production on 星期三 11月 12 10:52:29 2014
          Copyright (c) 1982, 2010, Oracle.  All rights reserved.
          連接到:
          Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
          With the Partitioning, Automatic Storage Management, OLAP, Data Mining
          and Real Application Testing options
          SQL>
          SQL>
          SQL> drop table t  purge;
          表已刪除。
          SQL> Create Table t  nologging As select *  from    dba_objects d ;
          表已創建。
          SQL>  create   index ind_objectname on  t(object_name);
          索引已創建。
          　---- t表所有列均可以為空
          SQL> desc t
          Name                      Null?    Type
          ----------------------------------------- -------- ----------------------------
          OWNER                               VARCHAR2(30)
          OBJECT_NAME                         VARCHAR2(128)
          SUBOBJECT_NAME                      VARCHAR2(30)
          OBJECT_ID                           NUMBER
          DATA_OBJECT_ID                      NUMBER
          OBJECT_TYPE                         VARCHAR2(19)
          CREATED                             DATE
          LAST_DDL_TIME                       DATE
          TIMESTAMP                           VARCHAR2(19)
          STATUS                              VARCHAR2(7)
          TEMPORARY                           VARCHAR2(1)
          GENERATED                           VARCHAR2(1)
          SECONDARY                           VARCHAR2(1)
          NAMESPACE                           NUMBER
          EDITION_NAME                        VARCHAR2(30)
          SQL>
          SQL>  set autotrace traceonly;
          SQL>  select t.object_name from t where t.object_name ='T';
          執行計劃
          ----------------------------------------------------------
          Plan hash value: 4280870634
          -----------------------------------------------------------------------------------
          | Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
          -----------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT |                |     1 |    66 |     3   (0)| 00:00:01 |
          |*  1 |  INDEX RANGE SCAN| IND_OBJECTNAME |     1 |    66 |     3   (0)| 00:00:01 |
          -----------------------------------------------------------------------------------
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          1 - access("T"."OBJECT_NAME"='T')
          Note
          -----
          - dynamic sampling used for this statement (level=2)
          - SQL plan baseline "SQL_PLAN_503ygb00mbj6k165e82cd" used for this statement
          統計信息
          ----------------------------------------------------------
          34  recursive calls
          43  db block gets
          127  consistent gets
          398  physical reads
          15476  redo size
          349  bytes sent via SQL*Net to client
          359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          SQL>  select t.object_name from t where UPPER(t.object_name) ='T';
          執行計劃
          ----------------------------------------------------------
          Plan hash value: 1601196873
          --------------------------------------------------------------------------
          | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
          --------------------------------------------------------------------------
          |   0 | SELECT STATEMENT  |      |    12 |   792 |   305   (1)| 00:00:04 |
          |*  1 |  TABLE ACCESS FULL| T    |    12 |   792 |   305   (1)| 00:00:04 |
          --------------------------------------------------------------------------
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          1 - filter(UPPER("T"."OBJECT_NAME")='T')
          Note
          -----
          - dynamic sampling used for this statement (level=2)
          - SQL plan baseline "SQL_PLAN_9p76pys5gdb2b94ecae5c" used for this statement
          統計信息
          ----------------------------------------------------------
          29  recursive calls
          43  db block gets
          1209  consistent gets
          1092  physical reads
          15484  redo size
          349  bytes sent via SQL*Net to client
          359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          SQL>  select t.object_name from t where UPPER(t.object_name) ='T' and t.object_name IS NOT NULL ;
          執行計劃
          ----------------------------------------------------------
          Plan hash value: 3379870158
          ---------------------------------------------------------------------------------------
          | Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
          ---------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT     |                |    51 |  3366 |   110   (1)| 00:00:02 |
          |*  1 |  INDEX FAST FULL SCAN| IND_OBJECTNAME |    51 |  3366 |   110   (1)| 00:00:02 |
          ---------------------------------------------------------------------------------------
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          1 - filter("T"."OBJECT_NAME" IS NOT NULL AND UPPER("T"."OBJECT_NAME")='T')
          Note
          -----
          - dynamic sampling used for this statement (level=2)
          - SQL plan baseline "SQL_PLAN_czkarb71kthws18b0c28f" used for this statement
          統計信息
          ----------------------------------------------------------
          29  recursive calls
          43  db block gets
          505  consistent gets
          384  physical reads
          15612  redo size
          349  bytes sent via SQL*Net to client
          359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          SQL>  select t.object_name,t.owner from t where UPPER(t.object_name) ||'AAA' ='T'||'AAA' and t.object_name IS NOT NULL ;
          執行計劃
          ----------------------------------------------------------
          Plan hash value: 1601196873
          --------------------------------------------------------------------------
          | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
          --------------------------------------------------------------------------
          |   0 | SELECT STATEMENT  |      |    51 |  4233 |   304   (1)| 00:00:04 |
          |*  1 |  TABLE ACCESS FULL| T    |    51 |  4233 |   304   (1)| 00:00:04 |
          --------------------------------------------------------------------------
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          1 - filter("T"."OBJECT_NAME" IS NOT NULL AND
          UPPER("T"."OBJECT_NAME")||'AAA'='TAAA')
          Note
          -----
          - dynamic sampling used for this statement (level=2)
          - SQL plan baseline "SQL_PLAN_au9a1c4hwdtb894ecae5c" used for this statement
          統計信息
          ----------------------------------------------------------
          30  recursive calls
          44  db block gets
          1210  consistent gets
          1091  physical reads
          15748  redo size
          408  bytes sent via SQL*Net to client
          359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          SQL> select t.object_name from t where UPPER(t.object_name) ||'AAA' ='T'||'AAA' and t.object_name IS NOT NULL ;
          執行計劃
          ----------------------------------------------------------
          Plan hash value: 3379870158
          ---------------------------------------------------------------------------------------
          | Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
          ---------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT     |                |    51 |  3366 |   110   (1)| 00:00:02 |
          |*  1 |  INDEX FAST FULL SCAN| IND_OBJECTNAME |    51 |  3366 |   110   (1)| 00:00:02 |
          ---------------------------------------------------------------------------------------
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          1 - filter("T"."OBJECT_NAME" IS NOT NULL AND
          UPPER("T"."OBJECT_NAME")||'AAA'='TAAA')
          Note
          -----
          - dynamic sampling used for this statement (level=2)
          - SQL plan baseline "SQL_PLAN_1gu36rnh3s2a318b0c28f" used for this statement
          統計信息
          ----------------------------------------------------------
          28  recursive calls
          44  db block gets
          505  consistent gets
          6  physical reads
          15544  redo size
          349  bytes sent via SQL*Net to client
          359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          SQL>
          　　其實很好理解的，索引可以看成是小表，一般而言索引總是比表本身要小得多，如果select 后需要檢索的項目在索引中就可以檢索的到那么Oracle優化器為啥還去大表中尋找數據呢？
          
		
          
			posted on 2014-12-03 13:35 順其自然EVO 閱讀(589) 評論(0)  編輯  收藏  所屬分類: 數據庫 
		
          
	
          
	
	


	


          
	    
    




          








          

				
			
          
			
          
				
					
          
	
          
		
          <2014年12月>
          
	
          日一二三四五六
          30123456
          78910111213
          14151617181920
          21222324252627
          28293031123
          45678910
          

          

					

          導航
          

					

          統計
          
	
					
					

          常用鏈接
          


          留言簿(55)
          


		
          隨筆分類
          
		
				
			
	
		
          隨筆檔案
          
		
				
			
	
		
          文章分類
          
		
				
			
	
		
          文章檔案
          
		
				
			
	



          搜索
          



          最新評論
	
          

          
	
			
		

          


          閱讀排行榜
          



          評論排行榜
          


				
			
          			
			

	

    







          
        
	




主站蜘蛛池模板：
通辽市|
渭源县|
肥乡县|
新昌县|
合肥市|
教育|
漳平市|
疏勒县|
彭山县|
泊头市|
涿州市|
西平县|
合江县|
彭州市|
苍溪县|
昌宁县|
云南省|
宝丰县|
青州市|
巨野县|
青冈县|
镇原县|
惠来县|
梨树县|
阿克陶县|
巴塘县|
商河县|
城市|
莆田市|
科尔|
石城县|
喜德县|
峡江县|
富顺县|
三河市|
拜泉县|
鹤山市|
泸定县|
姚安县|
永仁县|
剑阁县|