rownum和order by 以及index的关系

 本篇文章主要讨论rownum和排序以及索引的关系
问题1、rownum正序是指什么？
问题2、rownum和order by语句在一起时，执行的先后顺序是什么？order by语句如何影响rownum正序？
问题3、rownnum与索引之间的关系？

-- Create table
create table DAIMIN
( ID       NUMBER not null,
  PARENTID NUMBER
);
alter table daimin add constraint pk_daimin primary key(id);

SQL> select *from DAIMIN;
        ID   PARENTID
---------- ----------
         1          6
         2          3
         5          1
         3          2

问题1、rownum正序是指什么？
    rownum正序是指rownum的返回是按照1,2,3依次递增，如上面的查询显示的效果

问题2、rownum和order by语句在一起时，执行的先后顺序是什么？order by语句如何影响rownum正序？
下面执行两个语句进行比较：
SQL> select rownum as r, t.ID from daimin t order by t.ID;
         R         ID
---------- ----------
         1          1
         2          2
         3          3
         4          5
现象：是rownum是按照正序来返回的
        
该语句的执行计划如下：
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  COUNT               |             |       |       |       |
|   2 |   INDEX FULL SCAN    | PK_DAIMIN   |       |       |       |
--------------------------------------------------------------------
现象：使用了PK_DAIMIN这个主键索引，并且从执行计划中看出COUNT操作之后没有SORT ORDER BY操作
     （Rownum事实上在COUNT (STOPKEY)时产生）

SQL> select rownum as r, t.parentid from daimin t order by t.parentid;
         R   PARENTID
---------- ----------
         3          1
         4          2
         2          3
         1          6
现象：是rownum没有按照正序来返回

该语句的执行计划如下：
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  SORT ORDER BY       |             |       |       |       |
|   2 |   COUNT              |             |       |       |       |
|   3 |    TABLE ACCESS FULL | DAIMIN      |       |       |       |
--------------------------------------------------------------------
现象：没有使用索引查询 ，并且在COUNT操作之后有SORT ORDER BY操作
分析：为什么这两句SQL语句返回的rownum不一样呢？
      主要是由于第一句使用了索引已经排好序，然后再产生Rownum，此时已经是按照parentid排好序的顺序，
      再按照parentid排序之后原来的rownum标识不会被打乱，所以返回的rownum是正序；
      而第二句则使用全表扫描，在全表扫面是查询出来的结果集是按照表中原有的记录的先后顺序来返回的，
      然后在COUNT操作时给返回的记录标记1,2,3，在标好标记之后再按照parentid字段排序，这样就将原来
      在COUNT时的顺序打乱，重新排序，所以返回的rownum不是正序。

问题3、rownnum与索引之间的关系？是不是建了某个字段的索引都会使用索引呢（在没有改字段的where条件的情况下）？
下面做测试试验：
给DAIMIN表中的PARENTID字段建索引
create index DM_PARENTID on DAIMIN (PARENTID);

select rownum as r, t.parentid from daimin t order by t.parentid;
其执行计划：
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  SORT ORDER BY       |             |       |       |       |
|   2 |   COUNT              |             |       |       |       |
|   3 |    TABLE ACCESS FULL | DAIMIN      |       |       |       |
--------------------------------------------------------------------

查询结果：
SQL> select rownum as r, t.parentid from daimin t order by t.parentid;
         R   PARENTID
---------- ----------
         3          1
         4          2
         2          3
         1          6

分析：给parentid字段建了索引，但是该语句并没有使用索引，仍然采用的是全表扫描，所以返回的rownum仍然不是正序。

如果修改DAIMIN表中的PARENTID字段为not null
alter table DAIMIN modify PARENTID not null;

再次查询该语句的其执行计划：
---------------------------------------------------------------------
| Id  | Operation            |  Name        | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |       |       |       |
|   1 |  COUNT               |              |       |       |       |
|   2 |   INDEX FULL SCAN    | DM_PARENTID  |       |       |       |
---------------------------------------------------------------------

查询结果：
SQL> select rownum as r, t.parentid from daimin t order by t.parentid;
         R   PARENTID
---------- ----------
         1          1
         2          2
         3          3
         4          6

分析：将parentid字段设置为not null字段之后，由于在order by parentid时会采用parentid字段的索引DM_PARENTID，
      所以改语句查询出来的rownum的顺序是正序。

问题：为什么给parentid字段设置为not null字段之后，此时该查询语句会使用索引呢（在没有该字段的where条件时）？
      这里主要是牵涉到order by使用索引的条件，什么情况下order by会使用索引？
order by 使用索引是有条件的：
1)ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
2)ORDER BY中所有的列必须定义为非空.
3)另外，如果ORDER BY中的列在where 条件中出现，也会使用索引

上面的试验是满足了ORDER BY中列parentid为非空，所以order by parentid使用parentid字段的索引。

对于复合索引：
create index DM_INDEX on DAIMIN (ID, PARENTID);

以下几句都使用了索引：
Select rownum, t.* from DAIMIN t order by ID, PARENTID;  --使用复合索引DM_INDEX
select rownum, t.* from DAIMIN t order by  t.ID desc, t.PARENTID desc;    --使用复合索引DM_INDEX
Select rownum, t.* from DAIMIN t order by ID;           --使用主键索引
Select rownum, t.* from DAIMIN t order by ID desc;      --使用主键索引

以下几句不使用索引：
select rownum, t.* from DAIMIN t order by  t.ID asc, t.PARENTID desc;   --不使用复合索引DM_INDEX，全表扫描

Select rownum, t.* from DAIMIN t order by PARENTID;
（该句parentid字段还没有设置为not null时，只有复合索引DM_INDEX，此时不使用复合索引）

 

总结：
1)默认情况是按顺序先取rownum,再order by
2)如果order by 满足使用索引的情况，则先order by，再取rownum

order by 使用索引是有条件的：
1)ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
2)ORDER BY中所有的列必须定义为非空.
3)另外，如果ORDER BY中的列在where 条件中出现，也会使用索引