索引快速扫描(FFS)跟索引全扫描一样也是扫描索引块,但是不同在于FFS从索引段头开始多块读HWM以下的所有根块、枝块、叶块,所以通过FFS读出来的数据读取顺序是由物理存储位置决定的、无序的。
转入正题:今天做了个实验,以SCOTT的EMP表为模版建表,保存后再插入大量数据,并在EMPNO上见主键约束。分析表后查表中前十条记录,得到的结果却和想象的不一样,请看实验过程。
SQL> create table remp as select * from scott.emp;
Table created.
SQL> alter table remp add constraint pk_remp primary key (empno);
Table altered.
SQL> select * from remp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------------- ---------- --------- ----------------- ------------ ----------------- ----------------- -----------------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 这应该是前十条记录
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> create table t as select * from scott.emp where empno=7934;
Table created.
SQL> begin
2 for i in 1..7000 loop
3 update t set empno=i;
4 insert into remp select * from t;
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select count(*) from remp; 现在表中有了7000多条数据
COUNT(*)
-----------------
7014
SQL> analyze table remp compute statistics;
Table analyzed.
SQL> set autot on exp
SQL> select empno from remp where rownum<10; 问题在这里,按事先猜想执行计划的确是用索引快速扫描,但结果不应该是这样的。
EMPNO
-----------------
1
2
3
4
5
6
7
8
9
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1042857412
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 27 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEX FAST FULL SCAN| PK_REMP | 9 | 27 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
SQL> select /*+index(remp)*/ empno from remp where rownum<10;
EMPNO
-----------------
1
2
3
4
5
6
7
8
9
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 635523186
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 27 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEX FULL SCAN| PK_REMP | 9 | 27 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
索引全扫描和索引快速扫描得到的结果相同这让我不理解了,望大神给解答下。