国产亚洲人成网站在线观看,日本久久久a级免费,亚洲国产一区二区a毛片

blog已經(jīng)轉(zhuǎn)移至github,大家請(qǐng)?jiān)L問 http://qaseven.github.io/

SQL優(yōu)化中索引列使用函數(shù)之靈異事件

　在SQL優(yōu)化內(nèi)容中有一種說法說的是避免在索引列上使用函數(shù)、運(yùn)算等操作，否則Oracle優(yōu)化器將不使用索引而使用全表掃描，但是也有一些例外的情況，今天我們就來看看該靈異事件。

　　一般而言，以下情況都會(huì)使Oracle的優(yōu)化器走全表掃描，舉例:

　　1. substr(hbs_bh,1,4)=’5400’，優(yōu)化處理：hbs_bh like ‘5400%’

　　2. trunc(sk_rq)=trunc(sysdate)，優(yōu)化處理：sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)

　　3. 進(jìn)行了顯式或隱式的運(yùn)算的字段不能進(jìn)行索引，如：

　　ss_df+20>50，優(yōu)化處理：ss_df>30

　　'X' || hbs_bh>’X5400021452’，優(yōu)化處理：hbs_bh>'5400021542'

　　sk_rq+5=sysdate，優(yōu)化處理：sk_rq=sysdate-5

　　4. 條件內(nèi)包括了多個(gè)本表的字段運(yùn)算時(shí)不能進(jìn)行索引，如：ys_df>cx_df，無法進(jìn)行優(yōu)化

　　qc_bh || kh_bh='5400250000'，優(yōu)化處理：qc_bh='5400' and kh_bh='250000'

　　5. 避免出現(xiàn)隱式類型轉(zhuǎn)化

　　hbs_bh=5401002554，優(yōu)化處理：hbs_bh='5401002554'，注：此條件對(duì)hbs_bh 進(jìn)行隱式的to_number轉(zhuǎn)換，因?yàn)閔bs_bh字段是字符型。

　　有一些其它的例外情況，如果select 后邊只有索引列且where查詢中的索引列含有非空約束的時(shí)候，以上規(guī)則不適用，如下示例：

　　先給出所有腳本及結(jié)論：

　　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è)試代碼：

C:\Users\華榮>sqlplus lhr/lhr@orclasm

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 11月 12 10:52:29 2014

連接到:

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> drop table t purge;

表已刪除。

SQL> Create Table t nologging As select * from dba_objects d ;

表已創(chuàng)建。

SQL> create index ind_objectname on t(object_name);

索引已創(chuàng)建。

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

執(zhí)行計(jì)劃

----------------------------------------------------------

Plan hash value: 4280870634

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 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

統(tǒng)計(jì)信息

----------------------------------------------------------

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

執(zhí)行計(jì)劃

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

統(tǒng)計(jì)信息

----------------------------------------------------------

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 ;

執(zhí)行計(jì)劃

----------------------------------------------------------

Plan hash value: 3379870158

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 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

統(tǒng)計(jì)信息

----------------------------------------------------------

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 ;

執(zhí)行計(jì)劃

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

統(tǒng)計(jì)信息

----------------------------------------------------------

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 ;

執(zhí)行計(jì)劃

----------------------------------------------------------

Plan hash value: 3379870158

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 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

統(tǒng)計(jì)信息

----------------------------------------------------------

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>

　　其實(shí)很好理解的，索引可以看成是小表，一般而言索引總是比表本身要小得多，如果select 后需要檢索的項(xiàng)目在索引中就可以檢索的到那么Oracle優(yōu)化器為啥還去大表中尋找數(shù)據(jù)呢？

posted on 2014-12-03 13:35 順其自然EVO 閱讀(589) 評(píng)論(0) 編輯收藏所屬分類: 數(shù)據(jù)庫(kù)

新用戶注冊(cè) 刷新評(píng)論列表


只有注冊(cè)用戶登錄后才能發(fā)表評(píng)論。




網(wǎng)站導(dǎo)航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關(guān)文章: MySQL數(shù)字輔助表 PLSQL Developer鮮為人知的快捷鍵淘寶分布式配置管理服務(wù)Diamond iOS開發(fā)之FMDB AlwaysOn可用性組功能測(cè)試(2)-SQL Server群集故障轉(zhuǎn)移 SQL Server 2008 (R2) 單機(jī)版安裝的先決條件 AlwaysOn可用性組功能測(cè)試(2)-SQL Server群集故障轉(zhuǎn)移聊聊Oracle外鍵約束的幾個(gè)操作選項(xiàng) SQL優(yōu)化中索引列使用函數(shù)之靈異事件利用binlog進(jìn)行數(shù)據(jù)庫(kù)的還原

qileilove

SQL優(yōu)化中索引列使用函數(shù)之靈異事件

導(dǎo)航

統(tǒng)計(jì)

常用鏈接

留言簿(55)

隨筆分類

隨筆檔案

文章分類

文章檔案

搜索

最新評(píng)論

閱讀排行榜

評(píng)論排行榜