[20201110]oracle建立索引nosrt.txt

[20201110]oracle建立索引nosrt.txt

--//被別人問的問題，實際上這個引數很無聊，僅僅在表排序的情況下減少排序操作，要求你表中資料已經有序的情況下。

1.環境：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試:
SCOTT@book> create table t1 as select * from dba_objects;
Table created.

SCOTT@book> create index i_t1_object_id on t1(object_id) nosort ;
create index i_t1_object_id on t1(object_id) nosort
                               *
ERROR at line 1:
ORA-01409: NOSORT option may not be used; rows are not in ascending order

--//表T1並沒有按照object_id排序，建立索引時報錯。

SCOTT@book> create table t2 as select * from dba_objects order by object_id;
Table created.

SCOTT@book> create index i_t2_object_id on t2(object_id) nosort ;
Index created.

--//表T2按照object_id排序，建立索引時OK。
--//除非你匯入資料時按照該欄位排序，建立的索引才有用。