Is this a correct explanation of a Range-Hash partition?

Question

CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)
  SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
 );

In the above example a range hash partition is created and splits the database into four parts, ts1, ts2, ts3, ts4. Then a range partition is created that splits the data per sales quarter in the four parts (ts1, ts2, ts3, ts4). Even though the sales quarters are split into for partitions (ts1, ts2, ts3, ts4), these pieces of data are then further split into 8 subpartitions?

If it helps, this is the website that I used for the example: https://docs.oracle.com/database/121/VLDBG/GUID-A596B6B1-62C7-4701-A872-BDC5632CEE43.htm

Thanks

Answer 1

Nearly. I find the example actually quite confusing and had to test it before understanding it.

The table is split in for partions sales_q1_2006 ... sales_q4_2006. A time_id of the first quarter of 2006 lands in partition sales_q1_2006, etc. Each of those partitions is split into 8 subpartitions by the hash value of column cust_id.

Now, the (ts1, ts2, ts3, ts4) bit. It is a bit easier to understand if one uses 8 tablespaces for the 8 subpartitions:

   SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4, ts5, ts6, ts7, ts8)

If you create the table, each of the 8 subpartions is stored in a separate tablespace:

SELECT partition_position AS pos, partition_name, subpartition_name, tablespace_name 
  FROM USER_TAB_SUBPARTITIONS 
 ORDER BY table_name, partition_position, subpartition_name;

POS PARTITION     SUBPARTITION TABLESPACE
1   SALES_Q1_2006 SYS_SUBP525  TS1
1   SALES_Q1_2006 SYS_SUBP526  TS2
1   SALES_Q1_2006 SYS_SUBP527  TS3
1   SALES_Q1_2006 SYS_SUBP528  TS4
1   SALES_Q1_2006 SYS_SUBP529  TS5
1   SALES_Q1_2006 SYS_SUBP530  TS6
1   SALES_Q1_2006 SYS_SUBP531  TS7
1   SALES_Q1_2006 SYS_SUBP532  TS8
2   SALES_Q2_2006 SYS_SUBP533  TS1
2   SALES_Q2_2006 SYS_SUBP534  TS2
2   SALES_Q2_2006 SYS_SUBP535  TS3
...

So, the first hash subpartitions of all partitions are stored in tablespace ts1, the second hash subpartition of all partitions are stored in tablespace ts2, etc. until tablespace ts8.

Coming back to your example, with 4 tablespace names and 8 subpartitions: The four specified tablespaces ts1 to ts4 are just cycled through until all subpartitions got a tablespace. In your example, the 8 subpartitions go to tablespaces ts1, ts2, ts3, ts4, ts1, ts2, ts3 and ts4:

SELECT partition_position AS pos, partition_name, subpartition_name, tablespace_name 
  FROM USER_TAB_SUBPARTITIONS 
 ORDER BY table_name, partition_position, subpartition_name;

POS PARTITION       SUBPARTITION   TABLESPACE
1   SALES_Q1_2006   SYS_SUBP589    TS1 <= first cycle
1   SALES_Q1_2006   SYS_SUBP590    TS2
1   SALES_Q1_2006   SYS_SUBP591    TS3
1   SALES_Q1_2006   SYS_SUBP592    TS4
1   SALES_Q1_2006   SYS_SUBP593    TS1 <= next cycle
1   SALES_Q1_2006   SYS_SUBP594    TS2
1   SALES_Q1_2006   SYS_SUBP595    TS3
1   SALES_Q1_2006   SYS_SUBP596    TS4
2   SALES_Q2_2006   SYS_SUBP597    TS1 <= next partition
2   SALES_Q2_2006   SYS_SUBP598    TS2
2   SALES_Q2_2006   SYS_SUBP599    TS3
...