Multiple partitions

In 12c, one of the nice changes to come along is that partition maintenance operations can now be done on multiple partitions, for example


ALTER TABLE t1 MERGE PARTITIONS p01, p02, p03, p04 INTO p0;

ALTER TABLE SPLIT PARTITION p0 INTO 
  (PARTITION p01 VALUES LESS THAN (25),
   PARTITION p02 VALUES LESS THAN (50), 
   PARTITION p03 VALUES LESS THAN (75),
   PARTITION p04);

and so on. However, one of the things that you still cannot do is the same thing with SELECT



SQL> create table T ( x int, y int )
  2  partition by hash ( x )
  3  --partitions 8
  4  (
  5  partition  p1,
  6  partition  p2,
  7  partition  p3,
  8  partition  p4,
  9  partition  p5,
 10  partition  p6,
 11  partition  p7,
 12  partition  p8
 13  )
 14  /

Table created.

SQL>
SQL> insert /*+ APPEND */ into T
  2  select rownum, rownum
  3  from dual
  4  connect by rownum <= 1000000   5  / 1000000 rows created. SQL> select * from T partition ( p1,p2);
select * from T partition ( p1,p2)
                          *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL>
SQL> select * from T partition2 ( p1,p2);
select * from T partition2 ( p1,p2)
                           *
ERROR at line 1:
ORA-00933: SQL command not properly ended

So if you’re looking to come up with a way getting access to multiple partitions easily, here’s a little trick you could employ

have a partition-view style SQL statement that accesses ALL of the partitions
with each SQL, include a bind variable and some bitwise logic

and it will look something like this



SQL> set autotrace off
SQL>
SQL> variable b number
SQL> exec :b := 17

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace on stat
SQL> select count(*)
  2  from
  3  (
  4  select * from t partition ( p1 ) where bitand(:b,1) = 1 union all
  5  select * from t partition ( p2 ) where bitand(:b,2) = 2 union all
  6  select * from t partition ( p3 ) where bitand(:b,4) = 4 union all
  7  select * from t partition ( p4 ) where bitand(:b,8) = 8 union all
  8  select * from t partition ( p5 ) where bitand(:b,16) = 16 union all
  9  select * from t partition ( p6 ) where bitand(:b,32) = 32 union all
 10  select * from t partition ( p7 ) where bitand(:b,64) = 64 union all
 11  select * from t partition ( p8 ) where bitand(:b,128) = 128
 12  )
 13  /

  COUNT(*)
----------
    250498

1 row selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        524  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> exec :b := 255

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*)
  2  from
  3  (
  4  select * from t partition ( p1 ) where bitand(:b,1) = 1 union all
  5  select * from t partition ( p2 ) where bitand(:b,2) = 2 union all
  6  select * from t partition ( p3 ) where bitand(:b,4) = 4 union all
  7  select * from t partition ( p4 ) where bitand(:b,8) = 8 union all
  8  select * from t partition ( p5 ) where bitand(:b,16) = 16 union all
  9  select * from t partition ( p6 ) where bitand(:b,32) = 32 union all
 10  select * from t partition ( p7 ) where bitand(:b,64) = 64 union all
 11  select * from t partition ( p8 ) where bitand(:b,128) = 128
 12  )
 13  /

  COUNT(*)
----------
   1000000

1 row selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2091  consistent gets
          0  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>

You can see from the consistent gets that it looks like we are accessing just the partitions of note. A look at the execution plan shows why – we are applying a filter on each SELECT statement in the UNION ALL based on the bind variable to decide if we need to access the partition


SQL> set autotrace traceonly explain
SQL> select count(*)
  2  from
  3  (
  4  select * from t partition ( p1 ) where bitand(:b,1) = 1 union all
  5  select * from t partition ( p2 ) where bitand(:b,2) = 2 union all
  6  select * from t partition ( p3 ) where bitand(:b,4) = 4 union all
  7  select * from t partition ( p4 ) where bitand(:b,8) = 8 union all
  8  select * from t partition ( p5 ) where bitand(:b,16) = 16 union all
  9  select * from t partition ( p6 ) where bitand(:b,32) = 32 union all
 10  select * from t partition ( p7 ) where bitand(:b,64) = 64 union all
 11  select * from t partition ( p8 ) where bitand(:b,128) = 128
 12  )
 13  /

Execution Plan
----------------------------------------------------------
Plan hash value: 726641459

------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     1 |   621   (1)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE           |      |     1 |            |          |       |       |
|   2 |   VIEW                    |      |  1000K|   621   (1)| 00:00:01 |       |       |
|   3 |    UNION-ALL              |      |       |            |          |       |       |
|*  4 |     FILTER                |      |       |            |          |       |       |
|   5 |      PARTITION HASH SINGLE|      |   125K|    78   (2)| 00:00:01 |     1 |     1 |
|   6 |       TABLE ACCESS FULL   | T    |   125K|    78   (2)| 00:00:01 |     1 |     1 |
|*  7 |     FILTER                |      |       |            |          |       |       |
|   8 |      PARTITION HASH SINGLE|      |   124K|    78   (2)| 00:00:01 |     2 |     2 |
|   9 |       TABLE ACCESS FULL   | T    |   124K|    78   (2)| 00:00:01 |     2 |     2 |
|* 10 |     FILTER                |      |       |            |          |       |       |
|  11 |      PARTITION HASH SINGLE|      |   124K|    78   (2)| 00:00:01 |     3 |     3 |
|  12 |       TABLE ACCESS FULL   | T    |   124K|    78   (2)| 00:00:01 |     3 |     3 |
|* 13 |     FILTER                |      |       |            |          |       |       |
|  14 |      PARTITION HASH SINGLE|      |   124K|    78   (2)| 00:00:01 |     4 |     4 |
|  15 |       TABLE ACCESS FULL   | T    |   124K|    78   (2)| 00:00:01 |     4 |     4 |
|* 16 |     FILTER                |      |       |            |          |       |       |
|  17 |      PARTITION HASH SINGLE|      |   125K|    78   (2)| 00:00:01 |     5 |     5 |
|  18 |       TABLE ACCESS FULL   | T    |   125K|    78   (2)| 00:00:01 |     5 |     5 |
|* 19 |     FILTER                |      |       |            |          |       |       |
|  20 |      PARTITION HASH SINGLE|      |   125K|    78   (2)| 00:00:01 |     6 |     6 |
|  21 |       TABLE ACCESS FULL   | T    |   125K|    78   (2)| 00:00:01 |     6 |     6 |
|* 22 |     FILTER                |      |       |            |          |       |       |
|  23 |      PARTITION HASH SINGLE|      |   125K|    78   (2)| 00:00:01 |     7 |     7 |
|  24 |       TABLE ACCESS FULL   | T    |   125K|    78   (2)| 00:00:01 |     7 |     7 |
|* 25 |     FILTER                |      |       |            |          |       |       |
|  26 |      PARTITION HASH SINGLE|      |   124K|    78   (2)| 00:00:01 |     8 |     8 |
|  27 |       TABLE ACCESS FULL   | T    |   124K|    78   (2)| 00:00:01 |     8 |     8 |
------------------------------------------------------------------------------------------

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

   4 - filter(BITAND(TO_NUMBER(:B),1)=1)
   7 - filter(BITAND(TO_NUMBER(:B),2)=2)
  10 - filter(BITAND(TO_NUMBER(:B),4)=4)
  13 - filter(BITAND(TO_NUMBER(:B),8)=8)
  16 - filter(BITAND(TO_NUMBER(:B),16)=16)
  19 - filter(BITAND(TO_NUMBER(:B),32)=32)
  22 - filter(BITAND(TO_NUMBER(:B),64)=64)
  25 - filter(BITAND(TO_NUMBER(:B),128)=128)

One comment

Rajeshwaran, Jeyabal says:

June 13, 2017 at 6:08 pm

Connor – thanks for this post, the above “merge” partition command is syntactically incorrect.

demo@ORA12C> alter table t merge partitions p1,p2,p3,p4 into p0;
alter table t merge partitions p1,p2,p3,p4 into p0
*
ERROR at line 1:
ORA-14004: missing PARTITION keyword

demo@ORA12C> alter table t merge partitions p1,p2,p3,p4 into partition p0;

Table altered.

demo@ORA12C>

Got some thoughts? Leave a comment Cancel reply

Enter your comment here...

Fill in your details below or click an icon to log in:

Email (required) (Address never made public)

Name (required)

Website

You are commenting using your WordPress.com account. ( Log Out / Change )

You are commenting using your Twitter account. ( Log Out / Change )

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share this!

Like this:

Related

Got some thoughts? Leave a comment Cancel reply