Skip to Main Content
Mastering Oracle SQL
book

Mastering Oracle SQL

by Sanjay Mishra, Alan Beaulieu
April 2002
Intermediate to advanced content levelIntermediate to advanced
336 pages
9h 58m
English
O'Reilly Media, Inc.
Content preview from Mastering Oracle SQL

Consider Literal SQL for Decision Support Systems

We discussed the benefits of using bind variables previously. The use of bind variables is often beneficial in terms of performance. However, there is a downside to consider. Bind variables hide actual values from the optimizer. This hiding of actual values can have negative performance implications, especially in decision support systems. For example, consider the following statement:

SELECT * FROM CUSTOMER WHERE REGION_ID = :X

The optimizer can parse this statement, but it won’t be able to take into account the specific region being selected. If 90% of your customers were in region 5, then a full table scan would likely be the most efficient approach when selecting those customers. An index scan would probably be more efficient when selecting customers in other regions. When you hardcode values into your SQL statements, the cost-based optimizer (CBO) can look at histograms (a type of statistic) and generate an execution plan that takes into account the specific values you are supplying. When you use bind variables, however, the optimizer generates an execution plan without having a complete picture of the SQL statement. Such an execution plan may or may not be the most efficient.

In Decision Support Systems (DSS), it is very rare that multiple users use the same query over and over. More typically, a handful of users execute complex, different queries against a large database. Since it is very rare that the SQL statements will be ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle SQL

Oracle SQL

Dan Hotka
Oracle SQL Developer

Oracle SQL Developer

Ajith Narayanan, Susan Harper

Publisher Resources

ISBN: 0596001290Supplemental ContentCatalog PageErrata