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 Go to next message
ace_friends22
Messages: 5
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 Go to previous messageGo to next message
John Watson
Messages: 8928
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 #689439 is a reply to message #689438] Wed, 27 December 2023 06:08 Go to previous messageGo to next message
ace_friends22
Messages: 5
Registered: December 2023
Junior Member
Hi John,

Cannot copy SQL as working in VM. Let me explain you again.

Need to create a table with PARTITION by brand name (BRAND_NM). Expected values for brands will be "AAA", "BBB", "CCC", "DDD" & default

Under brand partition, need to create SUB-PARTITION using year (SAIL_YEAR). This should be auto created. i.e. for any new year, it should create sub-partition.
Re: Create Partition and Sub-Partition [message #689440 is a reply to message #689439] Wed, 27 December 2023 06:18 Go to previous messageGo to next message
John Watson
Messages: 8928
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 #689441 is a reply to message #689437] Wed, 27 December 2023 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you want to continue to get help feedback in your topics.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Re: Create Partition and Sub-Partition [message #689442 is a reply to message #689440] Wed, 27 December 2023 06:36 Go to previous messageGo to next message
ace_friends22
Messages: 5
Registered: December 2023
Junior Member
Hi John,

Oracle Version: 19c

CREATE TABLE ON_BOARD_FACT
(
ONBOARD_KEY NUMBER,
BRAND_NM VARCHAR2(50),
SAIL_YEAR VARCHAR2(4),
SAIL_START_DATE DATE,
SAIL_END_DATE DATE
)
PARTITION BY LIST("BRAND_NM")
SUBPARTITION BY LIST("SAIL_YEAR") [i]AUTOMATIC[/i]
(
PARTITION BRAND_AAA VALUES ('AAA')
PARTITION BRAND_BBB VALUES ('BBB')
PARTITION BRAND_CCC VALUES ('CCC')
PARTITION BRAND_DDD VALUES ('DDD')
PARTITION BRAND_EEE VALUES ('EEE')
PARTITION BRAND_DEFAULT VALUES (DEFAULT)
)
SAIL_YEAR starts with 2007.... I want to make SUBPARTITION automatic. But seems AUTOMATIC is not supported in SUB-PARTITION. What should be alternate way to achieve the same?

Re: Create Partition and Sub-Partition [message #689443 is a reply to message #689440] Wed, 27 December 2023 07:07 Go to previous messageGo to next message
ace_friends22
Messages: 5
Registered: December 2023
Junior Member
Hi John,

Another attempt

CREATE TABLE ON_BOARD_FACT
(
ONBOARD_KEY NUMBER,
BRAND_NM VARCHAR2(50),
SAIL_YEAR VARCHAR2(4),
SAIL_START_DATE DATE,
SAIL_END_DATE DATE
)
PARTITION BY LIST(BRAND_NM, SAIL_YEAR) AUTOMATIC
(
 PARTITION BY_DEFAULT VALUES (DEFAULT)
)
This also results into error with ORA-14851 DEFAULT [sub]partition cannot be specified for AUTOLIST [sub]partitioned objects.
Re: Create Partition and Sub-Partition [message #689444 is a reply to message #689442] Wed, 27 December 2023 07:19 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3270
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 Go to previous message
Solomon Yakobson
Messages: 3270
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.
Previous Topic: problem with sql passthru syntax - get "FROM keyword not found where expected (4 merged)
Next Topic: Partition
Goto Forum:
  


Current Time: Tue Apr 16 15:54:12 CDT 2024