· 通過全表掃描的方式訪問數(shù)據(jù)；
成都服務器托管，成都創(chuàng)新互聯(lián)提供包括服務器租用、遂寧服務器托管、帶寬租用、云主機、機柜租用、主機租用托管、CDN網(wǎng)站加速、域名注冊等業(yè)務的一體化完整服務。電話咨詢：18982081108
· 通過ROWID訪問數(shù)據(jù)；
· 通過索引的方式訪問數(shù)據(jù)；
· Oracle順序讀取表中所有的行，并逐條匹配WHERE限定條件。
· 采用多塊讀的方式進行全表掃描，可以有效提高系統(tǒng)的吞吐量，降低I/O次數(shù)。
· 即使創(chuàng)建索引，Oracle也會根據(jù)CBO的計算結(jié)果，決定是否使用索引。
注意事項：
· 只有全表掃描時才可以使用多塊讀。該方式下，單個數(shù)據(jù)塊僅訪問一次。
· 對于數(shù)據(jù)量較大的表，不建議使用全表掃描進行訪問。
· 當訪問表中的數(shù)據(jù)量超過數(shù)據(jù)總量的5%—10%時，通常Oracle會采用全表掃描的方式進行訪問。
· 并行查詢可能會導致優(yōu)化器選擇全表掃描的方式。1.2ROWID訪問表
· Rowid是數(shù)據(jù)存放在數(shù)據(jù)庫中的物理地址，能夠唯一標識表中的一條數(shù)據(jù)。
· Rowid指出了一條記錄所在的數(shù)據(jù)文件、塊號以及行號的位置，因此通過ROWID定位單行數(shù)據(jù)是最快的方法。
注意事項：
· Rowid作為一個偽列，其數(shù)值并不存儲在數(shù)據(jù)庫中，當查詢時才進行計算。
· Rowid除了在同一集簇中可能不唯一外，每條記錄的Rowid唯一。1.3 INDEX訪問表
· 通過索引查找相應數(shù)據(jù)行的Rowid，再根據(jù)Rowid查找表中實際數(shù)據(jù)的方式稱為“索引查找”或者“索引掃描”。
· 一個Rowid對應一條數(shù)據(jù)行（根據(jù)Rowid查找結(jié)果，僅需要對Rowid相應數(shù)據(jù)的數(shù)據(jù)塊進行一次I/O操作），因此該方式屬于“單塊讀”。
· 對于索引，除了存儲索引的數(shù)據(jù)外，還保存有該數(shù)據(jù)對應的Rowid信息。
· 索引掃描分為兩步：1）掃描索引確定相應的Rowid信息。 2）根據(jù)Rowid從表中獲得對應的數(shù)據(jù)。
注意事項：
· 對于選擇性高的數(shù)據(jù)行，索引的使用會提升查詢的性能。但對于DML操作，尤其是批量數(shù)據(jù)的操作，可能會導致性能的降低。
· 全表掃描的效率不一定比索引掃描差，關(guān)鍵看數(shù)據(jù)在數(shù)據(jù)塊上的具體分布。
索引是關(guān)系數(shù)據(jù)庫中用于存放每一條記錄的一種對象，主要目的是加快數(shù)據(jù)的讀取速度和完整性檢查。建立索引是一項技術(shù)性要求高的工作。一般在數(shù)據(jù)庫設計階段的與數(shù)據(jù)庫結(jié)構(gòu)一道考慮。應用系統(tǒng)的性能直接與索引的合理直接有關(guān)。
(1) 單列索引
單列索引是基于單個列所建立的索引。
(2) 復合索引
復合索引是基于兩列或是多列的索引，在同一張表上可以有多個索引，但是要求列的組合必須不同。
(1) 重命名索引
(2) 合并索引
（表使用一段時間后在索引中會產(chǎn)生碎片，此時索引效率會降低，可以選擇重建索引或者合并索引,合并索引方式更好些，無需額外存儲空間，代價較低）
(3) 重建索引
方式一：刪除原來的索引，重新建立索引
當不需要時可以將索引刪除以釋放出硬盤空間。命令如下：
例如：
注：當表結(jié)構(gòu)被刪除時，有其相關(guān)的所有索引也隨之被刪除。
方式二： Alter index 索引名稱 rebuild;
· 通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。
· 索引可以大大加快數(shù)據(jù)的檢索速度，這是創(chuàng)建索引的最主要的原因。
· 可以加速表和表之間的連接，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。
· 在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間。
· 通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。
· 索引的層次不要超過4層。
· 創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加。
· 除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大。
· 當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的維護速度。
· 更新數(shù)據(jù)的時候，系統(tǒng)必須要有額外的時間來同時對索引進行更新，以維持數(shù)據(jù)和索引的一致性。
1) 不恰當?shù)乃饕坏谑聼o補，反而會降低系統(tǒng)性能。因為大量的索引在進行插入、修改和刪除操作時比沒有索引花費更多的系統(tǒng)時間。
1) 應該建索引的列
· 在經(jīng)常需要搜索的列上，可以加快搜索的速度；
· 在作為主鍵的列上，強制該列的唯一性和組織表中數(shù)據(jù)的排列結(jié)構(gòu)；
· 在經(jīng)常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；
· 在經(jīng)常需要根據(jù)范圍進行搜索的列上創(chuàng)建索引，因為索引已經(jīng)排序，其指定的范圍是連續(xù)的；
· 在經(jīng)常需要排序的列上創(chuàng)建索引，因為索引已經(jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
· 在經(jīng)常使用在WHERE子句中的列上面創(chuàng)建索引，加快條件的判斷速度。
2) 不應該建索引的列
· 在大表上建立索引才有意義，小表無意義。
· 對于那些在查詢中很少使用或者參考的列不應該創(chuàng)建索引。
· 對于那些只有很少數(shù)據(jù)值的列也不應該增加索引。比如性別，在查詢的結(jié)果中，結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例，。增加索引，并不能明顯加快檢索速度。
· 對于那些定義為blob數(shù)據(jù)類型的列不應該增加索引。這是因為，這些列的數(shù)據(jù)量要么相當大，要么取值很少。
· 當修改性能遠遠大于檢索性能時，不應該創(chuàng)建索引。
一個表中有幾百萬條數(shù)據(jù)，對某個字段加了索引，但是查詢時性能并沒有什么提高，這主要可能是oracle的索引限制造成的。Oracle的索引有一些索引限制，在這些索引限制發(fā)生的情況下，即使已經(jīng)加了索引，oracle還是會執(zhí)行一次全表掃描，查詢的性能不會比不加索引有所提高，反而可能由于數(shù)據(jù)庫維護索引的系統(tǒng)開銷造成性能更差。
下面的查詢即使在djlx列有索引，查詢語句仍然執(zhí)行一次全表掃描。
把上面的語句改成如下的查詢語句，這樣，在采用基于規(guī)則的優(yōu)化器而不是基于代價的優(yōu)化器（更智能）時，將會使用索引。
特別注意：通過把不等于操作符改成OR條件，就可以使用索引，避免全表掃描。
使用IS NULL或IS NOT NULL同樣會限制索引的使用。因此在建表時，把需要索引的列設成NOT NULL。如果被索引的列在某些行中存在NULL值，就不會使用這個索引（除非索引是一個位圖索引）。
如果不使用基于函數(shù)的索引，那么在SQL語句的WHERE子句中對存在索引的列使用函數(shù)時，會使優(yōu)化器忽略掉這些索引。 下面的查詢不會使用索引（只要它不是基于函數(shù)的索引）
也是比較難于發(fā)現(xiàn)的性能問題之一。比如：bdcs_qlr_xz中的zjh是NVARCHAR2類型,在zjh字段上有索引。如果使用下面的語句將執(zhí)行全表掃描。
因為Oracle會自動把查詢語句改為
特別注意：不匹配的數(shù)據(jù)類型之間比較會讓Oracle自動限制索引的使用,即便對這個查詢執(zhí)行Explain Plan也不能讓您明白為什么做了一次“全表掃描”。
(1) 索引無效
(2) 索引有效
方法如下： Oracle中建立索引，會提高查詢速度： create index 索引名 on 表名(列名); 例如： create index index_userid on tbl_detail(userid); 如何找數(shù)據(jù)庫表的主鍵字段的名稱? SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' a...
在程序中，oracle優(yōu)化器在認為索引效率更高時，會自動調(diào)用索引。
也可以顯式調(diào)用索引：
select
/*+index(A,索引名)*/
*
from
A
where
b=‘’,c='';
一．索引介紹
1.1 索引的創(chuàng)建語法：
CREATE UNIUQE | BITMAP INDEX schema.index_name
ON schema.table_name
(column_name | expression ASC | DESC,
column_name | expression ASC | DESC,...)
TABLESPACE tablespace_name
STORAGE storage_settings
LOGGING | NOLOGGING
COMPUTE STATISTICS
NOCOMPRESS | COMPRESSnn
NOSORT | REVERSE
PARTITION | GLOBAL PARTITIONpartition_setting
相關(guān)說明
1） UNIQUE | BITMAP：指定UNIQUE為唯一值索引，BITMAP為位圖索引，省略為B-Tree索引。
2）column_name | expression ASC | DESC：可以對多列進行聯(lián)合索引，當為expression時即“基于函數(shù)的索引”
3）TABLESPACE：指定存放索引的表空間(索引和原表不在一個表空間時效率更高)
4）STORAGE：可進一步設置表空間的存儲參數(shù)
5）LOGGING | NOLOGGING：是否對索引產(chǎn)生重做日志(對大表盡量使用NOLOGGING來減少占用空間并提高效率)
6）COMPUTE STATISTICS：創(chuàng)建新索引時收集統(tǒng)計信息
7）NOCOMPRESS | COMPRESSnn：是否使用“鍵壓縮”(使用鍵壓縮可以刪除一個鍵列中出現(xiàn)的重復值)
8）NOSORT | REVERSE：NOSORT表示與表中相同的順序創(chuàng)建索引，REVERSE表示相反順序存儲索引值
9）PARTITION | NOPARTITION：可以在分區(qū)表和未分區(qū)表上對創(chuàng)建的索引進行分區(qū)
1.2 索引特點：
第一，通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。
第二，可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。
第三，可以加速表和表之間的連接，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。
第四，在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間。
第五，通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。
1.3 索引不足：
第一，創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加。
第二，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大。
第三，當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的維護速度。
1.4 應該建索引列的特點：
1）在經(jīng)常需要搜索的列上，可以加快搜索的速度；
2）在作為主鍵的列上，強制該列的唯一性和組織表中數(shù)據(jù)的排列結(jié)構(gòu)；
3）在經(jīng)常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；
4）在經(jīng)常需要根據(jù)范圍進行搜索的列上創(chuàng)建索引，因為索引已經(jīng)排序，其指定的范圍是連續(xù)的；
5）在經(jīng)常需要排序的列上創(chuàng)建索引，因為索引已經(jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
6）在經(jīng)常使用在WHERE子句中的列上面創(chuàng)建索引，加快條件的判斷速度。
1.5 不應該建索引列的特點：
第一，對于那些在查詢中很少使用或者參考的列不應該創(chuàng)建索引。這是因為，既然這些列很少使用到，因此有索引或者無索引，并不能提高查詢速度。相反，由于增加了索引，反而降低了系統(tǒng)的維護速度和增大了空間需求。
第二，對于那些只有很少數(shù)據(jù)值的列也不應該增加索引。這是因為，由于這些列的取值很少，例如人事表的性別列，在查詢的結(jié)果中，結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例，即需要在表中搜索的數(shù)據(jù)行的比例很大。增加索引，并不能明顯加快檢索速度。
第三，對于那些定義為blob數(shù)據(jù)類型的列不應該增加索引。這是因為，這些列的數(shù)據(jù)量要么相當大，要么取值很少。
第四，當修改性能遠遠大于檢索性能時，不應該創(chuàng)建索引。這是因為，修改性能和檢索性能是互相矛盾的。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。因此，當修改性能遠遠大于檢索性能時，不應該創(chuàng)建索引。
1.6 限制索引
限制索引是一些沒有經(jīng)驗的開發(fā)人員經(jīng)常犯的錯誤之一。在SQL中有很多陷阱會使一些索引無法使用。下面討論一些常見的問題：
1.6.1 使用不等于操作符（、!=）
下面的查詢即使在cust_rating列有一個索引，查詢語句仍然執(zhí)行一次全表掃描。
select cust_Id,cust_name from customers where cust_rating 'aa';
把上面的語句改成如下的查詢語句，這樣，在采用基于規(guī)則的優(yōu)化器而不是基于代價的優(yōu)化器（更智能）時，將會使用索引。
select cust_Id,cust_name from customers where cust_rating 'aa' or cust_rating 'aa';
特別注意：通過把不等于操作符改成OR條件，就可以使用索引，以避免全表掃描。
1.6.2 使用IS NULL 或IS NOT NULL
使用IS NULL 或IS NOT NULL同樣會限制索引的使用。因為NULL值并沒有被定義。在SQL語句中使用NULL會有很多的麻煩。因此建議開發(fā)人員在建表時，把需要索引的列設成 NOT NULL。如果被索引的列在某些行中存在NULL值，就不會使用這個索引（除非索引是一個位圖索引，關(guān)于位圖索引在稍后在詳細討論）。
1.6.3 使用函數(shù)
如果不使用基于函數(shù)的索引，那么在SQL語句的WHERE子句中對存在索引的列使用函數(shù)時，會使優(yōu)化器忽略掉這些索引。 下面的查詢不會使用索引（只要它不是基于函數(shù)的索引）
select empno,ename,deptno from emp where trunc(hiredate)='01-MAY-81';
把上面的語句改成下面的語句，這樣就可以通過索引進行查找。
select empno,ename,deptno from emp where hiredate(to_date('01-MAY-81')+0.9999);
1.6.4 比較不匹配的數(shù)據(jù)類型
也是比較難于發(fā)現(xiàn)的性能問題之一。 注意下面查詢的例子，account_number是一個VARCHAR2類型,在account_number字段上有索引。
下面的語句將執(zhí)行全表掃描：
select bank_name,address,city,state,zip from banks where account_number = 990354;
Oracle可以自動把where子句變成to_number(account_number)=990354，這樣就限制了索引的使用,改成下面的查詢就可以使用索引：
select bank_name,address,city,state,zip from banks where account_number ='990354';
特別注意：不匹配的數(shù)據(jù)類型之間比較會讓Oracle自動限制索引的使用,即便對這個查詢執(zhí)行Explain Plan也不能讓您明白為什么做了一次“全表掃描”。
1.7 查詢索引
查詢DBA_INDEXES視圖可得到表中所有索引的列表，注意只能通過USER_INDEXES的方法來檢索模式(schema)的索引。訪問USER_IND_COLUMNS視圖可得到一個給定表中被索引的特定列。
1.8 組合索引
當某個索引包含有多個已索引的列時，稱這個索引為組合（concatented）索引。在 Oracle9i引入跳躍式掃描的索引訪問方法之前，查詢只能在有限條件下使用該索引。比如：表emp有一個組合索引鍵，該索引包含了empno、 ename和deptno。在Oracle9i之前除非在where之句中對第一列（empno）指定一個值，否則就不能使用這個索引鍵進行一次范圍掃描。
特別注意：在Oracle9i之前，只有在使用到索引的前導索引時才可以使用組合索引！
1.9 ORACLE ROWID
通過每個行的ROWID，索引Oracle提供了訪問單行數(shù)據(jù)的能力。ROWID其實就是直接指向單獨行的線路圖。如果想檢查重復值或是其他對ROWID本身的引用，可以在任何表中使用和指定rowid列。
create table bug(
seq_id number,
pc_id varchar2 (8),
desc_cont varchar2 (200),
bug_date date,
bug_cont varchar2 (200),
bug_state varchar2 (1),
bug_type varchar2 (2),
remarks varchar2 (200),
finishdate date,
pc_type varchar2 (2),
bug_ip varchar2 (20),
yuyue_date date
);
ALTER TABLE bug ADD (
CONSTRAINT bug PRIMARY KEY (seq_id)
);
這里面的seq_id是自動增長列?，F(xiàn)在因為數(shù)據(jù)太多，為了增加效率。。要用到索引。索引字段有pc_id ，bug_date，bug_type
一 ROWID的概念
存儲了row在數(shù)據(jù)文件中的具 *** 置 位編碼的數(shù)據(jù) A Z a z + 和 /
row在數(shù)據(jù)塊中的存儲方式
SELECT ROWID last_name FROM hr employees WHERE department_id = ;
比如 OOOOOOFFFBBBBBBRRR
OOOOOO data object number 對應dba_objects data_object_id
FFF file# 對應v$datafile file#
BBBBBB block#
RRR row#
Dbms_rowid包
SELECT dbms_rowid rowid_block_number( AAAGFqAABAAAIWEAAA ) from dual;
具體到特定的物理文件
二 索引的概念
類似書的目錄結(jié)構(gòu)
Oracle 的 索引 對象 與表關(guān)聯(lián)的可選對象 提高SQL查詢語句的速度
索引直接指向包含所查詢值的行的位置 減少磁盤I/O
與所索引的表是相互獨立的物理結(jié)構(gòu)
Oracle 自動使用并維護索引 插入 刪除 更新表后 自動更新索引
語法 CREATE INDEX index ON table (column[ column] );
B tree結(jié)構(gòu)(非bitmap)
[一]了解索引的工作原理
表 emp
目標 查詢Frank的工資salary
建立索引 create index emp_name_idx on emp(name);
[試驗]測試索引的作用
運行/rdbms/admin/utlxplan 腳本
建立測試表
create table t as select * from dba_objects;
insert into t select * from t;
create table indextable
as select rownum id owner object_name subobject_name
object_id data_object_id object_type created
from t;
set autotrace trace explain
set timing on
分析表 可以得到cost
查詢 object_name= DBA_INDEXES
在object_name列上建立索引
再查詢
[思考]索引的代價
插入 更新
三 唯一索引
何時創(chuàng)建 當某列任意兩行的值都不相同
當建立Primary Key(主鍵)或者Unique constraint(唯一約束)時 唯一索引將被自動建立
語法 CREATE UNIQUE INDEX index ON table (column);
演示
四 組合索引
何時創(chuàng)建 當兩個或多個列經(jīng)常一起出現(xiàn)在where條件中時 則在這些列上同時創(chuàng)建組合索引
組合索引中列的順序是任意的 也無需相鄰 但是建議將最頻繁訪問的列放在列表的最前面
演示(組合列 單獨列)
五 位圖索引
何時創(chuàng)建
列中有非常多的重復的值時候 例如某列保存了 性別 信息
Where 條件中包含了很多OR操作符
較少的update操作 因為要相應的跟新所有的bitmap
結(jié)構(gòu) 位圖索引使用位圖作為鍵值 對于表中的每一數(shù)據(jù)行位圖包含了TRUE( ) FALSE( ) 或NULL值
優(yōu)點 位圖以一種壓縮格式存放 因此占用的磁盤空間比標準索引要小得多
語法 CREATE BITMAP INDEX index ON table (column[ column] );
掩飾
create table bitmaptable as select * from indextable where owner in( SYS PUBLIC );
分析 查找 建立索引 查找
六 基于函數(shù)的索引
何時創(chuàng)建 在WHERE條件語句中包含函數(shù)或者表達式時
函數(shù)包括 算數(shù)表達式 PL/SQL函數(shù) 程序包函數(shù) SQL函數(shù) 用戶自定義函數(shù)
語法 CREATE INDEX index ON table (FUNCTION(column));
演示
必須要分析表 并且query_rewrite_enabled=TRUE
或者使用提示/*+ INDEX(ic_index)*/
七 反向鍵索引
目的 比如索引值是一個自動增長的列
多個用戶對集中在少數(shù)塊上的索引行進行修改 容易引起資源的爭用 比如對數(shù)據(jù)塊的等待 此時建立反向索引
性能問題
語法
重建為標準索引 反之不行
八 鍵壓縮索引
比如表landscp的數(shù)據(jù)如下
site feature job
Britten Park Rose Bed Prune
Britten Park Rose Bed Mulch
Britten Park Rose Bed Spray
Britten Park Shrub Bed Mulch
Britten Park Shrub Bed Weed
Britten Park Shrub Bed Hoe
……
查詢時 以上 列均在where條件中同時出現(xiàn) 所以建立基于以上 列的組合索引 但是發(fā)現(xiàn)重復值很多 所以考慮壓縮特性
Create index zip_idx
on landscp(site feature job)
press ;
將索引項分成前綴(prefix)和后綴(postfix)兩部分 前兩項被放置到前綴部分
Prefix : Britten Park Rose Bed
Prefix : Britten Park Shrub Bed
實際所以的結(jié)構(gòu)為
Prune
Mulch
Spray
Mulch
Weed
Hoe
特點 組合索引的前綴部分具有非選擇性時 考慮使用壓縮 減少I/O 增加性能
九 索引組織表(IOT)
將表中的數(shù)據(jù)按照索引的結(jié)構(gòu)存儲在索引中 提高查詢速度
犧牲插入更新的性能 換取查詢性能 通常用于數(shù)據(jù)倉庫 提供大量的查詢 極少的插入修改工作
必須指定主鍵 插入數(shù)據(jù)時 會根據(jù)主鍵列進行B樹索引排序 寫入磁盤
十 分區(qū)索引
簇:
A cluster is a group of tables that share the same data blocks because they share mon columns and are often used together
lishixinzhi/Article/program/Oracle/201311/17769
重慶分公司
重慶分公司
