we have a list partitioned table. Let partitioned column be c1. We have an index on that column(c1). We are planning to drop the index. Our doubt is that if we drop the index, does it affect the performance? Suppose we are querying one of the partition. Does the oracle optimizer directly use the partition segment or rely on index(on the partitioned column) to choose the correct partition or does it perform a full table scan (going through all the segments) if we delete the index?


First, you can try to monitor index usage. You will take the answer used index or not used. Index monitoring can be switched on/off several times to get more information.

For example, here: https://oracle-base.com/articles/9i/performance-enhancements-9i#IndexMonitoring

Also, you can look into v$sql_plan view to see what recent queries used this index:

select s.sql_id, s.sql_text
  from v$sql_plan p, v$sql s
 where p.sql_id = s.sql_id
 and object_name = 'YOUR_INDEX_NAME_HERE' ;

Also, and most importantly, you can make the index invisible for the optimizer. That way you can clearly see if it affects performance of your queries.

Examples - https://oracle-base.com/articles/11g/invisible-indexes-11gr1

| improve this answer | |

Eduard's advice to make the index invisible would be a quick way to check. Some background information:

It would depend on the number of partitions, and amount of data in each partition, and the percentage of data per partition. I assume you are asking whether your queries that have a WHERE clause that specifies the value of the list partition column would be affected.

  • If you have, say 5 partitions, and fairly even distribution of rows in each partition, the index would probably not be used. If you have 100 partitions, likely index would be used.
  • If you have skewed data (IE: list partition A has 100 million rows, list partition B has 10 million rows, and the others have around 1 million rows, the index would likely help, but what would probably help almost as much is if you ensured you had statistics gathered for that column, such as the method_opt=>'FOR COLUMNS C1 SIZE 20' parameter to the DBMS_STATS.GATHER_TABLE_STATS procedure, assuming you had less than 20 partitions. I.E.: the SIZE 20 is the size of a histogram to use for column C1 so you would want the size to be a little larger than the number of unique values of C1.

You could also do some EXPLAIN PLAN commands with hints to use/ignore the index in question and see what happens.

| improve this answer | |
  • could you give us some arguments for your statements – miracle173 Dec 3 '19 at 11:26
  • Yeah, I see I may need to clarify a couple points; but the original post does not give many details. I'll try to add some more detail on the general approach. – Mark Stewart Dec 3 '19 at 14:56

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.