首页 > 代码库 > Oracle本地网络分表模拟分区裁剪

Oracle本地网络分表模拟分区裁剪

2024-07-12 23:38:50   215人阅读

来自讨论贴 http://www.itpub.net/thread-1877111-1-1.html

准备数据表

2014-07-20 01:38:10>create table tb_1 as select * from dba_objects where rownum<100;

表已创建。

2014-07-20 01:38:19>create table tb_2 as select * from dba_objects where rownum<100;

表已创建。

2014-07-20 01:38:47>create table tb_3 as select * from dba_objects where rownum<100;

表已创建。

2014-07-20 01:38:52>create table tb_4 as select * from dba_objects where rownum<100;

表已创建。

2014-07-20 01:38:57>alter table tb_1 add id int default 1;

表已更改。

2014-07-20 01:39:26>alter table tb_2 add id int default 2;

表已更改。

2014-07-20 01:39:33>alter table tb_3 add id int default 3;

表已更改。

2014-07-20 01:39:52>alter table tb_4 add id int default 4;

表已更改。

--到此我们有四个表，每个表有99条数据，每个表的ID都是相同的值。这个ID就相当于是分区键.

--创建一个view tb 没有指定where 参数，这个相当于是逻辑上的总表.

2014-07-20 01:41:43>create view tb as

select * from tb_1 union all

select * from tb_2  union all

select * from tb_3  union all

select * from tb_4;

--执行下面语句，相当于是查询id=1的分区，可惜计划显示扫描了所有分区.原因是这个视图里没有信息可以用来推断出id=1的记录都来自那些表.

2014-07-20 01:42:10>explain plan for select * from tb where id=1;

已解释。

2014-07-20 01:42:26>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------Plan hash value: 3388103150----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 220 | 2 (0)| 00:00:01 || 1 | VIEW | TB | 1 | 220 | 2 (0)| 00:00:01 || 2 | UNION-ALL | | | | | ||* 3 | TABLE ACCESS FULL| TB_1 | 99 | 21780 | 2 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL| TB_2 | 1 | 220 | 2 (0)| 00:00:01 ||* 5 | TABLE ACCESS FULL| TB_3 | 1 | 220 | 2 (0)| 00:00:01 ||* 6 | TABLE ACCESS FULL| TB_4 | 1 | 220 | 2 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------3 - filter("ID"=1)4 - filter("ID"=1)5 - filter("ID"=1)6 - filter("ID"=1)Note------ dynamic sampling used for this statement (level=2)

我们可以借助谓词推进和常量指示的方式来进行表过滤。

已选择25行。

2014-07-20 01:42:38>drop view tb;

视图已删除。

1. 依靠视图定义中的where条件来过滤。

2014-07-20 01:45:54>create view tb as
2 select * from tb_1 where id=1 union all
3 select * from tb_2 where id=2 union all
4 select * from tb_3 where id=3 union all
5 select * from tb_4 where id=4 ;

视图已创建。

2014-07-20 01:46:29>explain plan for select * from tb where id=1;

已解释。

2014-07-20 01:46:36>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------Plan hash value: 2497850530-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 220 | 2 (0)| 00:00:01 || 1 | VIEW | TB | 1 | 220 | 2 (0)| 00:00:01 || 2 | UNION-ALL | | | | | ||* 3 | TABLE ACCESS FULL | TB_1 | 99 | 21780 | 2 (0)| 00:00:01 ||* 4 | FILTER | | | | | ||* 5 | TABLE ACCESS FULL| TB_2 | 1 | 220 | 2 (0)| 00:00:01 ||* 6 | FILTER | | | | | ||* 7 | TABLE ACCESS FULL| TB_3 | 1 | 220 | 2 (0)| 00:00:01 ||* 8 | FILTER | | | | | ||* 9 | TABLE ACCESS FULL| TB_4 | 99 | 21780 | 2 (0)| 00:00:01 |-----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------3 - filter("ID"=1)4 - filter(NULL IS NOT NULL)5 - filter("ID"=2)6 - filter(NULL IS NOT NULL)7 - filter("ID"=3)8 - filter(NULL IS NOT NULL)9 - filter("ID"=4)
Note------ dynamic sampling used for this statement (level=2)

已选择31行。

2.依靠view定义里的常量值指示裁剪表

2014-07-20 01:46:37>drop view tb;

视图已删除。

2014-07-20 01:50:17>create view tb(latch,id,object_name) as
2 select 1 latch,id,object_name from tb_1 union all
3 select 2 latch,id,object_name from tb_2 union all
4 select 3 latch,id,object_name from tb_3 union all
5 select 4 latch,id,object_name from tb_4 ;

视图已创建。

2014-07-20 01:51:28>explain plan for select * from tb where latch=1;

已解释。

2014-07-20 01:51:53>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2497850530-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 102 | 8364 | 2 (0)| 00:00:01 || 1 | VIEW | TB | 102 | 8364 | 2 (0)| 00:00:01 || 2 | UNION-ALL | | | | | || 3 | TABLE ACCESS FULL | TB_1 | 99 | 7821 | 2 (0)| 00:00:01 ||* 4 | FILTER | | | | | || 5 | TABLE ACCESS FULL| TB_2 | 99 | 7821 | 2 (0)| 00:00:01 ||* 6 | FILTER | | | | | || 7 | TABLE ACCESS FULL| TB_3 | 99 | 7821 | 2 (0)| 00:00:01 ||* 8 | FILTER | | | | | || 9 | TABLE ACCESS FULL| TB_4 | 99 | 7821 | 2 (0)| 00:00:01 |-----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------4 - filter(NULL IS NOT NULL)6 - filter(NULL IS NOT NULL)8 - filter(NULL IS NOT NULL)Note------ dynamic sampling used for this statement (level=2)

已选择27行。


联系
我们