Home » SQL & PL/SQL » SQL & PL/SQL » Create Partition and Sub-Partition
Create Partition and Sub-Partition [message #689437] |
Wed, 27 December 2023 05:49 |
|
ace_friends22
Messages: 9 Registered: December 2023
|
Junior Member |
|
|
Hi Team,
Can someone help me to create table with
- Main partition using brand name ('AAA', 'BBB', 'CCC', 'DDD', 'EEE', Default)
- Sub-partition using year
The sub-partition will have data from 2007 and it should automatically created. for example. if I enter data for 2027, the subpartition should be created for 2027.
I hope I'm clear. I tried with Google, but not getting exact solution and if I make any change, it is giving errors.
|
|
|
Re: Create Partition and Sub-Partition [message #689438 is a reply to message #689437] |
Wed, 27 December 2023 06:03 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:I hope I'm clear. I tried with Google, but not getting exact solution and if I make any change, it is giving errors. Not clear! Well, not to me. If you show the SQL you have tried, and the errors, one might be able to help.
|
|
|
|
Re: Create Partition and Sub-Partition [message #689440 is a reply to message #689439] |
Wed, 27 December 2023 06:18 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Come on, man! What SQL have you tried?
You seem to be describing LIST-LIST composite partitioning, with INTERVAL for the subpartitions. Is that right? If that is what you want to do, you are out of luck. However, it seems a bizarre requirement. What are you actually trying to achieve? What is the problem, in business terms, that you think this partitioning strategy would address? There may be another way to do it.
|
|
|
|
|
|
Re: Create Partition and Sub-Partition [message #689444 is a reply to message #689442] |
Wed, 27 December 2023 07:19 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
INTERVAL/AUTOMATIC is supported for partitions only, not for sub-partitions. The best you can do is partition by both brand and year which will give you same physical layout as partition by brand and sub-partition by year:
SQL> CREATE TABLE ON_BOARD_FACT(
2 ONBOARD_KEY NUMBER,
3 BRAND_NM VARCHAR2(50),
4 SAIL_YEAR VARCHAR2(4),
5 SAIL_START_DATE DATE,
6 SAIL_END_DATE DATE
7 )
8 PARTITION BY LIST(BRAND_NM,SAIL_YEAR) AUTOMATIC
9 (
10 PARTITION BRAND_AAA_2007 VALUES ('AAA',2007),
11 PARTITION BRAND_BBB_2007 VALUES ('BBB',2007),
12 PARTITION BRAND_CCC_2007 VALUES ('CCC',2007),
13 PARTITION BRAND_DDD_2007 VALUES ('DDD',2007),
14 PARTITION BRAND_EEE_2007 VALUES ('EEE',2007)
15 )
16 /
Table created.
SQL> COLUMN PARTITION_NAME FORMAT A14
SQL> COLUMN HIGH_VAlue FORMAT A17
SQL> SELECT PARTITION_NAME,
2 HIGH_VALUE
3 FROM USER_TAB_PARTITIONS
4 WHERE TABLE_NAME = 'ON_BOARD_FACT'
5 ORDER BY PARTITION_POSITION
6 /
PARTITION_NAME HIGH_VALUE
-------------- -----------------
BRAND_AAA_2007 ( 'AAA', '2007' )
BRAND_BBB_2007 ( 'BBB', '2007' )
BRAND_CCC_2007 ( 'CCC', '2007' )
BRAND_DDD_2007 ( 'DDD', '2007' )
BRAND_EEE_2007 ( 'EEE', '2007' )
SQL> INSERT
2 INTO ON_BOARD_FACT
3 VALUES(1,'DDD',2017,DATE '2017-01-01',DATE '2017-06-30')
4 /
1 row created.
SQL> INSERT
2 INTO ON_BOARD_FACT
3 VALUES(1,'XXX',2023,DATE '2023-01-01',DATE '2023-12-31')
4 /
1 row created.
SQL> SELECT PARTITION_NAME,
2 HIGH_VALUE
3 FROM USER_TAB_PARTITIONS
4 WHERE TABLE_NAME = 'ON_BOARD_FACT'
5 ORDER BY PARTITION_POSITION
6 /
PARTITION_NAME HIGH_VALUE
-------------- -----------------
BRAND_AAA_2007 ( 'AAA', '2007' )
BRAND_BBB_2007 ( 'BBB', '2007' )
BRAND_CCC_2007 ( 'CCC', '2007' )
BRAND_DDD_2007 ( 'DDD', '2007' )
BRAND_EEE_2007 ( 'EEE', '2007' )
SYS_P4402 ( 'DDD', '2017' )
SYS_P4403 ( 'XXX', '2023' )
7 rows selected.
SQL>
SY.
[Updated on: Wed, 27 December 2023 07:28] Report message to a moderator
|
|
|
Re: Create Partition and Sub-Partition [message #689445 is a reply to message #689444] |
Wed, 27 December 2023 07:33 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
And you can rename automatic partitions if you want to:
SQL> ALTER TABLE ON_BOARD_FACT RENAME PARTITION SYS_P4402 TO BRAND_DDD_2017
2 /
Table altered.
SQL> ALTER TABLE ON_BOARD_FACT RENAME PARTITION SYS_P4403 TO BRAND_XXX_2023
2 /
Table altered.
SQL> SELECT PARTITION_NAME,
2 HIGH_VALUE
3 FROM USER_TAB_PARTITIONS
4 WHERE TABLE_NAME = 'ON_BOARD_FACT'
5 ORDER BY PARTITION_POSITION
6 /
PARTITION_NAME HIGH_VALUE
-------------- -----------------
BRAND_AAA_2007 ( 'AAA', '2007' )
BRAND_BBB_2007 ( 'BBB', '2007' )
BRAND_CCC_2007 ( 'CCC', '2007' )
BRAND_DDD_2007 ( 'DDD', '2007' )
BRAND_EEE_2007 ( 'EEE', '2007' )
BRAND_DDD_2017 ( 'DDD', '2017' )
BRAND_XXX_2023 ( 'XXX', '2023' )
7 rows selected.
SQL>
SY.
|
|
|
Goto Forum:
Current Time: Sun Sep 29 00:37:00 CDT 2024
|