轉自：http://tb.blog.csdn.net/TrackBack.aspx?PostId=1729625
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)索引條件中使用了<>、!=、not\not 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<to_date('2004-01-01','yyyy-mm-dd')+0.999;
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)
2，to_char函數
SQL> select empno,ename,deptno from emp
2 where to_char(hiredate,'yyyy-mm-dd')='2003-09-05';
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('2003-09-05','yyyy-mm-dd');
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)
3，substr函數
SQL> select dname from dept where substr(dname,1,3)='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'
SQL> select dname from dept where dname like 'abc%';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'DEPT_ID1' (NON-UNIQUE)
通常，為了均衡查詢的效率和DML的效率，我們要仔細的分析應用，找出來出現頻率相對較多、字段內容較少（比如varchar2(1000)就不適合建立索引，而varchar2(10)相對來說就適合建立索引）的列，合理的建立索引，比如有時候我們希望建立復合索引，有時候我們更希望建立單鍵索引。
posted on 2007-11-21 10:20
xzc 閱讀(453)
評論(0) 編輯 收藏 所屬分類:
Oracle