BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Table Access via Indexes

Oracle Tips by Burleson Consulting

Within Oracle, all indexes are accessed in a transparent fashion and there is very little that we can do to alter the way that Oracle utilizes an index. However, there are numerous internal techniques that we can use to change index access. I will cover these in detail in Chapter 20.

In the meantime, letís turn our attention to methods that can be used to change the Oracle table structures to reduce the amount of disk I/O that occurs when Oracle accesses tables. We will examine how resequencing rows can improve the performance of index range scans and see how the table storage parameters can affect the performance of Oracle insert, update, and delete statements.

It is important to recognize that there is a hierarchical relationship between the SQL statement, the execution plan, and the table access method (see Figure 6-3).

Figure 3: The levels of SQL execution

The SQL source code is used to generate the execution plan. The execution plan, in turn, dictates the table access methods. This is a clear hierarchy because a single SQL statement may be represented by many execution plans, depending on the optimizer_mode, hints, and the nature of the CBO statistics. Each execution plan may generate several table access methods, including full-table scans, index scans, and ROWID index access.

As I noted, each execution plan may combine several table access methods, and you must understand the low-level table access methods because they determine the elapsed time for the query. Next, letís look at the ways that the table access method may be changed.

Changing Table Access Methods

Because there are many different ways to write a SQL statement that provides identical results, one of the goals of tuning table access is to be able to rewrite SQL statements to get the desired execution plan. We will cover this topic in great detail in Chapter 19, but I need to introduce this important concept here because rewriting SQL can dramatically change the table access method.

There are many things that can be done within Oracle to change the table access method for a SQL statement. These include:

  • Changing an index from unique to non-unique

  • Manually rewriting the SQL statement

  • Adding or deleting an index

  • Forcing a change with a hint

  • Changing the optimizer_goal or optimizer_mode

In order to appreciate how these changes will affect the execution plan for a SQL statement, letís examine each of these scenarios.

Changing Indexes from Unique to Non-unique

Changing index structures to improve performance is most commonly seen in cases where subqueries are specified. The savvy Oracle SQL tuner is always on the lookout for both correlated and noncorrelated subqueries for several reasons. The foremost is to search for opportunities for replacing the subquery with a standard join, and the other is to examine the uniqueness of the indexes in the subquery to see if changing the index structure can change the table access method.

While we will examine the tuning of subqueries in detail in Chapter 19, for now, just note that subqueries can be specified in any of the following formats:

Where emp_name IN (subuery)
Where EXISTS (Subquery)
Where emp_name = (Subquery)

To see how changing an index can change the table access method, letís examine a simple example. The query that follows is intended to show all employees who received a bonus. In the Oracle demo database, bonuses are stored in a separate bonus table. To perform this query, we must specify the rows we want in the emp table and then look for matching rows in the bonus table:

select /*+ first_rows */
   ename,
   hiredate,
   comm
from
   emp
where
   ename IN (select ename from bonus)
;

Here is the execution plan, where nonunique indexes have been created on the ename columns in the emp and bonus tables:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
                                                                   331
  NESTED LOOPS
                                                                     1
    VIEW
                               VW_NSO_1                              1
      SORT
UNIQUE                                                               1
        TABLE ACCESS
FULL                           BONUS                                 1
    TABLE ACCESS
BY INDEX ROWID                 EMP                                   2
      INDEX
RANGE SCAN                     ENAME_IDX                             1

Why do we see the view in the execution plan when we know that both entities are tables? When the plan table OPERATION column contains the VIEW object with a object_name like VW_NSO_1, the operation represents a nested select operation.

Now, watch what happens when we replace the nonunique indexes with unique indexes on the ename column:

SQL> create unique index ename_idx on emp (ename);

Index created.

SQL> create unique index ename_bonus_idx on bonus (ename);

Index created.

Now we rerun the explain plan to see the execution plan for our query:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
                                                                     1
  NESTED LOOPS
                                                                     1
    TABLE ACCESS
FULL                           EMP                                   1
    INDEX
UNIQUE SCAN                    ENAME_BONUS_IDX                       2

We now have change the execution plan from a nested select to a standard nested index scan. Note how the presence of a unique rather than non-unique index radically changed the execution plan for the SQL statement.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation.



Hit Counter