--对索引列的查询和排序问题的研究
--1.创建表并插入模拟数据
create table test(id number,name varchar2(10));
insert into test values(1,'测试1');
insert into test values(2,'测试2');
insert into test values(5,'测试5');
insert into test values(4,'测试4');
insert into test values(3,'测试3');
insert into test values(6,'测试6');
insert into test values(9,'测试9');
insert into test values(8,'测试8');
insert into test values(7,'测试7');
commit;
--创建索引前:
--1.表的查询:表中的数据顺序为数据插入的顺序
SQL> select rownum,id from test where id<10;
ROWNUM ID
---------- ----------
1 1
2 2
3 5
4 4
5 3
6 6
7 9
8 8
9 7
9 rows selected
--2.排序操作: 先从表中查数据,同时给每行加上一个行号,最后再进行排序
SQL> select rownum,id from test where id<10 order by id asc;
ROWNUM ID
---------- ----------
1 1
2 2
5 3
4 4
3 5
6 6
9 7
8 8
7 9
9 rows selected
create index i_test_id on test(id);
--创建索引后:
--1.表的查询:从索引段中查找数据，排好序，再给每行加上行号
SQL> select rownum,id from test where id<10;
ROWNUM ID
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
--2.排序操作:找数据=> 排序 => 置行号
SQL> select rownum,id from test where id<10 order by id;
ROWNUM ID
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
9 rows selected
SQL> select rownum,id from test where id<10 order by id desc;
ROWNUM ID
---------- ----------
1 9
2 8
3 7
4 6
5 5
6 4
7 3
8 2
9 1
