Home » SQL & PL/SQL » SQL & PL/SQL » Table partitioning (Oracle 10g)
Table partitioning [message #407856] Fri, 12 June 2009 00:40 Go to next message
tranjith85
Messages: 1
Registered: June 2009
Junior Member
Hi all,

Below is the structure of the table in concern

CREATE TABLE "AR"."RA_CUSTOMER_TRX_ALL"
( "CUSTOMER_TRX_ID" NUMBER(15,0) NOT NULL ENABLE,
"LAST_UPDATE_DATE" DATE NOT NULL ENABLE,
"LAST_UPDATED_BY" NUMBER(15,0) NOT NULL ENABLE,
"CREATION_DATE" DATE NOT NULL ENABLE,
...
...
...
"CC_ERROR_TEXT" VARCHAR2(255),
"CC_ERROR_FLAG" VARCHAR2(1)
) PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TX_DATA_32M"
PARTITION BY RANGE ("CUSTOMER_TRX_ID")
(PARTITION "RA_CUSTOMER_TRX_OVRFL" VALUES LESS THAN (MAXVALUE)
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
STORAGE(INITIAL 33554432 NEXT 33554432 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TX_DATA_32M" NOCOMPRESS )

I see only one partition. I always thought there will be two or more partitions to maintain the table and speed up data access. What is the significance of the above structure. Going by the partiion name RA_CUSTOMER_TRX_OVRFL i guess its a overflow partiion. Can some one what a overflow partiion is and its uses.

Below is a tkprof segment that uses this table.


9164 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=36656 pr=0 pw=0 time=650033 us)
9164 TABLE ACCESS BY LOCAL INDEX ROWID RA_CUSTOMER_TRX_ALL PARTITION: KEY KEY (cr=36656 pr=0 pw=0 time=445625 us)
9164 INDEX UNIQUE SCAN RA_CUSTOMER_TRX_U1 PARTITION: KEY KEY (cr=27492 pr=0 pw=0 time=184365 us)(object id 842047)
0 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=0 pr=0 pw=0 time=132158 us)
0 TABLE ACCESS BY LOCAL INDEX ROWID RA_CUSTOMER_TRX_ALL PARTITION: 1 1 (cr=0 pr=0 pw=0 time=72855 us)
0 INDEX UNIQUE SCAN RA_CUSTOMER_TRX_U1 PARTITION: 1 1 (cr=0 pr=0 pw=0 time=31171 us)(object id 842047)

I also thought of putting the index structure below :

"
CREATE UNIQUE INDEX "AR"."RA_CUSTOMER_TRX_U1" ON "AR"."RA_CUSTOMER_TRX_ALL" ("CUSTOMER_TRX_ID")
PCTFREE 0 INITRANS 11 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TX_IDX_16M" LOCAL
(PARTITION "RA_CUSTOMER_TRX_OVRFL"
PCTFREE 0 INITRANS 11 MAXTRANS 255
STORAGE(INITIAL 16777216 NEXT 16777216 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "APPS_TS_TX_IDX_16M" )
"

I am new to partitioning and hope some one could throw light on the above structure and how it works.

Regards and thanks.
Re: Table partitioning [message #407861 is a reply to message #407856] Fri, 12 June 2009 01:09 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

2/ Remove all useless stuff like schema name, storage attributes or quotes around names

3/
Always post your Oracle version (4 decimals).

4/
Quote:
I always thought there will be two or more partitions to maintain the table and speed up data access.

It may be useful but not mandatory

5/
Quote:
Going by the partiion name RA_CUSTOMER_TRX_OVRFL i guess its a overflow partiion. Can some one what a overflow partiion is and its uses.

Overflow partition is not an Oracle notion. It may be a functinal one to prevent from an error when data don't/can't come into the other partitions (for instance you forget to create the appropriate one).

6/
Quote:
I also thought of putting the index structure below

May be yes, may be no, it depends on the SQL statements you will execute.

Regards
Michel
Previous Topic: ORA-01410: invalid ROWID
Next Topic: How to load image in Long Raw column
Goto Forum:
  


Current Time: Fri Dec 09 05:45:45 CST 2016

Total time taken to generate the page: 0.11004 seconds