熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle數據獲取方式測試

2013-11-13 16:10:10  來源: Oracle 

  首先建立實驗環境

  create table test as select * from dba_objects where =;

  create index ind_test_id on test(object_id);

  insert into test select * from dba_objects

  where object_id is not null and object_id> order by object_id desc;

  analyze table test compute statistics for table for all columns for all indexes;

  Table Access Full

  SQL> set autotrace trace;

  SQL> select object_id from test;

  set autotrace trace;

  select object_id from test;

  | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

   |  TABLE ACCESS FULL| TEST | |   K |      ()| :: |

  注意這是因為object_id列默認是可以為null的如果修改成not null那麼獲取方式會變成什麼方式?

  Index Fast Full Scan

  alter table test modify(object_id not null);

  select object_id from test;

  | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

   |  INDEX FAST FULL SCAN| IND_TEST_ID | |   K| ()| :: |

  Index Full Scan

  select/*+ index(test ind_TEST_ID)*/ object_id from test;

  | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

   |  INDEX FULL SCAN| IND_TEST_ID | |   K| ()| :: |

  Index Range Scan

  select/*+ index(test ind_TEST_ID)*/ object_id from test  where object_id < ;

  | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

   |  INDEX RANGE SCAN| IND_TEST_ID | |   K|      ()| ::

  SQL> select object_id from test where rownum<; INDEX FAST FULL SCAN

  OBJECT_ID

  

  

  

  

  

  

  

  

  

  

  

  已選擇

  SQL> select/*+ index(test ind_TEST_ID)*/ object_id from test  where rownum<; INDEX FULL SCAN

  OBJECT_ID

  

  

  

  

  

  

  

  

  

  

  

  已選擇

  select * from test where rownum < ;

    

  其他的不關注只關注OBJECT_ID列


From:http://tw.wingwit.com/Article/program/Oracle/201311/17932.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.