oracle如何運(yùn)用索引,oracle索引怎么用

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

一．索引介紹

創(chuàng)新互聯(lián)主營(yíng)湖北網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,成都app軟件開發(fā)公司,湖北h5成都微信小程序搭建,湖北網(wǎng)站營(yíng)銷推廣歡迎湖北等地區(qū)企業(yè)咨詢

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：可以對(duì)多列進(jìn)行聯(lián)合索引，當(dāng)為expression時(shí)即“基于函數(shù)的索引”

3）TABLESPACE：指定存放索引的表空間(索引和原表不在一個(gè)表空間時(shí)效率更高)

4）STORAGE：可進(jìn)一步設(shè)置表空間的存儲(chǔ)參數(shù)

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

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

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

8）NOSORT | REVERSE：NOSORT表示與表中相同的順序創(chuàng)建索引，REVERSE表示相反順序存儲(chǔ)索引值

9）PARTITION | NOPARTITION：可以在分區(qū)表和未分區(qū)表上對(duì)創(chuàng)建的索引進(jìn)行分區(qū)

1.2 索引特點(diǎn)：

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

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

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

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

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

1.3 索引不足：

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

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

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

1.4 應(yīng)該建索引列的特點(diǎn)：

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

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

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

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

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

6）在經(jīng)常使用在WHERE子句中的列上面創(chuàng)建索引，加快條件的判斷速度。

1.5 不應(yīng)該建索引列的特點(diǎn)：

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

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

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

第四，當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引。這是因?yàn)?，修改性能和檢索性能是互相矛盾的。當(dāng)增加索引時(shí)，會(huì)提高檢索性能，但是會(huì)降低修改性能。當(dāng)減少索引時(shí)，會(huì)提高修改性能，降低檢索性能。因此，當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引。

1.6 限制索引

限制索引是一些沒有經(jīng)驗(yàn)的開發(fā)人員經(jīng)常犯的錯(cuò)誤之一。在SQL中有很多陷阱會(huì)使一些索引無法使用。下面討論一些常見的問題：

1.6.1 使用不等于操作符（、!=）

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

select cust_Id,cust_name from customers where cust_rating 'aa';

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

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同樣會(huì)限制索引的使用。因?yàn)镹ULL值并沒有被定義。在SQL語句中使用NULL會(huì)有很多的麻煩。因此建議開發(fā)人員在建表時(shí)，把需要索引的列設(shè)成 NOT NULL。如果被索引的列在某些行中存在NULL值，就不會(huì)使用這個(gè)索引（除非索引是一個(gè)位圖索引，關(guān)于位圖索引在稍后在詳細(xì)討論）。

1.6.3 使用函數(shù)

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

select empno,ename,deptno from emp where trunc(hiredate)='01-MAY-81';

把上面的語句改成下面的語句，這樣就可以通過索引進(jìn)行查找。

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是一個(gè)VARCHAR2類型,在account_number字段上有索引。

下面的語句將執(zhí)行全表掃描：

select bank_name,address,city,state,zip from banks where account_number = 990354;

Oracle可以自動(dòng)把where子句變成to_number(account_number)=990354，這樣就限制了索引的使用,改成下面的查詢就可以使用索引：

select bank_name,address,city,state,zip from banks where account_number ='990354';

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

1.7 查詢索引

查詢DBA_INDEXES視圖可得到表中所有索引的列表，注意只能通過USER_INDEXES的方法來檢索模式(schema)的索引。訪問USER_IND_COLUMNS視圖可得到一個(gè)給定表中被索引的特定列。

1.8 組合索引

當(dāng)某個(gè)索引包含有多個(gè)已索引的列時(shí)，稱這個(gè)索引為組合（concatented）索引。在 Oracle9i引入跳躍式掃描的索引訪問方法之前，查詢只能在有限條件下使用該索引。比如：表emp有一個(gè)組合索引鍵，該索引包含了empno、 ename和deptno。在Oracle9i之前除非在where之句中對(duì)第一列（empno）指定一個(gè)值，否則就不能使用這個(gè)索引鍵進(jìn)行一次范圍掃描。

特別注意：在Oracle9i之前，只有在使用到索引的前導(dǎo)索引時(shí)才可以使用組合索引！

1.9 ORACLE ROWID

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

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

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

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ù)表記錄很多的時(shí)候建立索引，oracle的索引有哈希索引和聚簇索引

oracle中怎么建立和使用索引

在程序中，oracle優(yōu)化器在認(rèn)為索引效率更高時(shí)，會(huì)自動(dòng)調(diào)用索引。

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

select

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

*

from

A

where

b=‘’,c='';

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

oracle對(duì)于數(shù)據(jù)庫(kù)中的表信息，存儲(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)的表的各種信息。

本文標(biāo)題：oracle如何運(yùn)用索引,oracle索引怎么用
URL標(biāo)題：http://m.jiaotiyi.com/article/dsccsdd.html