| Local Index vs Global Index on partitioned table [message #513526] |
Tue, 28 June 2011 01:13  |
mandeepmandy
Messages: 79
Registered: May 2008
Location: USA
|
Member |
|
|
Hello,
I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.
And i really don't find any other bottleneck in query .Explain plan is not showing pstart and pend because the index is global. Anyone has idea if this global index is a slowing down the performance?
Appreciate yours replies.
Thanks
Mandeep
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Local Index vs Global Index on partitioned table [message #513710 is a reply to message #513605] |
Wed, 29 June 2011 02:12  |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
>Is there any performance degradation when using global index instead of local index on big partitioned table?
It really depends on the data and the query doesn't it.
There are situations where Global Indexes would be used (e.g. the query cannot / will not prune to a particular partition).
Using a Global Index to fetch a small set of rows from a Partitioned Table is the same as using a Global Index to fetch a small set of rows from a Non-Partitioned table.
So, your question isn't really relevant. You use a Global Index because you can't use a Locally Partitioned Index.
However, you could consider if the Index, itself, be Partitioned. Thus, it wouldn't be a Locally Partitioned index but a Globally Partitioned index --- partitioned by a key other than the Table's Partitioning key.
Hemant K Chitale
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
