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 Go to next message
jimit_shaili
Messages: 231
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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 Go to previous messageGo to next message
jimit_shaili
Messages: 231
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.
Re: List partition and Index partitioning [message #396295 is a reply to message #396284] Mon, 06 April 2009 01:28 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
some more information about my table ,that almost my 85% of query uses compcode and finyear in where clause.
Re: List partition and Index partitioning [message #396312 is a reply to message #396284] Mon, 06 April 2009 01:57 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear BlackSwan

please suggest me what should i have to do with my index.Make it local or global?

Re: List partition and Index partitioning [message #396506 is a reply to message #396284] Tue, 07 April 2009 01:03 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear friends

Should i go for local index or local partitioned index???
Please reply.
Re: List partition and Index partitioning [message #396515 is a reply to message #396506] Tue, 07 April 2009 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
jimit_shaili wrote on Tue, 07 April 2009 08:03
Dear friends

Should i go for local index or local partitioned index???
Please reply.

It only depends on your environment, workload and queries.

Regards
Michel

Re: List partition and Index partitioning [message #396751 is a reply to message #396284] Tue, 07 April 2009 23:47 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
thanks for reply.
Actually i'm not clear about what to do with index partition or which type of index partitioning.
so suggest me some good article/notes.link on index partitioning in oracle 9i.

Regards

Jimit

[Updated on: Tue, 07 April 2009 23:48]

Report message to a moderator

Re: List partition and Index partitioning [message #396754 is a reply to message #396284] Tue, 07 April 2009 23:50 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>so suggest me some good article/notes.link on index partitioning in oracle 9i.
V9 Oracle is obsoleted & unsupported now.

Is GOOGLE broken for you?

http://tahiti.oracle.com contains your answers.
Index partitioning [message #397132 is a reply to message #396284] Thu, 09 April 2009 03:06 Go to previous messageGo to next message
jimit_shaili
Messages: 231
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: Index partitioning [message #397134 is a reply to message #397132] Thu, 09 April 2009 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think it is useful to start a new topic when you already have one going on on the same subject.

Regards
Michel
Re: List partition and Index partitioning (merged) [message #397154 is a reply to message #396284] Thu, 09 April 2009 04:02 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
i'm going to create a partition with list partition option on 'finyear' column.
column 'finyear' is a part of my primary key index.by this mean should i make my primary key as local index or local partitioned index.suggest me which is better?

Thanks

Jimit

[Updated on: Thu, 09 April 2009 04:03]

Report message to a moderator

Re: List partition and Index partitioning (merged) [message #397178 is a reply to message #396284] Thu, 09 April 2009 05:37 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Please reply Michel
Re: List partition and Index partitioning (merged) [message #397287 is a reply to message #397178] Thu, 09 April 2009 22:03 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: List partition and Index partitioning (merged) [message #397310 is a reply to message #396284] Fri, 10 April 2009 00:34 Go to previous message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Thanks Ross Leishman

It's very nicely described...

Ending the thread.

Regards

Jimit
Previous Topic: Can any one help me to tune this query.
Next Topic: Bind variable versus CURSOR_SHARING='SIMILAR'
Goto Forum:
  


Current Time: Sun Dec 11 04:36:23 CST 2016

Total time taken to generate the page: 0.07640 seconds