Optimizer Chooses Expensive Index Full Scan over Index Fast Full Scan or Full Table Scan from 12.1
Last updated on APRIL 14, 2018
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 and later
Information in this document applies to any platform.
- Optimizer chooses expensive index full scan over index fast full scan or full table scan which are very faster.
- Queries take hours to complete using index full scan while it runs in seconds using index fast full scan.
- Problem happens after upgrading the database from lower version to 126.96.36.199 or higher.
- Normal 'SELECT COUNT(column) FROM table' queries are using index full scan over index fast full scan or full table scan.
- Problem happens even after the fix for <Bug 17863980> is included in 188.8.131.52.
- Good plan runs with index fast full scan or full table scan using parallelism.
- Bad plan runs with index full scan in serial mode without using parallelism.
- Cost of the plan using index fast full scan and parallelism is lesser than index full scan in serial. However, optimizer chooses high cost inefficient plan.
- Setting the parameter "_optimizer_cost_model"=IO gives the desired IFFS/FTS plan.
- Setting OPTIMIZER_FEATURES_ENABLE to 11.2 or 10.2 do not help.
- Deleting the system statistics gives desired plan.
- Setting higher SREADTIM system statistics gives desired plan but that may impact other SQL statements.
- Setting DB_FILE_MULTIBLOCK_READ_COUNT to non-default value causing the bad plans.
Upgrading the Database to 12.1 or higher.
Changes to hardware like disk storage etc.
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