Home » RDBMS Server » Performance Tuning » Index on Partition Table (merged)
Index on Partition Table (merged) [message #361065] Tue, 25 November 2008 00:04 Go to next message
chintan.patel
Messages: 140
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 Go to previous messageGo to next message
chintan.patel
Messages: 140
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
Re: TABLE PARTITIONS AND INDEX PARTITIONS [message #361137 is a reply to message #361125] Tue, 25 November 2008 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost your question.
And don't post your title (or post) in UPPER case.

Regards
Michel

[Updated on: Tue, 25 November 2008 02:52]

Report message to a moderator

Index Partitions [message #361358 is a reply to message #361065] Wed, 26 November 2008 01:20 Go to previous messageGo to next message
chintan.patel
Messages: 140
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 #361360 is a reply to message #361358] Wed, 26 November 2008 01:23 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

http://tahiti.oracle.com/
Re: Index Partitions [message #361369 is a reply to message #361358] Wed, 26 November 2008 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You already posted this twice yesterday, stop that.

Regards
Michel
Re: Index Partitions [message #361535 is a reply to message #361369] Wed, 26 November 2008 20:09 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: dbms_stats.gather_table_stats
Next Topic: Contention
Goto Forum:
  


Current Time: Sun Dec 04 00:26:13 CST 2016

Total time taken to generate the page: 0.11265 seconds