| Index on Partition Table (merged) [message #361065] |
Tue, 25 November 2008 00:04  |
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
|
Senior Member |
|
|
hi friends
i m planing to do partitions on normal table, because this table has over 18 lacks(380 mb) rows and its daily input rows are 10000. i am planing to do list partition on fyear column. below is the structure of my primary key.
***Primary key
ccode number
fyear varchar2
wno number
wsrno number
other normal indexes are
***index_1
ccode number
btypecd number
bno number
***index_2
ccode number
wdate date
ltno varchar2
***index_3
ccode number
ltno varchar2
my fyear wise data in rows are 4 lacks per year.
*fyear means financial year.
if i am doing partitions on this table which type of indexes (global or local) i have to use and which type of primary key index creates when i m doing partitions. if possible send me the link on which i get details of it.
thanks in advance
|
|
|
|
| TABLE PARTITIONS AND INDEX PARTITIONS [message #361125 is a reply to message #361065] |
Tue, 25 November 2008 01:56  |
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
|
Senior Member |
|
|
hi friends
i m planing to do partitions on normal table, because this table has over 18 lacks(380 mb) rows and its daily input rows are 10000. i am planing to do list partition on fyear column. below is the structure of my primary key.
***Primary key
ccode number
fyear varchar2
wno number
wsrno number
other normal indexes are
***index_1
ccode number
btypecd number
bno number
***index_2
ccode number
wdate date
ltno varchar2
***index_3
ccode number
ltno varchar2
my fyear wise data in rows are 4 lacks per year.
*fyear means financial year.
if i am doing partitions on this table which type of indexes (global or local) i have to use and which type of primary key index creates when i m doing partitions. if possible send me the link on which i get details of it.
thanks in advance
|
|
|
|
|
|
| Index Partitions [message #361358 is a reply to message #361065] |
Wed, 26 November 2008 01:20 |
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
|
Senior Member |
|
|
Hi Friends
I m confused about which type of indexes use on my partitioned tables (Global or local). have u any document or link on which its described with examples.
Thanks
|
|
|
|
|
|
|
|
| Re: Index Partitions [message #361535 is a reply to message #361369] |
Wed, 26 November 2008 20:09 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Partitioned indexes suit queries that always include the partition key with an = or IN clause, and sometimes with </> clauses.
For queries that do not include the partition key (as described above), partitioned indexes can still be useful where the number of rows returned by the index scan far exceeds the number number of partitions. For example, a query that scans 1000 rows from 10 partitions would work ok. It would be even better with a non-partitioned index, but it would be unlikely to make a critical difference.
For queries that do not include the partition key (as described above), use a non-partitioned index (or partition on a key that IS in the WHERE clause) when you are returning a small number of rows.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
