重建索引有多種方式，如drop and re-create、rebuild、rebuild online等。下面簡單比較這幾種方式異同以及優缺點：
首先建立測試表及數據：
SQL> CREATE TABLE TEST AS SELECT CITYCODE C1 FROM CITIZENINFO2;
Table created
SQL> ALTER TABLE TEST MODIFY C1 NOT NULL;
Table altered
SQL> SELECT COUNT(1) FROM TEST;
COUNT(1)
----------
16000000
一、drop and re-create和rebuild
首先看看正常建立索引時，對表的加鎖情況。
suk@ORACLE9I> @show_sid
SID
----------
14
suk@ORACLE9I> CREATE INDEX IDX_TEST_C1 ON TEST(C1);
索引已創建。
SQL> SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14;
OBJECT_NAME LMODE
------------------------------ ----------
OBJ$ 3
TEST 4
可見，普通情況下建立索引時，oracle會對基表加share鎖，由於share鎖和 row-X是不兼容的，也就是說，在建立索引期間，無法對基表進行DML操作。
對於刪除重建索引的方法就不介紹了，它與上面的描述是一樣的，下面我們看看用rebuild的方式建立索引有什麼特別。
suk@ORACLE9I> ALTER INDEX IDX_TEST_C1 REBUILD;
索引已更改。
另開一個會話，查詢此時test的加鎖情況：
SQL> SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14;
OBJECT_NAME LMODE
------------------------------ ----------
TEST 4
可見，rebuild的方式對基表的加鎖方式與CREATE時是一樣的。
另開一個會話，在索引正在rebuild時，執行如下SQL：
suk@ORACLE9I> SET AUTOTRACE TRACE
suk@ORACLE9I> SELECT /*+ INDEX(TEST) */ COUNT(1) FROM TEST WHERE ROWNUM<10;
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=26 Card=1)
1 0 SORT (AGGREGATE)
2 1 COUNT (STOPKEY)
3 2 INDEX (FULL SCAN) OF 'IDX_TEST_C1' (NON-UNIQUE) (Cost=
26 Card=1986621)
可以看到索引在重建時，查詢仍然可以使用舊索引。實際上，oracle在rebuild時，在創建新索引過程中，並不會刪除舊索引，直到新索引rebuild成功。
從這點可以知道rebuild比刪除重建的一個好處是不會影響原有的SQL查詢，但也正由於此，用rebuild方式建立索引需要相應表空間的空閑空間是刪除重建方式的2倍。
重建索引有多種方式，如drop and re-create、rebuild、rebuild online等。下面簡單比較這幾種方式異同以及優缺點：
相關文章：
oracle重建索引（一）
二、rebuild 和rebuild online
首先我們跟蹤一下rebuild online的過程。
另開一個會話查看鎖的信息：
SQL> SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14;
OBJECT_NAME LMODE
------------------------------ ----------
SYS_JOURNAL_10499 4
TEST 2
SQL> INSERT INTO TEST VALUES(11);
1 row inserted
SQL> COMMIT;
Commit complete
可以看到，在rebuild online期間，oracle對基表加的是RS所，此時我們可以對基表進行DML操作。但奇怪的話在相同的session中有一個SYS_JOURNAL_10499表被加SHARE鎖，這個表是干什麼用的呢？
我們看看trace文件，有這樣的信息：
create table "SUK"."SYS_JOURNAL_10499" (C0 NUMBER(6,0), opcode char(1),
partno number, rid rowid, primary key( C0 , rid )) organization index
TABLESPACE "TEST"
CREATE UNIQUE INDEX "SUK"."SYS_IOT_TOP_10605" on
"SUK"."SYS_JOURNAL_10499"("C0","RID") INDEX ONLY TOPLEVEL TABLESPACE "TEST"
NOPARALLEL
drop table "SUK"."SYS_JOURNAL_10499"
我們在查查10499是什麼東西：
SQL> SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_ID=10499;
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
IDX_TEST_C1 INDEX
從這些信息可以推測：表SYS_JOURNAL_10499就是實現在重建索引時不阻塞DML操作而設計的，它存儲的是在索引重建期間發生在基表的數據變化。可以推測，CREATE INDEX .... ONLINE應該也有一張類似的表。
實際上，oracle之所以在創建索引時鎖表阻止DML操作就是為了防止不能索引新變化的數據，在online方式重建時，有了臨時表SYS_JOURNAL_XXXX，oracle就可以放心大膽地讓用戶操作了，因為所有重建索引期間的數據變化信息都會保留在SYS_JOURNAL_XXX表中，當索引重建完後再加上SYS_JOURNAL_XXX記錄的數據，就不會漏索引數據了。（XXX是被重建的索引對應的OBJECT_ID)
導讀：
重建索引有多種方式，如drop and re-create、rebuild、rebuild online等。下面簡單比較這幾種方式異同以及優缺點：
相關文章：
oracle重建索引（一）
oracle重建索引（二）
三、rebuild和rebuild online的數據源
網上一直有這樣一個說法：重建索引是以原索引作為數據源的。那麼，這種說法是否准確呢？我們做實驗來驗證一下：
suk@ORACLE9I> COL SEGMENT_NAME FORMAT A30
--首先看看錶和索引的大小
suk@ORACLE9I> SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('TEST','IDX_TEST_C1');
SEGMENT_NAME BYTES
------------------------------ ----------
TEST 201326592
IDX_TEST_C1 293601280
suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD;
已解釋。
suk@ORACLE9I> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | | | |
| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |
| 2 | SORT CREATE INDEX | | | | |
| 3 | TABLE ACCESS FULL | TEST | | | |
-----------------------------------------------------------------------
Note: rule based optimization
已選擇11行。
--從執行計劃可以看出，當索引比表大時，rebuild索引用的數據源是基表。
suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD ONLINE;
已解釋。
suk@ORACLE9I> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | | | |
| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |
| 2 | SORT CREATE INDEX | | | | |
| 3 | TABLE ACCESS FULL | TEST | | | |
-----------------------------------------------------------------------
Note: rule based optimization
已選擇11行。
--從執行計劃可以看出，當索引比表大時，rebuild online索引用的數據源是基表。
--我們為TEST添加一列，使得表比索引大
suk@ORACLE9I> ALTER TABLE TEST ADD(C2 CHAR(30) DEFAULT '1');
表已更改。
suk@ORACLE9I> SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('TEST','IDX_TEST_C
1');
SEGMENT_NAME BYTES
------------------------------ ----------
TEST 1476395008
IDX_TEST_C1 293601280
suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD;
已解釋。
suk@ORACLE9I> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | | | |
| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |
| 2 | SORT CREATE INDEX | | | | |
| 3 | INDEX FAST FULL SCAN| IDX_TEST_C1 | | | |
-----------------------------------------------------------------------
Note: rule based optimization
已選擇11行。
--從執行計劃可以看出，當表比索引大時，執行計劃已經改變，rebuild索引是以索引作為數據源的。
suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD ONLINE;
已解釋。
suk@ORACLE9I> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | | | |
| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |
| 2 | SORT CREATE INDEX | | | | |
| 3 | TABLE ACCESS FULL | TEST | | | |
-----------------------------------------------------------------------
Note: rule based optimization
已選擇11行。
--從執行計劃可以看出，當表比索引大時，rebuild online仍然以基表作為數據源。
rebuild模式下，因為表數據不會產生變化，oracle主要考慮性能問題，把更快掃描完成的段作為數據源。在上面的例子中，我們並沒有對表進行分析，故oracle應該根據數據段的大小來決定那個作為數據源的。一般索引欄位比較多，或者對索引欄位的DML操作較多，可能會導致索引比表大，這時oracle就會使用基表作為新索引的數據源進行rebuild了。
而在rebuild online模式下，因為允許DML操作，而表數據變化的同時索引也會跟著變化，為了索引與基表數據的一致性，比如採用基表數據作為數據源，而不能用原索引數據作為數據源。
我們用反證法證明不能用原索引作為新索引的數據源。
例如：
T1發出rebuild online命令
T2刪除某條數據，刪數據的同時，oracle會自動維護了舊索引
T3掃描經過T2數據所在索引節點
T4插入一條記錄，新記錄對應的索引節點剛好重用了T2刪除的數據對應的索引節點空間
如果是這樣的話，新建的索引將不包含T4插入的記錄的信息。所以，rebuild online情況下新索引的數據源不能是原索引。
rebuild online情況下，如果非用原索引作為新索引的數據源的話，用中間表記錄索引變化的方法應該是可以實現的，但由於數據變化會同時引起索引變化的特定決定了這種方法將異常復雜及效率底下，所以oracle不考慮舊索引作為新索引的數據源是有道理的。
結論：
1、rebuild會阻塞對基表的DML操作，但不會影響rebuild期間查詢對原有索引的使用。
2、rebuild的數據源可能是基表，也可能是原索引。取決於基表和原索引的大小，那個小，rebuild時就會用那個作為數據源。這也說明了網上盛傳的rebuild以原索引作為資料庫的說法是不完全正確的。
3、rebuild online運行用戶在索引重建期間執行DML操作。
4、rebuild online的數據源是基表
② sql怎麼建立索引
什麼是索引
拿漢語字典的目錄頁（索引）打比方：正如漢語字典中的漢字按頁存放一樣，SQL Server中的數據記錄也是按頁存放的，每頁容量一般為4K 。為了加快查找的速度，漢語字（詞）典一般都有按拼音、筆畫、偏旁部首等排序的目錄（索引），我們可以選擇按拼音或筆畫查找方式，快速查找到需要的字（詞）。
同理，SQL Server允許用戶在表中創建索引，指定按某列預先排序，從而大大提高查詢速度。
• SQL Server中的數據也是按頁（ 4KB ）存放
• 索引：是SQL Server編排數據的內部方法。它為SQL Server提供一種方法來編排查詢數據。
• 索引頁：資料庫中存儲索引的數據頁；索引頁類似於漢語字（詞）典中按拼音或筆畫排序的目錄頁。
• 索引的作用：通過使用索引，可以大大提高資料庫的檢索速度，改善資料庫性能。
索引類型
• 唯一索引：唯一索引不允許兩行具有相同的索引值
• 主鍵索引：為表定義一個主鍵將自動創建主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引要求主鍵中的每個值是唯一的，並且不能為空
• 聚集索引(Clustered)：表中各行的物理順序與鍵值的邏輯（索引）順序相同，每個表只能有一個
• 非聚集索引(Non-clustered)：非聚集索引指定表的邏輯順序。數據存儲在一個位置，索引存儲在另一個位置，索引中包含指向數據存儲位置的指針。可以有多個，小於249個
索引類型：再次用漢語字典打比方，希望大家能夠明白聚集索引和非聚集索引這兩個概念。
唯一索引：
唯一索引不允許兩行具有相同的索引值。
如果現有數據中存在重復的鍵值，則大多數資料庫都不允許將新創建的唯一索引與表一起保存。當新數據將使表中的鍵值重復時，資料庫也拒絕接受此數據。例如，如果在stuInfo表中的學員員身份證號(stuID) 列上創建了唯一索引，則所有學員的身份證號不能重復。
提示：創建了唯一約束，將自動創建唯一索引。盡管唯一索引有助於找到信息，但為了獲得最佳性能，建議使用主鍵約束或唯一約束。
主鍵索引：
在資料庫關系圖中為表定義一個主鍵將自動創建主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引要求主鍵中的每個值是唯一的。當在查詢中使用主鍵索引時，它還允許快速訪問數據。
聚集索引（clustered index）
在聚集索引中，表中各行的物理順序與鍵值的邏輯（索引）順序相同。表只能包含一個聚集索引。例如：漢語字（詞）典默認按拼音排序編排字典中的每頁頁碼。拼音字母a，b，c，d……x，y，z就是索引的邏輯順序，而頁碼1，2，3……就是物理順序。默認按拼音排序的字典，其索引順序和邏輯順序是一致的。即拼音順序較後的字（詞）對應的頁碼也較大。如拼音「ha」對應的字(詞)頁碼就比拼音「ba」 對應的字(詞)頁碼靠後。
非聚集索引(Non-clustered)
如果不是聚集索引，表中各行的物理順序與鍵值的邏輯順序不匹配。聚集索引比非聚集索引（nonclustered index）有更快的數據訪問速度。例如，按筆畫排序的索引就是非聚集索引，「1」畫的字（詞）對應的頁碼可能比「3」畫的字（詞）對應的頁碼大（靠後）。
提示：SQL Server中，一個表只能創建1個聚集索引，多個非聚集索引。設置某列為主鍵，該列就默認為聚集索引
如何創建索引
使用T-SQL語句創建索引的語法：
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]
INDEX index_name
ON table_name (column_name…)
[WITH FILLFACTOR=x]
q UNIQUE表示唯一索引，可選
q CLUSTERED、NONCLUSTERED表示聚集索引還是非聚集索引，可選
q FILLFACTOR表示填充因子，指定一個0到100之間的值，該值指示索引頁填滿的空間所佔的百分比
在stuMarks表的writtenExam列創建索引：
USE stuDB
GO
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'IX_writtenExam')
DROP INDEX stuMarks.IX_writtenExam
/*--筆試列創建非聚集索引：填充因子為30％--*/
CREATE NONCLUSTERED INDEX IX_writtenExam
ON stuMarks(writtenExam)
WITH FILLFACTOR= 30
GO
/*-----指定按索引 IX_writtenExam 查詢----*/
SELECT * FROM stuMarks (INDEX=IX_writtenExam)
WHERE writtenExam BETWEEN 60 AND 90
雖然我們可以指定SQL Server按哪個索引進行數據查詢，但一般不需要我們人工指定。SQL Server將會根據我們創建的索引，自動優化查詢。
索引的優缺點
• 優點
– 加快訪問速度
– 加強行的唯一性
• 缺點
– 帶索引的表在資料庫中需要更多的存儲空間
– 操縱數據的命令需要更長的處理時間，因為它們需要對索引進行更新
創建索引的指導原則
• 請按照下列標准選擇建立索引的列。
– 該列用於頻繁搜索
– 該列用於對數據進行排序
• 請不要使用下面的列創建索引：
– 列中僅包含幾個不同的值。
– 表中僅包含幾行。為小型表創建索引可能不太劃算，因為SQL Server在索引中搜索數據所花的時間比在表中逐行搜索所花的時間更長
③ SQL Server 2000資料庫中如何重建索引
連續索引頁由從一個頁到下一個頁的指針鏈接在一起。當對數據的更改影響到索引時，索引中的信息可能會在資料庫中分散開來。重建索引可以重新組織索引數據（對於聚集索引還包括表數據)的存儲，清除碎片。這可通過減少獲得請求數據所需的頁讀取數來提高磁碟性能。 在Microsoft�0�3 SQL Server�6�4 2000 中，如果要用一個步驟重新創建索引，而不想刪除舊索引並重新創建同一索引，則使用 CREATE INDEX 語句的 DROP_EXISTING 子句可以提高效率。這一優點既適用於聚集索引也適用於非聚集索引。 以刪除舊索引然後重新創建同一索引的方式重建聚集索引，是一種昂貴的方法，因為所有二級索引都使用聚集鍵指向數據行。如果只是刪除聚集索引然後重新創建，則會使所有非聚集索引都被刪除和重新創建兩次。一旦刪除聚集索引並再次重建該索引，就會發生這種情形。通過在一個步驟中重新創建索引，可以避免這一昂貴的做法。在一個步驟中重新創建索引時，會告訴 SQL Server 要重新組織現有索引，避免了刪除和重新創建非聚集索引這些不必要的工作。該方法的另一個重要優點是可以使用現有索引中的數據排序次序，從而避免了對數據重新排序。這對於聚集索引和非聚集索引都十分有用，可以顯著減少重建索引的成本。另外，通過使用 DBCC DBREINDEX 語句，SQL Server 還允許對一個表重建（在一個步驟中）一個或多個索引，而不必單獨重建每個索引。 DBCC DBREINDEX 也可用於重建執行 PRIMARY KEY 或 UNIQUE 約束的索引，而不必刪除並創建這些約束（因為對於為執行 PRIMARY KEY 或 UNIQUE 約束而創建的索引，必須先刪除該約束，然後才能刪除該索引）。
④ 如何重建SQL索引 要具體的命令
USE TableName
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
⑤ sql問題，索引的修改。alter index語句如何使用，謝謝
alter index常用的語法如下：
(1)重建指定索引：
ALTER INDEX ind ON TA
REBUILD;
(2)重建全部索引：
ALTER INDEX ALL ON TA
REBUILD;
(3)禁用索引：
ALTER INDEX ALL ON TA
DISABLE;
（再次啟用使用REBUILD重建而不是ENABLED）
(4)指定參數重建索引：
ALTER INDEX ALL ON TA
REBUILD WITH(FILLFACTOR=80);
(5)指定參數修改索引：
ALTER INDEX ALL ON TA
SET(IGNORE_DUP_KEY = ON);
注意：alter index語法，不能用於修改索引定義，如添加或刪除列，或更改列的順序
⑥ SQL中如何重建一張表的索引
SELECT
tab.name AS [表名],
idx.name AS [索引名稱],
col.name AS [列名]
FROM
sys.indexes idx
JOIN sys.index_columns idxCol
ON (idx.object_id = idxCol.object_id
AND idx.index_id = idxCol.index_id
)
JOIN sys.tables tab
ON (idx.object_id = tab.object_id)
JOIN sys.columns col
ON (idx.object_id = col.object_id
AND idxCol.column_id = col.column_id);
⑦ sql server如何重建索引到其它文件組
在日常工作中，我們發現很多實施案例中，sql server的資料庫數據與索引在一起。我見過一個客戶的，他的資料庫總共大小才60g，但索引與數據完全混在一起，從管理資料庫的直覺來看，性能方面肯定有問題，所以我建議他們，不管怎麼樣，把索引與資料庫分開，對性能是有好處的！但是sql server的索引，想要通過重建的方式，把數據與索引分開，並不是一件容易的事懷，在使用rebuild時，並不能增加文件組選項。後來研究發現，可以通過以下方式把數據與非聚簇索引分開，具體如下：
set nocount on
declare @index table
(
object_id int,
objectName sysname,
index_id int,
indexName sysname,
fill_factor tinyint,
allow_row_locks bit,
allow_page_locks bit,
is_padded bit,
indexText varchar(max),
indexTextEnd varchar(max)
)
declare @indexColumn table
(
object_id int,
index_id int,
column_id int,
index_column_id int,
max_index_column_id int,
is_descending_key bit,
is_included_column bit,
columnName varchar(255),
indexText varchar(max) null
)
insert into @index
select
i.object_id,
object_name(i.object_id),
i.index_id,
i.name,
fill_factor,
allow_row_locks,
allow_page_locks,
is_padded,
'CREATE NONCLUSTERED INDEX [' + i.name + '] ON [dbo].[' + object_name(i.object_id) + '] ' + char(13),
'WITH (PAD_INDEX = ' +
CASE WHEN is_padded = 1 THEN ' ON ' ELSE ' OFF ' END +
', STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ' +
CASE WHEN allow_row_locks = 1 THEN ' ON ' ELSE ' OFF ' END +
', ALLOW_PAGE_LOCKS = ' +
CASE WHEN allow_page_locks = 1 THEN ' ON ' ELSE ' OFF ' END +
CASE WHEN fill_factor > 0 THEN ', FILLFACTOR = ' + convert(varchar(3), fill_factor) ELSE '' END +
') ON [IndexFG];print('''+i.name+' @ '+object_name(i.object_id)+''')' --+ CHAR(13) + ' GO;'+ CHAR(13) --注意標紅的地方，這是新的文件組的名稱
from sys.indexes i
where i.type = 2 and not exists(select 1 from sys.key_constraints kc where kc.name=i.name)
and objectproperty(i.object_id , 'IsUserTable') = 1
order by object_name(i.object_id), i.name
insert into @indexColumn
select
i.object_id,
i.index_id,
ic.column_id,
ic.index_column_id,
max(ic.index_column_id) over (partition by i.object_id, i.index_id, is_included_column),
is_descending_key,
is_included_column,
'[' + c.name + ']',
null
from @index i
join sys.index_columns ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
join sys.columns c
on ic.object_id = c.object_id
and ic.column_id = c.column_id
order by i.object_id, i.index_id, ic.index_column_id
declare @fields varchar(max)
declare @object_id int, @index_id int
select @fields = null, @object_id = -1, @index_id = -1
update @indexColumn
set @fields = indexText =
case when object_id = isnull(@object_id, object_id) and index_id = isnull(@index_id, index_id)
then isnull(@fields + ', ', ' ') + columnName + case when is_descending_key = 0 then ' ASC' else ' DESC' end
else columnName + case when is_descending_key = 0 then ' ASC' else ' DESC' end
end,
@object_id = case when object_id <> @object_id
then object_id else @object_id end,
@index_id = case when index_id <> @index_id
then index_id else @index_id end
from @indexColumn
where is_included_column = 0
select @fields = null, @object_id = -1, @index_id = -1
update @indexColumn
set @fields = indexText =
case when object_id = isnull(@object_id, object_id) and index_id = isnull(@index_id, index_id)
then isnull(@fields + ', ', ' ') + columnName
else columnName
end,
@object_id = case when object_id <> @object_id
then object_id else @object_id end,
@index_id = case when index_id <> @index_id
then index_id else @index_id end
from @indexColumn
where is_included_column = 1
update @index
set indexText = i.indexText + '( ' + char(13) + char(9) + ic.indexText + char(13) + ') '
from @index i join @indexColumn ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
and ic.index_column_id = ic.max_index_column_id
and ic.is_included_column = 0
update @index
set indexText = i.indexText + 'INCLUDE ( ' + char(13) + char(9) + ic.indexText + char(13) + ') '
from @index i join @indexColumn ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
and ic.index_column_id = ic.max_index_column_id
and ic.is_included_column = 1
update @index
set indexText = indexText + indexTextEnd
from @index
select indexText, objectName, indexName
from @index
最後的查詢結果第一行就是執行的命令！
⑧ sql 如何建全文索引
一、設置詞法分析器
Oracle實現全文檢索，其機制其實很簡單。即通過Oracle專利的詞法分析器(lexer),將文章中所有的表意單元（Oracle稱為term）找出來，記錄在一組以dr$開頭的表中，同時記下該term出現的位置、次數、hash值等信息。檢索時，Oracle從這組表中查找相應的term，並計算其出現頻率，根據某個演算法來計算每個文檔的得分（score）,即所謂的『匹配率』。而lexer則是該機制的核心，它決定了全文檢索的效率。Oracle針對不同的語言提供了不同的lexer,而我們通常能用到其中的三個：
basic_lexer:針對英語。它能根據空格和標點來將英語單詞從句子中分離，還能自動將一些出現頻率過高已經失去檢索意義的單詞作為『垃圾』處理，如if , is等，具有較高的處理效率。但該lexer應用於漢語則有很多問題，由於它只認空格和標點，而漢語的一句話中通常不會有空格，因此，它會把整句話作為一個term,事實上失去檢索能力。以『中國人民站起來了』這句話為例，basic_lexer分析的結果只有一個term ,就是『中國人民站起來了』。此時若檢索『中國』，將檢索不到內容。
chinese_vgram_lexer:專門的漢語分析器，支持所有漢字字元集（SUTF8）。該分析器按字為單元來分析漢語句子。『中國人民站起來了』這句話，會被它分析成如下幾個term: 『中』，『中國』，『國人』，『人民』，『民站』，『站起』，起來』，『來了』，『了』。可以看出，這種分析方法，實現演算法很簡單，並且能實現『一網打盡』，但效率則是差強人意。
chinese_lexer:這是一個新的漢語分析器，只支持utf8字元集。上面已經看到，chinese vgram lexer這個分析器由於不認識常用的漢語詞彙，因此分析的單元非常機械，像上面的『民站』，『站起』在漢語中根本不會單獨出現，因此這種term是沒有意義的，反而影響效率。chinese_lexer的最大改進就是該分析器能認識大部分常用漢語詞彙，因此能更有效率地分析句子，像以上兩個愚蠢的單元將不會再出現，極大提高了效率。但是它只支持utf8,如果你的資料庫是zhs16gbk字元集，則只能使用笨笨的那個Chinese vgram lexer.
如果不做任何設置，Oracle預設使用basic_lexer這個分析器。要指定使用哪一個lexer,可以這樣操作：
BEGIN
ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');
END;
/
其中my_lexer是分析器名。
二、建立全文索引
在建立intermedia索引時，指明所用的lexer:
CREATE INDEX myindex ON mytable(mycolumn) indextype is ctxsys.context parameters('lexer my_lexer');
※個人體會：全文索引建立後，用pl/sql developer工具view table，在index這一欄是看不到索引信息的。
而本人在刪除全文索引時遇到過一下報錯：
SQL> drop index searchkeytbl_key;
drop index searchkeytbl_key
ORA-29868: cannot issue DDL on a domain index marked as LOADING
解決方法：
ORA-29868: cannot issue DDL on a domain index marked as LOADING
說明：在創建索引的時候斷開、重啟等導致索引中斷沒有執行成功，之後再drop或者rebuild等操作的時候都會報此錯誤
解決：只能drop index ind_name force強行刪除，然後再重建
三、索引同步維護
用以下的兩個job來完成(該job要建在和表同一個用戶下) :
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.sync_index(''index_name'');',
SYSDATE, 'SYSDATE + (1/24/4)');
commit;
END; //同步
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''myindex'',''FULL'');',
SYSDATE, 'SYSDATE + 1');
commit; //優化
建完後手動運行下：
exec dbms_job.run(jobno);
※個人體會：運行job可能會有問題，此時可以單獨運行索引，嘗試一下
exec ctx_ddl.sync_index('index_name');
如果單獨運行沒有問題，則檢查job是否寫錯或者當前操作的oracle資料庫用戶有無運行存儲過程的許可權
SQL> exec dbms_job.run(190);
begin dbms_job.run(190); end;
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 406
ORA-06512: at "SYS.DBMS_JOB", line 272
ORA-06512: at line 1
以上報錯就是用戶沒有運行任何存儲過程造成的，此時需要對用戶加上這個許可權：
SQL> grant execute any procere to oracle_username;
再看一下job的情況
select * from user_jobs;
四、測試
關聯查詢： select * from table_name where contains (column_name,'keyword') >0;
SQL> select * from searchkeytbl where type='城市' and contains (key,'楊浦') >0;
USERNAME TYPE KEY
-------------------- ---------------------------------------- --------------------------------------------------------------------------------
mujian80 城市 上海市楊浦區
五、問題
加全文索引遇到的問題(不斷更新)
SQL> create index gh_ghname_idx on gh(ghname) indextype is ctxsys.context parameters('lexer gh_ghname_lexer');
create index gh_ghname_idx on gh(ghname) indextype is ctxsys.context parameters('lexer gh_ghname_lexer')
ORA-24795: Illegal COMMIT attempt made
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvddl.IndexCreate
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvdml.MaintainKTab
ORA-24795: Illegal COMMIT attempt made
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364
To avoid the error, please use one of the following solutions
1. Don't use a 32k-blocksized tablespace to store the internal index objects
- or -
2. Download Patch 5596325 from Metalink and apply it as described in the README file.
看一下 可能是用於創建索引的表空間不夠了
reports——>DBA——>total free space pl/sql developer工具，查看錶空間的剩餘空間
select * from v$datafile; 查看數據文件信息
⑨ 如何重建sql資料庫索引
數據更新是一種常見的操作，然後數據倉庫的概念一般要求的是數據是集成、穩定的。HIVE作為一種分布式環境下以HDFS為支撐的數據倉庫，它同樣更多的要求數據是不可變的。
然而現實很多任務中，往往需要對數據進行更新操作，經查，Hive自0.11版本之後就提供了更新操作。於是想著試驗一下，看看HIVE更新的操作和性能。
⑩ sql創建索引的命令
1.添加PRIMARY KEY（主鍵索引）
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE (
`column`
)
3.添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )