How optimizer stats with Histograms can change execution Plan

Optimizer stats can play a key part in deciding execution plan. Here is an example

Table “RSODSACTREQ” has 313783 of total rows

Database with NO histograms :
For following 4 statements the SQL plan is always same i.e FULL TABLE SCAN

1. SELECT MAX( “TIMESTAMP” ) FROM SAPLIVE.”RSODSACTREQ” WHERE “ODSOBJECT” = ‘ZPNPCAMP’
2. SELECT MAX( “TIMESTAMP” ) FROM SAPLIVE.”RSODSACTREQ” WHERE “ODSOBJECT” = ‘ZCUS_ACT’
3. SELECT MAX( “TIMESTAMP” ) FROM SAPLIVE.”RSODSACTREQ” WHERE “ODSOBJECT” = ‘ZTCIT_OH’
4. SELECT MAX( “TIMESTAMP” ) FROM SAPLIVE.”RSODSACTREQ” WHERE “ODSOBJECT” = ‘ZPA0037A’

Execution Plan :
SELECT STATEMENT Optimizer Mode=CHOOSE 1 350
SORT AGGREGATE 1 16
TABLE ACCESS FULL SAPLIVE.RSODSACTREQ 2 K 39 K 350

Database with histograms:
1. SELECT MAX( “TIMESTAMP” ) FROM SAPLIVE.”RSODSACTREQ” WHERE “ODSOBJECT” = ‘ZPNPCAMP’

Here Oracle can predict the resulting number of rows are lot more and prefers a full table scan

SELECT STATEMENT Optimizer Mode=CHOOSE 1 350
SORT AGGREGATE 1 16
TABLE ACCESS FULL SAPLIVE.RSODSACTREQ 262 K 3 M 350

2. SELECT MAX( “TIMESTAMP” ) FROM SAPLIVE.”RSODSACTREQ” WHERE “ODSOBJECT” = ‘ZCUS_ACT’
For this value it can predict values returned are less and will use a index scan.

SELECT STATEMENT Optimizer Mode=CHOOSE 1 19
SORT AGGREGATE 1 16
TABLE ACCESS BY INDEX ROWID SAPLIVE.RSODSACTREQ 93 1 K 19
INDEX RANGE SCAN SAPLIVE.RSODSACTREQ~STP 93 3

 

3. SELECT MAX( “TIMESTAMP” ) FROM SAPLIVE.”RSODSACTREQ” WHERE “ODSOBJECT” = ‘ZTCIT_OH’
For this value it can predict values returned are less and will use a index scan.

SELECT STATEMENT Optimizer Mode=CHOOSE 1 9
SORT AGGREGATE 1 16
TABLE ACCESS BY INDEX ROWID SAPLIVE.RSODSACTREQ 35 560 9
INDEX RANGE SCAN SAPLIVE.RSODSACTREQ~STP 35 3

4. SELECT MAX( “TIMESTAMP” ) FROM SAPLIVE.”RSODSACTREQ” WHERE “ODSOBJECT” = ‘ZPA0037A’
For this value it can predict values returned are less and will use a index scan.

SELECT STATEMENT Optimizer Mode=CHOOSE 1 4
SORT AGGREGATE 1 16
TABLE ACCESS BY INDEX ROWID SAPLIVE.RSODSACTREQ 1 16 4
INDEX RANGE SCAN SAPLIVE.RSODSACTREQ~0 1 3