Home » RDBMS Server » Performance Tuning » List partition and Index partitioning (merged)
| List partition and Index partitioning (merged) [message #396284] |
Mon, 06 April 2009 01:03  |
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
|
Senior Member |
|
|
Dear Friends,
I want to know about index partitioning.
I have a table with 30,00,000 rows and sized 600mb.
My data was speared evenly on basis of financial years which is character field.
i'm going to partition my table with list partitioning option.
My question is :
i have indexes on this table (other than primary key).
what should i have to do with my indexes.
how should i partition my current indexes.
My table structure is
CREATE TABLE MYTABLE
(
COMPCODE NUMBER(4) NOT NULL,
FINYEAR VARCHAR2(9 BYTE) NOT NULL,
FACTORYCODE NUMBER(4) NOT NULL,
DRUMCODE NUMBER(6) NOT NULL,
WSHEETNO NUMBER(6) NOT NULL,
WSSRNO NUMBER(4) NOT NULL,
ACPID CHAR(1 BYTE) NOT NULL,
WSDATE DATE NOT NULL,
BLENDTYPECD NUMBER(Cool NOT NULL,
BLENDNO NUMBER(Cool NOT NULL,
ORGLOTNO VARCHAR2(25 BYTE) NOT NULL,
LOTNO VARCHAR2(25 BYTE) NOT NULL,
RCHEST NUMBER(6) NOT NULL,
GROSSWEIGHT NUMBER(7,3) NOT NULL,
TAREWEIGHT NUMBER(7,3) NOT NULL,
NETKGSPERCHEST NUMBER(7,3) NOT NULL,
LKG NUMBER(10,3) NOT NULL,
MAINTEACATCD NUMBER(4) NOT NULL,
TEASUBCATCD NUMBER(4) NOT NULL,
SAMKG NUMBER(10,3) NOT NULL,
DEPTCD NUMBER(4),
TYPECDOFBLEND NUMBER(Cool NOT NULL,
REJBLENDNO NUMBER(Cool NOT NULL,
BLDKG NUMBER(10,3) NOT NULL,
BLENDSALEPRICE NUMBER(6,2) NOT NULL,
BLENDNETKG NUMBER(7,3) NOT NULL,
REMARK VARCHAR2(35 BYTE),
PREPAREDBY VARCHAR2(30 BYTE) NOT NULL,
WEIGHTEDBY VARCHAR2(30 BYTE) NOT NULL,
INO NUMBER(Cool NOT NULL,
ITYPE VARCHAR2(3 BYTE) NOT NULL,
RMTYPE VARCHAR2(1 BYTE) NOT NULL,
PRODID NUMBER(Cool NOT NULL,
USERID VARCHAR2(30 BYTE),
LOGINID VARCHAR2(20 BYTE),
UDATE DATE,
PRODSTAT VARCHAR2(3 BYTE) DEFAULT 'PND',
USESHRT NUMBER(6,3) DEFAULT 0,
SAMPTYPE VARCHAR2(30 BYTE) DEFAULT '',
RECTYPE VARCHAR2(3 BYTE) DEFAULT '',
OPL CHAR(1 BYTE) DEFAULT '-'
)
| Quote: |
Primary key on following columns :
(COMPCODE,FINYEAR,WSHEETNO,WSSRNO)
Bitmap index on following columns :
(COMPCODE, FINYEAR)
Normal B-Tree indexes :
1) (COMPCODE, BLENDTYPECD, BLENDNO)
2) (COMPCODE,WSDATE,LOTNO)
|
so please suggest me what should i have to do with my indexes.
either keep it as it is or change them to local/global.
|
|
|
|
| Re: List partition and Index partitioning [message #396285 is a reply to message #396284] |
Mon, 06 April 2009 01:06  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>so please suggest me what should i have to do with my indexes.
>either keep it as it is or change them to local/global.
implement the solution which benchmarks the better results!
Why do you need us?
You have table & DDL. We don't
You have data & DML. We don't.
You have code. We don't.
You have ALL the information & can't debug the problem.
Why do you expect others, who have NO details, to solve your mystery?
[Updated on: Mon, 06 April 2009 01:06] Report message to a moderator |
|
|
|
| Re: List partition and Index partitioning [message #396294 is a reply to message #396284] |
Mon, 06 April 2009 01:25 |
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
|
Senior Member |
|
|
Actually it was my mistake that i didn't post much information about my data.
all the data have unique compcode.
3000 rows per wsdate [sysdate].
500 rows per wsdate per blendtypecd.
45 rows per wsdate per blendtypecd per blendno/WSHEETNO.
thanks for quick reply.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Index partitioning [message #397132 is a reply to message #396284] |
Thu, 09 April 2009 03:06 |
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
|
Senior Member |
|
|
Dear all,
I am not able to find the specific reason to go for global index and global partitioned index..
I wanted to know at what circumstances we can use local index,global index and global partition index??
Here i'm posting table related details.
CREATE TABLE MYTABLE
(
COMPCODE NUMBER(4) NOT NULL,
FINYEAR VARCHAR2(9 BYTE) NOT NULL,
FACTORYCODE NUMBER(4) NOT NULL,
DRUMCODE NUMBER(6) NOT NULL,
WSHEETNO NUMBER(6) NOT NULL,
WSSRNO NUMBER(4) NOT NULL,
ACPID CHAR(1 BYTE) NOT NULL,
WSDATE DATE NOT NULL,
BLENDTYPECD NUMBER(Cool NOT NULL,
BLENDNO NUMBER(Cool NOT NULL,
ORGLOTNO VARCHAR2(25 BYTE) NOT NULL,
LOTNO VARCHAR2(25 BYTE) NOT NULL,
RCHEST NUMBER(6) NOT NULL,
GROSSWEIGHT NUMBER(7,3) NOT NULL,
TAREWEIGHT NUMBER(7,3) NOT NULL,
NETKGSPERCHEST NUMBER(7,3) NOT NULL,
LKG NUMBER(10,3) NOT NULL,
MAINTEACATCD NUMBER(4) NOT NULL,
TEASUBCATCD NUMBER(4) NOT NULL,
SAMKG NUMBER(10,3) NOT NULL,
DEPTCD NUMBER(4),
TYPECDOFBLEND NUMBER(Cool NOT NULL,
REJBLENDNO NUMBER(Cool NOT NULL,
BLDKG NUMBER(10,3) NOT NULL,
BLENDSALEPRICE NUMBER(6,2) NOT NULL,
BLENDNETKG NUMBER(7,3) NOT NULL,
REMARK VARCHAR2(35 BYTE),
PREPAREDBY VARCHAR2(30 BYTE) NOT NULL,
WEIGHTEDBY VARCHAR2(30 BYTE) NOT NULL,
INO NUMBER(Cool NOT NULL,
ITYPE VARCHAR2(3 BYTE) NOT NULL,
RMTYPE VARCHAR2(1 BYTE) NOT NULL,
PRODID NUMBER(Cool NOT NULL,
USERID VARCHAR2(30 BYTE),
LOGINID VARCHAR2(20 BYTE),
UDATE DATE,
PRODSTAT VARCHAR2(3 BYTE) DEFAULT 'PND',
USESHRT NUMBER(6,3) DEFAULT 0,
SAMPTYPE VARCHAR2(30 BYTE) DEFAULT '',
RECTYPE VARCHAR2(3 BYTE) DEFAULT '',
OPL CHAR(1 BYTE) DEFAULT '-'
)
I have primary key on following columns
(COMPCODE,FINYEAR,WSHEETNO,WSSRNO)
My table holds 6,00,000 (Approx)rows for each year [Size 100mb].
Total table size 500mb and 30,00,000 rows.
My table holds last five years data and still growing.
I'm planning to partition the table on FINYEAR column with 'list partition' option.
I have normal B-Tree indexes on this table.
1.> INDEX IDX_MYTAB1 ON(COMPCODE,BLENDTYPECD,BLENDNO) COLUMNS,size 115mb
2.> INDEX IDX_MYTAB2 ON(COMPCODE,WSDATE,LOTNO) COLUMN,size 108mb
My table holds normally 3000 rows per WSDATE(SYSDATE),30 rows per BLENDNO and
my uniqueness is COMPCODE,FINYEAR,WSHEETNO,WSSRNO,BLENDTYPECD,BLENDNO,LOTNO)
Please suggest me index partitioning method for each of my indexs including primary key index.
Any answer please??
Regards
Jimit
|
|
|
|
|
|
|
|
|
|
| Re: List partition and Index partitioning (merged) [message #397287 is a reply to message #397178] |
Thu, 09 April 2009 22:03 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If your queries mostly use the Fin Year, then try using LOCALly partitioned indexes, but make FINYEAR the first column of the index. Those queries that use both will work just as fast as before.
Queries that do not use FINYEAR will be marginally slower, but if you have only a dozen or so partitions, you will hardly notice it unless the query is inside a loop that executes thousands of times.
If you get an unacceptable drop in performance of those queries, rebuild the index that the query uses as a GLOBAL index - hash partition it on the leading column if you like (make it more manageable).
Global and globally partitioned indexes will noticeably out-perform locally partitioned indexes when you are searching for a small number of rows without the partition key column.
Ross Leishman
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jun 26 11:40:51 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
