1

I am working for a new project that require to partition the table based on two columns (city and area). does oracle database support that ?

I worked on projects before where I partition the database based on one column when creating the table. but I have no idea on how to partition using two columns do we use the same semantic or different one 

CREATE TABLE TEST (....)
PARTITION BY RANGE (date1) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION TEST_INITIAL VALUES less than (DATE '2000-01-01')
);
lena
  • 89
  • 1
  • 1
  • 12
  • if anyone want to `group by + join rows data`, see https://stackoverflow.com/questions/12558509/concatenate-and-group-multiple-rows-in-oracle – yu yang Jian May 13 '21 at 08:35

2 Answers2

1

If you have Oracle 12.2 or later, this is a snap. Use AUTOMATIC partitioning. E.g.,

CREATE TABLE my_auto_partitioned_table
  ( id NUMBER,
    city_name  VARCHAR2(80),
    area_name  VARCHAR2(80),
    other_data VARCHAR2(400) )
PARTITION BY LIST ( city_name, area_name) AUTOMATIC
 ( PARTITION p_dummy VALUES (null, null) )
;

Pre 12.2, it is possible, with LIST-LIST partitioning, but it is a real pain because you have to pre-create all your partitions and subpartitions. E.g.,

CREATE TABLE my_partitioned_table 
  ( id NUMBER,
    city_name  VARCHAR2(80),
    area_name  VARCHAR2(80),
    other_data VARCHAR2(400) )
PARTITION BY LIST ( city_name )
SUBPARTITION BY LIST ( area_name )
-- if your area names are generic (e.g., "north"/"south" or "downtown"/"suburbs"), 
-- you can use a SUBPARTITION TEMPLATE clause right here...
( PARTITION p_philadelpha VALUES ( 'PHILADELPHIA')
  ( SUBPARTITION p_philly1 VALUES ('SOUTH PHILLY','WEST PHILLY'),
    SUBPARTITION p_philly2 VALUES ('NORTH PHILLY','OLD CITY')
  ),
  PARTITION p_new_york VALUES ( 'NEW YORK')
  ( SUBPARTITION p_nyc1 VALUES ('SOHO'),
    SUBPARTITION p_nyc2 VALUES ('HELL''S KITCHEN')
  )
);
Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
  • this multiple partitioning will increase the performance and decrease the time required to retrieve data from database? – lena Aug 22 '19 at 20:59
  • Not necessarily. It will _often_ help. But, partitioning is not a magical cure-all. It can make things better or worse, depending on how your partitioned table is indexed and how your application accesses it. Best advice is to try it out and test it. Watch out for local indexes being used in queries that do not specify a single city and area. – Matthew McPeak Aug 22 '19 at 21:05
  • correct me if I am wrong, the point of partitioning is to decrease the time required data from database. let say I hav 1M record and I partition it into 25 city and 70 area will this not enhance the performance and if it will not improve it what will ? – lena Aug 22 '19 at 21:10
  • _One_ of the points is to improve performance by giving the optimizer more efficient access paths that would otherwise not be available (e.g., partition pruning). Not all queries will benefit from those additional access paths. It can make things worse too -- most commonly if an index on the unpartitioned table becomes a local index in the partitioned table and the partitioned keys are not specified in the query. If your queries against this table all specified filter conditions for city and area, then the partitioning has a good chance of helping performance. – Matthew McPeak Aug 22 '19 at 21:42
  • For the record, some other "points" of partitioning include: management (splitting up large tables into smaller segments), information life-cycle management (storing older partitions on slower disk instead of SSD for example), concurrency (e.g., global hash partitioning an index on a monotonically increasing key value). – Matthew McPeak Aug 22 '19 at 21:44
  • 1
    A couple of things. Be careful with multi-column partitioning as things are not often as straight forward as you might imagine. LIST-LIST might be ok though. You can also over-partition. Too many partitions and you get into meta-data hell. – BobC Aug 23 '19 at 05:15
  • @BobC Can you please elaborate on the pitfalls of the 12.2 multi-column partitioning? I have not used it in production yet. Maybe you can save me from something I'll regret? – Matthew McPeak Aug 23 '19 at 11:22
0

I would not do that. It is the same if somebody would ask: I like to partition my table by Months and Day of a date column. Just define the partition by Day, then you are done.

Anyway, in general you can use several columns if you define a virtual column and partition on that:

CREATE TABLE my_auto_partitioned_table
  ( id NUMBER,
    city_name  VARCHAR2(80),
    area_name  VARCHAR2(80),
    partition_key VARCHAR2(200) GENERATED ALWAYS AS (city_name||-||area_name) VIRTUAL)
PARTITION BY LIST ( partition_key ) ...

But I don't think this would make so much sense in your case.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • I did not mention this approach because I believe without having tested it that this approach requires queries to specify `WHERE city_name || '-' || area_name = '..whatever..'` in order to benefit from partition pruning. Do you know better and, if so, can you elaborate in your answer a bit? – Matthew McPeak Aug 23 '19 at 11:20
  • @MatthewMcPeak In deed, in this example the setup is rather pointless. I have a table with column `end_time` and `delay_time`. The real end time is `real_end_time GENERATED ALWAYS AS (end_time + delay_time)`. Partition is based on `real_end_time` and typically the virtual column is used in WHERE condition. – Wernfried Domscheit Aug 23 '19 at 11:47
  • Another (real) example: My table has timestamp values as Unix timestamp. The partiton key is `end_time GENERATED ALWAYS AS (timestamp '1970-01-01 00:00:00 UTC' + unix_timestamp * INTERVAL '1' SECOND)` - Well the partition is based just on one column but it utilize a virtual column. – Wernfried Domscheit Aug 23 '19 at 11:47