Home » RDBMS Server » Server Administration » Split Partitioning a MAX VALUE partition (Oracle 10g Release 1)
Split Partitioning a MAX VALUE partition [message #323064] |
Tue, 27 May 2008 04:04 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
himang
Messages: 282 Registered: March 2005 Location: Bangalore
|
Senior Member |
![himanshu_gupta10%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
We have a table which is Range partitioned on Date (Daily) and has 10 sub partitions (List). We have a MAX VALUE partition which we are trying to partition further by splitting the max value partition. The table has around 21 million records but the max value partition is currently empty. When we are trying to split the empty max value partition, it is taking approx 10-15 minutes for each partition split. Does it really takes this much of time for splitting an empty partition. We do have some global indexes on the table but does splitting an empty partition takes time?
|
|
|
|
Re: Split Partitioning a MAX VALUE partition [message #323085 is a reply to message #323064] |
Tue, 27 May 2008 04:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
himang
Messages: 282 Registered: March 2005 Location: Bangalore
|
Senior Member |
![himanshu_gupta10%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
Table table structure is as follows
CREATE TABLE OB_TEMP
(
ob_audit_log_id VARCHAR2(36 CHAR) NOT NULL,
order_group_id VARCHAR2(36 CHAR) NOT NULL,
action_code VARCHAR2(10 CHAR) NOT NULL,
action_code_level VARCHAR2(1 CHAR),
frictionless_code VARCHAR2(1 CHAR),
order_form_id VARCHAR2(36 CHAR),
action_reason_code VARCHAR2(6 CHAR),
order_queue_id VARCHAR2(5 CHAR),
business_unit_id INTEGER,
top_channel_code VARCHAR2(20 CHAR),
lower_channel_code VARCHAR2(20 CHAR),
company_num VARCHAR2(8 CHAR),
op_rep_id VARCHAR2(36 CHAR),
op_rep_name VARCHAR2(50 CHAR),
op_manager_id VARCHAR2(36 CHAR),
op_manager_name VARCHAR2(50 CHAR),
primary_online_payment_code VARCHAR2(8 CHAR),
order_form_cnt NUMBER,
system_cnt NUMBER,
doms_quote_num NUMBER,
doms_order_num NUMBER,
salesrep_num NUMBER,
keycode VARCHAR2(10 CHAR),
customer_set_id VARCHAR2(9 CHAR),
brand_id NUMBER,
order_form_total_amt NUMBER(18,3),
order_group_total_amt NUMBER(18,3),
general_contact_id VARCHAR2(36 CHAR),
error_code VARCHAR2(10 CHAR),
error_category_code VARCHAR2(2 CHAR),
error_desc VARCHAR2(160 CHAR),
ship_to_contact_id VARCHAR2(36 CHAR),
export_flag VARCHAR2(1 CHAR),
create_date DATE,
update_by INTEGER,
update_system VARCHAR2(20 CHAR),
action_date TIMESTAMP(6),
assisting_salesrep_num NUMBER,
disposition_code VARCHAR2(3 CHAR),
shipping_option_type VARCHAR2(2 CHAR),
tax_total_amt NUMBER(18,3),
ship_total_amt NUMBER(18,3),
service_tag VARCHAR2(50 CHAR),
po_number VARCHAR2(30 CHAR),
backend_customer_link_num INTEGER,
og_disposition_code VARCHAR2(3 CHAR),
ob_request_id VARCHAR2(36 CHAR),
source_application_name VARCHAR2(5 CHAR),
country_cd VARCHAR2(2 CHAR),
irn VARCHAR2(20 CHAR),
order_form_total_amt_currency VARCHAR2(3 CHAR),
order_group_total_amt_currency VARCHAR2(3 CHAR),
tax_total_amt_currency VARCHAR2(3 CHAR),
ship_total_amt_currency VARCHAR2(3 CHAR),
oms_system_id VARCHAR2(5 CHAR),
entered_date DATE,
of_cost_amt NUMBER(18,3),
og_cost_amt NUMBER(18,3),
of_cost_amt_currency VARCHAR2(3 CHAR),
og_cost_amt_currency VARCHAR2(3 CHAR),
dp_id NUMBER(13)
)
TABLESPACE OO_TEMP
PCTUSED 0
PCTFREE 0
INITRANS 16
MAXTRANS 255
STORAGE (
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOLOGGING
PARTITION BY RANGE (ACTION_DATE)
SUBPARTITION BY LIST (ACTION_CODE)
SUBPARTITION TEMPLATE
(SUBPARTITION S_GROUP_START VALUES ('CUST'),
SUBPARTITION S_FORM_NACKED VALUES ('OBNAK', 'IPNAK'),
SUBPARTITION S_START VALUES ('START'),
SUBPARTITION S_FRICLESS_GC VALUES ('OBCMP'),
SUBPARTITION S_FRICNED_GC VALUES ('ESMAN'),
SUBPARTITION S_CAN_GC VALUES ('ESCAN'),
SUBPARTITION S_GDREQ VALUES ('GDREQ'),
SUBPARTITION S_GDRSP VALUES ('GDRSP'),
SUBPARTITION S_IPACK VALUES ('IPACK'),
SUBPARTITION S_OGSVD VALUES ('OGSVD'),
SUBPARTITION S_OTHER VALUES (DEFAULT)
)
(
PARTITION TM_MAXVALUE VALUES LESS THAN (MAXVALUE)
NOLOGGING
NOCOMPRESS
TABLESPACE OO_TEMP
PCTFREE 0
INITRANS 16
MAXTRANS 255
STORAGE (
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
( SUBPARTITION TM_MAXVALUE_S_GROUP_START VALUES ('CUST') TABLESPACE OO_TEMP,
SUBPARTITION TM_MAXVALUE_S_FORM_NACKED VALUES ('OBNAK', 'IPNAK') TABLESPACE OO_TEMP,
SUBPARTITION TM_MAXVALUE_S_START VALUES ('START') TABLESPACE OO_TEMP,
SUBPARTITION TM_MAXVALUE_S_FRICLESS_GC VALUES ('OBCMP') TABLESPACE OO_TEMP,
SUBPARTITION TM_MAXVALUE_S_FRICNED_GC VALUES ('ESMAN') TABLESPACE OO_TEMP,
SUBPARTITION TM_MAXVALUE_S_CAN_GC VALUES ('ESCAN') TABLESPACE OO_TEMP,
SUBPARTITION TM_MAXVALUE_S_GDREQ VALUES ('GDREQ') TABLESPACE OO_TEMP,
SUBPARTITION TM_MAXVALUE_S_GDRSP VALUES ('GDRSP') TABLESPACE OO_TEMP,
SUBPARTITION TM_MAXVALUE_S_IPACK VALUES ('IPACK') TABLESPACE OO_TEMP,
SUBPARTITION TM_MAXVALUE_S_OGSVD VALUES ('OGSVD') TABLESPACE OO_TEMP,
SUBPARTITION TM_MAXVALUE_S_OTHER VALUES (DEFAULT) TABLESPACE OO_TEMP)
)
CACHE
NOPARALLEL
/
Then the table was splitted initally for 365 partitions, with ALTER TABLE SPLIT PARTITION command and data was loaded for each day. As of now also the TM_MAXVALUE partition is EMPTY and we are trying to SPLIT the max value partition with command
ALTER TABLE OB_TEMP SPLIT PARTITION TM_MAXVALUE AT (TIMESTAMP'2008-03-01 00:00:00') INTO (PARTITION TM_2008_03_01, PARTITION TM_MAXVALUE)
Please note there is no data in MAX VALUE partition but there are global indexes on the table and currently table is having 21 million records... Can this be a reason for slow performance?
|
|
|
|
Re: Split Partitioning a MAX VALUE partition [message #323105 is a reply to message #323085] |
Tue, 27 May 2008 05:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
According to your posting I am not able to see any empty MAX Partition. You are having only one partition and you are trying to create another partition by using the split partition method. So, Oracle has to scan all the 21 Million records in the current partition, delete from the partition you are splitting and it has to insert into the new partition and update the global indexes.
Also, I noticed you are using subpartition template. If you use a subpartition template you don't need to specify the list of values again. You need to mention is only the partition name. If you want to have a different list set of subpartition, then only you need to explicitly mention the subpartition names only.
So considering all this into account, time you have mentioned is reasonable.
By all means follow the advice given by Michael and let us know.
Regards
Raj
|
|
|
|
|
|
|
|
Re: Split Partitioning a MAX VALUE partition [message #323636 is a reply to message #323133] |
Thu, 29 May 2008 01:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
himang
Messages: 282 Registered: March 2005 Location: Bangalore
|
Senior Member |
![himanshu_gupta10%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
Just for information for other people, we resolved the issue by :-
1. First dropped the MAX_VALUE partition (as there was no data in max_value partition, so no data loss risk).
2. Then added the required partition. This didn't took time at all.
3. Then again added back the MAX_VALUE partition.
This whole activity took less than a minute and was achieved without any issue.
Regards
Himanshu
[Updated on: Thu, 29 May 2008 01:46] Report message to a moderator
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Sep 09 02:24:50 CDT 2024
|