CODEWITHSUNDEEP
sqloracle-databaseindexing
Milain
Milain

Reputation: 1

Oracle don't use index after drop partitions

We have simple join by pk in Oracle 12c

select *
from fcfcore.fsc_cash_flow_fact cff
inner join fcfcore.fsc_account_dim ad
   on cff.account_key = ad.account_key;

And two contours (dev, test). Structure of tables, indexes and other is identical, but on one (dev), in the explain plan we see index range scan / unique scan, but in onother (test) is only full scan, and this is very sad and slow. Previously, the plan on all contours was the same, and the index was used. The only thing that changed - was drop of old partitions on test, but in the end we only got the index XPKFSC_ACCOUNT_DIM is stopped working, so it looks like a coincidence, but possibly influenced. The index has also stopped being used in other queries.

dev

fsc_cash_flow_fact ~ 12kk partitioned table by date
XIF1FSC_CASH_FLOW_FACT - local index on account_key

fsc_account_dim ~ 23kk
XPKFSC_ACCOUNT_DIM - pk on account_key

test

fsc_cash_flow_fact ~ 1kkk partitioned table by date
XIF1FSC_CASH_FLOW_FACT - local index on account_key

fsc_account_dim ~ 23kk
XPKFSC_ACCOUNT_DIM - pk on account_key

Rebuild index, gather statistic - didn't help. Hints is last hope, but I think if index is don't work in many queries it is not good solution.

Any ideas what can be wrong?

Upvotes: 0

Views: 244

Answers (1)

user123664
user123664

Reputation:

The index is a primary key index so it is a global index. Because of the drop partitions, the index became unusable. Normally a rebuild of the index plus gather statistics will solve this.

Upvotes: 1

Related Questions