Hi
Oracle linux 7.3
11.2.0.4.0
In one of my databases, there is a range partitioned table for logs, so that each month has its own partition.
Recently gathered statistics, and table has 126 million rows, each partition has about 3-4 million rows.
Table has 6 indexes (one is composite index, with 2 columns).
So, when I drop these partitions I am using this statement. FYI, I have tried using parallel clause also, but I would say, that this does not make any difference. Each partition drop takes still up to 4 hours (3 to 4 hours most of the time).
ALTER TABLE <OWNER>.<TABLE_NAME> DROP PARTITION <PARTITION_NAME> UPDATE INDEXES [PARALLEL N];
Btw, how do I know, do I have global indexes or local indexes on that partitioned table..?
Indexes are created like this, so basically does not specify local or global keyword, just wondering which one of these is default..?
CREATE INDEX <OWNER>.<index_name> ON <owner>.<table_name> (<column_name>);
Or could the problem be that my indexes are too fragmented, is it even possible this slows this operation down..?
Regards
Raul