分区裁剪 oracle,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 select1 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行。

原文：http://www.cnblogs.com/princessd8251/p/3855745.html