We have a partitioned table with more than 200 columns and 60 indexes. It has 10 foreign keys with related indexes and the remaining indexes are global style. It partitioned in a yearly basis and sub-partitioned in company.
Now, we're have performance downgrade on inserting two hundred thousand of records at the last day of each month. The time become slower and slower. The latest durations are 15, 18 and 21 minutes. The table has 300 million of records and distributed on 9 partitions.
Can we have any gains on performance after following actions
1, partitioned in monthly basis
2, change those non-foreign key indexes to global partitioned indexes.
Also, another other suggestions ? Please advise.
This is not a partitioning issue...this is an indexing issue.
Look at this example, where I progressively keep adding indexes to the table and load records
SQL> create table t nologging tablespace largets
2 as select d.* from dba_objects d;
Table created.
SQL>
SQL> create table t_new as select * from t;
Table created.
SQL>
SQL> set serverout on
SQL> declare
2 s timestamp;
3 begin
4 for i in 0 .. 20 loop
5 if i > 0 then
6 execute immediate 'create index tix'||i||' on t ( object_name,'||i||') tablespace larget
7 end if;
8 s := systimestamp;
9 insert into t select * from t_new;
10 commit;
11 dbms_output.put_line('Indexes='||i||',elapsed='||(systimestamp-s));
12 end loop;
13 end;
14 /
Indexes=0,elapsed=+000000000 00:00:00.130000000
Indexes=1,elapsed=+000000000 00:00:02.786000000
Indexes=2,elapsed=+000000000 00:00:01.318000000
Indexes=3,elapsed=+000000000 00:00:04.571000000
Indexes=4,elapsed=+000000000 00:00:03.676000000
Indexes=5,elapsed=+000000000 00:00:06.290000000
Indexes=6,elapsed=+000000000 00:00:06.402000000
Indexes=7,elapsed=+000000000 00:00:11.753000000
Indexes=8,elapsed=+000000000 00:00:07.487000000
Indexes=9,elapsed=+000000000 00:00:12.361000000
Indexes=10,elapsed=+000000000 00:00:14.313000000
Indexes=11,elapsed=+000000000 00:00:14.317000000
Indexes=12,elapsed=+000000000 00:00:19.009000000
Indexes=13,elapsed=+000000000 00:00:24.281000000
Indexes=14,elapsed=+000000000 00:00:15.017000000
Indexes=15,elapsed=+000000000 00:00:17.748000000
Indexes=16,elapsed=+000000000 00:00:24.017000000
Indexes=17,elapsed=+000000000 00:00:28.805000000
Indexes=18,elapsed=+000000000 00:00:28.195000000
Indexes=19,elapsed=+000000000 00:00:41.183000000
Indexes=20,elapsed=+000000000 00:00:39.166000000
PL/SQL procedure successfully completed.
By the time I get to 20 indexes, I'm running nearly 300 times slower than no indexes.
I would be having a good long review to see if those indexes are all required.
If they *really* are (which I'm dubious of :-)) then you could still look at something like:
- which of those indexes are needed *during* that load (I suspect very few)
- set those to unusable
- do your load
- then rebuild those indexes
That approach has some sustainability issues, because rebuild time gets longer as you add more rows.
But start with those indexes...60 is ... a lot !