OCP043 第十讲Managing Schema Objects
本讲内容主要包括：
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 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A">50000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3441 consistent gets
99 physical reads
0 redo size
920044 bytes sent via SQL*Net to client
37132 bytes received via SQL*Net from client
3335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50000 rows processed
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from hr.t04310_b where a > 50000;
50000 rows selected.
Elapsed: 00:00:00.47
Execution Plan
----------------------------------------------------------
Plan hash value: 4149340864
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49996 | 195K| 43 (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T04310_B | 49996 | 195K| 43 (5)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A">50000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3518 consistent gets
181 physical reads
0 redo size
920044 bytes sent via SQL*Net to client
37132 bytes received via SQL*Net from client
3335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50000 rows processed
2.分区表的索引
SQL> conn /as sysdba
Connected.
SQL> select count(*) from dba_indexes i where i.owner='HR' and i.table_name='T04310_A';
COUNT(*)
----------
0
SQL> create index i_t04310_a on t04310_a(a) local;
Index created.
SQL> select segment_type from dba_extents e where e.owner='HR'
2 and e.segment_name='I_T04310_A' and rownum <= 3;
SEGMENT_TYPE
-----------------
INDEX PARTITION
INDEX PARTITION
INDEX PARTITION
3：哈希分区表，哈希表适合于倾斜表场合，即大部分值分布在同一个区间，小部分值在另外的区间；等值查询效果比较明显
SQL> CREATE TABLE HR.T04310_C
2 ( A NUMBER, B DATE) PARTITION BY HASH (A)
3* (PARTITION T04310_C_P1 TABLESPACE USERS, PARTITION T04310_C_P2 TABLESPACE EXAMPLE)
Table created.
SQL> create table hr.t04310_d(a number,b date);
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_C'
TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT
---------- --------------------- ---------------
T04310_C HASH 2
SQL> begin
2 for i in 1..5000
3 loop
4 insert into hr.t04310_c values (1,sysdate);
5 end loop;
6 commit;
7* end;
PL/SQL procedure successfully completed
SQL> begin
2 for i in 1..5000
3 loop
4 insert into hr.t04310_d values (1,sysdate);
5 end loop;
6 commit;
7* end;
PL/SQL procedure successfully completed
SQL> begin
2 for i in 1..100
3 loop
4 insert into hr.t04310_c values (i,sysdate);
5 end loop;
6 commit;
7* end;
PL/SQL procedure successfully completed.
SQL> begin
2 for i in 1..100
3 loop
4 insert into hr.t04310_d values (i,sysdate);
5 end loop;
6 commit;
7* end;
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('HR','T04310_C');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('HR','T04310_D');
PL/SQL procedure successfully completed.
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from hr.t04310_c where a=100;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1269988501
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01
| | |
| 1 | PARTITION HASH SINGLE| | 1 | 11 | 3 (0)| 00:00:01
| 1 | 1 |
|* 2 | TABLE ACCESS FULL | T04310_C | 1 | 11 | 3 (0)| 00:00:01
| 1 | 1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"=100)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
6 physical reads
0 redo size
575 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from hr.t04310_d where a=100;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2640584124
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51 | 561 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T04310_D | 51 | 561 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16 consistent gets
14 physical reads
0 redo size
575 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
4：在线重定义分区表
准备基表，创建表依附对象，位图索引和触发器
SQL> create table hr.t04310_e as select * from dba_source;
Table created.
SQL> create bitmap index hr.i_t04310_e on hr.t04310_e(type);
Index created.
SQL> create or replace trigger hr.trg_t04310_e
2 after update of line on hr.t04310_e
3 referencing new as new old as old for each row
4 begin
5 if :old.line <= :new.line
6 then
7 raise_application_error('-20001','new values must big than old');
8 end if;
9* end;
Trigger created
SQL> select * from user_errors;
no rows selected
构造长查询语句，模拟表正在被访问
SQL> select * from hr.t04310_e a,hr.t04310_e b;
开始在线重定义分区表
SQL> CREATE TABLE "HR"."T04310_E$REORG"
2 ( OWNER VARCHAR2(30),NAME VARCHAR2(30), TYPE VARCHAR2(12),LINE NUMBER,TEXT VARCHAR2(4000) )
3 PARTITION BY RANGE ( LINE )
4 (PARTITION line_P1 VALUES LESS THAN (5000) TABLESPACE USERS,
5 PARTITION line_P2 VALUES LESS THAN (MAXVALUE) TABLESPACE EXAMPLE);
Table created.
SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE('"HR"', '"T04310_E"', '"T04310_E$REORG"', NULL, DBMS_REDEFINITION.CONS_USE_ROWID);
3* END;
PL/SQL procedure successfully completed.
SQL> CREATE BITMAP INDEX "HR"."I_T04310_E$REORG" ON "HR"."T04310_E$REORG" ("TYPE") local
2 TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING;
Index created.
SQL> BEGIN
2 DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('"HR"', '"T04310_E"', '"T04310_E$REORG"',
3 DBMS_REDEFINITION.CONS_INDEX, '"HR"', '"I_T04310_E"', '"I_T04310_E$REORG"');
4* END;
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('"HR"', '"T04310_E"', '"T04310_E$REORG"');
3* END;
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE TRIGGER "HR"."TRG_T04310_E$REORG" AFTER
2 UPDATE OF "LINE" ON "HR"."T04310_E$REORG" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW begin
3 if :old.line <= :new.line
4 then
5 raise_application_error('-20001','new values must big than old');
6 end if;
7* end;
Trigger created.
SQL> BEGIN DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('"HR"', '"T04310_E"', '"T04310_E$REORG"',
2 DBMS_REDEFINITION.CONS_TRIGGER, '"HR"', '"TRG_T04310_E"', '"TRG_T04310_E$REORG"');
3* END;
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE('"HR"', '"T04310_E"', '"T04310_E$REORG"');
3* END;
PL/SQL procedure successfully completed
验证结果
SQL> select table_name,partitioning_type,partition_count from dba_part_tables pt
2* where pt.owner='HR' and pt.table_name='T04310_E'
TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT
---------- --------------------- ---------------
T04310_E RANGE 2
5.截断分区表的单个分区
SQL> select count(*) from hr.t04310_a;
COUNT(*)
----------
100000
SQL> alter table hr.t04310_a truncate partition t04310a_p1;
Table truncated.
SQL> select count(*) from hr.t04310_a;
COUNT(*)
----------
90001
二：索引组织表
索引组织表同普通堆表的对比：访问更快，不重复存储，更少的存储空间，使用第二索引，适用于小表
–Have faster key-based access to table data
–Do not duplicate the storage of primary key values
–Require less storage
–Use secondary indexes and logical row IDs
–Have higher availability because table reorganization does not invalidate secondary indexes
索引组织表的限制条件：必须有主键约束且不能延迟，不能是簇表，不能被复合分区，不能包含rowid和long型的字段类型
–Must have a primary key that is not DEFERRABLE
–Cannot be clustered
–Cannot use composite partitioning
–Cannot contain a column of type ROWID or LONG
SQL> CREATE TABLE "HR"."IOT" ( "A" NUMBER, "B" DATE, PRIMARY KEY ("A") VALIDATE ) ORGANIZATION INDEX;
Table created.
SQL> select object_name,object_id from dba_objects o where o.owner='HR' and o.object_name='IOT';
OBJECT_NAM OBJECT_ID
---------- ----------
IOT 68596
三：簇表，即两张表公共存储在同一个区上，join查询的速度较快
簇表分索引簇，哈希簇，排序哈希簇
SQL> create cluster cluster1 (a number) index;
Cluster created.
SQL> create table c1 (a number,b varchar2(20)) cluster cluster1(a);able created
Table created
SQL> create table c2 (a number,b date) cluster cluster1(a);
Table created
SQL> create cluster cluster2 (a number) hashkeys 3;
Cluster created.
SQL> create cluster cluster3 (a number,b number sort) hashkeys 3;
Cluster created
四：预估表大小和增长趋势
本文出自 “月牙天冲” 博客
相关新闻>>
- 发表评论
-
- 最新评论 更多>>
