We have a select query which is really slow. We are using views in the from clause. Global nonpartitioned index is used for accessing the rows of a partitioned table. My doubt is that if Global index is used, then all the index entries are checked(not just entries belonging to a particular partition). Doesn't that affect the performance?
SELECT * FROM table partition(aa);
If we are using a query similar to above one where we are mentioning the partition in from clause, then does the global index check only entries corresponding to partition aa or does it go through all the entries in the index? Does oracle optimizer know that we are querying just partition only and choose only corresponding local index(we haven't created local partitioned index but if it helps,then we can do that)?
Or should we mention the partition column in the where clause for optimizer to choose local partitioned index?