oracle的表类型有哪些,oracle的表类型

Oracle表分类：普通堆表，索引组织表，簇表，全局临时表，外部表，分区表。

普通堆表的不足之处：

1.表更新日志开销(可以在适当的场合选择全局临时表)

2.表的delete操作有瑕疵(可在适当场合考虑全局临时表和分区表)

3.表的数据太大检索慢(可在适当场合选择分区表)

4.索引回表读开销大(可在适当的场合选择索引组织表)

5.有序插入难有序读出,(后插入的记录首先寻找并填充 delete 操作后的 "空块") (可在适当的场合选择簇表)

回表：在数据中，当查询数据的时候，在索引中查找索引后，获得该行的rowid，根据rowid再查询表中数据，就是回表。

create table s1 as select * from dba_objects where rownum<=200;

create index index_obj on s1(object_id);

set autotrace traceonly;

LISN@orcl>select * from s1 where object_id < 10;

select * from s1 where object_id < 10;

8 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3647141056

-----------------------------------------------------------------------------------------

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

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |        |     8 |  1656 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| S1    |     8 |  1656 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN        | INDEX_OBJ |     8 |    |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"<10)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

4  consistent gets

0  physical reads

0  redo size

2235  bytes sent via SQL*Net to client

523  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

8  rows processed

-- INDEX RANGE SCAN| INDEX_OBJ    不需要回表

#####################