When you create an index, Oracle usually does a sort. I read about the NOSORT
option recently. This allows Oracle to create an index without doing a
sort. I decided to give it a try on an Oracle 9.2.0.7.0 database. First I
created a table, counted the number of sorts my session had done,
created an index on the table, counted the number of sorts again and saw
that it had increased by 1:
SQL> create table andrews_table
2 as select owner, table_name
3 from dba_tables
4 /
Table created.
SQL> select a.name, b.value
2 from v$sysstat a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%sorts%'
5 /
NAME VALUE
-------------------- ----------
sorts (memory) 17
sorts (disk) 0
sorts (rows) 11527
SQL> create index andrews_index
2 on andrews_table(table_name)
3 /
Index created.
SQL> select a.name, b.value
2 from v$sysstat a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%sorts%'
5 /
NAME VALUE
-------------------- ----------
sorts (memory) 18
sorts (disk) 0
sorts (rows) 13069
SQL>
Then I dropped the index and tried to recreate it with the NOSORT option. I expected this to fail as the table was not ordered on the indexed column:
SQL> drop index andrews_index
2 /
Index dropped.
SQL> create index andrews_index
2 on andrews_table(table_name)
3 nosort
4 /
on andrews_table(table_name)
*
ERROR at line 2:
ORA-01409: NOSORT option may not be used; rows are not
in ascending order
SQL>
Finally, I dropped and recreated the table in table_name order, counted the number of sorts my session had done, created an index with the NOSORT option on the sorted table_name column, counted the number of sorts again and saw that it had not increased:
SQL> drop table andrews_table
2 /
Table dropped.
SQL> create table andrews_table
2 as select owner, table_name
3 from dba_tables
4 order by table_name
5 /
Table created.
SQL> select a.name, b.value
2 from v$sysstat a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%sorts%'
5 /
NAME VALUE
-------------------- ----------
sorts (memory) 35
sorts (disk) 0
sorts (rows) 26085
SQL> create index andrews_index
2 on andrews_table(table_name)
3 nosort
4 /
Index created.
SQL> select a.name, b.value
2 from v$sysstat a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%sorts%'
5 /
NAME VALUE
-------------------- ----------
sorts (memory) 35
sorts (disk) 0
sorts (rows) 26085
SQL>
