類型轉換導致執行計畫不走索引測試案例,執行計畫索引

來源:互聯網
上載者:User

類型轉換導致執行計畫不走索引測試案例,執行計畫索引


測試環境類比:
SQL> drop table t_col_type purge;
create table t_col_type(id varchar2(20),col2 varchar2(20),col3 varchar2(20));
insert into t_col_type select rownum,'abc','efg' from dual connect by level<=10000;
commit;
create index idx_id on t_col_type(id);
set linesize 1000
set autotrace traceonlydrop table t_col_type purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist

 


SQL> select * from t_col_type where id=6;


Execution Plan
----------------------------------------------------------
Plan hash value: 3191204463

--------------------------------------------------------------------------------
| Id  | Operation                                  | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                        |     1    |    36 |     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL            | T_COL_TYPE |     1    |    36 |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER("ID")=6)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         64  consistent gets
          0  physical reads
          0  redo size
        640  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


這裡好像有點奇怪啊,明明建了index [create index idx_id on t_col_type(id);]但是為啥沒有用到呢?

---查看錶上列是否有索引
SQL> select index_name , table_name,column_name from all_ind_columns where table_name ='T_COL_TYPE';

INDEX_NAME
------------------------------------------------------------
TABLE_NAME
------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
IDX_ID
T_COL_TYPE
ID


----查看錶結構
SQL> desc scott.T_COL_TYPE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 VARCHAR2(20)----------注意這裡的字元類型
 COL2                                               VARCHAR2(20)
 COL3                                               VARCHAR2(20)


再次關注下 執行計畫中的謂語資訊:
1 - filter(TO_NUMBER("ID")=6)  ----------這裡發生了類型轉換

所以在執行計畫中就無法用已有的索引,那麼如何才能讓他正確走索引呢?

 

select * from t_col_type where id='6';------注意下這裡的區別加了單引號,表明這是個字元,


Execution Plan
----------------------------------------------------------
Plan hash value: 3998173245

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 |  SELECT STATEMENT                         |                       |     1 |    36 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | T_COL_TYPE |     1 |    36 |     2   (0)| 00:00:01 |
|*  2 |              INDEX RANGE SCAN             | IDX_ID              |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("ID"='6')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         39  consistent gets
          1  physical reads
          0  redo size
        640  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 


 


Oracle字元類型與數實值型別的轉換

額,我對你上面的話的理解是:每次進行篩選的時候,一定要避免隱式轉換。在索引欄位上使用函數,或者其他的轉換都會導致索引不可用,而你說的優先轉換字元類型到數實值型別,假如你進行篩選的欄位是字元類型,那Oracle絕對不會給你轉成數實值型別,你的那句話應該是相對於其他類型來說的吧我認為,比如date類型之類的。所以我認為兩個應該都是對的,只是說的是兩個不同方面的規則吧。

我做過一個項目,用兩張有3千萬的表進行join,結果發現速度慢得受不了,該加的索引都加了,用執行計畫看了,發現索引用不到,問題也就出在隱式轉換身上。所以,第一句是對的,而第二句其實我也不是很確定哪個優先,從表面上看是看不出來的,要瞭解到Oracle內部的轉換機制。。。小小意見,一起探討探討。。。
 
SQL索引的兩個問題

1. 首先你要明白索引的儲存結構, 這些都是使用索引的一些技巧, 這些技巧對於任何索引都是通用的,索引是佔用實體儲存體的與基表具有邏輯影射的一組內容,索引的儲存一般都是有序的,如果你要比較同一個表的兩個索引(a,b),執行計畫就會選擇a[1]和b[1..n] 所有的記錄比較, 然後接著a[2]和b[1...n]比較的次數為笛卡兒積, 相當於 C中的兩次FOR迴圈
for(i=0;i<=n;i++)
for(j=0;j<=n;j++)
{ if(a[i]>b[j])
then "this record should be put into memory"
}
你知道, 索引和表的記錄在oracle中是根據他的二元高度確定cost的, 二元高度越高就證明要經過的I/O次數越多, 執行計畫就越差, 如果索引記錄和基表記錄也不在同一個塊中,那麼更會增加需要query的時間, 所以一般這種情況oracle的執行計畫會選擇全表掃描來的更快一點。
2. 你說的對, 但是在這裡,oracle的內部轉換不會根據優先順序進行,
比如說 WHERE Ename = 123
但是Ename 是varchar類型, 這個時候就會內部轉換為
WHERE TO_NUMBER(ENAME) = 123
記住,在oracle 中,所有字元均被轉換為大寫, 一般oracle 認為你要檢索的條件是"神聖的", 所以一般只轉換索引的列進行(本人見解, 不同意見者, 歡迎拍磚)
 

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.