oracle中怎么建立和使用索引

在程序中，oracle優化器在認為索引效率更高時，會自動調用索引。

創新互聯公司專注于網站建設，為客戶提供成都做網站、成都網站建設、成都外貿網站建設、網頁設計開發服務，多年建網站服務經驗，各類網站都可以開發，品牌網站建設，公司官網，公司展示網站，網站設計，建網站費用，建網站多少錢，價格優惠，收費合理。

也可以顯式調用索引：

select

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

*

from

A

where

b=‘’,c='';

oracle的索引是怎么使用的？

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

oracle怎么通過索引查詢數據語句

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

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

表創建標準語法:

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr] , …);

--設計要求：建立一張用來存儲學生信息的表，表中的字段包含了學生的學號、姓名、年齡、入學日期、年級、班級、email等信息，

--并且為grade指定了默認值為1，如果在插入數據時不指定grade得值，就代表是一年級的學生

--DML是不需要commit的，隱式事務

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)

);

-- 注意日期格式要轉換，不能是字符串，varchar2類型要用引號，否則出現類型匹配

--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;

-- 給表添加列，添加新列時不允許為not null,因為與舊值不兼容

alter table student add address varchar(100);

-- 刪除列

alter table student drop column address;

--修改列

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

正規表設計使用power disinger

--表的重命名

rename student to stu;

-- 表刪除

drop table stu;

**

在刪除表的時候，經常會遇到多個表關聯的情況(外鍵)，多個表關聯的時候不能隨意刪除，使用如下三種方式:

2.表的約束（constraint）

約束:創建表時，指定的插入數據的一些規則

約束是在表上強制執行的數據校驗規則

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

1). NOT NULL 非空約束 ---- 插入數據時列值不能空

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

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

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

-- 插入部門編號為50的，部門表并沒有編號為50的，報錯

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

5). CHECK 自定義檢查約束---根據用戶需求去限定某些列的值，使用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)

);

-- 創建表時沒添加外鍵約束 也可以修改 其中fk_0001為外鍵名稱

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

索引創建有兩種方式:

組合索引：多個列組成的索引

--索引：加快數據剪碎

create index i_ename on emp(ename);

--當創建某個字段索引后，查詢某個字段會自動使用到索引

select * from emp where ename = 'SMITH';

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

drop index i_ename;

一些概念：

回表：

覆蓋索引

組合索引

最左匹配

Oracle數據訪問和索引的使用

· 通過全表掃描的方式訪問數據；

· 通過ROWID訪問數據；

· 通過索引的方式訪問數據；

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

· 采用多塊讀的方式進行全表掃描，可以有效提高系統的吞吐量，降低I/O次數。

· 即使創建索引，Oracle也會根據CBO的計算結果，決定是否使用索引。

注意事項：

· 只有全表掃描時才可以使用多塊讀。該方式下，單個數據塊僅訪問一次。

· 對于數據量較大的表，不建議使用全表掃描進行訪問。

· 當訪問表中的數據量超過數據總量的5%—10%時，通常Oracle會采用全表掃描的方式進行訪問。

· 并行查詢可能會導致優化器選擇全表掃描的方式。1.2ROWID訪問表

· Rowid是數據存放在數據庫中的物理地址，能夠唯一標識表中的一條數據。

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

注意事項：

· Rowid作為一個偽列，其數值并不存儲在數據庫中，當查詢時才進行計算。

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

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

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

· 對于索引，除了存儲索引的數據外，還保存有該數據對應的Rowid信息。

· 索引掃描分為兩步：1）掃描索引確定相應的Rowid信息。 2）根據Rowid從表中獲得對應的數據。

注意事項：

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

· 全表掃描的效率不一定比索引掃描差，關鍵看數據在數據塊上的具體分布。

索引是關系數據庫中用于存放每一條記錄的一種對象，主要目的是加快數據的讀取速度和完整性檢查。建立索引是一項技術性要求高的工作。一般在數據庫設計階段的與數據庫結構一道考慮。應用系統的性能直接與索引的合理直接有關。

(1) 單列索引

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

(2) 復合索引

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

(1) 重命名索引

(2) 合并索引

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

(3) 重建索引

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

當不需要時可以將索引刪除以釋放出硬盤空間。命令如下：

例如：

注：當表結構被刪除時，有其相關的所有索引也隨之被刪除。

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

· 通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性。

· 索引可以大大加快數據的檢索速度，這是創建索引的最主要的原因。

· 可以加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義。

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

· 通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。

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

· 創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加。

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

· 當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了數據的維護速度。

· 更新數據的時候，系統必須要有額外的時間來同時對索引進行更新，以維持數據和索引的一致性。

1) 不恰當的索引不但于事無補，反而會降低系統性能。因為大量的索引在進行插入、修改和刪除操作時比沒有索引花費更多的系統時間。

1) 應該建索引的列

· 在經常需要搜索的列上，可以加快搜索的速度；

· 在作為主鍵的列上，強制該列的唯一性和組織表中數據的排列結構；

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

· 在經常需要根據范圍進行搜索的列上創建索引，因為索引已經排序，其指定的范圍是連續的；

· 在經常需要排序的列上創建索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；

· 在經常使用在WHERE子句中的列上面創建索引，加快條件的判斷速度。

2) 不應該建索引的列

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

· 對于那些在查詢中很少使用或者參考的列不應該創建索引。

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

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

· 當修改性能遠遠大于檢索性能時，不應該創建索引。

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

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

把上面的語句改成如下的查詢語句，這樣，在采用基于規則的優化器而不是基于代價的優化器（更智能）時，將會使用索引。

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

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

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

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

因為Oracle會自動把查詢語句改為

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

(1) 索引無效

(2) 索引有效

網站名稱：oracle怎么使用索引,oracle 如何建立索引
網站URL：http://vcdvsql.cn/article0/hspeio.html

成都網站建設公司_創新互聯，為您提供網頁設計公司、標簽優化、電子商務、網站制作、微信公眾號、搜索引擎優化

廣告

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