Oracle索引技術(shù)之如何建立最佳索引

怎樣建立最佳索引?1、明確地創(chuàng)建索引create index index_name on table_name(field_name)tablespace tablespace_namepctfree 5initrans 2maxtrans 255storage(minextents 1maxextents 16382pctincrease 0); 2、創(chuàng)建基于函數(shù)的索引常用與UPPER、LOWER、TO_CHAR(date)等函數(shù)分類上，例：create index idx_func on emp(UPPER(ename)) tablespace tablespace_name; 3、創(chuàng)建位圖索引對(duì)基數(shù)較小，且基數(shù)相對(duì)穩(wěn)定的列建立索引時(shí)，首先應(yīng)該考慮位圖索引，例：create bitmap index idx_bitm on class (classno) tablespace tablespace_name; 4、明確地創(chuàng)建唯一索引可以用create unique index語(yǔ)句來創(chuàng)建唯一索引，例：create unique index dept_unique_idx on dept(dept_no) tablespace idx_1; 5、創(chuàng)建與約束相關(guān)的索引可以用using index字句，為與unique和primary key約束相關(guān)的索引，例：alter table table_nameadd constraint PK_primary_keyname primary key(field_name)using index tablespace tablespace_name; 如何創(chuàng)建局部區(qū)索引？1)基礎(chǔ)表必須是分區(qū)表2)分區(qū)數(shù)量與基礎(chǔ)表相同3)每個(gè)索引分區(qū)的子分區(qū)數(shù)量與相應(yīng)的基礎(chǔ)表分區(qū)相同4)基礎(chǔ)表的自分區(qū)中的行的索引項(xiàng)，被存儲(chǔ)在該索引的相應(yīng)的自分區(qū)中，例如create index TG_CDR04_SERV_ID_IDX on TG_CDR04(SERV_ID)Pctfree 5Tablespace TBS_AK01_IDXStorage(MaxExtents 32768PctIncrease 0FreeLists 1FreeList Groups 1)local/ 如何創(chuàng)建范圍分區(qū)的全局索引？基礎(chǔ)表可以是全局表和分區(qū)表create index idx_start_date on tg_cdr01(start_date)global partition by range(start_date)(partition p01_idx vlaues less than ('0106')partition p01_idx vlaues less than ('0111')...partition p01_idx vlaues less than ('0401'))/ 如何重建現(xiàn)存的索引？重建現(xiàn)存的索引的當(dāng)前時(shí)刻不會(huì)影響查詢重建索引可以刪除額外的數(shù)據(jù)塊提高索引查詢效率alter index idx_name rebuild nologging;對(duì)于分區(qū)索引alter index idx_name rebuild partition partition_name nologging; 刪除索引的原因？1)不再需要的索引2)索引沒有針對(duì)其相關(guān)的表所發(fā)布的查詢提供所期望的性能改善3)應(yīng)用沒有用該索引來查詢數(shù)據(jù)4)該索引無(wú)效，必須在重建之前刪除該索引5)該索引已經(jīng)變的太碎了，必須在重建之前刪除該索引語(yǔ)句：drop index idx_name;drop index idx_name partition partition_name; 建立索引的代價(jià)？基礎(chǔ)表維護(hù)時(shí)，系統(tǒng)要同時(shí)維護(hù)索引，不合理的索引將嚴(yán)重影響系統(tǒng)資源，主要表現(xiàn)在CPU和I/O上。插入、更新、刪除數(shù)據(jù)產(chǎn)生大量db file sequential read鎖等待。

創(chuàng)新互聯(lián)建站技術(shù)團(tuán)隊(duì)十載來致力于為客戶提供成都網(wǎng)站設(shè)計(jì)、網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)、品牌網(wǎng)站設(shè)計(jì)、成都全網(wǎng)營(yíng)銷、搜索引擎SEO優(yōu)化等服務(wù)。經(jīng)過多年發(fā)展，公司擁有經(jīng)驗(yàn)豐富的技術(shù)團(tuán)隊(duì)，先后服務(wù)、推廣了近1000家網(wǎng)站，包括各類中小企業(yè)、企事單位、高校等機(jī)構(gòu)單位。

Oracle 10g中如何創(chuàng)建表

從數(shù)據(jù)庫(kù)用戶角度看，數(shù)據(jù)庫(kù)中的數(shù)據(jù)是以表、視圖等方式儲(chǔ)存的。那么用戶自己是怎么創(chuàng)建表的呢？簡(jiǎn)單的有兩種方式，一種是用戶可以在Oracle提供的SQL*Plus中使用CREATE TABLE語(yǔ)句創(chuàng)建表，也可以通過OEM圖形化工具創(chuàng)建表。

工具/材料

電腦

Oracle 10g

CREATE TABLE創(chuàng)建表

01

首先用戶必須要有CREATE TABLE系統(tǒng)權(quán)限，并且只能在自己的模式中創(chuàng)建表，圖中的例子使用CREATE TABLE語(yǔ)句在當(dāng)前模式中創(chuàng)建了一個(gè)共有5列的表：表的名稱為employees，包含 emp_id， name， job，age，dep_id 等5列，后面的number、 varchar2、等為數(shù)據(jù)類型，括號(hào)里面為長(zhǎng)度。

02

如果想要在其他模式中創(chuàng)建表，則必須在表名前加上模式名，并且用戶還要有CREATE ANY TABLE系統(tǒng)權(quán)限，例如圖中語(yǔ)句將在SYSTEM模式中創(chuàng)建employees1表

OEM創(chuàng)建表

01

在OEM主界面中選擇【管理】選項(xiàng)卡，進(jìn)入如圖所示的數(shù)據(jù)庫(kù)管理界面；然后在“數(shù)據(jù)庫(kù)對(duì)象”中單擊【表】選項(xiàng)。

02

系統(tǒng)進(jìn)入管理表界面，在該頁(yè)面中單擊【創(chuàng)建】按鈕，進(jìn)入如圖所示的“創(chuàng)建表的組織形式”界面。在該界面中用戶可以選擇創(chuàng)建的表類型---標(biāo)準(zhǔn)表、臨時(shí)表或索引表。

03

在選擇創(chuàng)建的表類型后，單擊【繼續(xù)】按鈕，進(jìn)入如圖所示的“創(chuàng)建表”界面。在“名稱”文本框中輸入要?jiǎng)?chuàng)建表的名稱。系統(tǒng)在“方案”文本框中顯示當(dāng)前模式，這表示新創(chuàng)建的表將屬于該模式。

04

在“創(chuàng)建表”頁(yè)面中選擇“約束條件”選項(xiàng)卡，進(jìn)入如圖所示的“表約束條件”頁(yè)面。該頁(yè)面中用戶可以為創(chuàng)建的表定義約束條件，包括主鍵約束（PRIMARY）、唯一約束（UNIQUE)、檢查約束（CHECK）和外鍵（FOREIGN）約束。

05

設(shè)置儲(chǔ)存參數(shù)，選擇“儲(chǔ)存”選項(xiàng)卡，進(jìn)入如圖所示的儲(chǔ)存設(shè)置界面，最后保存表格，單機(jī)【確定】按鈕，系統(tǒng)會(huì)根據(jù)用戶對(duì)表的設(shè)置，創(chuàng)建一個(gè)新表。

oracle建立索引

對(duì)三個(gè)字段建立索引：

create index Stuname on student(name);

create index Stusex on student(sex);

create index Stugrade on student(grade);

注意的問題，考慮是不是要建立唯一索引（unique），如果有學(xué)號(hào)的話，可以考慮建立唯一索引引。

再就是對(duì)經(jīng)常查詢，但又相對(duì)穩(wěn)定的可以建立聚簇索引，提高查詢效率

oracle分區(qū)表如何建索引組織表

方法如下：

Oracle中建立索引，會(huì)提高查詢速度： create index 索引名 on 表名(列名);

例如：

create index index_userid on tbl_detail(userid);

如何找數(shù)據(jù)庫(kù)表的主鍵字段的名稱?

SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' and table_name='AAA'; select * from dba_cons_columns where CONSTRAINT_NAME='SYS_AAA';

Oracle 在創(chuàng)建主鍵（可以不加constraint SYS_AAA），會(huì)為庫(kù)表自動(dòng)創(chuàng)建索引，

索引的列為主鍵列。 并且當(dāng)庫(kù)表某些列名或者庫(kù)表名改變時(shí)候，

Oracle自動(dòng)創(chuàng)建的索引SYS_AAA,中的索引列也會(huì)自動(dòng)更新（類似于視圖），并且SYS_AAA會(huì)與名字更改后的庫(kù)表還是保持索引關(guān)系。 關(guān)鍵系統(tǒng)庫(kù)表： desc dba_constraints desc dba_cons_columns

desc dba_indexes desc dba_ind_columns desc DBA_TAB_COLUMNS

例子1：更改庫(kù)表的列名

ALTER TABLE AAA RENAME COLUMN ID TO AAA_ID; create table AAA ( ID NUMBER(8), NAME CHAR(20),

constraint SYS_AAA primary key(ID) );

//查找約束名字

select c.CONSTRAINT_NAME,c.table_name,cc.COLUMN_NAME from user_constraints c, user_cons_columns cc

where c.constraint_name=cc.constraint_name and c.table_name ='AAA' AND C.CONSTRAINT_TYPE='P';

CONSTRAINT_NAME TABLE_NAME COLUMN_NAME

SYS_AAA AAA ID

//查找索引

select index_name,index_type,uniqueness from user_indexes where table_name='AAA'; INDEX_NAME INDEX_TYPE UNIQUENES

oracle 數(shù)據(jù)庫(kù)如何建立索引 如何用索引?

創(chuàng)建索引語(yǔ)法：

CREATE [UNIQUE] | [BITMAP] INDEX index_name

--unique表示唯一索引

ON table_name([column1 [ASC|DESC],column2

--bitmap，創(chuàng)建位圖索引

[ASC|DESC],…] | [express])[TABLESPACE tablespace_name][PCTFREE n1]

--指定索引在數(shù)據(jù)塊中空閑空間

[STORAGE (INITIAL n2)][NOLOGGING]

--表示創(chuàng)建和重建索引時(shí)允許對(duì)表做DML操作，默認(rèn)情況下不應(yīng)該使用

[NOLINE][NOSORT];

--表示創(chuàng)建索引時(shí)不進(jìn)行排序，默認(rèn)不適用，如果數(shù)據(jù)已經(jīng)是按照該索引順序排列的可以使用

擴(kuò)展資料：

1、如果有兩個(gè)或者以上的索引，其中有一個(gè)唯一性索引，而其他是非唯一，這種情況下oracle將使用唯一性索引而完全忽略非唯一性索引

2、至少要包含組合索引的第一列（即如果索引建立在多個(gè)列上，只有它的第一個(gè)列被where子句引用時(shí)，優(yōu)化器才會(huì)使用該索引）

3、小表不要簡(jiǎn)歷索引

4、對(duì)于基數(shù)大的列適合建立B樹索引，對(duì)于基數(shù)小的列適合簡(jiǎn)歷位圖索引

5、列中有很多空值，但經(jīng)常查詢?cè)摿猩戏强沼涗洉r(shí)應(yīng)該建立索引

6、經(jīng)常進(jìn)行連接查詢的列應(yīng)該創(chuàng)建索引

7、使用create index時(shí)要將最常查詢的列放在最前面

8、LONG（可變長(zhǎng)字符串?dāng)?shù)據(jù)，最長(zhǎng)2G）和LONG RAW（可變長(zhǎng)二進(jìn)制數(shù)據(jù)，最長(zhǎng)2G）列不能創(chuàng)建索引

9、限制表中索引的數(shù)量（創(chuàng)建索引耗費(fèi)時(shí)間，并且隨數(shù)據(jù)量的增大而增大；索引會(huì)占用物理空間；當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，降低了數(shù)據(jù)的維護(hù)速度）

如何合理創(chuàng)建Oracle數(shù)據(jù)庫(kù)索引的3個(gè)要求

如何合理創(chuàng)建Oracle數(shù)據(jù)庫(kù)索引的3個(gè)要求：

在Oracle數(shù)據(jù)庫(kù)中，創(chuàng)建索引雖然比較簡(jiǎn)單。但是要合理的創(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ù)庫(kù)管理員做出很大的努力。具體的來說，要做到這個(gè)三個(gè)適當(dāng)有如下幾個(gè)要求。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

總之，在適當(dāng)?shù)谋?、適當(dāng)?shù)牧猩辖⑦m當(dāng)?shù)乃饕?。這一句話包含的意思有很多，以上內(nèi)容只是一部分內(nèi)容。俗話說，師傅領(lǐng)進(jìn)門，修行靠自身。筆者在這里指能夠點(diǎn)到為止。一些具體的索引優(yōu)化內(nèi)容還是需要各位讀者在日常工作中去體會(huì)與總結(jié)