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

来自讨论贴 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行。