0

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?

Milain
  • 1
  • Provide structure of tables and indexes. It is not clear why you did drop the partitions on the test side ? If the index is not being used, the CBO is deciding it is more expensive than a normal full scan. Also the execution plan of the statements. I can see you are not using any partition pruning, as there is no where clause – Roberto Hernandez Sep 30 '21 at 15:08
  • 1
    Post an execution plan as described [here](https://stackoverflow.com/a/34975420/4808122). Also note that for a join without a `Where` predicate you *typically* expect a `hash join` with two `full table scans` except you optimize to see quickly the `first_rows` in your SQL*Developer (which you don't stated). – Marmite Bomber Sep 30 '21 at 15:55

1 Answers1

1

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.