Home » RDBMS Server » Performance Tuning » Index Partitioning (Oracle 9i,version-92010,OS-Linux RHAS 2.1)
icon6.gif  Index Partitioning [message #333649] Mon, 14 July 2008 01:11 Go to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Hi Friends

I want to know about index partitioning. I have a table with 30,00,000 rows and sized 100mb.my data was speared evenly on basis of financial years.i'm going to partition my table with list partitioning option. but my main worry was 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.

Thanks in advance.

Report message to a moderator

Re: Index Partitioning [message #333655 is a reply to message #333649] Mon, 14 July 2008 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to partition your table?

Regards
Michel

Report message to a moderator

Re: Index Partitioning [message #333717 is a reply to message #333655] Mon, 14 July 2008 04:18 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel
Actually i have just given example of data with only one years of data[size and rows], my table holds the data for last five years,so my actual table size 562mb. i want partition on this table, mainly for data security and secondly performance gain through my applications.

Thanks for quick reply.

Report message to a moderator

Re: Index Partitioning [message #333730 is a reply to message #333717] Mon, 14 July 2008 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Partition is not a feature for security. You can't put a security policy on partitions. (Maybe you are talking about reliability puting part of your table in read-only mode as a side effect of both partition and read only tablespace?)

2/ What make you think you will have better performances with partitioning?

Regards
Michel

Report message to a moderator

Re: Index Partitioning [message #333737 is a reply to message #333649] Mon, 14 July 2008 05:31 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Hi michel

I think i get performance gain by applying optimizer hints in my queries, right now my queries goes for FTS rather than particular year wise partition data search.

Report message to a moderator

Re: Index Partitioning [message #333740 is a reply to message #333737] Mon, 14 July 2008 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So your queries are on year? Maybe you should also partition your index on year.

Regards
Michel

Report message to a moderator

Re: Index Partitioning [message #333954 is a reply to message #333649] Tue, 15 July 2008 00:47 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel
Thanks again for quick reply.
I have last question,please help me.

My table structure is like this,
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)

My main concern is , after partitioning my table what should i have to do with my indexes.
I do this on my dev server and i found that my index size is greater than my one of partition size.
How i can partition the two indexes??
Should i go for local or global index?why?

Report message to a moderator

Re: Index Partitioning [message #333962 is a reply to message #333954] Tue, 15 July 2008 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The answer depends on your queries.
If they include finyear then you should add this column to your indexes and make them local.
By the way, why this column is not an integer if it is a year?

Regards
Michel

Report message to a moderator

Re: Index Partitioning [message #333987 is a reply to message #333649] Tue, 15 July 2008 02:21 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Yes,My 90% of queries includes COMPCODE AND FINYEAR in where clause of select statement,my database was design by third party and right now i just maintain it [I know it's a mistake, Finyear is a character field].
How can i add partition on my indexes?

Regards

Jimit

Report message to a moderator

Re: Index Partitioning [message #333993 is a reply to message #333987] Tue, 15 July 2008 02:39 Go to previous message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How can i add partition on my indexes?

It depends on the answer to the following, can you do this:
Quote:
you should add this column to your indexes

If yes then
Quote:
make them local

Regards
Michel

Report message to a moderator

Previous Topic: pls help to reduce some loop
Next Topic: what are the various steps of query processing
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Jul 01 07:19:49 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.