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

Last updated on AUGUST 04, 2018

In this Document
Symptoms
Cause
Solution
References

## Symptoms

• Query with an order by clause where all order by columns are covered by an index
• Predicates used are > 15 digits:

Good Index Scan :
`605172067679091691<=objid AND objid<=605172067679091706`

Poor FTS :
`605172067679091891<=objid AND objid<=605172067679091906`

Also Note that the range between high and low values in the predicates are the same: (ie the difference is 15 for both. The difference being 15 is a co-incidence).
• For most predicate ranges, an index is picked which gives good performance but for others returning the same number of rows, a Full Table Scan (FTS) is chosen which results in slower performance
• Adding column statistics (histograms) makes no difference
• Comparing the sections and showing only the differences in BAD/Good :

`Card: Original: 18498700 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00..Access Path: index (RangeScan)Index: ATSTRVALIXresc_io: 210058.00 resc_cpu: 1881119764ix_sel: 0.05 ix_sel_with_filters: 0.05Cost: 210124.06 Resp: 210124.06 Degree: 1Best:: AccessPath: TableScanCost: 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: ATSTRVALIXresc_io: 9.00 resc_cpu: 72263ix_sel: 1.0477e-06 ix_sel_with_filters: 1.0477e-06Cost: 9.00 Resp: 9.00 Degree: 1Best:: AccessPath: IndexRange Index: ATSTRVALIXCost: 9.00 Degree: 1 Resp: 9.00 Card: 9.69 Bytes: 0`

• When min/max values present for column statistics , all values above are in range
• In the bad example ix_sel 0.05 is a default value and this is what is causing high cost for index scan. There is no obvious reason why a different ix_sel is used here
• Issue can be reproduced on 10.2.0.3, 10.2.0.4 and 11.1.0.7