Home » RDBMS Server » Performance Tuning » Index Partitioning (Oracle 9i,version-92010,OS-Linux RHAS 2.1)
Index Partitioning [message #333649] |
Mon, 14 July 2008 01:11 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
|
Re: Index Partitioning [message #333717 is a reply to message #333655] |
Mon, 14 July 2008 04:18 ![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) |
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.
|
|
|
|
Re: Index Partitioning [message #333737 is a reply to message #333649] |
Mon, 14 July 2008 05:31 ![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) |
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.
|
|
|
|
Re: Index Partitioning [message #333954 is a reply to message #333649] |
Tue, 15 July 2008 00:47 ![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) |
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( NOT NULL,
BLENDNO NUMBER( 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( NOT NULL,
REJBLENDNO NUMBER( 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( NOT NULL,
ITYPE VARCHAR2(3 BYTE) NOT NULL,
RMTYPE VARCHAR2(1 BYTE) NOT NULL,
PRODID NUMBER( 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?
|
|
|
|
Re: Index Partitioning [message #333987 is a reply to message #333649] |
Tue, 15 July 2008 02:21 ![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) |
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
|
|
|
Re: Index Partitioning [message #333993 is a reply to message #333987] |
Tue, 15 July 2008 02:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68678 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
Regards
Michel
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:31:45 CDT 2024
|