如何在ORACLE數(shù)據(jù)庫的字段上建立索引

1、如何在 ORACLE 數(shù)據(jù)庫的字段上建立索引？Oracle中建立索引，會提高查詢速度：create index 索引名 on 表名 (列名 );例如：create index index_userid on tbl_detail(userid);如何找數(shù)據(jù)庫表的主鍵字段的名稱 ?SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' and table_name='AAA' select * from dba_cons_columns where CONSTRAINT_NAME='SYS_AAA&

2、#39;Oracle 在創(chuàng)建主鍵(可以不加 constraint SYS_AAA )，會為庫表自動創(chuàng)建索引，索引的列為主鍵列。 并且當庫表某些列名或者庫表名改變時候， Oracle 自動創(chuàng)建的索引 SYS_AAA, 中的索引列也會自動更 新(類似于視圖)，并且 SYS_AAA 會與名字更改后的庫表還是保持索引關(guān)系。關(guān)鍵系統(tǒng)庫表：desc dba_constraintsdesc dba_cons_columnsdesc dba_indexesdesc dba_ind_columnsdesc DBA_TAB_COLUMNS例子 1：更改庫表的列名ALTER TABLE AAA RENAME COL

3、UMN 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 ccwhere c.constraint_name=cc.constraint_name and c.table_name ='AAA'AND C.CONSTRAINT_TYPE=&#

4、39;P'CONSTRAINT_NAMETABLE_NAME COLUMN_NAMESYS_AAA AAA ID/查找索引select index_name,index_type,uniqueness from user_indexes where table_name='AAA' INDEX_NAME INDEX_TYPE UNIQUENESSYS_AAA NORMAL UNIQUE/查找索引有那些列SQL> select INDEX_NAME,column_name from dba_ind_columns where table_name ='AAA

5、' INDEX_NAME COLUMN_NAMESYS_AAA IDALTER TABLE AAA RENAME COLUMN ID TO AAA_ID;/查找約束名字select c.CONSTRAINT_NAME,c.table_name,cc.COLUMN_NAME from user_constraints c, user_cons_columns ccwhere c.constraint_name=cc.constraint_name and c.table_name ='AAA'AND C.CONSTRAINT_TYPE='P'CONSTRA

6、INT_NAME TABLE_NAME COLUMN_NAMESYS_AAA AAA AAA_IDSQL> select INDEX_NAME,column_name from dba_ind_columns where table_name ='AAA' INDEX_NAME COLUMN_NAMESYS_AAA AAA_ID例子 2：更改庫表的表名ALTER TABLE AAA RENAME TO AAA_AAA;select INDEX_NAME,column_name from dba_ind_columns where table_name ='AAA_

7、AAA' INDEX_NAME COLUMN_NAMESYS_AAA AAA_IDCREATE INDEX為給定表或視圖創(chuàng)建索引。只有表或視圖的所有者才能為表創(chuàng)建索引。表或視圖的所有者可以隨時創(chuàng)建索引，無論表中是否有 數(shù)據(jù)。可以通過指定限定的數(shù)據(jù)庫名稱，為另一個數(shù)據(jù)庫中的表或視圖創(chuàng)建索引。語法CREATE UNIQUE CLUSTERED| NONCLUSTERED INDEX index_nameON table | view ( column ASC | DESC ,.n ) WITH < index_option> ,.n ON filegroup < inde

8、x_option > := PAD_INDEX |FILLFACTORfillfactor |IGNORE_DUP_KEY |DROP_EXISTING |STATISTICS_NORECOMPUTE |SORT_IN_TEMPDB參數(shù)UNIQUE為表或視圖創(chuàng)建唯一索引（不允許存在索引值相同的兩行）。視圖上的聚集索引必須是 UNIQUE 索引。在創(chuàng)建索引時，如果數(shù)據(jù)已存在， Microsoft&reg; SQL Server? 會檢查是否有重復(fù)值，并在 每次使用 INSERT 或 UPDATE 語句添加數(shù)據(jù)時進行這種檢查。如果存在重復(fù)的鍵值，將取 消 CREATE INDEX 語

9、句，并返回錯誤信息， 給出第一個重復(fù)值。 當創(chuàng)建 UNIQUE 索引時， 有多個 NULL 值被看作副本。如果存在唯一索引，那么會產(chǎn)生重復(fù)鍵值的 UPDATE 或 INSERT 語句將回滾，SQL Server 將顯示錯誤信息。即使 UPDATE 或 INSERT 語句更改了許多行但只產(chǎn)生了 一個重復(fù)值，也會出現(xiàn)這種情況。如果在有唯一索引并且指定了 IGNORE_DUP_KEY 子句情況 下輸入數(shù)據(jù)，則只有違反 UNIQUE 索引的行才會失敗。在處理 UPDATE 語句時， IGNORE_DUP_KEY 不起作用。SQL Server 不允許為已經(jīng)包含重復(fù)值的列創(chuàng)建唯一索引，無論是否設(shè)置了 I

10、GNORE_DUP_KEY 。如果嘗試這樣做， SQL Server 會顯示錯誤信息；重復(fù)值必須先刪除， 才能為這些列創(chuàng)建唯一索引。CLUSTERED創(chuàng)建一個對象，其中行的物理排序與索引排序相同，并且聚集索引的最低一級（葉級）包含實際的 數(shù)據(jù)行。一個表或視圖只允許同時有一個聚集索引。具有聚集索引的視圖稱為索引視圖。必須先為視圖創(chuàng)建唯一聚集索引，然后才能為該視圖定義其它 索引。在創(chuàng)建任何非聚集索引之前創(chuàng)建聚集索引。創(chuàng)建聚集索引時重建表上現(xiàn)有的非聚集索引。如果沒有指定 CLUSTERED ，則創(chuàng)建非聚集索引。說明 因為按照定義，聚集索引的葉級與其數(shù)據(jù)頁相同，所以創(chuàng)建聚集索引時使 用 ON file

11、group 子句實際上會將表從創(chuàng)建該表時所用的文件移到新的文件組中。 在特定的文件 組上創(chuàng)建表或索引之前， 應(yīng)確認哪些文件組可用并且有足夠的空間供索引使用。 文件組的大小必須至 少是整個表所需空間的 1.2 倍，這一點很重要。NONCLUSTERED創(chuàng)建一個指定表的邏輯排序的對象。對于非聚集索引，行的物理排序獨立于索引排序。非聚集索引 的葉級包含索引行。每個索引行均包含非聚集鍵值和一個或多個行定位器（指向包含該值的行）。如 果表沒有聚集索引，行定位器就是行的磁盤地址。如果表有聚集索引，行定位器就是該行的聚集索引 鍵。每個表最多可以有 249 個非聚集索引（無論這些非聚集索引的創(chuàng)建方式如何：是使

12、用 PRIMARY KEY 和 UNIQUE 約束隱式創(chuàng)建，還是使用 CREATE INDEX 顯式創(chuàng) 建）。每個索引均可以提供對數(shù)據(jù)的不同排序次序的訪問。對于索引視圖，只能為已經(jīng)定義了聚集索引的視圖創(chuàng)建非聚集索引。因此，索引視圖中非聚集索引 的行定位器一定是行的聚集鍵。index_name是索引名。 索引名在表或視圖中必須唯一， 但在數(shù)據(jù)庫中不必唯一。 索引名必須遵循標識符規(guī)則。table包含要創(chuàng)建索引的列的表?？梢赃x擇指定數(shù)據(jù)庫和表所有者。view要建立索引的視圖的名稱。必須使用 SCHEMABINDING 定義視圖才能在視圖上創(chuàng)建索引。視 圖定義也必須具有確定性。如果選擇列表中的所有表達

13、式、 WHERE 和 GROUP BY 子句都 具有確定性，則視圖也具有確定性。而且，所有鍵列必須是精確的。只有視圖的非鍵列可能包含浮點 表達式（使用 float 數(shù)據(jù)類型的表達式），而且 float 表達式不能在視圖定義的其它任何位置 使用。若要在確定性視圖中查找列，請使用 COLUMNPROPERTY 函數(shù)（ IsDeterministic 屬性）。該 函數(shù)的 IsPrecise 屬性可用來確定鍵列是否精確。必須先為視圖創(chuàng)建唯一的聚集索引，才能為該視圖創(chuàng)建非聚集索引。在 SQL Server 企業(yè)版或開發(fā)版中， 查詢優(yōu)化器可使用索引視圖加快查詢的執(zhí)行速度。 要使優(yōu) 化程序考慮將該視圖作為替

14、換，并不需要在查詢中引用該視圖。在創(chuàng)建索引視圖或?qū)⑴c索引視圖的表中的行進行操作時， 有 7 個 SET 選項必須指派特定 的值。 SET 選項 ARITHABORT 、 CONCAT_NULL_YIELDS_NULL 、QUOTED_IDENTIFIER 、ANSI_NULLS、ANSI_PADDING 和 ANSI_WARNING 必須為 ON。SET 選 項 NUMERIC_ROUNDABORT 必須為OFF。如果與上述設(shè)置有所不同， 對索引視圖所引用的任何表執(zhí)行的數(shù)據(jù)修改語句 （INSERT、 UPDATE、 DELETE） 都將失敗， SQL Server 會顯示一條錯誤信息，列出所

15、有違反設(shè)置要求的 SET 選 項。此外，對于涉及索引視圖的 SELECT 語句，如果任何 SET 選項的值不是所需的值， 則 SQL Server 在處理該 SELECT 語句時不考慮索引視圖替換。在受上述 SET 選項影 響的情況中，這將確保查詢結(jié)果的正確性。如果應(yīng)用程序使用 DB-Library 連接，則必須為服務(wù)器上的所有 7 個 SET 選項指派所 需的值。 （默認情況下， OLE DB 和 ODBC 連接已經(jīng)正確設(shè)置了除 ARITHABORT 外所 有需要的 SET 選項。）如果并非所有上述 SET 選項均有所需的值，則某些操作（例如BCP、復(fù)制或分布式查詢）可能無法對參與索引視圖的

16、表執(zhí)行更新。在大多數(shù)情況下，將ARITHABORT 設(shè)置為 ON （通過服務(wù)器配置選項中的 user options）可以避免這一問題。強烈建議在服務(wù)器的任一數(shù)據(jù)庫中創(chuàng)建計算列上的第一個索引視圖或索引后，盡早在服務(wù)器范圍內(nèi)將 ARITHABORT 用戶選項設(shè)置為 ON。有關(guān)索引視圖注意事項和限制的更多信息，請參見注釋部分。column應(yīng)用索引的列。 指定兩個或多個列名， 可為指定列的組合值創(chuàng)建組合索引。 在 table 后的圓括號 中列出組合索引中要包括的列（按排序優(yōu)先級排列）。說明 由 ntext、 text 或 image 數(shù)據(jù)類型組成的列不能指定為索引列。另外，視圖不能包 括任何text

17、、 ntext 或 image 列，即使在 CREATE INDEX 語句中沒有引用這些列。當兩列或多列作為一個單位搜索最好，或者許多查詢只引用索引中指定的列時，應(yīng)使用組合索引。 最多可以有 16 個列組合到一個組合索引中。組合索引中的所有列必須在同一個表中。組合索引 值允許的最大大小為 900 字節(jié)。也就是說，組成組合索引的固定大小列的總長度不得超 過 900 字節(jié)。有關(guān)組合索引中可變類型列的更多信息，請參見注釋部分。ASC | DESC確定具體某個索引列的升序或降序排序方向。默認設(shè)置為ASC。n表示可以為特定索引指定多個 columns 的占位符PAD_INDEX指定索引中間級中每個頁（節(jié)

18、點）上保持開放的空間。 PAD_INDEX 選項只有在指定了 FILLFACTOR 時才有用，因為 PAD_INDEX 使用由 FILLFACTOR 所指定的百分比 默認情況下， 給定中間級頁上的鍵集， SQL Server 將確保每個索引頁上的可用空間至少可以容納 一個索引允許的最大行。如果為 FILLFACTOR 指定的百分比不夠大，無法容納一行， SQL Server 將在內(nèi)部使用允許的最小值替代該百分比。說明 中間級索引頁上的行數(shù)永遠都不會小于兩行，無論 FILLFACTOR 的值有多小。FILLFACTOR = fillfactor指定在 SQL Server 創(chuàng)建索引的過程中，各索

19、引頁葉級的填滿程度。如果某個索引頁填滿，SQL Server 就必須花時間拆分該索引頁，以便為新行騰出空間，這需要很大的開銷。對于更新頻 繁的表，選擇合適的 FILLFACTOR 值將比選擇不合適的 FILLFACTOR 值獲得更好的更新性 能。 FILLFACTOR 的原始值將在 sysindexes 中與索引一起存儲。如果指定了 FILLFACTOR ，SQL Server 會向上舍入每頁要放置的行數(shù)。例如，發(fā) 出 CREATE CLUSTERED INDEX .FILLFACTOR = 33 將創(chuàng)建一 個 FILLFACTOR 為 33% 的聚集索引。假設(shè) SQL Server 計算出每

20、頁空間 的 33% 為 5.2 行。 SQL Server 將其向上舍入，這樣，每頁就放置 6 行。設(shè)置只是在索引首次創(chuàng)建時應(yīng)用SQL Server 并不會動態(tài)說明 顯式的 FILLFACTOR 保持頁上可用空間的指定百分比。用戶指定的 FILLFACTOR 值可以從 1 到 100。如果沒有指定值，默認值為0。如果 FILLFACTOR 設(shè)置為 0 ，則只填滿葉級頁。可以通過執(zhí)行sp_configure 更改默認的 FILLFACTOR 設(shè)置。只有不會出現(xiàn) INSERT 或 UPDATE 語句時（例如對只讀表），才可以使 用 FILLFACTOR 100。如果 FILLFACTOR 為 10

21、0， SQL Server 將創(chuàng)建葉級 頁 100% 填滿的索引。如果在創(chuàng)建 FILLFACTOR 為 100% 的索引之后執(zhí) 行 INSERT 或 UPDATE ，會對每次 INSERT 操作以及有可能每次 UPDATE 操作進行 頁拆分。如果 FILLFACTOR 值較小（ 0 除外），就會使 SQL Server 創(chuàng)建葉級頁不完全填充的 新索引。例如，如果已知某個表包含的數(shù)據(jù)只是該表最終要包含的數(shù)據(jù)的一小部分，那么為該表創(chuàng)建 索引時， FILLFACTOR 為 10 會是合理的選擇。 FILLFACTOR 值較小還會使索引占用較多的 存儲空間。下表說明如何在已指定 FILLFACTOR的

22、情況下填充索引頁。FILLFACTOR 中間級頁 葉級頁0 一個可用項 100%1% -99 一個可用項填滿<= FILLFACTOR% 填滿100% 一個可用項 100% 填滿一個可用項是指頁上可以容納另一個索引項的空間重要 用某個 FILLFACTOR 值創(chuàng)建聚集索引會影響數(shù)據(jù)占用存儲空間的數(shù)量，因 為 SQL Server 在創(chuàng)建聚集索引時會重新分布數(shù)據(jù)。IGNORE_DUP_KEY控制當嘗試向?qū)儆谖ㄒ痪奂饕牧胁迦胫貜?fù)的鍵值時所發(fā)生的情況。如果為索引指定了 IGNORE_DUP_KEY ，并且執(zhí)行了創(chuàng)建重復(fù)鍵的 INSERT 語句， SQL Server 將發(fā)出警告 消息并忽略

23、重復(fù)的行。如果沒有為索引指定 個 INSERT 語句IGNORE_DUP_KEY ，SQLServer 會發(fā)出一條警告消息，并回滾整IGNORE_DUP_KEYIGNORE_DUP_KEYIGNORE_DUP_KEY下表顯示何時可使用索引類型 選項聚集 不允許唯一聚集 允許使用 非聚集 不允許 唯一非聚集 允許使用DROP_EXISTING指定應(yīng)除去并重建已命名的先前存在的聚集索引或非聚集索引。指定的索引名必須與現(xiàn)有的索引名 相同。因為非聚集索引包含聚集鍵，所以在除去聚集索引時，必須重建非聚集索引。如果重建聚集索 引，則必須重建非聚集索引，以便使用新的鍵集。為已經(jīng)具有非聚集索引的表重建聚集索引

24、時（使用相同或不同的鍵集），DROP_EXISTING 子句可以提高性能。 DROP_EXISTING 子句代替了先對舊的聚集索引執(zhí)行DROP INDEX 語句，然后再對新的聚集索引執(zhí)行 CREATE INDEX 語句的過程。非聚集索引只需重建一次，而且還 只是在鍵不同的情況下才需要。如果鍵沒有改變（提供的索引名和列與原索引相同），則 DROP_EXISTING 子句不會重新對數(shù) 據(jù)進行排序。在必須壓縮索引時，這樣做會很有用。無法使用 DROP_EXISTING 子句將聚集索引轉(zhuǎn)換成非聚集索引； 不過， 可以將唯一聚集索引更 改為非唯一索引，反之亦然。說明 當執(zhí)行帶 DROP_EXISTING

25、 子句的 CREATE INDEX 語句時， SQL Server 假定索引是一致的（即索引沒有損壞）。指定索引中的行應(yīng) 按 CREATE INDEX 語句中引用的指定鍵排序。STATISTICS_NORECOMPUTE指定過期的索引統(tǒng)計不會自動重新計算。若要恢復(fù)自動更新統(tǒng)計，可執(zhí)行沒 有 NORECOMPUTE 子句的 UPDATE STATISTICS 。重要 如果禁用分布統(tǒng)計的自動重新計算， 的查詢選取最佳執(zhí)行計劃?？赡軙恋K SQL Server 查詢優(yōu)化器為涉及該表SORT_IN_TEMPDB指定用于生成索引的中間排序結(jié)果將存儲在 tempdb 數(shù)據(jù)庫中。如果 tempdb 與用戶數(shù)

26、據(jù)庫 不在同一磁盤集，則此選項可能減少創(chuàng)建索引所需的時間，但會增加創(chuàng)建索引時使用的磁盤空間。有關(guān)更多信息，請參見 tempdb 和索引創(chuàng)建。ON filegroup在給定的 filegroup 上創(chuàng)建指定的索引。該文件組必須已經(jīng)通過執(zhí) 行 CREATE DATABASE 或 ALTER DATABASE 創(chuàng)建。注釋為表或索引分配空間時， 每次遞增一個擴展盤區(qū) （8 個 8 KB 的頁）。每填滿一個擴展盤區(qū)， 就會再分配一個。如果表非常小或是空表，其索引將使用單頁分配，直到向索引添加了 8 頁后， 再轉(zhuǎn)而進行擴展盤區(qū)分配。若要獲得有關(guān)索引已分配和占用的空間數(shù)量的報表，請使用 sp_spaceus

27、ed創(chuàng)建聚集索引要求數(shù)據(jù)庫中的可用空間大約為數(shù)據(jù)大小的1.2 倍。該空間不包括現(xiàn)有表占用的空間；將對數(shù)據(jù)進行復(fù)制以創(chuàng)建聚集索引，舊的無索引數(shù)據(jù)將在索引創(chuàng)建完成后刪除。使 用 DROP_EXISTING 子句時，聚集索引所需的空間數(shù)量與現(xiàn)有索引的空間要求相同。所需的額 外空間可能還受指定的 FILLFACTOR 的影響。在 SQL Server 2000 中創(chuàng)建索引時，可以使用 SORT_IN_TEMPDB 選項指示數(shù)據(jù)庫引 擎在 tempdb 中存儲中間索引排序結(jié)果。如果 tempdb 在不同于用戶數(shù)據(jù)庫所在的磁盤集上， 則此選項可能減少創(chuàng)建索引所需的時間， 但會增加用于創(chuàng)建索引的磁盤空間。

28、除在用戶數(shù)據(jù)庫中創(chuàng)建 索引所需的空間外， tempdb 還必須有大約相同的額外空間來存儲中間排序結(jié)果。有關(guān)更多信息， 請參見 tempdb 和索引創(chuàng)建。CREATE INDEX 語句同其它查詢一樣優(yōu)化。 SQL Server 查詢處理器可以選擇掃描另一個 索引，而不是執(zhí)行表掃描，以節(jié)省 I/O 操作。在某些情況下，可以不必排序。在運行 SQL Server 企業(yè)管理器和程序員版的多處理器計算機上， CREATE INDEX 自動 使用多個處理器執(zhí)行掃描和排序，與其它查詢的操作方式相同。執(zhí)行一條CREATE INDEX 語句所使用的處理器數(shù)由配置選項 max degree of parallel

29、ism 和當前的工作負荷決定。如 果 SQL Server 檢測到系統(tǒng)正忙，則在開始執(zhí)行語句之前， CREATE INDEX 操作的并發(fā)程 度會自動降低。自上一次文件組備份以來受 CREATE INDEX 語句影響的全部文件組必須作為一個單位備 份。有關(guān)文件和文件組備份的更多信息，請參見 BACKUP 。備份和 CREATE INDEX 操作不相互防礙。如果正在進行備份，則在完整日志記錄模式中創(chuàng) 建索引，而這可能需要額外的日志空間。若要顯示有關(guān)對象索引的報表，請執(zhí)行sp_helpindex?？梢詾榕R時表創(chuàng)建索引。在除去表或終止會話時，所有索引和觸發(fā)器都將被除去。索引中的可變類型列索引鍵允許的最

30、大大小為 900 字節(jié)，不過 SQL Server 2000 允許在可能包含大量可變 類型列的列上創(chuàng)建索引，而這些列的最大大小超過 900 字節(jié)。在創(chuàng)建索引時， SQL Server 檢查下列條件：所有參與索引定義的固定數(shù)據(jù)列的總長度必須小于或等于 900 字節(jié)。當所要創(chuàng)建的索引只由固 定數(shù)據(jù)列構(gòu)成時，固定數(shù)據(jù)列的總計大小必須小于或等于 900 字節(jié)。否則將不能創(chuàng)建索引， 且 SQL Server 將返回錯誤。如果索引定義由固定類型列和可變類型列組成，且固定數(shù)據(jù)列滿足前面的條件(小于或等于 900 字節(jié))，則 SQL Server 仍要檢查可變類型列的總大小。如果可變類型列的最大大 小與固定數(shù)

31、據(jù)列大小的和大于 900 字節(jié)，則 SQL Server 將創(chuàng)建索引，不過將給用戶返回 警告消息以提醒用戶： 如果隨后在可變類型列上的插入或更新操作導(dǎo)致總大小超過 900 字節(jié)，則 操作將失敗且用戶將收到運行時錯誤。同樣，如果索引定義只由可變類型列組成，且這些列的最大總 大小大于 900 字節(jié)，則 SQL Server 將創(chuàng)建索引，不過將返回警告消息。有關(guān)更多信息，請參見索引鍵的最大值。在計算列和視圖上創(chuàng)建索引時的考慮在 SQL Server 2000 中，還可以在計算列和視圖上創(chuàng)建索引。在視圖上創(chuàng)建唯一聚集索引 可以提高查詢性能，因為視圖存儲在數(shù)據(jù)庫中的方式與具有聚集索引的表的存儲方式相同。

32、UNIQUE 或 PRIMARY KEY 只要滿足所有索引條件，就可以包含計算列。具體說來就是， 計算列必須具有確定性、必須精確，且不能包含text、 ntext 或 image 列。有關(guān)確定性的更多信息，請參見確定性函數(shù)和非確定性函數(shù)。在計算列或視圖上創(chuàng)建索引可能導(dǎo)致前面產(chǎn)生的 INSERT 或 UPDATE 操作失敗。當計算列 導(dǎo)致算術(shù)錯誤時可能產(chǎn)生這樣的失敗。例如，雖然下表中的計算列 c 將導(dǎo)致算術(shù)錯誤，但 是 INSERT 語句仍有效：CREATE TABLE t1 (a int, b int, c AS a/b)GOINSERT INTO t1 VALUES ('1'

33、, '0')GO相反，如果創(chuàng)建表之后在計算列 c 上創(chuàng)建索引，則上述 INSERT 語句將失敗。CREATETABLEt1 (a int, bint, cASa/b)GOCREATEUNIQUECLUSTEREDINDEXIdx1ON t1.cGOINSERTINTO t1VALUES ('1','0')GO在通過數(shù)字或 float 表達式定義的視圖上使用索引所得到的查詢結(jié)果， 可能不同于不在視圖上使 用索引的類似查詢所得到的結(jié)果。這種差異可能是由對基礎(chǔ)表進行INSERT、DELETE 或 UPDATE 操作時的舍入錯誤引起的。若要防止 SQL

34、Server 使用索引視圖，請在查詢中包含 OPTION (EXPAND VIEWS) 提示。此外，任何所列選項設(shè)置不正確均會阻止優(yōu)化程序使 用視圖上的索引。 有關(guān) OPTION (EXPAND VIEWS) 提示的更多信息， 請參見 SELECT 。對索引視圖的限制定義索引視圖的 SELECT 語句不得包含 TOP、DISTINCT 、COMPUTE 、HAVING 和 UNION 關(guān)鍵字。也不能包含子查詢。SELECT 列表中不得包含星號(*) 、'table.*' 通配符列表、 DISTINCT 、COUNT(*) 、COUNT(<expressio n>)、

35、基表中的計算列和標量聚合。非聚合 SELECT 列表中不能包含表達式。聚合 SELECT 列表(包含 GROUP BY 的 查詢)中可能包含 SUM 和 COUNT_BIG(<expression>);它一定包含 COUNT_BIG(*)。不允 許有其它聚合函數(shù)( MIN、 MAX 、 STDEV，.)。使用 AVG 的復(fù)雜聚合無法參與索引視圖的 SELECT 列表。不過，如果查詢使用這樣的聚 合，則優(yōu)化程序?qū)⒛苁褂迷撍饕晥D，用 SUM 和 COUNT_BIG 的簡單聚合組合代 替 AVG 。若某列是從取值為 float 數(shù)據(jù)類型或使用 float 表達式進行取值的表達式得到的，

36、則不能作為索引視圖或表中計算列的索引鍵。這樣的列被視為是不精確的。使用COLUMNPROPERTY 函數(shù)決定特定計算列或視圖中的列是否精確。索引視圖受限于以下的附加限制：索引的創(chuàng)建者必須擁有表。所有表、視圖和索引必須在同一數(shù)據(jù)庫中創(chuàng)建。定義索引視圖的 SELECT 語句不得包含視圖、行集函數(shù)、行內(nèi)函數(shù)或派生表。同一物理表在該 語句中只能出現(xiàn)一次。在任何聯(lián)接表中，均不允許進行 OUTER JOIN 操作搜索條件中不允許使用子查詢或者 CONTAINS 或 FREETEXT 謂詞。如果視圖定義包含 GROUP BY 子句，則視圖的 SELECT 列表中必須包含所有分組依據(jù) 列及 COUNT_BIG

37、(*) 表達式。此外， CREATE UNIQUE CLUSTERED INDEX 子句中必 須只包含這些列。可以創(chuàng)建索引的視圖的定義主體必須具有確定性且必須精確，這類似于計算列上的索引要求。請參 見在計算列上創(chuàng)建索引。權(quán)限CREATE INDEX 的權(quán)限默認授予 sysadmin 固定服務(wù)器角色、 db_ddladmin 和 db_owner 固定數(shù)據(jù)庫角色和表所有者且不能轉(zhuǎn)讓。示例A. 使用簡單索引面的示例為 authors 表的 au_id 列創(chuàng)建索引SET NOCOUNT OFFUSE pubsIF EXISTS (SELECT name FROM sysindexesWHERE na

38、me = 'au_id_ind')DROP INDEX authors.au_id_indGOUSE pubs葉級頁填滿<= FILLFACTOR% 填滿CREATE INDEFILLFACTOR 中間級頁 0 一個可用項 100% 1% -99 一個可用項100% 一個可用項 100% 填滿一個可用項是指頁上可以容納另一個索引項的空間重要 用某個 FILLFACTOR 值創(chuàng)建聚集索引會影響數(shù)據(jù)占用存儲空間的數(shù)量，因 為 SQL Server 在創(chuàng)建聚集索引時會重新分布數(shù)據(jù)。IGNORE_DUP_KEY控制當嘗試向?qū)儆谖ㄒ痪奂饕牧胁迦胫貜?fù)的鍵值時所發(fā)生的情況。如果為索

39、引指定了 IGNORE_DUP_KEY ，并且執(zhí)行了創(chuàng)建重復(fù)鍵的 INSERT 語句， SQL Server 將發(fā)出警告 消息并忽略重復(fù)的行。如果沒有為索引指定 個 INSERT 語句IGNORE_DUP_KEY ，SQLServer 會發(fā)出一條警告消息，并回滾整下表顯示何時可使用IGNORE_DUP_KEY索引類型 選項聚集 不允許唯一聚集 允許使用 IGNORE_DUP_KEY 非聚集 不允許唯一非聚集 允許使用 IGNORE_DUP_KEYDROP_EXISTING指定應(yīng)除去并重建已命名的先前存在的聚集索引或非聚集索引。指定的索引名必須與現(xiàn)有的索引名 相同。因為非聚集索引包含聚集鍵，所以

40、在除去聚集索引時，必須重建非聚集索引。如果重建聚集索 引，則必須重建非聚集索引，以便使用新的鍵集。為已經(jīng)具有非聚集索引的表重建聚集索引時（使用相同或不同的鍵集），DROP_EXISTING 子句可以提高性能。 DROP_EXISTING 子句代替了先對舊的聚集索引執(zhí)行DROP INDEX 語句，然后再對新的聚集索引執(zhí)行 CREATE INDEX 語句的過程。非聚集索引只需重建一次，而且還 只是在鍵不同的情況下才需要。如果鍵沒有改變（提供的索引名和列與原索引相同），則 DROP_EXISTING 子句不會重新對數(shù) 據(jù)進行排序。在必須壓縮索引時，這樣做會很有用。無法使用 DROP_EXISTING

41、 子句將聚集索引轉(zhuǎn)換成非聚集索引； 不過， 可以將唯一聚集索引更 改為非唯一索引，反之亦然。說明 當執(zhí)行帶 DROP_EXISTING 子句的 CREATE INDEX 語句時， SQL Server 假定索引是一致的（即索引沒有損壞）。指定索引中的行應(yīng) 按 CREATE INDEX 語句中引用的指定鍵排序。STATISTICS_NORECOMPUTE指定過期的索引統(tǒng)計不會自動重新計算。若要恢復(fù)自動更新統(tǒng)計，可執(zhí)行沒 有 NORECOMPUTE 子句的 UPDATE STATISTICS 。重要 如果禁用分布統(tǒng)計的自動重新計算，可能會妨礙 SQL Server 查詢優(yōu)化器為涉及該表 的查詢選取

42、最佳執(zhí)行計劃SORT_IN_TEMPDB指定用于生成索引的中間排序結(jié)果將存儲在 tempdb 數(shù)據(jù)庫中。如果 tempdb 與用戶數(shù)據(jù)庫 不在同一磁盤集，則此選項可能減少創(chuàng)建索引所需的時間，但會增加創(chuàng)建索引時使用的磁盤空間。有關(guān)更多信息，請參見 tempdb 和索引創(chuàng)建。ON filegroup在給定的 filegroup 上創(chuàng)建指定的索引。該文件組必須已經(jīng)通過執(zhí) 行 CREATE DATABASE 或 ALTER DATABASE 創(chuàng)建。注釋為表或索引分配空間時， 每次遞增一個擴展盤區(qū) （8 個 8 KB 的頁）。每填滿一個擴展盤區(qū)， 就會再分配一個。如果表非常小或是空表，其索引將使用單頁分

43、配，直到向索引添加了 8 頁后， 再轉(zhuǎn)而進行擴展盤區(qū)分配。若要獲得有關(guān)索引已分配和占用的空間數(shù)量的報表，請使用 sp_spaceused創(chuàng)建聚集索引要求數(shù)據(jù)庫中的可用空間大約為數(shù)據(jù)大小的1.2 倍。該空間不包括現(xiàn)有表占用的空間；將對數(shù)據(jù)進行復(fù)制以創(chuàng)建聚集索引，舊的無索引數(shù)據(jù)將在索引創(chuàng)建完成后刪除。使 用 DROP_EXISTING 子句時，聚集索引所需的空間數(shù)量與現(xiàn)有索引的空間要求相同。所需的額 外空間可能還受指定的 FILLFACTOR 的影響。在 SQL Server 2000 中創(chuàng)建索引時，可以使用 SORT_IN_TEMPDB 選項指示數(shù)據(jù)庫引 擎在 tempdb 中存儲中間索引排序結(jié)

44、果。如果 tempdb 在不同于用戶數(shù)據(jù)庫所在的磁盤集上， 則此選項可能減少創(chuàng)建索引所需的時間， 但會增加用于創(chuàng)建索引的磁盤空間。 除在用戶數(shù)據(jù)庫中創(chuàng)建 索引所需的空間外， tempdb 還必須有大約相同的額外空間來存儲中間排序結(jié)果。有關(guān)更多信息， 請參見 tempdb 和索引創(chuàng)建。CREATE INDEX 語句同其它查詢一樣優(yōu)化。 SQL Server 查詢處理器可以選擇掃描另一個 索引，而不是執(zhí)行表掃描，以節(jié)省 I/O 操作。在某些情況下，可以不必排序。在運行 SQL Server 企業(yè)管理器和程序員版的多處理器計算機上， CREATE INDEX 自動 使用多個處理器執(zhí)行掃描和排序，與其

45、它查詢的操作方式相同。執(zhí)行一條CREATE INDEX 語句所使用的處理器數(shù)由配置選項 max degree of parallelism 和當前的工作負荷決定。如 果 SQL Server 檢測到系統(tǒng)正忙，則在開始執(zhí)行語句之前， CREATE INDEX 操作的并發(fā)程 度會自動降低。自上一次文件組備份以來受 CREATE INDEX 語句影響的全部文件組必須作為一個單位備 份。有關(guān)文件和文件組備份的更多信息，請參見 BACKUP 。備份和 CREATE INDEX 操作不相互防礙。如果正在進行備份，則在完整日志記錄模式中創(chuàng) 建索引，而這可能需要額外的日志空間。若要顯示有關(guān)對象索引的報表，請執(zhí)

46、行sp_helpindex?？梢詾榕R時表創(chuàng)建索引。在除去表或終止會話時，所有索引和觸發(fā)器都將被除去。索引中的可變類型列索引鍵允許的最大大小為 900 字節(jié)，不過 SQL Server 2000 允許在可能包含大量可變 類型列的列上創(chuàng)建索引，而這些列的最大大小超過 900 字節(jié)。在創(chuàng)建索引時， SQL Server 檢查下列條件：900 字節(jié)。當所要創(chuàng)建的索引只由固所有參與索引定義的固定數(shù)據(jù)列的總長度必須小于或等于900 字節(jié)。否則將不能創(chuàng)建索引，定數(shù)據(jù)列構(gòu)成時，固定數(shù)據(jù)列的總計大小必須小于或等于 且 SQL Server 將返回錯誤。如果索引定義由固定類型列和可變類型列組成，且固定數(shù)據(jù)列滿足前

47、面的條件(小于或等于 900 字節(jié))，則 SQL Server 仍要檢查可變類型列的總大小。如果可變類型列的最大大 小與固定數(shù)據(jù)列大小的和大于 900 字節(jié)，則 SQL Server 將創(chuàng)建索引，不過將給用戶返回 警告消息以提醒用戶： 如果隨后在可變類型列上的插入或更新操作導(dǎo)致總大小超過 900 字節(jié)，則 操作將失敗且用戶將收到運行時錯誤。同樣，如果索引定義只由可變類型列組成，且這些列的最大總 大小大于 900 字節(jié)，則 SQL Server 將創(chuàng)建索引，不過將返回警告消息。有關(guān)更多信息，請參見索引鍵的最大值。在計算列和視圖上創(chuàng)建索引時的考慮在 SQL Server 2000 中，還可以在計算

48、列和視圖上創(chuàng)建索引。在視圖上創(chuàng)建唯一聚集索引 可以提高查詢性能，因為視圖存儲在數(shù)據(jù)庫中的方式與具有聚集索引的表的存儲方式相同。UNIQUE 或 PRIMARY KEY 只要滿足所有索引條件，就可以包含計算列。具體說來就是， 計算列必須具有確定性、必須精確，且不能包含text、 ntext 或 image 列。有關(guān)確定性的更多信息，請參見確定性函數(shù)和非確定性函數(shù)。在計算列或視圖上創(chuàng)建索引可能導(dǎo)致前面產(chǎn)生的 INSERT 或 UPDATE 操作失敗。當計算列 導(dǎo)致算術(shù)錯誤時可能產(chǎn)生這樣的失敗。例如，雖然下表中的計算列 c 將導(dǎo)致算術(shù)錯誤，但 是 INSERT 語句仍有效：CREATE TABLE t1 (a int, b int, c AS a/b)GOINSERT IN