Oracle Local network sub-table simulation partition clipping

From discussion stickers http://www.itpub.net/thread-1877111-1-1.html

Preparing Data Sheets

2014-07-20 01:38:10>create Table tb_1 as SELECT * from Dba_objects where rownum<100;

The table is created.

2014-07-20 01:38:19>create Table tb_2 as SELECT * from Dba_objects where rownum<100;

The table is created.

2014-07-20 01:38:47>create Table Tb_3 as SELECT * from Dba_objects where rownum<100;

The table is created.

2014-07-20 01:38:52>create Table Tb_4 as SELECT * from Dba_objects where rownum<100;

The table is created.

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

The table has changed.

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

The table has changed.

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

The table has changed.

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

The table has changed.

Here we have four tables, each with 99 data, and the ID of each table is the same value. This ID is equivalent to a partition key.

--Creating a view TB does not specify the where parameter, which is equivalent to a logical summary table.

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;

--Executes the following statement, which is equivalent to querying the id=1 partition, but the plan shows that all partitions are scanned. The reason is that there is no information in this view that can be used to infer that id=1 records are from those tables.

2014-07-20 01:42:10>explain Plan for SELECT * from TB where id=1;

has been explained.

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)

We can use predicate propulsion and constant indication to make table filtering.

25 rows have been selected.

2014-07-20 01:42:38>drop View TB ;

The view has been deleted.

1. Filter by the Where condition in the view definition.

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;

The view is created.

2014-07-20 01:46:29>explain Plan for SELECT * from TB where id=1;

has been explained.

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 rows have been selected.

2. The clipping table is indicated by the constant value in the view definition

2014-07-20 01:46:37>drop View TB;

The view has been deleted.

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;

The view is created.

2014-07-20 01:51:28>Explain plan for SELECT * from TB where latch=1;

has been explained.

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 was NOT NULL) 8-filter (NULL was NOT NULL)Note------Dynamic sampling used for this statement (level=2)

27 rows have been selected.