Possible Sub Optimal "Full Table Scan" plan when using a Between Predicate (Doc ID 1050258.1)

Last updated on SEPTEMBER 15, 2016

In this Document
  Symptoms
  Cause
  Solution
  References


Symptoms


BAD: 

Card: Original: 18498700 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00
..
Access Path: index (RangeScan)
Index: ATSTRVALIX
resc_io: 210058.00 resc_cpu: 1881119764
ix_sel: 0.05 ix_sel_with_filters: 0.05
Cost: 210124.06 Resp: 210124.06 Degree: 1
Best:: AccessPath: TableScan
Cost: 28101.67 Degree: 1 Resp: 28101.67 Card: 0.00 Bytes: 0

Note index Cost is very high, 210,058 compared to FTS cost of 28,101, so from the optimizer point of view the FTS selection is correct. ix_sel: 0.05 is a default value.

GOOD

Card: Original: 18498700 Rounded: 10 Computed: 9.69 Non Adjusted: 9.69
..
Access Path: index (RangeScan)
Index: ATSTRVALIX
resc_io: 9.00 resc_cpu: 72263
ix_sel: 1.0477e-06 ix_sel_with_filters: 1.0477e-06
Cost: 9.00 Resp: 9.00 Degree: 1
Best:: AccessPath: IndexRange Index: ATSTRVALIX
Cost: 9.00 Degree: 1 Resp: 9.00 Card: 9.69 Bytes: 0



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms