Thursday, December 27, 2012

NOSORT and ORA-01409

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>

No comments: