| |
| Title: |
Dynamic Mechanisms of Query Building |
| Presenter: |
Michael Rosenblum |
| Organization: |
Dulcian Inc |
| |
| Summary: |
Most advanced applications, especially ones that were in production for some time, eventually cause some SQL queries simultaneously become too scary to modify and too under-performing to live with them. Obviously, throwing more hardware sometime can do the job – same as various DBA-level tricks. But the first approach leads to extreme costs (causing Oracle to have pretty bad reputation) and the second approach creates yet another layer of places where business rules can be hidden (causing any significant upgrades/modifications to take way more time).
Working with Oracle database for 20+ years I found another approach, which I proselyte for quite a while: if queries become too complex for the Cost-Based-Optimizer to handle, they should be constructed dynamically utilizing all of the information available at run-time. Originally, the functionality was based on Dynamic SQL + object types, nowadays you also get Oracle Macros, but the idea is the same: don’t try to cover all possibilities during design time, i.e. run only what is needed when it is needed.
The best example of this situation is what’s called “advanced search”, i.e. a screen that allows users to enter multiple criteria and parameters. There are multiple ways of solving this problem with various levels of efficiency. One thing for sure – at some point (very soon!) pure SQL would not be adequate and you will be forced to adopt some kind of alternative.
This presentation will demonstrate available options plus corresponding cost/benefit analysis using various real performance challenges from speaker’s experience. |
| |
| Topic: |
Developer
Database/DBaaS Administration
|
| Session Type: |
One Hour Session |
| Target Audience: |
|
| Experience Level: |
|
| Session: |
9: 02/13/2025 10:40 am to 11:40 am |
| Location: |
Room 100B |
| |
| Biographical Sketch: |
Michael Rosenblum is a Software Architect/Development DBA at Dulcian, Inc. where he is responsible for system tuning and application architecture. Michael supports Dulcian developers by writing complex PL/SQL routines and researching new features. He is the co-author of "PL/SQL for Dummies" (Wiley Press, 2006), "PL/SQL Performance Tuning Tips & Techniques" (Oracle Press, 2014), contributing author of "Expert PL/SQL Practices" (APress, 2011), and author of a number of database-related articles (IOUG Select Journal, ODTUG Tech Journal) and conference papers. Michael is an Oracle ACE, a frequent presenter at various Oracle user group conferences (Oracle OpenWorld, ODTUG, IOUG Collaborate, RMOUG, NYOUG, etc.), and winner of the ODTUG Kaleidoscope 2009 Best Speaker Award.
|
| |
| |
