OraFAQ Forum: SQL & PL/SQL » Create Partition and Sub-Partition

Home » SQL & PL/SQL » SQL & PL/SQL » Create Partition and Sub-Partition

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

Re: Create Partition and Sub-Partition [message #689438 is a reply to message #689437]

Wed, 27 December 2023 06:03

John Watson
Messages: 8931
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.

Report message to a moderator

Re: Create Partition and Sub-Partition [message #689439 is a reply to message #689438]

Wed, 27 December 2023 06:08

ace_friends22
Messages: 9
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.

Report message to a moderator

Re: Create Partition and Sub-Partition [message #689440 is a reply to message #689439]

Wed, 27 December 2023 06:18

John Watson
Messages: 8931
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.

Report message to a moderator

Re: Create Partition and Sub-Partition [message #689441 is a reply to message #689437]

Wed, 27 December 2023 06:28

Michel Cadot
Messages: 68647
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.

Report message to a moderator

Re: Create Partition and Sub-Partition [message #689442 is a reply to message #689440]

Wed, 27 December 2023 06:36

ace_friends22
Messages: 9
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?

Report message to a moderator

Re: Create Partition and Sub-Partition [message #689443 is a reply to message #689440]

Wed, 27 December 2023 07:07

ace_friends22
Messages: 9
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.

Report message to a moderator

Re: Create Partition and Sub-Partition [message #689444 is a reply to message #689442]

Wed, 27 December 2023 07:19

Solomon Yakobson
Messages: 3273
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: 3273
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.

Report message to a moderator

Previous Topic:	problem with sql passthru syntax - get "FROM keyword not found where expected (4 merged)
Next Topic:	Partition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 10:20:44 CDT 2024