Skip to Main Content
Oracle SQL*Plus: The Definitive Guide
book

Oracle SQL*Plus: The Definitive Guide

by Jonathan Gennick
March 1999
Intermediate to advanced content levelIntermediate to advanced
528 pages
14h 39m
English
O'Reilly Media, Inc.
Content preview from Oracle SQL*Plus: The Definitive Guide

Improving on EXPLAIN PLAN Results

If you don’t like the results you get from EXPLAIN PLAN, there are some things you can do to change how Oracle executes your query. Generally speaking, these things fall into the following three categories:

  • Restating the query

  • Creating or modifying indexes

  • Using hints

First, though, you have to be sure the default execution path is a bad one. This isn’t as easy as you may think.

Knowing Good Results from Bad

Knowing a good execution plan from a bad one requires some degree of experience and judgment. It also helps to understand your data. In many cases, it may not be enough to look at the plan. You may have to do some benchmarking as well.

Consider the issue of doing a full table scan — reading all the rows in the table — to find rows for a query. On the surface, reading the entire table to find the desired rows seems like a very inefficient approach. Many people avoid it out of hand, thinking that an indexed retrieval is always better. If you have a reasonably large table and are searching for only one or two rows, then a full table scan is not an efficient approach. However, if you are retrieving or summarizing a large percentage of the rows in the table, then a full table scan will likely outperform an indexed retrieval. The problem is that somewhere in between these two extremes lies a large grey area. That’s where you have to do some benchmarking and use some judgment based on your expectations of what the query will be asked to do when ...

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*Plus: The Definitive Guide, 2nd Edition

Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Jonathan Gennick
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl
Oracle SQL

Oracle SQL

Dan Hotka

Publisher Resources

ISBN: 1565925785Supplemental ContentCatalog PageErrata