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?

  • The question is too broad as it stands. Let's see the table schema and query. – Rick James Jul 14 '19 at 23:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.