Tuning SQL Date Range Queries

Transcript

1. Your SQL Office Hours session will begin soon… Tuning SQL Date Range Queries Chris Saxon @ChrisRSaxon & @SQLDaily https://www.youtube.com/c/TheMagicofSQL https://blogs.oracle.com/sql
2. Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Welcome to Ask TOM Office Hours!
3. Why is select … where col = 'VALUE' and date_value between to_date ( :st_dt, 'YYYY-MM-DD' ) and to_date ( :en_dt, 'YYYY-MM-DD' ) fast for short date ranges and slooooow for large ones?
4. How many rows will select … where col = :bind_variable return? # rows / # distinct or use histogram
5. How many rows will select … where date_value between to_date ( :st_dt, 'YYYY-MM-DD' ) and to_date ( :en_dt, 'YYYY-MM-DD' ) return? ¯\_(ツ)_/¯
6. Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Look at the bind values! (peek)
7. Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | DEMO
8. Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | • One query may have many "good plans" • The database spots this with adaptive cursor sharing • Dynamic statistics can help improve estimates further • Adaptive plans (12c) can change join type (NL <> HJ) Summary
9. Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | asktom.oracle.com #MakeDataGreatAgain Ryan McGuire / Gratisography