INDEX FAST FULL SCAN与INDEX FULL SCAN与区别

索引快速全扫描的执行结果不一定是有序的。这是因为索引快速全扫描时Oracle是根据索引行在磁盘上的物理存储顺序来扫描，而不是根据索引行的逻辑顺序来扫描的，所以扫描结果才不一定有序.


我们来测试一下.
1.创建测试表
create table system.wh(num1 number(10));


begin
    for i in 1 .. 10000 loop      
             insert into wh values(i) select trunc(dbms_random.value(0,10000)) from dual;;
             commit;
    end loop;
end;
/


create index ll on wh(num1);


SQL> select count(*) from wh;


  COUNT(*)
----------
  20480000


有2亿条数据


SQL> SQL> select /*+index(t ll)*/ distinct num1 from wh t where num1<10 ;


      NUM1
----------
         0
         1
         4
         5
         7
         9


6 rows selected.


SQL> 
SQL> select /*+index_ffs(t ll)*/ distinct num1 from wh t where num1<10;




      NUM1
----------
         1
         4
         5
         7
         0
         9


6 rows selected.


我们再继续测试,如果结果是有序的，那么order by情况如何呢.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bv7d314kjy4vu, child number 0
-------------------------------------
select /*+index(t ll)*/ distinct num1 from wh t where num1<10 order by
num1


Plan hash value: 569328679


--------------------------------------------
| Id  | Operation          | Name | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT   |      |        |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   1 |  SORT UNIQUE NOSORT|      |      6 |
|*  2 |   INDEX RANGE SCAN | LL   |  20484 |
--------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("NUM1"<10)


Note
-----


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - Warning: basic plan statistics not available. These are only collected when
:


       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system leve
l






26 rows selected.


 SQL> select /*+index_ffs(t ll)*/ distinct num1 from wh t where num1<10 order by num1;




      NUM1
----------
         0
         1
         4
         5
         7
         9


6 rows selected.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  g9m4jcpw16wuq, child number 0
-------------------------------------
select /*+index_ffs(t ll)*/ distinct num1 from wh t where num1<10 order
by num1


Plan hash value: 451598605


--------------------------------------------------------------------------
| Id  | Operation             | Name | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |        |       |       |          |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   1 |  SORT UNIQUE          |      |      6 |  2048 |  2048 | 2048  (0)|
|*  2 |   INDEX FAST FULL SCAN| LL   |  20484 |       |       |          |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter("NUM1"<10)


Note
-----


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - Warning: basic plan statistics not available. These are only collected when
:


       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system leve
l






26 rows selected.


这回出现了  SORT UNIQUE NOSORT 与 SORT UNIQUE          
猜测就是第一条SQL不用sort,而第二条sql产生了sort,这样理论就合理了


索引快速全扫描可以使用多块读，也可以并行执行。
这次就不测试速度了，下次再测试

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7569309/viewspace-2134320/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7569309/viewspace-2134320/