ORDER BY to use Index

It's difficult thing, If we try to learn something every days, every days. However It's a good thing, If we love and enjoy to learn.

So I planed to learn something (new) every days. I hope so.
I learned... (It's not new... just something we might forgot).

If a user requires the optimizer to consider using an index to drive an ORDER BY statement instead of sorting, then the indexed columns must be NOT NULL as otherwise they are not considered.

SQL> create table a (x VARCHAR2(128), y VARCHAR2(19));

Table created.

SQL> desc A
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
X VARCHAR2(128)
Y VARCHAR2(19)

SQL> insert into a select OBJECT_NAME, OBJECT_TYPE from user_objects;

43 rows created.

SQL> commit;

Commit complete.

SQL> create index a_indx1 on a (x,y);

Index created.

SQL> exec dbms_stats.gather_index_stats(USER,'A_INDX1');

PL/SQL procedure successfully completed.

SQL> explain plan for select * from a order by x,y;

Explained.

SQL> select * from TABLE(dbms_xplan.display(NULL, NULL , 'ALL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3819873049
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43 | 3311 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 43 | 3311 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| A | 43 | 3311 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

Indexes Columns be NULL, then change to be Not NULL.

SQL> alter table a modify (x not null, y not null);

Table altered.

SQL> desc A
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
X NOT NULL VARCHAR2(128)
Y NOT NULL VARCHAR2(19)

SQL> explain plan for select * from a order by x,y;

Explained.

SQL> select * from TABLE(dbms_xplan.display(NULL, NULL , 'ALL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2241847120
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43 | 3311 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | A_INDX1 | 43 | 3311 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

After changed to be Not NULL, the optimizer consider an index.