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.
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.
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
Ahas 100 million rows, list partition
Bhas 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_STATSprocedure, assuming you had less than 20 partitions. I.E.: the
SIZE 20is the size of a histogram to use for column
C1so you would want the size to be a little larger than the number of unique values of
You could also do some
EXPLAIN PLAN commands with hints to use/ignore the index in question and see what happens.