索引的分析和比較
1、各種索引的建立方法
(1)*tree索引
create index indexname on tablename(columnname);
(2)反向索引
create index indexname on tablename(columnname) reverse;
(3)降序索引
create index indexname on tablename(columnname DESC);
(4)點陣圖索引
create BITMAP index indexname on tablename(columnname);
(5)函式索引
create index indexname on tablename(functionname(columnname));
建立索引後要分析才能使用
analyze table test compute statistics for table for all indexed columns for all indexes;
analyze index test validate structure;
select name,lf_rows from index_stats;用這條語句可以查詢儲存了多少條索引
analyze index test compute statistics;
從字面理解validate structure 主要在於校驗物件的有效性. compute statistics在於統計相關的資訊..
查詢索引
select index_name,index_type from user_indexes where table_name='TEST';
2、開啟autotrace功能
執行$ORACLE_HOME/rdbms/admin/utlxplan.sql和$ORACLE_HOME/sqlplus/admin/plustrce.sql
然後給相關使用者授予plustrace角色，然後這些使用者就可以使用autotrace功能了
3、無效索引
(1)型別不匹配
create table test(a varchar(2),b number);
insert into test values('1',1);
create index test_index on test(a);
analyze table test compute statistics for table for all indexed columns for all indexes;
set autotrace on;
型別匹配的情況
select /*+ RULE */ * from test where a='1';
A B
-- ----------
1 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'TEST_INDEX' (NON-UNIQUE) 使用了索引
型別不匹配的情況
select /*+ RULE */ * from test where a=1;
A B
-- ----------
1 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TEST' 選擇了全表掃描
(2)條件包含函式但沒有建立函式索引
alter system set QUERY_REWRITE_ENABLED=true;
alter system set query_rewrite_integrity=enforced;
insert into test values('a',2);
select /*+ RULE */ * from test where upper(a) = 'A';
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TEST'
由於沒有建立函式索引，所以選擇全表掃描
create index test_index_fun on test(upper(a));
analyze table test compute statistics for table for all indexed columns for all indexes;
select /*+ RULE */ * from test where upper(a) = 'A';
A B
-------------------------------------------------- ----------
a 2
a 3
a 4
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TEST'
雖然建立了函式索引，但由於工作於RBO模式，所以函式索引沒用，選擇了全表掃描
select * from test where upper(a) = 'A';
A B
-------------------------------------------------- ----------
a 2
a 3
a 4
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=9)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=9)
2 1 INDEX (RANGE SCAN) OF 'TEST_INDEX_FUN' (NON-UNIQUE) (Cos
t=1 Card=1)
當函式索引工作於CBO模式下，選擇了基於函式的索引，上面建立的索引函式TEST_INDEX_FUN已經用到
(3)符合索引中的前導列沒有被作為查詢條件
create index test_index_com on test(a,b);
select /*+ RULE */ * from test where a = '1';
A B
-- ----------
1 1
前導列a作為了查詢條件，但由於之前建立了a的索引，所以使用了TEST_INDEX而沒有使用test_index_com
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'TEST_INDEX' (NON-UNIQUE)
select /*+ RULE */ * from test where b = '1';
A B
-- ----------
1 1
2 1
3 1
4 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TEST'
前導列a沒有作為查詢條件，所以選擇全部掃描
select /*+ RULE */ * from test where b = '1' and a= '1';
A B
-- ----------
1 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 INDEX (RANGE SCAN) OF 'TEST_INDEX_COM' (NON-UNIQUE)
前導列a作為了查詢條件，使用了索引
(4)CBO模式下選擇的行數比例過大，最佳化器選擇全表掃描
declare
i number;
j number;
begin
for i in 1 .. 10 loop
for j in 1 .. 10000 loop
insert into test values(to_char(j),i);
end loop;
end loop;
end;
/
declare i number;
begin
for i in 1 .. 100 loop
insert into test values(to_char(i),i);
end loop;
end;
/
SQL> select count(*) from test;
COUNT(*)
----------
200000
select * from test where a = '1';
已選擇10000行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=9333 Bytes=7
4664)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=27 Card=9333 Bytes=746
64)
比例過大，選擇全表掃描
select * from test where a = '99';
已選擇10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=16)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=2 Byt
es=16)
2 1 INDEX (RANGE SCAN) OF 'TEST_INDEX' (NON-UNIQUE) (Cost=1
Card=2)
比例小，選擇索引
select /*+ RULE */ * from test where a = '1';
已選擇10000行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'TEST_INDEX' (NON-UNIQUE)
如果指定為RBO最佳化器，肯定就用索引了
(5)在CBO模式下表很久沒分析，表的增長明顯，最佳化器採取了全表掃描
(6)索引條件中使用了<>、!=、notnot in、not like等運算子，導致查詢不使用索引
先做一個測試在一個表中插入130萬條資料，其中不等於1的資料有30萬條，以下是幾種語句執行的結果
序號 語句 時間 代價
1 select * from test where b<>1; 00: 00: 03.04 398
2 select * from test where b not like 1; 00: 00: 03.03 398
3 select * from test where b !=1; 00: 00: 03.01 398
4 select * from test where b not in(1); 00: 00: 03.00 398
5 select * from test where b<1 union select * from test where b>1; 00: 00: 03.01 264
6 select * from test where b<1 union all select * from test where b>1; 00: 00: 02.09 132
7 select * from test where b<1 or b>1; 00: 00: 02.08 96
從以上可以看出最最佳化的語句是7，在查詢過程中使用索引的有5、6、7
所以，如果建立了索引，在語句中儘量不要使用<>、!=、not、not in、not like操作，如果非要使用，請儘量用or和union操作替換
(7)索引對空值的影響
我們首先做一些測試資料：
SQL> create table t(x int, y int);
請注意，這裡我對錶t做了一個唯一（聯合）索引：
SQL> create unique index t_idx on t(x,y);
SQL> insert into t values(1,1);
SQL> insert into t values(1,NULL);
SQL> insert into t values(NULL,1);
SQL> insert into t values(NULL,NULL);
SQL> commit;
下面我們分析一下索引：
SQL> analyze index t_idx validate structure;
SQL> select name,lf_rows from index_stats;
NAME LF_ROWS
------------------------------ ----------
T_IDX 3
然後，我們就可以看到，當前的索引中僅僅儲存了3行資料。
請注意，上面我們插入並提交了四行資料。
所以，這裡就有一個結論：
Oracle的索引不儲存該索引包含的列中全部為空的行。
這同時也帶來個好處，但當一個表中的某一列大部分為空值，至少90%以上是空值的時候，就可以為該列建立索引。
比如該表為t，該列為x
select * from t where x is null;
此時會選擇全表掃描
select * from t where x=1;
此時就會使用索引，而且索引中不儲存值為空的行，所以索引中只有10%左右的行，因此在這10%的行中找出x=1的行比在全表中找出x=1的行要快的多
我們繼續插入資料，現在再插入幾行全部為空的行：
SQL> insert into t values(NULL,NULL);
SQL> insert into t values(NULL,NULL);
我們看到這樣的插入，居然沒有違反前面我們設定的唯一約束（unique on t(x,y)），
所以，這裡我們又得出一個結論：
Oracle認為 NULL<>NULL ，進而 (NULL,NULL)<>(NULL,NULL)
換句話說，Oracle認為空值（NULL）不等於任何值，包括空值也不等於空值。
我們看到下面的插入會違反唯一約束(DEMO.T_IDX)，這個很好理解了，因為它不是全部為空的值，即它不是（NULL,NULL），只有全部為空的行才被認為是不同的行：
SQL> insert into t values(1,null);
ORA-00001: 違反唯一約束條件 (DEMO.T_IDX)
SQL> insert into t values(null,1);
ORA-00001: 違反唯一約束條件 (DEMO.T_IDX)
SQL>
請看下面的例子:
SQL> select x,y,count(*) from t group by x,y;
X Y COUNT(*)
----- -------- ----------
3
1 1
1 1
1 1 1
Executed in 0.03 seconds
SQL> select x,y,count(*) from t where x is null and y is null group by x,y;
X Y COUNT(*)
---- ------- ----------
3
Executed in 0.01 seconds
SQL>
SQL> select x,y,count(*) from t group by x,y having count(*)>1;
X Y COUNT(*)
------ -------------------- ----------
3
Executed in 0.02 seconds
SQL>
可以看見，完全為空的行有三行，這裡我們又可以得出一個結論：
oracle在group by子句中認為完全為空的行是相同的行
換句話說，在group by子句中，oracle認為(NULL,NULL)=(NULL,NULL)
SQL> select * from t where x is null;
X Y
---------- ----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=4 Bytes=8)
SQL> select * from t where x=1;
X Y
---------- ----------
1 1
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=4)
1 0 INDEX (RANGE SCAN) OF 'T_IDX' (UNIQUE) (Cost=1 Card=2 Byte
s=4)
從以上可以看出，在使用IS NULL 和 IS NOT NULL條件的時候，Oracle不使用索引
那麼我們如何使用空值的比較條件呢？
首先，儘量不在前導列上使用空值，其次我們在建立表的時候，為每個列都指定為非空約束（NOT NULL），並且在必要的列上使用default值
8、不要為所有的列建立索引
我們知道，建立索引是為了提高查詢的效率，但是同時也應該注意到，索引增加了對DML操作（insert, update, delete）的代價，而且，一給中的索引如果太多，那麼多數的索引是根本不會被使用到的，而另一方面我們維護這些不被使用的所以還要大幅度降低系統的效能。所以，索引不是越多越好，而是要恰到好處的使用。
比如說，有些列由於使用了函式，我們要使用已有的索引（如一些複合索引）是不可能的，那麼就必須建立單獨的函式索引，如果說這個函式索引很少會被應用（僅僅在幾個特別的sql中會用到），我們就可以嘗試改寫查詢，而不去建立和維護那個函式索引，例如：
1，trunc函式
SQL> select empno,ename,deptno from emp where trunc(hiredate)='2004-01-01';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMP'
將上面的查詢轉換為：
SQL> select empno,ename,deptno from emp
2 where hiredate >= to_date('2004-01-01','yyyy-mm-dd')
3 and hiredate Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 2 1 INDEX (RANGE SCAN) OF 'EMP_ID3' (NON-UNIQUE) SQL> select empno,ename,deptno from emp 2 where to_char(hiredate,'yyyy-mm-dd')='2003-09-05'; ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMP' SQL> select empno,ename,deptno from emp 2 where hiredate=to_date('2003-09-05','yyyy-mm-dd'); ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 2 1 INDEX (RANGE SCAN) OF 'EMP_ID3' (NON-UNIQUE) SQL> select dname from dept where substr(dname,1,3)='abc'; ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'DEPT' Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 INDEX (RANGE SCAN) OF 'DEPT_ID1' (NON-UNIQUE) 通常，為了均衡查詢的效率和DML的效率，我們要仔細的分析應用，找出來出現頻率相對較多、欄位內容較少（比如varchar2(1000)就不適合建立索引，而varchar2(10)相對來說就適合建立索引）的列，合理的建立索引，比如有時候我們希望建立複合索引，有時候我們更希望建立單鍵索引。
2，to_char函式
Execution Plan
Execution Plan
3，substr函式
Execution Plan
SQL> select dname from dept where dname like 'abc%';
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/77544/viewspace-1001970/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 全域性索引和本地索引的比較索引
- 【索引】oracle查詢使用索引和不使用索引的比較索引Oracle
- [轉]Oracle分割槽索引--本地索引和全域性索引比較Oracle索引
- Oracle分割槽索引--本地索引和全域性索引比較（轉）Oracle索引
- multi-key索引和wildCard索引場景比較索引
- 比較有索引和無索引的查詢速度（在mysql資料庫中）索引MySql資料庫
- Mysql中的Btree與Hash索引比較MySql索引
- Memcached 及 Redis 架構分析和比較Redis架構
- 很多人比較糾結的約束和索引的關係索引
- js 深比較和淺比較JS
- Lock的獨佔鎖和共享鎖的比較分析
- 【索引】Bitmap點陣圖索引與普通的B-Tree索引鎖的比較索引
- 【原創】InnoDB 和TokuDB的讀寫分析與比較
- ejb 和 javabean的比較JavaBean
- Go和Python比較的話，哪個比較好？GoPython
- 幾個分析函式的比較函式
- Tweetolife：Twitter上的性別和時間比較分析
- Oracle date 型別比較和String比較Oracle型別
- TreeMap和HashMap的元素比較HashMap
- JPA和mybatis的CRUD比較MyBatis
- Wordpress 和 Movable Type 的比較
- Unity和虛幻的比較Unity
- 圖論中的常見演算法分析比較和模板圖論演算法
- Go 與 C++ 的對比和比較GoC++
- TCP和UDP比較TCPUDP
- Java和JavaSciprt比較Java
- Redis 和 Memcached 比較Redis
- MongoDB和Redis比較。MongoDBRedis
- MongoDB和MySQL比較MongoDBMySql
- ETL和EAI比較AI
- 幾個比較火的BI分析工具
- COPA 獲利分析的兩種方式比較
- B-Tree索引與Bitmap點陣圖索引的鎖代價比較研究索引
- 【索引】Oracle之不可見索引和虛擬索引的比對索引Oracle
- Dojo與jQuery綜合比較分析jQuery
- oracle中字串的大小比較，字串與數字的比較和運算Oracle字串
- tbase和postgres-xl的比較
- mongodb和hbase的簡單比較MongoDB