中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

Oracle like、不等于、隱式轉換走索引與不走索引情況

發布時間：2020-08-10 08:57:57 來源：ITPUB博客 閱讀：529 作者：你好我是李白 欄目：關系型數據庫

1. 概述 

# like
（1）當使用like查詢時，后模糊匹配，則走索引，如like 'test%'
（2）當使用like查詢時，前模糊匹配，則不走索引，如like '%test'
# <> 不走索引
因為不等于，即等于大量數據，所以不走索引
# 隱式轉換，當發生在索引列時，不走索引，發生在條件值列時，走索引
（1）如果隱式轉換發生在值列，則走索引，例如查詢使用日期查詢時，
select * from test_implic where bir_date = '20180122 14:22:32';
（2）如果索引列發生了隱式轉換，則不走索引，如列數據類型為varchar2，使用如下查詢時
select bir_date from test_implic where id = 2000;
（3）當number列等于字符串時，走索引

2.測試

（1） like 后模糊匹配走索引  like 前模糊匹配走全表

# 創建測試表
create table test_bind(id number,name varchar2(20));
#插入數據
declare
i number;
begin
for i in 1..100000
loop
insert into test_bind values(i,'haha');
end loop;
end;
/
declare
i number;
begin
for i in 100000..100010
loop
insert into test_bind values(i,'test');
end loop;
end;
/
# 創建索引
create index IDX_TEST_BIND on test_bind(name);
# 收集統計信息
exec dbms_stats.gather_table_stats('LIBAI','TEST_BIND');
# 查詢，后模糊匹配，可以看到走了索引
LIBAI@honor1 > set autotrace on
LIBAI@honor1 > select * from test_bind where name like 'te%';
                                      ID NAME
---------------------------------------- ----------------------------------------
                                  100001 test
                                  100002 test
                                  100003 test
                                  100004 test
                                  100005 test
                                  100006 test
                                  100007 test
                                  100008 test
                                  100009 test
                                  100010 test
10 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2889536435
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     9 |    90 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_BIND     |     9 |    90 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_BIND |     9 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("NAME" LIKE 'te%')
       filter("NAME" LIKE 'te%')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        782  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed
         
# 前模糊匹配，可以看到走了全表掃描
LIBAI@honor1 > select * from test_bind where name like '%st';
                                      ID NAME
---------------------------------------- ----------------------------------------
                                  100001 test
                                  100002 test
                                  100003 test
                                  100004 test
                                  100005 test
                                  100006 test
                                  100007 test
                                  100008 test
                                  100009 test
                                  100010 test
10 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 3519963602
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  5001 | 50010 |    69   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_BIND |  5001 | 50010 |    69   (2)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("NAME" LIKE '%st' AND "NAME" IS NOT NULL)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        236  consistent gets
          0  physical reads
          0  redo size
        734  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

（2） <> 不走索引

LIBAI@honor1 > select * from test_bind where name <> 'test';
                                      ID NAME
---------------------------------------- ----------------------------------------
                                  100001 test
                                  100002 test
                                  100003 test
                                  100004 test
                                  100005 test
                                  100006 test
                                  100007 test
                                  100008 test
                                  100009 test
                                  100010 test
10 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3519963602
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |    18 |   180 |    69   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_BIND |    18 |   180 |    69   (2)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("NAME"<>'haha')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        236  consistent gets
          0  physical reads
          0  redo size
        734  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

（3） 隱式轉換

# 構造測試環境

create table test_implic (id varchar2(20),name varchar2(20),bir_date date default sysdate);
declare
i varchar2(10);
begin
for i in 1..10000
loop
insert into test_implic values(i,'czh',sysdate);
end loop;
commit;
end;
/
create index idx_test_implic_id on test_implic(id);
create index idx_test_implic_bir_date on test_implic(bir_date);
exec dbms_stats.gather_table_stats('LIBAI','TEST_IMPLIC');

# 當varchar2類型等于數字時，不走索引

LIBAI@honor1 > select bir_date from test_implic where id = 2000;
BIR_DATE
-------------------
2020-01-19 20:00:51
Execution Plan
----------------------------------------------------------
Plan hash value: 965190314
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |    13 |    11   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_IMPLIC |     1 |    13 |    11   (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("ID")=2000)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         38  consistent gets
          0  physical reads
          0  redo size
        531  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
LIBAI@honor1 > select bir_date from test_implic where id = to_char(2000);
BIR_DATE
-------------------
2020-01-19 20:00:51
Execution Plan
----------------------------------------------------------
Plan hash value: 3908402167
--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_IMPLIC        |     1 |    13 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_IMPLIC_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"='2000')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          4  physical reads
          0  redo size
        531  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

# 當number等于字符串時，走索引

LIBAI@honor1 > select * from test_bind where id = '1000';
                                      ID NAME
---------------------------------------- ----------------------------------------
                                    1000 haha
Execution Plan
----------------------------------------------------------
Plan hash value: 2345277976
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    10 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_BIND        |     1 |    10 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_BIND_ID |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=1000)
Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
         33  consistent gets
          0  physical reads
          0  redo size
        595  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

# 當日期等于字符串時，走索引

LIBAI@honor1 > select * from test_implic where bir_date = '20180122 14:22:32';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3390782276
--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                          |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_IMPLIC              |     1 |    17 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_IMPLIC_BIR_DATE |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("BIR_DATE"='20180122 14:22:32')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          4  physical reads
          0  redo size
        466  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
向AI問一下細節

免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。

AI
助
手
铁力市| 陆丰市| 西宁市| 嘉峪关市| 定西市| 扎赉特旗| 衡东县| 木兰县| 砚山县| 吴旗县| 海盐县| 吕梁市| 龙里县| 揭阳市| 瑞安市| 汉中市| 中阳县| 中牟县| 威信县| 泰顺县| 崇义县| 海南省| 盐池县| 兴隆县| 岳普湖县| 白山市| 通山县| 香格里拉县| 汝城县| 江油市| 黄平县| 巫山县| 吉林省| 康平县| 青浦区| 宜春市| 邹平县| 翁牛特旗| 龙岩市| 类乌齐县| 原阳县|