Home » SQL & PL/SQL » SQL & PL/SQL » understanding table partition (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)
understanding table partition [message #681947] Tue, 15 September 2020 20:26 Go to next message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
i'm trying to understand about table partition. below is a snippet of code of a table that we had which were created with partition.


CREATE TABLE "W_CASE_MONTH_SMRY" 
   (	"CASE_ID" VARCHAR2(14 BYTE) NOT NULL ENABLE, 
	"RPT_MONTH_ID" NUMBER(6,0) NOT NULL ENABLE, 
        ...  
   ) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 ROW STORE COMPRESS ADVANCED  LOGGING 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
  PARTITION BY RANGE ("RPT_MONTH_ID") 
  SUBPARTITION BY HASH ("CASE_ID","RPT_MONTH_ID") 
  SUBPARTITIONS 16
 ...
 PARTITION "W_CASE_MONTH_202007"  VALUES LESS THAN (202008) 
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "W_CASE_MONTH_SMRY_2020" 
 COMPRESS BASIC  LOGGING 
 ( SUBPARTITION "W_CASE_M_S_2020_07_S01" SEGMENT CREATION IMMEDIATE 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "WRR_CASE_MTH_SMRY_2020" 
 COMPRESS BASIC,
 ...

basically what I am trying to understand is the line of code about the partition:
PARTITION "W_CASE_MONTH_202007"  VALUES LESS THAN (202008) 
column RPT_MONTH_ID is of number datatype and has values such as 202001, 202002, 20203, … 202009. when the partition says VALUES LESS THAN (202008) does it includes all rows that are less than 202008? those that are 202007, 202006, 202005, … and below?

thanks,
warren

Re: understanding table partition [message #681948 is a reply to message #681947] Wed, 16 September 2020 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It includes all rows above or equal to the previous partition definition and less than the value in its definition.
Here greater than or equal to 202007 and less than 202008 and as the column is defined as an integer this means just all rows with value 202007.

Re: understanding table partition [message #681949 is a reply to message #681948] Wed, 16 September 2020 04:28 Go to previous messageGo to next message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
thanks Michel. if we want partition of rows of only one month prior how do we accomplish it? that it would look something like


PARTITION "W_CASE_MONTH_202006" VALUES EQUAL TO (202006) …
PARTITION "W_CASE_MONTH_202007" VALUES EQUAL TO (202007) …
PARTITION "W_CASE_MONTH_202008" VALUES EQUAL TO (202008) …


Re: understanding table partition [message #681950 is a reply to message #681949] Wed, 16 September 2020 04:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
PARTITION "W_CASE_MONTH_202006" VALUES LESS THAN (202007) …
PARTITION "W_CASE_MONTH_202007" VALUES LESS THAN (202008) …
PARTITION "W_CASE_MONTH_202008" VALUES LESS THAN (202009) …

But I'd look into list partitioning.

SY.
Re: understanding table partition [message #681951 is a reply to message #681950] Wed, 16 September 2020 06:47 Go to previous messageGo to next message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
thanks Solomon.

so that I understand

PARTITION "W_CASE_MONTH_202006" VALUES LESS THAN (202007) … --> is like where RPT_MONTH_ID = 202006
PARTITION "W_CASE_MONTH_202007" VALUES LESS THAN (202008) … --> is like where RPT_MONTH_ID = 202007
PARTITION "W_CASE_MONTH_202008" VALUES LESS THAN (202009) … --> is like where RPT_MONTH_ID = 202008

please advise if my understanding is correct. thanks so much again.
Re: understanding table partition [message #681952 is a reply to message #681951] Wed, 16 September 2020 06:58 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
wtolentino wrote on Wed, 16 September 2020 12:47
thanks Solomon.

so that I understand

PARTITION "W_CASE_MONTH_202006" VALUES LESS THAN (202007) … --> is like where RPT_MONTH_ID = 202006
PARTITION "W_CASE_MONTH_202007" VALUES LESS THAN (202008) … --> is like where RPT_MONTH_ID = 202007
PARTITION "W_CASE_MONTH_202008" VALUES LESS THAN (202009) … --> is like where RPT_MONTH_ID = 202008

please advise if my understanding is correct. thanks so much again.
In this example, partition w_case_month_202006 will contain rows where rpt_month_id has any value from minus infinity to plus 202006 point 9 recurring.

You might be better off creating a virtual column of type date or timestamp, and partitioning on that. There is a lot of intelligence built into the date datatype that you are losing by working with numbers.
Re: understanding table partition [message #681953 is a reply to message #681952] Wed, 16 September 2020 10:24 Go to previous messageGo to next message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
thanks John.

John Watson wrote on Wed, 16 September 2020 07:58
In this example, partition w_case_month_202006 will contain rows where rpt_month_id has any value from minus infinity to plus 202006 point 9 recurring.

You might be better off creating a virtual column of type date or timestamp, and partitioning on that. There is a lot of intelligence built into the date datatype that you are losing by working with numbers.

rpt_month_id has values in 202008, 202007, 202006, 202005, …, 202001.

in the case of partition w_case_month_202006 (PARTITION "W_CASE_MONTH_202006" VALUES LESS THAN (202007)) it will have the rows where rpt_month_id in 202006, 202005, …, 202001 right?




Re: understanding table partition [message #681954 is a reply to message #681953] Wed, 16 September 2020 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If there is no partition defined with a value less than this value (202007), it will have the rows with "VALUES LESS THAN (202007)" what is not clear in the definition?

Re: understanding table partition [message #681961 is a reply to message #681954] Wed, 16 September 2020 14:50 Go to previous messageGo to next message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
Michel Cadot wrote on Wed, 16 September 2020 11:36

If there is no partition defined with a value less than this value (202007), it will have the rows with "VALUES LESS THAN (202007)" what is not clear in the definition?


thanks michel. i guess i was thinking about "VALUES LESS THAN" is the same as "where rpt_month_id < 202007. so i was thinking that it will have rows that has 202006, 202005, 202004, 202003, 202002, and 202001.

[Updated on: Wed, 16 September 2020 14:51]

Report message to a moderator

Re: understanding table partition [message #681962 is a reply to message #681961] Thu, 17 September 2020 00:28 Go to previous message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It will only if there are not in another partition; why Oracle would duplicate rows?
See Oracle glossary:
Quote:
partitioning: The ability to decompose very large tables and indexes into smaller and more manageable pieces called partitions.

It is like when you cut a piece of bread, each slice (assuming you are right handed and so cut from right to left) contains all the bread that is at the right of the cut, of course it can't contain the bread that are in the previous slices you cut.

Previous Topic: Invisible double quotes
Next Topic: Date interval Blank count
Goto Forum:
  


Current Time: Fri Sep 18 12:59:22 CDT 2020