Recently, a friend of mine asked me how would I simulate a partitioning feature in Oracle Standard Edition, where the partitioning  option is not available. Well, building a data warehouse in Oracle environment without two crucial features such as bitmap indices and partitioning is not a very pleasant task.

Fortunately, his requirements were very simple. The queries on the system were date-based,  and he wanted to optimize read access for these queries. Full table scan took too long and index access was also ineffective because of large portion data being accessed. This is exactly one of the situations where partitioning comes in handy. Data was written by a single process.

Simply put, he wanted to simulate partition pruning. Of course, what came first to mind is a simulation using a separate tables and a view containing union all operation on all these tables. But, how to achieve the pruning? The answer is very simple: check constraint. Although check constraints can be very helpful, they are very often omitted from the design.

Let’s go back to my friend’s case. The whole process would look like:

  1. Create a new table table_yyyymm (he wanted to simulate range partitioning)
  2. Create the check constraint on “partitioning” date column
  3. Append a new union all branch to the view

One simple example is worth thousand words:

Firstly, I will create three tables. Every table will contain monthly data. I will also create a check constraint for every table which will cover the date range of data in the table. I will generate 100 000 rows for every table and then I will compute some statistics and create an index on a string column.

create table test_201501 (
 id number(38) not null primary key,
 p_key date not null,
 a_string varchar2(30),
 some_data char(500),
 constraint check_201501 check (p_key >= date '2015-01-01' and p_key < last_day(date '2015-01-01') + 1 ));

insert into test_201501
 select rownum, trunc(date '2015-01-01', 'MM') + trunc(dbms_random.value(0, 28)), dbms_random.string('l', ;30), 'x'
 from dual connect by level <= 1e5;
 commit;

begin dbms_stats.gather_table_stats(user, 'test_201501'); end;

create index i_test_201501_a_string on test_201501(a_string);
create table test_201502 (
 id number(38) not null primary key,
 p_key date not null,
 a_string varchar2(30),
 some_data char(500),
 constraint check_201502 check (p_key >= date '2015-02-01' and p_key < last_day(date '2015-02-01') + 1 ));

insert into test_201502
 select rownum, trunc(date '2015-02-01', 'MM') + trunc(dbms_random.value(0, 28)), dbms_random.string('l', 30), 'x'
 from dual connect by level <= 1e5;
 commit;

begin dbms_stats.gather_table_stats(user, 'test_201502'); end;

create index i_test_201502_a_string on test_201502(a_string);
create table test_201503 (
 id number(38) not null primary key,
 p_key date not null,
 a_string varchar2(30),
 some_data char(500),
 constraint check_201503 check (p_key >= date '2015-03-01' and p_key < last_day(date '2015-03-01') + 1 )):
insert into test_201503
 select rownum, trunc(date '2015-03-01', 'MM') + trunc(dbms_random.value(0, 28)), dbms_random.string('l', 30), 'x'
 from dual connect by level <= 1e5;
 commit;

begin dbms_stats.gather_table_stats(user, 'test_201503'); end;

 create index i_test_201503_a_string on test_201503(a_string);

And finally, I will create a view containing union all operation on all created tables.

create or replace view v_test as
 select * from test_201501 union all
 select * from test_201502 union all
 select * from test_201503 ;

Let us look on few execution plans.

In the first query, we use equality predicate on our “partitioning key”.

explain plan for select * from v_test where p_key = date '2015-01-01';
select * from table(dbms_xplan.display());
Explained
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 1461023347
------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  3571 |  7131K|  4066   (1)| 00:00:49 |
|   1 |  VIEW                | V_TEST      |  3571 |  7131K|  4066   (1)| 00:00:49 |
|   2 |   UNION-ALL          |             |       |       |            |          |
|*  3 |    TABLE ACCESS FULL | TEST_201501 |  3571 |  7131K|  1356   (1)| 00:00:17 |
|*  4 |    FILTER            |             |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| TEST_201502 |     1 |  2045 |  1356   (1)| 00:00:17 |
|*  6 |    FILTER            |             |       |       |            |          |
|*  7 |     TABLE ACCESS FULL| TEST_201503 |     1 |  2045 |  1356   (1)| 00:00:17 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("P_KEY"=TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   4 - filter(NULL IS NOT NULL)
   5 - filter("P_KEY"=TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   6 - filter(NULL IS NOT NULL)
   7 - filter("P_KEY"=TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

As you can see, the optimizer included two contradictions into execution plan. These contradictions prevent database from executing full table scans. Optimizer knows that it does not need to access the tables, because of the existing check constraints.

Now let us take some range predicates which will take date from more than one table.

explain plan for select * from v_test where p_key >= date '2015-01-30' and p_key <= date '2015-02-05';
select * from table(dbms_xplan.display());
Explained
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 2807230337
------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 28073 |    54M|  4066   (1)| 00:00:49 |
|   1 |  VIEW                | V_TEST      | 28073 |    54M|  4066   (1)| 00:00:49 |
|   2 |   UNION-ALL          |             |       |       |            |          |
|*  3 |    TABLE ACCESS FULL | TEST_201501 |  3571 |  7131K|  1356   (1)| 00:00:17 |
|*  4 |    TABLE ACCESS FULL | TEST_201502 | 18386 |    35M|  1356   (1)| 00:00:17 |
|*  5 |    FILTER            |             |       |       |            |          |
|*  6 |     TABLE ACCESS FULL| TEST_201503 |  3571 |  7131K|  1356   (1)| 00:00:17 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("P_KEY">=TO_DATE(' 2015-01-30 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "P_KEY"<=TO_DATE(' 2015-02-05 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   4 - filter("P_KEY"<=TO_DATE(' 2015-02-05 00:00:00', 'syyyy-mm-dd               hh24:mi:ss') AND "P_KEY">=TO_DATE(' 2015-01-30 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   5 - filter(NULL IS NOT NULL)
   6 - filter("P_KEY"<=TO_DATE(' 2015-02-05 00:00:00', 'syyyy-mm-dd               hh24:mi:ss') AND "P_KEY">=TO_DATE(' 2015-01-30 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Again, you can see that optimizer has correctly chosen to avoid the “partition” which does not contain data fulfilling the condition.
The last query contains two range predicates. A predicate on the “partitioning” key and range predicate on the indexed column. It works also as expected, in this case. The FILTER operation with the contradiction NULL IS NOT NULL is added to execution plan and only tables TEST_201501 and TEST_201502 are accessed.

explain plan for select * from v_test where p_key >= date '2015-01-30' and p_key <= date '2015-02-05' and a_string like 'abc%';
select * from table(dbms_xplan.display());
Explained
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
Plan hash value: 4237185743
---------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                        |     1 |  2045 |     4   (0)| 00:00:01 |
|   1 |  VIEW                          | V_TEST                 |     1 |  2045 |     4   (0)| 00:00:01 |
|   2 |   UNION-ALL                    |                        |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID | TEST_201501            |     1 |  2045 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN           | I_TEST_201501_A_STRING |     1 |       |     2   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID | TEST_201502            |     1 |  2045 |     4   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | I_TEST_201502_A_STRING |     1 |       |     2   (0)| 00:00:01 |
|*  7 |    FILTER                      |                        |       |       |            |          |
|*  8 |     TABLE ACCESS BY INDEX ROWID| TEST_201503            |     1 |  2045 |     4   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | I_TEST_201503_A_STRING |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("P_KEY">=TO_DATE(' 2015-01-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "P_KEY"<=TO_DATE(' 2015-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   4 - access("A_STRING" LIKE 'abc%')
       filter("A_STRING" LIKE 'abc%')
   5 - filter("P_KEY"<=TO_DATE(' 2015-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND               "P_KEY">=TO_DATE(' 2015-01-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - access("A_STRING" LIKE 'abc%')
       filter("A_STRING" LIKE 'abc%')
   7 - filter(NULL IS NOT NULL)
   8 - filter("P_KEY"<=TO_DATE(' 2015-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND               "P_KEY">=TO_DATE(' 2015-01-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   9 - access("A_STRING" LIKE 'abc%')
       filter("A_STRING" LIKE 'abc%')

Check constraints are important part of database design and as you can see CBO can leverage from theirs existence. An alternative way to implement this kind of pruning would be usage of the predicates from check constraints directly in the view definition. The result would be the same. The FILTER operation with contradiction NULL IS NOT NULL would be included in executions plans. One of the drawbacks of this implementation is absence of an integrity constraint on the table.

alter table test_201501 drop constraint check_201501;
alter table test_201502 drop constraint check_201502;
alter table test_201503 drop constraint check_201503;

create or replace view v_test as 
select * from test_201501 where p_key >= date '2015-01-01' and p_key < last_day(date '2015-01-01') + 1 union all  
select * from test_201502 where p_key >= date '2015-02-01' and p_key < last_day(date '2015-02-01') + 1 union all  
select * from test_201503 where p_key >= date '2015-03-01' and p_key < last_day(date '2015-03-01') + 1

You can download a test case generator here: sim_pruning.sql

UPDATE 3.2.2015

Pierre asked a very good question, in a comment below: Does it work with bind variables? Short answer is: check constraint does not and predicate in the view does work. Most recently, I drop the constraints and created v_test view with the predicates. Let’s test it with bind variables.

SQL> var x varchar2(10);
SQL> var y varchar2(10);

SQL> exec :x := '2015-01-31';

PL/SQL procedure successfully completed.

SQL> exec :y := '2015-02-01';

PL/SQL procedure successfully completed.

set lines 160
set pages 9999
col PLAN_TABLE_OUTPUT for a160;

SQL> select * from v_test where p_key >= to_date(:x, 'yyyy-mm-dd') and p_key <= to_date(:y, 'yyyy-mm-dd') and a_string like 'abc%';

no rows selected

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  chxg11wt1bc8t, child number 1
-------------------------------------
select * from v_test where p_key >= to_date(:x, 'yyyy-mm-dd') and p_key
<= to_date(:y, 'yyyy-mm-dd') and a_string like 'abc%'

Plan hash value: 3241718393

----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                   | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                        |       |       |     4 (100)|       |
|*  1 |  FILTER                         |                        |       |       |            |       |
|   2 |   VIEW                          | V_TEST                 |     1 |  2045 |     4   (0)| 00:00:01 |
|   3 |    UNION-ALL                    |                        |       |       |            |       |
|*  4 |     FILTER                      |                        |       |       |            |       |
|*  5 |      TABLE ACCESS BY INDEX ROWID| TEST_201501            |     1 |  2045 |     5   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | I_TEST_201501_A_STRING |     3 |       |     2   (0)| 00:00:01 |
|*  7 |     FILTER                      |                        |       |       |            |       |
|*  8 |      TABLE ACCESS BY INDEX ROWID| TEST_201502            |     1 |  2045 |    10   (0)| 00:00:01 |
|*  9 |       INDEX RANGE SCAN          | I_TEST_201502_A_STRING |     8 |       |     2   (0)| 00:00:01 |
|* 10 |     FILTER                      |                        |       |       |            |       |
|* 11 |      TABLE ACCESS BY INDEX ROWID| TEST_201503            |     1 |  2045 |     4   (0)| 00:00:01 |
|* 12 |       INDEX RANGE SCAN          | I_TEST_201503_A_STRING |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE(:X,'yyyy-mm-dd')<=TO_DATE(:Y,'yyyy-mm-dd'))
   4 - filter((TO_DATE(:Y,'yyyy-mm-dd')>=TO_DATE(:X,'yyyy-mm-dd') AND TO_DATE(' 2015-02-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')>TO_DATE(:X,'yyyy-mm-dd') AND TO_DATE(' 2015-01-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')<=TO_DATE(:Y,'yyyy-mm-dd')))
   5 - filter(("P_KEY">=GREATEST(TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'),TO_DATE(:X,'yyyy-mm-dd')) AND "P_KEY"<TO_DATE(' 2015-02-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "P_KEY"<=TO_DATE(:Y,'yyyy-mm-dd')))
   6 - access("A_STRING" LIKE 'abc%')
       filter("A_STRING" LIKE 'abc%')
   7 - filter((TO_DATE(:Y,'yyyy-mm-dd')>=TO_DATE(:X,'yyyy-mm-dd') AND TO_DATE(' 2015-03-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')>TO_DATE(:X,'yyyy-mm-dd') AND TO_DATE(' 2015-02-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')<=TO_DATE(:Y,'yyyy-mm-dd')))
   8 - filter(("P_KEY"<=TO_DATE(:Y,'yyyy-mm-dd') AND "P_KEY">=GREATEST(TO_DATE(' 2015-02-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'),TO_DATE(:X,'yyyy-mm-dd')) AND "P_KEY"<TO_DATE(' 2015-03-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   9 - access("A_STRING" LIKE 'abc%')
       filter("A_STRING" LIKE 'abc%')
  10 - filter((TO_DATE(:Y,'yyyy-mm-dd')>=TO_DATE(:X,'yyyy-mm-dd') AND TO_DATE(' 2015-04-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')>TO_DATE(:X,'yyyy-mm-dd') AND TO_DATE(' 2015-03-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')<=TO_DATE(:Y,'yyyy-mm-dd')))
  11 - filter(("P_KEY"<=TO_DATE(:Y,'yyyy-mm-dd') AND "P_KEY">=GREATEST(TO_DATE(' 2015-03-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'),TO_DATE(:X,'yyyy-mm-dd')) AND "P_KEY"<TO_DATE(' 2015-04-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
  12 - access("A_STRING" LIKE 'abc%')
       filter("A_STRING" LIKE 'abc%')


SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

Session altered.

SQL> select * from v_test where p_key >= to_date(:x, 'yyyy-mm-dd') and p_key <= to_date(:y, 'yyyy-mm-dd') and a_string like 'abc%';

no rows selected

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

SQL ID: chxg11wt1bc8t Plan Hash: 3241718393

select *
from
 v_test where p_key >= to_date(:x, 'yyyy-mm-dd') and p_key <= to_date(:y,
  'yyyy-mm-dd') and a_string like 'abc%'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0         15          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0         17          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 69
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  FILTER  (cr=15 pr=0 pw=0 time=130 us)
         0          0          0   VIEW  V_TEST (cr=15 pr=0 pw=0 time=123 us cost=4 size=2045 card=1)
         0          0          0    UNION-ALL  (cr=15 pr=0 pw=0 time=122 us)
         0          0          0     FILTER  (cr=5 pr=0 pw=0 time=62 us)
         0          0          0      TABLE ACCESS BY INDEX ROWID TEST_201501 (cr=5 pr=0 pw=0 time=59 us cost=5 size=2045 card=1)
         3          3          3       INDEX RANGE SCAN I_TEST_201501_A_STRING (cr=2 pr=0 pw=0 time=27 us cost=2 size=0 card=3)(object id 766078)
         0          0          0     FILTER  (cr=10 pr=0 pw=0 time=49 us)
         0          0          0      TABLE ACCESS BY INDEX ROWID TEST_201502 (cr=10 pr=0 pw=0 time=45 us cost=10 size=2045 card=1)
         8          8          8       INDEX RANGE SCAN I_TEST_201502_A_STRING (cr=2 pr=0 pw=0 time=10 us cost=2 size=0 card=8)(object id 766081)
         0          0          0     FILTER  (cr=0 pr=0 pw=0 time=2 us)
         0          0          0      TABLE ACCESS BY INDEX ROWID TEST_201503 (cr=0 pr=0 pw=0 time=0 us cost=4 size=2045 card=1)
         0          0          0       INDEX RANGE SCAN I_TEST_201503_A_STRING (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 766084)

As you can see in the execution plan there were predicates inluded.

 4 - filter((TO_DATE(:Y,'yyyy-mm-dd')>=TO_DATE(:X,'yyyy-mm-dd') 
             AND TO_DATE(' 2015-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')>TO_DATE(:X,'yyyy-mm-dd')                  
             AND TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=TO_DATE(:Y,'yyyy-mm-dd')))

These predicates are combinations of predicates from the view and the predicates from the query with bind variables. These predicates are evaluated before the whole child subtree of FILTER operation is executed and if the predicate is evaluated to FALSE then the subtree is not executed as you can see in the trace (notice the last branch ):

FILTER  (cr=0 pr=0 pw=0 time=2 us)

Consistent reads (cr) for the whole subtree are zero.
Unfortunately, it doesn’t work with check constraints:

----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                   | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                        |       |       |     4 (100)|       |
|*  1 |  FILTER                         |                        |       |       |            |       |
|   2 |   VIEW                          | V_TEST                 |     1 |  2045 |     4   (0)| 00:00:01 |
|   3 |    UNION-ALL                    |                        |       |       |            |       |
|*  4 |     FILTER                      |                        |       |       |            |       |
|*  5 |      TABLE ACCESS BY INDEX ROWID| TEST_201501            |     1 |  2045 |     4   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | I_TEST_201501_A_STRING |     1 |       |     2   (0)| 00:00:01 |
|*  7 |     FILTER                      |                        |       |       |            |       |
|*  8 |      TABLE ACCESS BY INDEX ROWID| TEST_201502            |     1 |  2045 |     4   (0)| 00:00:01 |
|*  9 |       INDEX RANGE SCAN          | I_TEST_201502_A_STRING |     1 |       |     2   (0)| 00:00:01 |
|* 10 |     FILTER                      |                        |       |       |            |       |
|* 11 |      TABLE ACCESS BY INDEX ROWID| TEST_201503            |     1 |  2045 |     4   (0)| 00:00:01 |
|* 12 |       INDEX RANGE SCAN          | I_TEST_201503_A_STRING |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE(:X,'yyyy-mm-dd')<=TO_DATE(:Y,'yyyy-mm-dd'))
   4 - filter(TO_DATE(:Y,'yyyy-mm-dd')>=TO_DATE(:X,'yyyy-mm-dd'))
   5 - filter(("P_KEY">=TO_DATE(:X,'yyyy-mm-dd') AND "P_KEY"<=TO_DATE(:Y,'yyyy-mm-dd')))
   6 - access("A_STRING" LIKE 'abc%')
       filter("A_STRING" LIKE 'abc%')
   7 - filter(TO_DATE(:Y,'yyyy-mm-dd')>=TO_DATE(:X,'yyyy-mm-dd'))
   8 - filter(("P_KEY"<=TO_DATE(:Y,'yyyy-mm-dd') AND "P_KEY">=TO_DATE(:X,'yyyy-mm-dd')))
   9 - access("A_STRING" LIKE 'abc%')
       filter("A_STRING" LIKE 'abc%')
  10 - filter(TO_DATE(:Y,'yyyy-mm-dd')>=TO_DATE(:X,'yyyy-mm-dd'))
  11 - filter(("P_KEY"<=TO_DATE(:Y,'yyyy-mm-dd') AND "P_KEY">=TO_DATE(:X,'yyyy-mm-dd')))
  12 - access("A_STRING" LIKE 'abc%')
       filter("A_STRING" LIKE 'abc%')

select *
from
 v_test where p_key >= to_date(:x, 'yyyy-mm-dd') and p_key <= to_date(:y,
  'yyyy-mm-dd') and a_string like 'abc%'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0         85          0           0
Execute      1      0.00       0.00          0          6          0           0
Fetch        1      0.00       0.00          0         26          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.01          0        117          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 69
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  FILTER  (cr=26 pr=0 pw=0 time=125 us)
         0          0          0   VIEW  V_TEST (cr=26 pr=0 pw=0 time=120 us cost=4 size=2045 card=1)
         0          0          0    UNION-ALL  (cr=26 pr=0 pw=0 time=118 us)
         0          0          0     FILTER  (cr=5 pr=0 pw=0 time=31 us)
         0          0          0      TABLE ACCESS BY INDEX ROWID TEST_201501 (cr=5 pr=0 pw=0 time=30 us cost=4 size=2045 card=1)
         3          3          3       INDEX RANGE SCAN I_TEST_201501_A_STRING (cr=2 pr=0 pw=0 time=15 us cost=2 size=0 card=1)(object id 766078)
         0          0          0     FILTER  (cr=10 pr=0 pw=0 time=40 us)
         0          0          0      TABLE ACCESS BY INDEX ROWID TEST_201502 (cr=10 pr=0 pw=0 time=36 us cost=4 size=2045 card=1)
         8          8          8       INDEX RANGE SCAN I_TEST_201502_A_STRING (cr=2 pr=0 pw=0 time=10 us cost=2 size=0 card=1)(object id 766081)
         0          0          0     FILTER  (cr=11 pr=0 pw=0 time=40 us)
         0          0          0      TABLE ACCESS BY INDEX ROWID TEST_201503 (cr=11 pr=0 pw=0 time=36 us cost=4 size=2045 card=1)
         8          8          8       INDEX RANGE SCAN I_TEST_201503_A_STRING (cr=3 pr=0 pw=0 time=8 us cost=2 size=0 card=1)(object id 766084)

In this case the filter predicate is much simpler and looks like:

 filter(TO_DATE(:Y,'yyyy-mm-dd')>=TO_DATE(:X,'yyyy-mm-dd'))

So the check constraint predicates are not included in the condition. Therefore, it's not sufficient to achieve the "pruning". Notice that consistent reads (cr) are 11 in the trace. The "pruning" didn't happen.

FILTER  (cr=11 pr=0 pw=0 time=40 us)

If you use bind variables you should use the approach with predicates in view. Anyway, you can also use check constraints to assure data integrity.

3 thoughts on “Simple Simulation of Partitioning (Pruning)

  1. pierre says:

    A classical, though not very known approach. This would even do “partitioning” on an XE Oracle.

    How does this work with bind vars, though?

  2. Thank you Pierre, this is an excellent question. Fortunately it works, it will update the post.

  3. I prepared 2 test cases and I will update the post tomorrow 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

This Blog will give regular Commentators DoFollow Status. Implemented from IT Blögg