Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Tuning SQL Date Range Queries
Search
Chris
November 01, 2019
Technology
0
240
Tuning SQL Date Range Queries
Chris
November 01, 2019
Tweet
Share
More Decks by Chris
See All by Chris
Create reusable SQL expressions with SQL macros
chrissaxon
0
72
All About Insert
chrissaxon
0
160
Generating days between two dates
chrissaxon
0
180
Converting rows to columns and back again
chrissaxon
0
180
Finding the Longest Common Substring & Gestalt Pattern Matching with SQL & PL/SQL
chrissaxon
0
540
DBA Masterclass Application Tuning
chrissaxon
0
3.1k
A Preview of Oracle Database 20c PLSQL Enhancements
chrissaxon
0
220
Why Is the Optimizer Estimating the Wrong Number of Rows?
chrissaxon
0
89
Which Indexes Should I Create?
chrissaxon
0
140
Other Decks in Technology
See All in Technology
JAWS DAYS 2025 アーキテクチャ道場 事前説明会 / JAWS DAYS 2025 briefing document
naospon
0
2.8k
Oracle Database Technology Night #87-1 : Exadata Database Service on Exascale Infrastructure(ExaDB-XS)サービス詳細
oracle4engineer
PRO
1
220
【Snowflake九州ユーザー会#2】BigQueryとSnowflakeを比較してそれぞれの良し悪しを掴む / BigQuery vs Snowflake: Pros & Cons
civitaspo
2
280
アジャイルな開発チームでテスト戦略の話は誰がする? / Who Talks About Test Strategy?
ak1210
1
820
1行のコードから社会課題の解決へ: EMの探究、事業・技術・組織を紡ぐ実践知 / EM Conf 2025
9ma3r
12
4.8k
大規模アジャイルフレームワークから学ぶエンジニアマネジメントの本質
staka121
PRO
3
1.6k
IAMのマニアックな話2025
nrinetcom
PRO
6
1.4k
EDRの検知の仕組みと検知回避について
chayakonanaika
12
5.3k
ExaDB-XSで利用されているExadata Exascaleについて
oracle4engineer
PRO
3
300
入門 PEAK Threat Hunting @SECCON
odorusatoshi
0
180
JavaにおけるNull非許容性
skrb
2
2.7k
RayでPHPのデバッグをちょっと快適にする
muno92
PRO
0
200
Featured
See All Featured
A Tale of Four Properties
chriscoyier
158
23k
Fireside Chat
paigeccino
35
3.2k
Save Time (by Creating Custom Rails Generators)
garrettdimon
PRO
29
1.1k
Practical Orchestrator
shlominoach
186
10k
Fontdeck: Realign not Redesign
paulrobertlloyd
83
5.4k
Building an army of robots
kneath
303
45k
Chrome DevTools: State of the Union 2024 - Debugging React & Beyond
addyosmani
4
380
The Art of Programming - Codeland 2020
erikaheidi
53
13k
[RailsConf 2023] Rails as a piece of cake
palkan
53
5.3k
Keith and Marios Guide to Fast Websites
keithpitt
411
22k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
33
2.8k
The Power of CSS Pseudo Elements
geoffreycrofte
75
5.5k
Transcript
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
Copyright © 2018, Oracle and/or its affiliates. All rights reserved.
| Welcome to Ask TOM Office Hours!
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?
How many rows will select … where col = :bind_variable
return? # rows / # distinct or use histogram
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? ¯\_(ツ)_/¯
Copyright © 2018, Oracle and/or its affiliates. All rights reserved.
| Look at the bind values! (peek)
Copyright © 2018, Oracle and/or its affiliates. All rights reserved.
| DEMO
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
Copyright © 2018, Oracle and/or its affiliates. All rights reserved.
| asktom.oracle.com #MakeDataGreatAgain Ryan McGuire / Gratisography