Skip to Main Content
  • Questions

  • How to restrict statement to operate just on several partitions of hash partitioned table.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 17, 2016 - 9:01 am UTC

Last updated: January 30, 2016 - 5:33 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

Hi


We have Oracle 12c.
There is hash partitioned table with n partitions. It is hashed by the surrogate primary key taken from sequence.
It has child tables which are partitioned also by the same key.

There is need for batch programs to be run on the for this table. Idea is to run same program with multiple (m) 'threads', each
handling its own set of partitions.

How could we accomplish this?
What would be the best way to do this without using dynamic sql ?

(I am writing this from home and don't now have database access.
First idea was something like

select
*
from
table t
where
mod(dbms_rowid.rowid_object(t.rowid),m) = :thread_number
)

Is ora_hash function used for hash partitioned tables ?


Keep on the good work...



and Connor said...

As long as you have a power of 2 number of partitions, then ora_hash will work...but since I can't find any documentation saying that this is the cast iron link between the two... I can't recommend that you use it, ie, you want to get something in writing from Support, otherwise you never really know if we change it in future. The risk is up to you.

SQL> col o1 format a20
SQL> col o2 format a20
SQL>
SQL> select
  2    ( select subobject_name
  3      from   user_objects
  4      where  object_id = t1.actual_partition
  5    ) o1,
  6    ( select partition_name
  7      from   user_tab_partitions
  8      where  partition_position = t1.predicted_partition
  9      and    table_name = 'T'
 10    ) o2,
 11    c
 12  from (
 13    select ora_hash(x,7)+1 predicted_partition, dbms_mview.pmarker(rowid) actual_partition, count(*) c
 14    from t
 15    group by ora_hash(x,7), dbms_mview.pmarker(rowid)
 16  ) t1 ;

O1                   O2                            C
-------------------- -------------------- ----------
SYS_P1527            SYS_P1527                124778
SYS_P1526            SYS_P1526                125358
SYS_P1530            SYS_P1530                125140
SYS_P1531            SYS_P1531                125207
SYS_P1533            SYS_P1533                124748
SYS_P1528            SYS_P1528                124983
SYS_P1529            SYS_P1529                124730
SYS_P1532            SYS_P1532                125056

8 rows selected.


But ultimately, do you really want to have 'm' threads each scanning the *entire* table, because "where ora_hash() = x" still scans all partitions. Based on that ... I think dynamic SQL so you can nominate the partition would still appear to be the best option.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How to use several partitins in same sql-statement

anonymous, January 18, 2016 - 7:51 am UTC

Thank You very much for Your prompt answer.

Is there any other way to restrict sql-statement to use specific partitions than using code of style:

select * from table partition (sys_t1)
union all
select * from table partition (sys_t5)
union all
select * from table partition (sys_t9)
union all
select * from table partition (sys_t13)


I still think there Oracle should have some fancy function to restrict use to specific partitions...


Connor McDonald
January 18, 2016 - 12:00 pm UTC

That is the way you'll need to do it, however, you could take advantage of Oracle removing parts of the SQL from consideration as early as possible.

For example, you could build a single SQL dynamically with ALL of the partitions

select * from table partition (sys_t1)
union all
...
select * from table partition (sys_t9)
union all
select * from table partition (sys_t10)

and then add a bind variable to each one, ie

select * from table partition (sys_t1) where bitand(:bindvar,1) = 1
union all
...
select * from table partition (sys_t9) where bitand(:bindvar,2) = 2
union all
select * from table partition (sys_t10) where bitand(:bindvar,4) = 4
etc etc

and then some bitwise logic to get whatever partitions you want.

If I explain that query, you can see that we do FILTER before each partition access, so we decide by looking at the bind variable before deciding whether to visit that partition

SQL> set autotrace traceonly explain
SQL> select *
  2  from
  3  (
  4  select * from t partition ( SYS_P1537 ) where bitand(:b,1) = 1 union all
  5  select * from t partition ( SYS_P1538 ) where bitand(:b,2) = 2 union all
  6  select * from t partition ( SYS_P1539 ) where bitand(:b,4) = 4 union all
  7  select * from t partition ( SYS_P1540 ) where bitand(:b,8) = 8 union all
  8  select * from t partition ( SYS_P1541 ) where bitand(:b,16) = 16 union all
  9  select * from t partition ( SYS_P1542 ) where bitand(:b,32) = 32 union all
 10  select * from t partition ( SYS_P1543 ) where bitand(:b,64) = 64 union all
 11  select * from t partition ( SYS_P1544 ) where bitand(:b,128) = 128
 12  )
 13  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2067716848

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |  1026K|    25M|   618   (1)| 00:00:01 |       |       |
|   1 |  VIEW                    |      |  1026K|    25M|   618   (1)| 00:00:01 |       |       |
|   2 |   UNION-ALL              |      |       |       |            |          |       |       |
|*  3 |    FILTER                |      |       |       |            |          |       |       |
|   4 |     PARTITION HASH SINGLE|      |   112K|  2867K|    77   (0)| 00:00:01 |     1 |     1 |
|   5 |      TABLE ACCESS FULL   | T    |   112K|  2867K|    77   (0)| 00:00:01 |     1 |     1 |
|*  6 |    FILTER                |      |       |       |            |          |       |       |
|   7 |     PARTITION HASH SINGLE|      |   124K|  3149K|    77   (0)| 00:00:01 |     2 |     2 |
|   8 |      TABLE ACCESS FULL   | T    |   124K|  3149K|    77   (0)| 00:00:01 |     2 |     2 |
|*  9 |    FILTER                |      |       |       |            |          |       |       |
|  10 |     PARTITION HASH SINGLE|      |   120K|  3056K|    77   (0)| 00:00:01 |     3 |     3 |
|  11 |      TABLE ACCESS FULL   | T    |   120K|  3056K|    77   (0)| 00:00:01 |     3 |     3 |
|* 12 |    FILTER                |      |       |       |            |          |       |       |
|  13 |     PARTITION HASH SINGLE|      |   123K|  3136K|    77   (0)| 00:00:01 |     4 |     4 |
|  14 |      TABLE ACCESS FULL   | T    |   123K|  3136K|    77   (0)| 00:00:01 |     4 |     4 |
|* 15 |    FILTER                |      |       |       |            |          |       |       |
|  16 |     PARTITION HASH SINGLE|      |   146K|  3728K|    77   (0)| 00:00:01 |     5 |     5 |
|  17 |      TABLE ACCESS FULL   | T    |   146K|  3728K|    77   (0)| 00:00:01 |     5 |     5 |
|* 18 |    FILTER                |      |       |       |            |          |       |       |
|  19 |     PARTITION HASH SINGLE|      |   144K|  3675K|    77   (0)| 00:00:01 |     6 |     6 |
|  20 |      TABLE ACCESS FULL   | T    |   144K|  3675K|    77   (0)| 00:00:01 |     6 |     6 |
|* 21 |    FILTER                |      |       |       |            |          |       |       |
|  22 |     PARTITION HASH SINGLE|      |   120K|  3063K|    77   (0)| 00:00:01 |     7 |     7 |
|  23 |      TABLE ACCESS FULL   | T    |   120K|  3063K|    77   (0)| 00:00:01 |     7 |     7 |
|* 24 |    FILTER                |      |       |       |            |          |       |       |
|  25 |     PARTITION HASH SINGLE|      |   133K|  3394K|    77   (0)| 00:00:01 |     8 |     8 |
|  26 |      TABLE ACCESS FULL   | T    |   133K|  3394K|    77   (0)| 00:00:01 |     8 |     8 |
-------------------------------------------------------------------------------------------------

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

   3 - filter(BITAND(TO_NUMBER(:B),1)=1)
   6 - filter(BITAND(TO_NUMBER(:B),2)=2)
   9 - filter(BITAND(TO_NUMBER(:B),4)=4)
  12 - filter(BITAND(TO_NUMBER(:B),8)=8)
  15 - filter(BITAND(TO_NUMBER(:B),16)=16)
  18 - filter(BITAND(TO_NUMBER(:B),32)=32)
  21 - filter(BITAND(TO_NUMBER(:B),64)=64)
  24 - filter(BITAND(TO_NUMBER(:B),128)=128)

How to use several partitins in same sql-statement, one possibility

fdsafa anonymous, January 29, 2016 - 11:44 am UTC

Hi

Modified this little bit more:
Created statement so that if would contain all partitions (Still to do: generate automatically views having partition information)


select * from
(
select T.*,'SYS_P1218' PARTITION_NAME, 1 PARTITION_POSITION from T_TEST partition (SYS_P1218) T union all
select T.*,'SYS_P1219' PARTITION_NAME, 2 PARTITION_POSITION from T_TEST partition (SYS_P1219) T union all
select T.*,'SYS_P1220' PARTITION_NAME, 3 PARTITION_POSITION from T_TEST partition (SYS_P1220) T union all
select T.*,'SYS_P1221' PARTITION_NAME, 4 PARTITION_POSITION from T_TEST partition (SYS_P1221) T union all
select T.*,'SYS_P1222' PARTITION_NAME, 5 PARTITION_POSITION from T_TEST partition (SYS_P1222) T union all
select T.*,'SYS_P1223' PARTITION_NAME, 6 PARTITION_POSITION from T_TEST partition (SYS_P1223) T union all
select T.*,'SYS_P1224' PARTITION_NAME, 7 PARTITION_POSITION from T_TEST partition (SYS_P1224) T union all
select T.*,'SYS_P1225' PARTITION_NAME, 8 PARTITION_POSITION from T_TEST partition (SYS_P1225) T union all
select T.*,'SYS_P1226' PARTITION_NAME, 9 PARTITION_POSITION from T_TEST partition (SYS_P1226) T union all
select T.*,'SYS_P1227' PARTITION_NAME, 10 PARTITION_POSITION from T_TEST partition (SYS_P1227) T
) where mod(partition_position,2) = 0;

explain plan shows that this will automatically prune
those partitions which are not to be handled.


3 - filter(NULL IS NOT NULL)
8 - filter(NULL IS NOT NULL)
13 - filter(NULL IS NOT NULL)
18 - filter(NULL IS NOT NULL)
23 - filter(NULL IS NOT NULL)


So now we can use this technique to prune partitions and have sql-statement to access only specific partitions.


Connor McDonald
January 30, 2016 - 5:33 am UTC

I opted for the "bitand" option, because this lets you selectively *any* combination of the partitions in the view, without having to do

"and position in (1,4,5,6,8)"

etc.

But whatever works for you is totally fine with me. Glad you got to a resolution
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_MVIEW

More on PL/SQL routine DBMS_MVIEW here