本讲内容主要包括：
1.分区表及其在线重定义
2.索引组织表
3.簇表
4.预估表大小和增长趋势
一:分区表
分区表可以将表分多个区进行存储，可以存储在不同的表空间上，为单个分区单独将来索引；单独truncate某个分区等；优势很明显，分区表主要有：范围分区，哈希分区，散列分区，范围哈希，范围散列等几种；
1.范围分区表，范围分区表时候值分布比较均匀的表；分区表在dba_tables表中的存储表空间字段为空
SQL> CREATE TABLE hr.t04310_a ( a NUMBER) PARTITION BY RANGE (a)
2 (PARTITION t04310a_p1 VALUES LESS THAN (10000) TABLESPACE USERS,
3 PARTITION t04310a_p2 VALUES LESS THAN (20000) TABLESPACE EXAMPLE,
4 PARTITION t04310a_p3 VALUES LESS THAN (30000) TABLESPACE "USERS",
5 PARTITION t04310a_p4 VALUES LESS THAN (40000) TABLESPACE "EXAMPLE",
6 PARTITION t04310a_p5 VALUES LESS THAN (50000) TABLESPACE "USERS",
7 PARTITION t04310a_p6 VALUES LESS THAN (60000) TABLESPACE "EXAMPLE",
8 PARTITION t04310a_p7 VALUES LESS THAN (70000) TABLESPACE "USERS",
9 PARTITION t04310a_p8 VALUES LESS THAN (80000) TABLESPACE "EXAMPLE",
10* PARTITION t04310a_p9 VALUES LESS THAN (MAXVALUE) TABLESPACE "USERS")
Table created.
SQL> select table_name,partitioning_type,partition_count from dba_part_tables pt
2 where pt.owner='HR' and pt.table_name='T04310_A';
TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT
---------- --------------------- ---------------
T04310_A RANGE 9
SQL> select tablespace_name,table_name from dba_tables t
2 where t.owner='HR' and t.table_name='T04310_A';
TABLESPACE TABLE_NAME
---------- ----------
T04310_A
新建普通表，分别插入100000条数据，并收集优化器统计信息
SQL> create table hr.t04310_b(a number);
Table created.
SQL> begin
2 for i in 1..100000
3 loop
4 insert into hr.t04310_a values (i);
5 end loop;
6 commit;
7 end;
PL/SQL procedure successfully completed.
SQL> begin
2 for i in 1..100000
3 loop
4 insert into hr.t04310_b values (i);
5 end loop;
6 commit;
7* end;
PL/SQL procedure successfully completed.
SQL> select count(*) from hr.t04310_a;
COUNT(*)
----------
100000
SQL> select count(*) from hr.t04310_b;
COUNT(*)
----------
100000
SQL> exec dbms_stats.gather_table_stats('HR','T04310_A');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('HR','T04310_B');
PL/SQL procedure successfully completed.
打开任务计划，执行查询比较，可以看出分区表的优势
SQL> alter system flush buffer_cache;
System altered.
SQL> conn hr/hr
Connected.
SQL> set timing on
SQL> set autot traceonly;
SQL> select * from hr.t04310_a where a > 50000;
50000 rows selected.
Elapsed: 00:00:00.50
Execution Plan
----------------------------------------------------------
Plan hash value: 4164223666
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49995 | 195K| 23 (5)| 00:00
:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 49995 | 195K| 23 (5)| 00:00
:01 | 6 | 9 |
|* 2 | TABLE ACCESS FULL | T04310_A | 49995 | 195K| 23 (5)| 00:00
:01 | 6 | 9 |
--------------------------------------------------------------------------------
Pr