Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: June 03, 2013 - 11:45 pm UTC

Last updated: August 07, 2017 - 5:59 am UTC

Version: 11gr2

Viewed 10K+ times! This question is

You Asked

Tom,
does Oracle support hash partitioning and range or list partitioning on each hash partition.
i.e hash-range composite partition and hash-list composite partition ?

"vldb and partitioning manual" does not list this combination - wasnt sure if this was not supported or not quite common, hence not listed in the manual.

and Tom said...

11gR2 filled in all of the partitioning schemes, to include HASH as the top level, you have hash-hash, hash-range, and hash-list

ops$tkyte%ORA11GR2> CREATE TABLE t ( dt  date, x   int, y   varchar2(30))
  2  partition by hash(x)
  3  subPARTITION BY RANGE (dt)
  4  (
  5          partition hp1
  6          (
  7                  subPARTITION hp1_p1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
  8                  subPARTITION hp1_p2 VALUES LESS THAN (MAXVALUE)
  9          ),
 10          partition hp2
 11          (
 12                  subPARTITION hp2_p1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 13                  subPARTITION hp2_p2 VALUES LESS THAN (MAXVALUE)
 14          )
 15  )
 16  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> drop table t;

Table dropped.

ops$tkyte%ORA11GR2> CREATE TABLE t ( dt  date, x   int, y   varchar2(30))
  2  partition by hash(x)
  3  subPARTITION BY list (y)
  4  (
  5          partition hp1
  6          (
  7                  subPARTITION hp1_p1 VALUES ( 'a' ),
  8                  subPARTITION hp1_p2 VALUES ( 'b' )
  9          ),
 10          partition hp2
 11          (
 12                  subPARTITION hp2_p1 VALUES ( 'a' ),
 13                  subPARTITION hp2_p2 VALUES ( 'b' )
 14          )
 15  )
 16  /

Table created.

Rating

  (6 ratings)

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

Comments

A reader, June 05, 2013 - 5:20 pm UTC

exactly the answer i wanted to hear. Thanks !

A reader, June 05, 2013 - 5:20 pm UTC

thanks. So I believe it a documentation that need to be updated >?
Tom Kyte
June 05, 2013 - 5:45 pm UTC

yes, it is....

Partitioning for read performance

Lal, July 15, 2013 - 1:37 pm UTC

Tom,
I need to design a table to store the transaction records for different customers. The number of distinct customers will be in millions.
Whenever the cutomer logs in to the application, i need to fetch the last 100 transactions for the customer and show it.
To get the maximum performance i should store the data such that, minimum blocks are accessed for each customer.

ie all records related to one customer should be stored near to each other, such that less blocks are read.
Can i use partitioning for this case, to improve the read performance.

Option 1
List Partition based on customerid:- In this case millions of partitions will be created and also need to use ddls to add a partition for each new customer. Is it ok to have millions of partitions for a table? Any limits for the number of partitions for a table also will it impact the read performance?

Option 2
composite partition (Hash-List) partition and local index:- Hash on customerid column and then list based on a indicator (eg active indicator), so that a list of members data will be in one partition and when the data is inserted it will be with Active_ind Y and will go to subpartition for Active. A nightly job can move the records older than last 100 rows to inactive subpartition by updating the applicable records with Active_ind N. A local index will be created using customerid and Active_ind

There will be child tables also for the transaction table,which can be partitioned using the reference partition feature.

Will partitioning helps to reduce the no of blocks in this case? If not which is best way to design the tables for this case.
Is this achievable with in 1 sec, assuming the data is not cached.

As always, expecting you expert advice on this.

Tom Kyte
July 16, 2013 - 4:52 pm UTC

Can i use partitioning for this case, to improve the read performance.


probably not. option 1 isn't feasible. and think about option 2. so you hash partition on customer-id and put all of the active records into a single partition. That'll still be a LOT of customer-ids and since they don't all arrive at once (the records for a customer do not all show up at the same time) the odds of them being together are small.


getting 100 records via an index should be achievable in under 1/2 second even if you had to read every single row off of disk one at a time (100 reads * 5ms = 0.5 seconds).


Now that said, partitioning will be something to use here since you do hae hundreds of millions of rows at least - if not billions. You will want to partition.



how much of the transaction information do you have to retrieve? would it be possible to create a covering index that had all of the necessary information - removing the table from the picture altogether for this query?

A reader, July 16, 2013 - 6:37 pm UTC

Tom, can an IOT help to minimize the blocks to be accessed in this case?

Tom Kyte
July 16, 2013 - 8:28 pm UTC

yes, but - and this is a big but - if this is an OLTP application (lots of inserts) and you have secondary indexes (you have indexes on something OTHER THAN customer id), you'll be spending a ton of time rebuilding this IOT - since the rowid hints in the secondary indexes will go stale.

as the rows move around in the index structure, their "rowids" will change. the rowid they had when inserted will be stored in the secondary index. What will happen over time is you'll end up doing two range scans to get a row - one through the secondary index to get the rowid hint, a table access that will fail since the row moved, and then a range scan through the IOT by primary key to find the row.


inserts will take longer (you have to move data around in the index to put the row away in the right place).

secondary indexes will need to be rebuilt, how often will be a function of how active this table is.


You would want to definitely benchmark/simulate this first.

I normally think of IOTs in a read mostly system - one with data loads (optimally into new partitions so the existing rows don't move).

sorted hash clustered table

Matteo, July 17, 2013 - 9:08 am UTC

Hi Tom,
in your Expert Oracle Database Architecture book you introduced the Sorted hash clustered table.
From time to time I think when I can use this type of table.
Do you think it could be a good choice in this case? I don't know if this kind of table is partitionable.

I work with a telco rating platform where the table that contains Call Details Records is an IOT. Rating is quite fast and the insert on the table is a very tiny part of the process service time; there is also an extra index on a monotonically increasing string column.

Thanks
Matteo

Tom Kyte
July 17, 2013 - 6:06 pm UTC

clustered tables cannot be partitioned.

Question About Hash-List Partitioning

Arju Arafin, August 06, 2017 - 11:28 pm UTC

In case of Hash-List composite partitioning do I have to manually write name for all the Partitions?
Can it be done by only mention the number of partitions like partitions 12?
Chris Saxon
August 07, 2017 - 5:59 am UTC

You have to define the values for the list subpartition. But you can just give a number for the top-level hash partitions:

create table t (
  x int , y int
) partition by hash (x) 
  subpartition by list (y)
  subpartition template (
    subpartition sp0 values (0)
  ) partitions 12 ;

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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.