Why Is the Optimizer Estimating the Wrong Number of Rows?

Slide 1

Slide 1 text

Your SQL Office Hours begins soon… Why Is the Optimizer Estimating the Wrong Number of Rows? Chris Saxon @ChrisRSaxon & @SQLDaily https://www.youtube.com/c/TheMagicofSQL https://blogs.oracle.com/sql

Slide 2

Slide 2 text

Slide 3

Slide 3 text

Slide 4

Slide 4 text

_ ∗ ∗ …

Slide 5

Slide 5 text

Stats problems Value skew Range skew Correlation 1 2 3

Slide 6

Slide 6 text

Slide 7

Slide 7 text

Slide 8

Slide 8 text

blue green red Colours 4 8 14 Number of rows

Slide 9

Slide 9 text

blue green red Colours select * from bricks where colour = 'blue' 4 8 14 Number of rows

Slide 10

Slide 10 text

blue green red Colours select * from bricks where colour = 4 8 14 'pink' 2 Number of rows

Slide 11

Slide 11 text

blue green red Colours insert into bricks values ( 'yellow', … ); Number of rows Ignore

Slide 12

Slide 12 text

blue green red Colours Number of rows insert into bricks values ( 'yellow', … ); insert into bricks values ( 'yellow', … ); insert into bricks values ( 'yellow', … ); insert into bricks values ( 'yellow', … ); insert into bricks values ( 'yellow', … );

Slide 13

Slide 13 text

blue-green green-red red-yellow Colours 10 10 10 Number of rows

Slide 14

Slide 14 text

blue-green green-red red-yellow Colours 10 10 10 select * from bricks where colour = 'red' Number of rows >= 1 and < 20

Slide 15

Slide 15 text

blue-green green-red red-yellow Colours Number of rows

Slide 16

Slide 16 text

blue-green red yellow Number of rows Colours 4 14 12 4 yellow 8 green 14 red

Slide 17

Slide 17 text

Where clause Index columns BRICK_ID HYBRID COLOUR FREQUENCY SHAPE FREQUENCY WEIGHT NONE NOTES NONE

Slide 18

Slide 18 text

Where clause Index columns BRICK_ID HYBRID COLOUR FREQUENCY SHAPE FREQUENCY WEIGHT NONE NOTES NONE Primary key?!

Slide 19

Slide 19 text

Slide 20

Slide 20 text

select * from bricks where brick_id between 5 and 10 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 We got nothing!

Slide 21

Slide 21 text

1-11 12-16 17-20 Brick_id 4 5 5 Many values Number of rows

Slide 22

Slide 22 text

select * from bricks where colour = 'red' and shape = 'cylinder';

Slide 23

Slide 23 text

Slide 24

Slide 24 text

COLOUR SHAPE red cube red cube blue cuboid red cube green star blue cuboid

Slide 25

Slide 25 text

COLOUR SHAPE ( COLOUR, SHAPE ) red cube red cube blue cuboid red cube green star blue cuboid

Slide 26

Slide 26 text

Slide 27

Slide 27 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | • Basic stats have limitations • Histograms & extended stats address these • Histograms created based on usage • Correlation/expressions you need to specify • Control with set_table_prefs Summary

Slide 28

Slide 28 text

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. | Understanding Optimizer Statistics With Oracle Database 19c https://www.oracle.com/technetwork/database /bi-datawarehousing/twp-stats-concepts-19c- 5324209.pdf Databases for Developers: Performance https://devgym.oracle.com/pls/apex/dg/class/ databases-for-developers-performance.html Further Reading