oracle怎么用索引 oracle怎么使用索引查詢

Oracle使用(九)_表的創(chuàng)建/約束/索引

表創(chuàng)建標(biāo)準(zhǔn)語法:

創(chuàng)新互聯(lián)憑借在網(wǎng)站建設(shè)、網(wǎng)站推廣領(lǐng)域領(lǐng)先的技術(shù)能力和多年的行業(yè)經(jīng)驗(yàn)，為客戶提供超值的營銷型網(wǎng)站建設(shè)服務(wù)，我們始終認(rèn)為：好的營銷型網(wǎng)站就是好的業(yè)務(wù)員。我們已成功為企業(yè)單位、個(gè)人等客戶提供了成都網(wǎng)站設(shè)計(jì)、做網(wǎng)站服務(wù)，以良好的商業(yè)信譽(yù)，完善的服務(wù)及深厚的技術(shù)力量處于同行領(lǐng)先地位。

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr] , …);

--設(shè)計(jì)要求：建立一張用來存儲(chǔ)學(xué)生信息的表，表中的字段包含了學(xué)生的學(xué)號(hào)、姓名、年齡、入學(xué)日期、年級(jí)、班級(jí)、email等信息，

--并且為grade指定了默認(rèn)值為1，如果在插入數(shù)據(jù)時(shí)不指定grade得值，就代表是一年級(jí)的學(xué)生

--DML是不需要commit的，隱式事務(wù)

create table student

(

stu_id number(10),

name varchar2(20),

age number(2),

hiredate date,

grade varchar2(10) default 1,

classes varchar2(10),

email varchar2(50)

);

-- 注意日期格式要轉(zhuǎn)換，不能是字符串，varchar2類型要用引號(hào)，否則出現(xiàn)類型匹配

--DML 需要收到commit

insert into student values(20211114,'zhangsan',22,to_date('2021-11-14','YYYY-MM-DD'),'2','1',' 123@qq.com ');

insert into student(stu_id,name,age,hiredate,classes,email) values(20211114,'zhangsan',22,to_date('2021-11-14','YYYY-MM-DD'),'1',' 1234@qq.com ');

select * from student;

-- 給表添加列，添加新列時(shí)不允許為not null,因?yàn)榕c舊值不兼容

alter table student add address varchar(100);

-- 刪除列

alter table student drop column address;

--修改列

alter table student modify(email varchar2(100));

正規(guī)表設(shè)計(jì)使用power disinger

--表的重命名

rename student to stu;

-- 表刪除

drop table stu;

**

在刪除表的時(shí)候，經(jīng)常會(huì)遇到多個(gè)表關(guān)聯(lián)的情況(外鍵)，多個(gè)表關(guān)聯(lián)的時(shí)候不能隨意刪除，使用如下三種方式:

2.表的約束（constraint）

約束:創(chuàng)建表時(shí)，指定的插入數(shù)據(jù)的一些規(guī)則

約束是在表上強(qiáng)制執(zhí)行的數(shù)據(jù)校驗(yàn)規(guī)則

Oracle 支持下面五類完整性約束:

1). NOT NULL 非空約束 ---- 插入數(shù)據(jù)時(shí)列值不能空

2). UNIQUE Key 唯一鍵約束 ----限定列唯一標(biāo)識(shí)，唯一鍵的列一般被用作索引

3). PRIMARY KEY 主鍵約束 ----唯一且非空，一張表最好有主鍵，唯一標(biāo)識(shí)一行記錄

4). FOREIGN KEY 外鍵約束---多個(gè)表間的關(guān)聯(lián)關(guān)系，一個(gè)表中的列值，依賴另一張表某主鍵或者唯一鍵

-- 插入部門編號(hào)為50的，部門表并沒有編號(hào)為50的，報(bào)錯(cuò)

insert into emp(empno,ename,deptno) values(9999,'hehe',50);

5). CHECK 自定義檢查約束---根據(jù)用戶需求去限定某些列的值，使用check約束

-- 添加主鍵約束/not null約束/check約束/唯一鍵約束

create table student

(

stu_id number(10) primary key,

name varchar2(20) not null,

age number(3) check(age0 and age126),

hiredate date,

grade varchar2(10) default 1,

classes varchar2(10),

email varchar2(50) unique,

deptno number(2),

);

-- 添加外鍵約束

create table stu

(

stu_id number(10) primary key,

name varchar2(20) not null,

age number(3) check(age0 and age126),

hiredate date,

grade varchar2(10) default 1,

classes varchar2(10),

email varchar2(50) unique,

deptno number(2),

FOREIGN KEY(deptno) references dept(deptno)

);

-- 創(chuàng)建表時(shí)沒添加外鍵約束 也可以修改 其中fk_0001為外鍵名稱

alter table student add constraint fk_0001 foreign key(deptno) references dept(deptno);

索引創(chuàng)建有兩種方式:

組合索引：多個(gè)列組成的索引

--索引：加快數(shù)據(jù)剪碎

create index i_ename on emp(ename);

--當(dāng)創(chuàng)建某個(gè)字段索引后，查詢某個(gè)字段會(huì)自動(dòng)使用到索引

select * from emp where ename = 'SMITH';

--刪除索引 索引名稱也是唯一的

drop index i_ename;

一些概念：

回表：

覆蓋索引

組合索引

最左匹配

oracle怎么通過索引查詢數(shù)據(jù)語句

oracle對于數(shù)據(jù)庫中的表信息，存儲(chǔ)在系統(tǒng)表中。查詢已創(chuàng)建好的表索引，可通過相應(yīng)的sql語句到相應(yīng)的表中進(jìn)行快捷的查詢：\x0d\x0a1. 根據(jù)表名，查詢一張表的索引\x0d\x0a\x0d\x0aselect * from user_indexes where table_name=upper('表名');\x0d\x0a\x0d\x0a2. 根據(jù)索引號(hào)，查詢表索引字段\x0d\x0a\x0d\x0aselect * from user_ind_columns where index_name=('索引名');\x0d\x0a\x0d\x0a3.根據(jù)索引名，查詢創(chuàng)建索引的語句\x0d\x0a\x0d\x0aselect dbms_metadata.get_ddl('INDEX','索引名', ['用戶名']) from dual ; --['用戶名']可省，默認(rèn)為登錄用戶\x0d\x0a\x0d\x0aPS：dbms_metadata.get_ddl還可以得到建表語句，如：\x0d\x0a\x0d\x0aSELECT DBMS_METADATA.GET_DDL('TABLE','表名', ['用戶名']) FROM DUAL ; //取單個(gè)表的建表語句，['用戶名']可不輸入，默認(rèn)為登錄用戶\x0d\x0aSELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u; //取用戶下所有表的建表語句\x0d\x0a\x0d\x0a當(dāng)然，也可以用pl/sql developer工具來查看相關(guān)的表的各種信息。

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

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

· 通過ROWID訪問數(shù)據(jù)；

· 通過索引的方式訪問數(shù)據(jù)；

· Oracle順序讀取表中所有的行，并逐條匹配WHERE限定條件。

· 采用多塊讀的方式進(jìn)行全表掃描，可以有效提高系統(tǒng)的吞吐量，降低I/O次數(shù)。

· 即使創(chuàng)建索引，Oracle也會(huì)根據(jù)CBO的計(jì)算結(jié)果，決定是否使用索引。

注意事項(xiàng)：

· 只有全表掃描時(shí)才可以使用多塊讀。該方式下，單個(gè)數(shù)據(jù)塊僅訪問一次。

· 對于數(shù)據(jù)量較大的表，不建議使用全表掃描進(jìn)行訪問。

· 當(dāng)訪問表中的數(shù)據(jù)量超過數(shù)據(jù)總量的5%—10%時(shí)，通常Oracle會(huì)采用全表掃描的方式進(jìn)行訪問。

· 并行查詢可能會(huì)導(dǎo)致優(yōu)化器選擇全表掃描的方式。1.2ROWID訪問表

· Rowid是數(shù)據(jù)存放在數(shù)據(jù)庫中的物理地址，能夠唯一標(biāo)識(shí)表中的一條數(shù)據(jù)。

· Rowid指出了一條記錄所在的數(shù)據(jù)文件、塊號(hào)以及行號(hào)的位置，因此通過ROWID定位單行數(shù)據(jù)是最快的方法。

注意事項(xiàng)：

· Rowid作為一個(gè)偽列，其數(shù)值并不存儲(chǔ)在數(shù)據(jù)庫中，當(dāng)查詢時(shí)才進(jìn)行計(jì)算。

· Rowid除了在同一集簇中可能不唯一外，每條記錄的Rowid唯一。1.3 INDEX訪問表

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

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

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

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

注意事項(xiàng)：

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

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

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

(1) 單列索引

單列索引是基于單個(gè)列所建立的索引。

(2) 復(fù)合索引

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

(1) 重命名索引

(2) 合并索引

（表使用一段時(shí)間后在索引中會(huì)產(chǎn)生碎片，此時(shí)索引效率會(huì)降低，可以選擇重建索引或者合并索引,合并索引方式更好些，無需額外存儲(chǔ)空間，代價(jià)較低）

(3) 重建索引

方式一：刪除原來的索引，重新建立索引

當(dāng)不需要時(shí)可以將索引刪除以釋放出硬盤空間。命令如下：

例如：

注：當(dāng)表結(jié)構(gòu)被刪除時(shí)，有其相關(guān)的所有索引也隨之被刪除。

方式二： Alter index 索引名稱 rebuild;

· 通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。

· 索引可以大大加快數(shù)據(jù)的檢索速度，這是創(chuàng)建索引的最主要的原因。

· 可以加速表和表之間的連接，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。

· 在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)，同樣可以顯著減少查詢中分組和排序的時(shí)間。

· 通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。

· 索引的層次不要超過4層。

· 創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加。

· 除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會(huì)更大。

· 當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。

· 更新數(shù)據(jù)的時(shí)候，系統(tǒng)必須要有額外的時(shí)間來同時(shí)對索引進(jìn)行更新，以維持?jǐn)?shù)據(jù)和索引的一致性。

1) 不恰當(dāng)?shù)乃饕坏谑聼o補(bǔ)，反而會(huì)降低系統(tǒng)性能。因?yàn)榇罅康乃饕谶M(jìn)行插入、修改和刪除操作時(shí)比沒有索引花費(fèi)更多的系統(tǒng)時(shí)間。

1) 應(yīng)該建索引的列

· 在經(jīng)常需要搜索的列上，可以加快搜索的速度；

· 在作為主鍵的列上，強(qiáng)制該列的唯一性和組織表中數(shù)據(jù)的排列結(jié)構(gòu)；

· 在經(jīng)常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；

· 在經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，其指定的范圍是連續(xù)的；

· 在經(jīng)常需要排序的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時(shí)間；

· 在經(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)該增加索引。這是因?yàn)?，這些列的數(shù)據(jù)量要么相當(dāng)大，要么取值很少。

· 當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引。

一個(gè)表中有幾百萬條數(shù)據(jù)，對某個(gè)字段加了索引，但是查詢時(shí)性能并沒有什么提高，這主要可能是oracle的索引限制造成的。Oracle的索引有一些索引限制，在這些索引限制發(fā)生的情況下，即使已經(jīng)加了索引，oracle還是會(huì)執(zhí)行一次全表掃描，查詢的性能不會(huì)比不加索引有所提高，反而可能由于數(shù)據(jù)庫維護(hù)索引的系統(tǒng)開銷造成性能更差。

下面的查詢即使在djlx列有索引，查詢語句仍然執(zhí)行一次全表掃描。

把上面的語句改成如下的查詢語句，這樣，在采用基于規(guī)則的優(yōu)化器而不是基于代價(jià)的優(yōu)化器（更智能）時(shí)，將會(huì)使用索引。

特別注意：通過把不等于操作符改成OR條件，就可以使用索引，避免全表掃描。

使用IS NULL或IS NOT NULL同樣會(huì)限制索引的使用。因此在建表時(shí)，把需要索引的列設(shè)成NOT NULL。如果被索引的列在某些行中存在NULL值，就不會(huì)使用這個(gè)索引（除非索引是一個(gè)位圖索引）。

如果不使用基于函數(shù)的索引，那么在SQL語句的WHERE子句中對存在索引的列使用函數(shù)時(shí)，會(huì)使優(yōu)化器忽略掉這些索引。 下面的查詢不會(huì)使用索引（只要它不是基于函數(shù)的索引）

也是比較難于發(fā)現(xiàn)的性能問題之一。比如：bdcs_qlr_xz中的zjh是NVARCHAR2類型,在zjh字段上有索引。如果使用下面的語句將執(zhí)行全表掃描。

因?yàn)镺racle會(huì)自動(dòng)把查詢語句改為

特別注意：不匹配的數(shù)據(jù)類型之間比較會(huì)讓Oracle自動(dòng)限制索引的使用,即便對這個(gè)查詢執(zhí)行Explain Plan也不能讓您明白為什么做了一次“全表掃描”。

(1) 索引無效

(2) 索引有效

在Oracle中合理創(chuàng)建數(shù)據(jù)庫的索引

在Oracle數(shù)據(jù)庫中 創(chuàng)建索引雖然比較簡單 但是要合理的創(chuàng)建索引則比較困難了 筆者認(rèn)為 在創(chuàng)建索引時(shí)要做到三個(gè)適當(dāng) 即在適當(dāng)?shù)谋砩?適當(dāng)?shù)牧猩蟿?chuàng)建適當(dāng)數(shù)量的索引 雖然這可以通過一句話來概括優(yōu)化的索引的基本準(zhǔn)則 但是要做到這一點(diǎn)的話 需要數(shù)據(jù)庫管理員做出很大的努力 具體的來說 要做到這個(gè)三個(gè)適當(dāng)有如下幾個(gè)要求

一 根據(jù)表的大小來創(chuàng)建索引

雖然給表創(chuàng)建索引 可以提高查詢的效率 但是數(shù)據(jù)庫管理員需要注意的是 索引也需要一定的開銷的 為此并不是說給所有的表都創(chuàng)建索引 那么就可以提高數(shù)據(jù)庫的性能 這個(gè)認(rèn)識(shí)是錯(cuò)誤的 恰恰相反 如果不管三七二十一 給所有的表都創(chuàng)建了索引 那么其反而會(huì)給數(shù)據(jù)庫的性能造成負(fù)面的影響 因?yàn)榇藭r(shí)濫用索引的開銷可能已經(jīng)遠(yuǎn)遠(yuǎn)大于由此帶來的性能方面的收益 所以筆者認(rèn)為 數(shù)據(jù)庫管理員首先需要做到 為合適的表來建立索引 而不是為所有的表建立索引

一般來說 不需要為比較小的表創(chuàng)建索引 如在一個(gè)ERP系統(tǒng)的數(shù)據(jù)庫中 department表用來存儲(chǔ)企業(yè)部門的信息 一般企業(yè)的部分也就十幾個(gè) 最多不會(huì)超過一百個(gè) 這 條記錄對于人來說 可能算是比較多了 但是對于計(jì)算機(jī)來說 這給他塞塞牙縫都還不夠 所以 對類似的小表沒有必要建立索引 因?yàn)榧词菇⒘怂饕?其性能也不會(huì)得到很大的改善 相反索引建立的開銷 如維護(hù)成本等等 要比這個(gè)要大 也就是說 付出的要比得到的多 顯然違反常理

另外 就是對于超大的表 也不一定要建立索引 有些表雖然比較大 記錄數(shù)量非常的多 但是此時(shí)為這個(gè)表建立索引并一定的合適 如系統(tǒng)中有一張表 其主要用來保存數(shù)據(jù)庫中的一些變更信息 往往這些信息只給數(shù)據(jù)庫管理員使用 此時(shí)為這張表建立索引的話 反而不合適 因?yàn)檫@張表很少用到 只有在出問題的時(shí)候才需要查看 其次其即使查看 需要查詢的紀(jì)錄也不會(huì)很多 可能就是最近一周的更新記錄等等 對于對于一些超大的表 建立索引有時(shí)候往往不能夠達(dá)到預(yù)計(jì)的效果 而且在打表上建立索引 其索引的開銷要比普通的表大的多 那么到底是否給大表建立索引呢?筆者認(rèn)為 主要是看兩個(gè)方面的內(nèi)容 首先是需要關(guān)注一下 在這張大表中經(jīng)常需要查詢的記錄數(shù)量 一般來說 如果經(jīng)常需要查詢的數(shù)據(jù)不超過 %到 %的話 那就沒有必要為其建立索引的必要 因?yàn)榇藭r(shí)建立索引的開銷可能要比性能的改善大的多 這個(gè)比例只是一個(gè)經(jīng)驗(yàn)的數(shù)據(jù) 如果數(shù)據(jù)庫管理員需要得出一個(gè)比較精確的結(jié)論 那么就需要進(jìn)行測試分析 即數(shù)據(jù)庫管理員需要測試一下全表掃描的時(shí)間 看看其是否比建立索引后的查詢時(shí)間要長或者短 如果是長的話 則說明有建立索引的必要 但是如果沒有的話 則說明還是全表掃描速度來的快 此時(shí)也就沒有必要建立索引了

總之 在考慮是否該為表建立索引時(shí) 一般來說小表沒有建立索引的必要 而對于打表的話 則需要進(jìn)行實(shí)際情況實(shí)際分析 簡單一點(diǎn)的 可以根據(jù)大致的比率來確定 如果要精確一點(diǎn)的 則可以進(jìn)行全表掃描性能分析 以判斷建立索引后是否真的如預(yù)期那樣改善了數(shù)據(jù)庫性能

二 根據(jù)列的特征來創(chuàng)建索引

列的特點(diǎn)不同 索引創(chuàng)建的效果也不同 數(shù)據(jù)庫管理員需要了解為哪些列創(chuàng)建索引可以起到事倍功半的效果 同時(shí)也需要了解為哪些列創(chuàng)建索引反而起到的是事倍功半的效果 這有利于他們了解到底給為怎么樣的字段建立索引

根據(jù)筆者的經(jīng)驗(yàn) 往往為如下特征的列創(chuàng)建索引能夠起到比較明顯的效果 如對于一些重復(fù)內(nèi)容比較少的列 特別是對于那些定義了唯一約束的列 在這些列上建立索引 往往可以起到非常不錯(cuò)的效果 如對于一些null值的列與非Null值的列混合情況下 如果用戶需要經(jīng)常查詢所有的非Null值記錄的列 則最好為其設(shè)置索引 如果經(jīng)常需要多表連接查詢 在用與連接的列上設(shè)置索引可以達(dá)到事半功倍的效果

可見 索引設(shè)置的是否恰當(dāng) 不僅跟數(shù)據(jù)庫設(shè)計(jì)架構(gòu)有關(guān) 而且還跟企業(yè)的經(jīng)濟(jì)業(yè)務(wù)相關(guān) 為此 對于一些套裝軟件 雖然一開始數(shù)據(jù)庫管理員已經(jīng)做了索引的優(yōu)化工作 但是隨著后來經(jīng)濟(jì)數(shù)據(jù)的增加 這個(gè)索引的效果會(huì)越來越打折扣 這主要是因?yàn)橛涗浀谋砘绊懙搅怂饕齼?yōu)化的效果 所以筆者建議各位數(shù)據(jù)庫管理員 即使采用的是大牌軟件公司的套裝軟件 也需要隔一段時(shí)間 如一年 對數(shù)據(jù)庫的索引進(jìn)行優(yōu)化 該去掉的去掉 該調(diào)整的調(diào)整 以提高數(shù)據(jù)庫的性能

如在數(shù)據(jù)庫中有一張表是用來保存用戶信息的 其中有個(gè)字段身份證號(hào)碼 這是一個(gè)唯一的字段 在數(shù)據(jù)庫設(shè)計(jì)時(shí) 給這個(gè)字段創(chuàng)建了索引 但是當(dāng)這個(gè)數(shù)據(jù)庫投入使用之后 用戶不怎么輸入用戶的身份證號(hào)碼 而且平時(shí)也基本不按這個(gè)號(hào)碼來進(jìn)行查詢 當(dāng)記錄月來月多時(shí) 這個(gè)身份證號(hào)碼上的索引字段不但不能夠改善數(shù)據(jù)庫的查詢性能 反而成了雞肋 對于這些有很多NULL值的列 而且不會(huì)經(jīng)常查詢所有的非NULL值記錄的列 數(shù)據(jù)庫管理員要下決心 即使清除這些列上的索引

所以說索引的優(yōu)化與調(diào)整是一個(gè)動(dòng)態(tài)的過程 并不是說數(shù)據(jù)庫設(shè)計(jì)好之后就不需要經(jīng)過調(diào)整 數(shù)據(jù)庫管理員往往需要根據(jù)記錄的變化情況 來進(jìn)行適當(dāng)?shù)淖兏?以提高索引的效果

三 在一個(gè)表上創(chuàng)建多少索引合適?

雖然說 在表上創(chuàng)建索引的數(shù)量沒有限制 但是決不是越多越好 也就是說 在創(chuàng)建索引這項(xiàng)事情上 + 〉 往往不成立 有時(shí)候 創(chuàng)建索引越多 其可能會(huì)得到適得其反的效果 那么在一個(gè)表上 到底給創(chuàng)建多少索引合適呢?這個(gè)沒有一個(gè)明確的標(biāo)準(zhǔn) 而是需要數(shù)據(jù)庫管理員根據(jù)實(shí)際的用途以及數(shù)據(jù)庫中記錄的情況 來進(jìn)行判斷

通常來說 表的索引越多 其查詢的速度也就越快 但是 表的更新速度則會(huì)降低 這主要是因?yàn)楸淼母?如往表中插入一條記錄)速度 反而隨著索引的增加而增加 這主要是因?yàn)?在更新記錄的同時(shí)需要更新相關(guān)的索引信息 為此 到底在表中創(chuàng)建多少索引合適 就需要在這個(gè)更新速度與查詢速度之間取得一個(gè)均衡點(diǎn) 如對于一些數(shù)據(jù)倉庫或者決策型數(shù)據(jù)庫系統(tǒng) 其主要用來進(jìn)行查詢 相關(guān)的記錄往往是在數(shù)據(jù)庫初始化的時(shí)候倒入 此時(shí) 設(shè)置的索引多一點(diǎn) 可以提高數(shù)據(jù)庫的查詢性能 同時(shí)因?yàn)橛涗洸辉趺锤?所以索引比較多的情況下 也不會(huì)影響到更新的速度 即使在起初的時(shí)候需要導(dǎo)入大量的數(shù)據(jù) 此時(shí)也可以先將索引禁用掉 等到數(shù)據(jù)導(dǎo)入完畢后 再啟用索引 可以通過這種方式來減少索引對數(shù)據(jù)更新的影響 相反 如果那些表中經(jīng)常需要更新記錄 如一些事務(wù)型的應(yīng)用系統(tǒng) 數(shù)據(jù)更新操作是家常便飯的事情 此時(shí)如果在一張表中建立過多的索引 則會(huì)影響到更新的速度 由于更新操作比較頻繁 所以對其的負(fù)面影響 要比查詢效率提升要大的多 此時(shí)就需要限制索引的數(shù)量 只在一些必要的字段上建立索引

筆者在平時(shí)數(shù)據(jù)庫優(yōu)化時(shí) 往往會(huì)根據(jù)這些表的用途來為列設(shè)置索引 可以查詢相關(guān)的動(dòng)態(tài)視圖 看看對于這張表的操作 是更新操作(包括更新 刪除 插入等等)占的比例大 還是查詢操作占的比例大 當(dāng)過多的索引已經(jīng)影響到更新操作的速度時(shí) 則數(shù)據(jù)庫管理員就需要先禁用某些索引 以提高數(shù)據(jù)庫的性能

lishixinzhi/Article/program/Oracle/201311/18407

Oracle數(shù)據(jù)庫中的索引詳解

一 ROWID的概念

存儲(chǔ)了row在數(shù)據(jù)文件中的具 *** 置 位編碼的數(shù)據(jù) A Z a z + 和 /

row在數(shù)據(jù)塊中的存儲(chǔ)方式

SELECT ROWID last_name FROM hr employees WHERE department_id = ;

比如 OOOOOOFFFBBBBBBRRR

OOOOOO data object number 對應(yīng)dba_objects data_object_id

FFF file# 對應(yīng)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

與所索引的表是相互獨(dú)立的物理結(jié)構(gòu)

Oracle 自動(dòng)使用并維護(hù)索引 插入 刪除 更新表后 自動(dòng)更新索引

語法 CREATE INDEX index ON table (column[ column] );

B tree結(jié)構(gòu)(非bitmap)

[一]了解索引的工作原理

表 emp

目標(biāo) 查詢Frank的工資salary

建立索引 create index emp_name_idx on emp(name);

　[試驗(yàn)]測試索引的作用

運(yùn)行/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列上建立索引

再查詢

[思考]索引的代價(jià)

插入 更新

　三 唯一索引

何時(shí)創(chuàng)建 當(dāng)某列任意兩行的值都不相同

當(dāng)建立Primary Key(主鍵)或者Unique constraint(唯一約束)時(shí) 唯一索引將被自動(dòng)建立

語法 CREATE UNIQUE INDEX index ON table (column);

演示

四 組合索引

何時(shí)創(chuàng)建 當(dāng)兩個(gè)或多個(gè)列經(jīng)常一起出現(xiàn)在where條件中時(shí) 則在這些列上同時(shí)創(chuàng)建組合索引

組合索引中列的順序是任意的 也無需相鄰 但是建議將最頻繁訪問的列放在列表的最前面

演示(組合列 單獨(dú)列)

　五 位圖索引

何時(shí)創(chuàng)建

列中有非常多的重復(fù)的值時(shí)候 例如某列保存了 性別 信息

Where 條件中包含了很多OR操作符

較少的update操作 因?yàn)橐鄳?yīng)的跟新所有的bitmap

結(jié)構(gòu) 位圖索引使用位圖作為鍵值 對于表中的每一數(shù)據(jù)行位圖包含了TRUE( ) FALSE( ) 或NULL值

優(yōu)點(diǎn) 位圖以一種壓縮格式存放 因此占用的磁盤空間比標(biāo)準(zhǔn)索引要小得多

語法 CREATE BITMAP INDEX index ON table (column[ column] );

掩飾

create table bitmaptable as select * from indextable where owner in( SYS PUBLIC );

分析 查找 建立索引 查找

　六 基于函數(shù)的索引

何時(shí)創(chuàng)建 在WHERE條件語句中包含函數(shù)或者表達(dá)式時(shí)

函數(shù)包括 算數(shù)表達(dá)式 PL/SQL函數(shù) 程序包函數(shù) SQL函數(shù) 用戶自定義函數(shù)

語法 CREATE INDEX index ON table (FUNCTION(column));

演示

必須要分析表 并且query_rewrite_enabled=TRUE

或者使用提示/*+ INDEX(ic_index)*/

七 反向鍵索引

目的 比如索引值是一個(gè)自動(dòng)增長的列

多個(gè)用戶對集中在少數(shù)塊上的索引行進(jìn)行修改 容易引起資源的爭用 比如對數(shù)據(jù)塊的等待 此時(shí)建立反向索引

性能問題

語法

重建為標(biāo)準(zhǔn)索引 反之不行

　八 鍵壓縮索引

比如表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

……

查詢時(shí) 以上 列均在where條件中同時(shí)出現(xiàn) 所以建立基于以上 列的組合索引 但是發(fā)現(xiàn)重復(fù)值很多 所以考慮壓縮特性

Create index zip_idx

on landscp(site feature job)

press ;

將索引項(xiàng)分成前綴(prefix)和后綴(postfix)兩部分 前兩項(xiàng)被放置到前綴部分

Prefix : Britten Park Rose Bed

Prefix : Britten Park Shrub Bed

實(shí)際所以的結(jié)構(gòu)為

Prune

Mulch

Spray

Mulch

Weed

Hoe

特點(diǎn) 組合索引的前綴部分具有非選擇性時(shí) 考慮使用壓縮 減少I/O 增加性能

九 索引組織表(IOT)

將表中的數(shù)據(jù)按照索引的結(jié)構(gòu)存儲(chǔ)在索引中 提高查詢速度

犧牲插入更新的性能 換取查詢性能 通常用于數(shù)據(jù)倉庫 提供大量的查詢 極少的插入修改工作

必須指定主鍵 插入數(shù)據(jù)時(shí) 會(huì)根據(jù)主鍵列進(jìn)行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

Oracle創(chuàng)建索引SQL簡單的例子，在表中的指定字段和如何使用索引呢？

create index index_name on table_name(column_name) ;\x0d\x0a只要你查詢使用到建了索引的字段，一般都會(huì)用到索引。 \x0d\x0a \x0d\x0a--創(chuàng)建表\x0d\x0acreate table aaa\x0d\x0a(\x0d\x0a a number,\x0d\x0a b number\x0d\x0a);\x0d\x0a--創(chuàng)建索引\x0d\x0acreate index idx_a on aaa (a);\x0d\x0a--使用索引\x0d\x0aselect * from aaa where a=1;\x0d\x0a這句查詢就會(huì)使用索引 idx_a

網(wǎng)站題目：oracle怎么用索引 oracle怎么使用索引查詢
當(dāng)前地址：http://muchs.cn/article12/hheogc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供用戶體驗(yàn)、軟件開發(fā)、ChatGPT、網(wǎng)站建設(shè)、品牌網(wǎng)站建設(shè)、網(wǎng)站維護(hù)

廣告

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