Oracle數(shù)據(jù)訪問和索引的使用

· 通過全表掃描的方式訪問數(shù)據(jù)；

10余年的鄂托克前網(wǎng)站建設(shè)經(jīng)驗，針對設(shè)計、前端、開發(fā)、售后、文案、推廣等六對一服務(wù)，響應(yīng)快，48小時及時工作處理。全網(wǎng)整合營銷推廣的優(yōu)勢是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動調(diào)整鄂托克前建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計，從而大程度地提升瀏覽體驗。創(chuàng)新互聯(lián)建站從事“鄂托克前網(wǎng)站設(shè)計”,“鄂托克前網(wǎng)站推廣”以來，每個客戶項目都認真落實執(zhí)行。

· 通過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會采用全表掃描的方式進行訪問。

· 并行查詢可能會導(dǎo)致優(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訪問表

· 通過索引查找相應(yīng)數(shù)據(jù)行的Rowid，再根據(jù)Rowid查找表中實際數(shù)據(jù)的方式稱為“索引查找”或者“索引掃描”。

· 一個Rowid對應(yīng)一條數(shù)據(jù)行（根據(jù)Rowid查找結(jié)果，僅需要對Rowid相應(yīng)數(shù)據(jù)的數(shù)據(jù)塊進行一次I/O操作），因此該方式屬于“單塊讀”。

· 對于索引，除了存儲索引的數(shù)據(jù)外，還保存有該數(shù)據(jù)對應(yīng)的Rowid信息。

· 索引掃描分為兩步：1）掃描索引確定相應(yīng)的Rowid信息。 2）根據(jù)Rowid從表中獲得對應(yīng)的數(shù)據(jù)。

注意事項：

· 對于選擇性高的數(shù)據(jù)行，索引的使用會提升查詢的性能。但對于DML操作，尤其是批量數(shù)據(jù)的操作，可能會導(dǎo)致性能的降低。

· 全表掃描的效率不一定比索引掃描差，關(guān)鍵看數(shù)據(jù)在數(shù)據(jù)塊上的具體分布。

索引是關(guān)系數(shù)據(jù)庫中用于存放每一條記錄的一種對象，主要目的是加快數(shù)據(jù)的讀取速度和完整性檢查。建立索引是一項技術(shù)性要求高的工作。一般在數(shù)據(jù)庫設(shè)計階段的與數(shù)據(jù)庫結(jié)構(gòu)一道考慮。應(yīng)用系統(tǒng)的性能直接與索引的合理直接有關(guān)。

(1) 單列索引

單列索引是基于單個列所建立的索引。

(2) 復(fù)合索引

復(fù)合索引是基于兩列或是多列的索引，在同一張表上可以有多個索引，但是要求列的組合必須不同。

(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) 應(yīng)該建索引的列

· 在經(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) 不應(yīng)該建索引的列

· 在大表上建立索引才有意義，小表無意義。

· 對于那些在查詢中很少使用或者參考的列不應(yīng)該創(chuàng)建索引。

· 對于那些只有很少數(shù)據(jù)值的列也不應(yīng)該增加索引。比如性別，在查詢的結(jié)果中，結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例，。增加索引，并不能明顯加快檢索速度。

· 對于那些定義為blob數(shù)據(jù)類型的列不應(yīng)該增加索引。這是因為，這些列的數(shù)據(jù)量要么相當大，要么取值很少。

· 當修改性能遠遠大于檢索性能時，不應(yīng)該創(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同樣會限制索引的使用。因此在建表時，把需要索引的列設(shè)成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中怎么建立和使用索引

在程序中，oracle優(yōu)化器在認為索引效率更高時，會自動調(diào)用索引。

也可以顯式調(diào)用索引：

select

/*+index(A,索引名)*/

*

from

A

where

b=‘’,c='';

請教：oracle中索引應(yīng)用

一．索引介紹

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è)置表空間的存儲參數(shù)

5）LOGGING | NOLOGGING：是否對索引產(chǎn)生重做日志(對大表盡量使用NOLOGGING來減少占用空間并提高效率)

6）COMPUTE STATISTICS：創(chuàng)建新索引時收集統(tǒng)計信息

7）NOCOMPRESS | COMPRESSnn：是否使用“鍵壓縮”(使用鍵壓縮可以刪除一個鍵列中出現(xiàn)的重復(fù)值)

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 應(yīng)該建索引列的特點：

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 不應(yīng)該建索引列的特點：

第一，對于那些在查詢中很少使用或者參考的列不應(yīng)該創(chuàng)建索引。這是因為，既然這些列很少使用到，因此有索引或者無索引，并不能提高查詢速度。相反，由于增加了索引，反而降低了系統(tǒng)的維護速度和增大了空間需求。

第二，對于那些只有很少數(shù)據(jù)值的列也不應(yīng)該增加索引。這是因為，由于這些列的取值很少，例如人事表的性別列，在查詢的結(jié)果中，結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例，即需要在表中搜索的數(shù)據(jù)行的比例很大。增加索引，并不能明顯加快檢索速度。

第三，對于那些定義為blob數(shù)據(jù)類型的列不應(yīng)該增加索引。這是因為，這些列的數(shù)據(jù)量要么相當大，要么取值很少。

第四，當修改性能遠遠大于檢索性能時，不應(yīng)該創(chuàng)建索引。這是因為，修改性能和檢索性能是互相矛盾的。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。因此，當修改性能遠遠大于檢索性能時，不應(yī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ā)人員在建表時，把需要索引的列設(shè)成 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之前，只有在使用到索引的前導(dǎo)索引時才可以使用組合索引！

1.9 ORACLE ROWID

通過每個行的ROWID，索引Oracle提供了訪問單行數(shù)據(jù)的能力。ROWID其實就是直接指向單獨行的線路圖。如果想檢查重復(fù)值或是其他對ROWID本身的引用，可以在任何表中使用和指定rowid列。

oracle中，索引是干什么用的？

索引是建立在表的一列或多個列上的輔助對象，目的是加快訪問表中的數(shù)據(jù)；Oracle存儲索引的數(shù)據(jù)結(jié)構(gòu)是B*樹，位圖索引也是如此，只不過是葉子節(jié)點不同B*數(shù)索引；索引由根節(jié)點、分支節(jié)點和葉子節(jié)點組成，上級索引塊包含下級索引塊的索引數(shù)據(jù)，葉節(jié)點包含索引數(shù)據(jù)和確定行實際位置的rowid。

使用索引的目的：

1、加快查詢速度

2、減少I/O操作

3、消除磁盤排序

4、何時使用索引

5、查詢返回的記錄數(shù)

6、排序表40%

7、非排序表

7%

8、表的碎片較多（頻繁增加、刪除）

如何使用oracle中的索引

方法如下： Oracle中建立索引，會提高查詢速度： create index 索引名 on 表名(列名); 例如： create index index_userid on tbl_detail(userid); 如何找數(shù)據(jù)庫表的主鍵字段的名稱? SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' a...

oracle的索引是怎么使用的？

索引就像書的目錄一樣，是為了方便查詢的。一般在數(shù)據(jù)表記錄很多的時候建立索引，oracle的索引有哈希索引和聚簇索引

文章名稱：oracle索引怎么用的 oracle中索引的作用
轉(zhuǎn)載注明：http://www.chinadenli.net/article22/hiphcc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供商城網(wǎng)站、微信小程序、手機網(wǎng)站建設(shè)、網(wǎng)站設(shè)計、企業(yè)網(wǎng)站制作、營銷型網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)