understanding table partition [message #681947] |
Tue, 15 September 2020 20:26 |
wtolentino
Messages: 413 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 #681949 is a reply to message #681948] |
Wed, 16 September 2020 04:28 |
wtolentino
Messages: 413 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 |
Solomon Yakobson
Messages: 3286 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 |
wtolentino
Messages: 413 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 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
wtolentino wrote on Wed, 16 September 2020 12:47thanks 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 |
wtolentino
Messages: 413 Registered: March 2005
|
Senior Member |
|
|
thanks John.
John Watson wrote on Wed, 16 September 2020 07:58In 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 #681961 is a reply to message #681954] |
Wed, 16 September 2020 14:50 |
wtolentino
Messages: 413 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 |
|
Michel Cadot
Messages: 68694 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.
|
|
|