SQL最佳化中索引列使用函式之靈異事件

dbhelper發表於2015-01-23

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

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最佳化器為啥還去大表中尋找資料呢?

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1411727/,如需轉載,請註明出處,否則將追究法律責任。

相關文章