Home » RDBMS Server » Performance Tuning » create partitioning a table (Linux, oracle 11.2)
create partitioning a table [message #663810] Wed, 21 June 2017 00:58 Go to next message
kar116
Messages: 3
Registered: February 2016
Location: bangalore
Junior Member
Hi,

We want to partition the Claims_Highway table, which size 393 GB and rows are 412,462,817. This table is having large transactional tables for manageability and performance. This will allow us to archive data over 5 years old and aid with purging data in the future. We will partition the data based on the date the record was created and utilize Oracle Interval Partitioning. The partitions will be by MONTH.  

Among the tables Claims_Highway and Claims_Highway_Exceptions is one group  of tables which has to be partitioned primarily, in that table we have decided and finalized to go for partition on (DETECTION_RECV_DT) column as primary partition key.

DETECTION_RECV_DT column datatype is date.

We have other tables which fall under parent - child relationships.  Claims and Facility_claims tables have referential integrity with other tables so the partitioning will be based on the created_at date of the parent table.

Below is the table structure. Please check it and let me know what is the best method to do the partition (like range or hash or list)
and also send me the create table structure based on below with partition.

CREATE TABLE P2ADMIN.CLAIMS_HIGHWAY
(
CLAIM_REPOSITORY_ID VARCHAR2(10 BYTE),
DETECTION_RECV_DT DATE,
DETECTION_RESP_DT DATE,
DETECTION_RECOMMENDATION VARCHAR2(1 BYTE),
DETECTION_REASON_CD VARCHAR2(3 BYTE),
ADJUDICATION_PLATFORM VARCHAR2(4 BYTE),
BUSINESS_ENTITY VARCHAR2(100 BYTE),
CLAIM_TYPE VARCHAR2(1 BYTE),
FUNDING_TYPE VARCHAR2(3 BYTE),
APPROVAL_RECV_DT DATE,
INVESTIGATION_RESP_DT DATE,
PAID_RECV_DT DATE,
REPLY_QUEUE VARCHAR2(100 BYTE),
CLAIM_INBOUND_FLOW_NAME VARCHAR2(100 BYTE),
CREATED_BY VARCHAR2(20 BYTE),
UPDATED_BY VARCHAR2(20 BYTE),
CREATED_AT DATE,
UPDATED_AT DATE,
APPROVAL_RECOMMENDATION VARCHAR2(2 BYTE),
APPROVAL_REASON_CD VARCHAR2(5 BYTE),
INVESTIGATION_RECOMMENDATION VARCHAR2(1 BYTE),
ENVELOP_XML VARCHAR2(4000 BYTE),
MESSAGE_XML CLOB,
SUPPLEMENTAL LOG GROUP GGS_191733 (CLAIM_REPOSITORY_ID) ALWAYS
)
LOB (MESSAGE_XML) STORE AS BASICFILE MESSAGE_XML (
TABLESPACE APP1_LOBS
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 104K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
TABLESPACE PRO_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 20M
NEXT 20M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;


CREATE INDEX P2ADMIN.CLAIMS_HIGHWAY_IDX1 ON P2ADMIN.CLAIMS_HIGHWAY
(DETECTION_RECV_DT)
LOGGING
TABLESPACE PRO_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

CREATE INDEX P2ADMIN.CLAIMS_HIGHWAY_IDX2 ON P2ADMIN.CLAIMS_HIGHWAY
(DETECTION_RESP_DT)
LOGGING
TABLESPACE PRO_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

CREATE INDEX P2ADMIN.CLAIMS_HIGHWAY_IDX3 ON P2ADMIN.CLAIMS_HIGHWAY
(APPROVAL_RECV_DT)
LOGGING
TABLESPACE PRO_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

CREATE UNIQUE INDEX P2ADMIN.CLAIMS_HIGHWAY_PK ON P2ADMIN.CLAIMS_HIGHWAY
(CLAIM_REPOSITORY_ID)
LOGGING
TABLESPACE PRO_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

CREATE OR REPLACE SYNONYM P2APP.CLAIMS_HIGHWAY FOR P2ADMIN.CLAIMS_HIGHWAY;


ALTER TABLE P2ADMIN.CLAIMS_HIGHWAY ADD (
CONSTRAINT CLAIMS_HIGHWAY_PK
PRIMARY KEY
(CLAIM_REPOSITORY_ID)
USING INDEX P2ADMIN.CLAIMS_HIGHWAY_PK
ENABLE VALIDATE);

GRANT SELECT ON P2ADMIN.CLAIMS_HIGHWAY TO P2ADMIN_RO;

GRANT DELETE, INSERT, SELECT, UPDATE ON P2ADMIN.CLAIMS_HIGHWAY TO P2ADMIN_RW;

GRANT SELECT ON P2ADMIN.CLAIMS_HIGHWAY TO RPT_OWNER;

GRANT SELECT ON P2ADMIN.CLAIMS_HIGHWAY TO RPT_STAGE_OWNER;
Re: create partitioning a table [message #663811 is a reply to message #663810] Wed, 21 June 2017 01:30 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your post is inconsistent. First this:
Quote:
We will partition the data based on the date the record was created and utilize Oracle Interval Partitioning. The partitions will be by MONTH.
and then this:
Quote:
Please check it and let me know what is the best method to do the partition (like range or hash or list)
If you have already decided your partitioning strategy, why are you asking for advice?
Re: create partitioning a table [message #663814 is a reply to message #663810] Wed, 21 June 2017 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Tue, 23 February 2016 07:58

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
...
Also answer to the question we post you and feedback in your previous topic.


[Updated on: Wed, 21 June 2017 01:42]

Report message to a moderator

Re: create partitioning a table [message #663819 is a reply to message #663814] Wed, 21 June 2017 02:06 Go to previous messageGo to next message
kar116
Messages: 3
Registered: February 2016
Location: bangalore
Junior Member
Hi John,

Sorry for the confusion.

We will partition the data based on the date the record was created and utilize Oracle Interval Partitioning. The partitions will be by MONTH.

Can you please send me the create table script by using partition?
Re: create partitioning a table [message #663822 is a reply to message #663819] Wed, 21 June 2017 02:13 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
No, I won't write a script for you (unless you are asking for consulting services?) You need to read up on DBMS_REDEFINITION. The job is simple, but huge.










Re: create partitioning a table [message #663940 is a reply to message #663822] Tue, 27 June 2017 04:16 Go to previous messageGo to next message
Deep Chakraborty
Messages: 10
Registered: June 2006
Junior Member
You can use CTAS. If upgrading to 12c, you can use simply ALTER TABLE MODIFY command

[Updated on: Tue, 27 June 2017 04:24]

Report message to a moderator

Re: create partitioning a table [message #663941 is a reply to message #663940] Tue, 27 June 2017 04:32 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Just to the right of the OP heading:
Quote:
(Linux, oracle 11.2)
Re: create partitioning a table [message #663955 is a reply to message #663940] Tue, 27 June 2017 23:11 Go to previous message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
biswachk wrote on Tue, 27 June 2017 09:16
You can use CTAS. If upgrading to 12c, you can use simply ALTER TABLE MODIFY command
Did you really convert the heap table to partition table with more than 100 million rows in real OLTP?
Did you discover and understand how difficult to convert? Many thing effect against to Database, even the small table.
Previous Topic: Full table scan
Next Topic: SQL hint between view & table
Goto Forum:
  


Current Time: Thu Mar 28 11:16:11 CDT 2024