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

 

 


 

 

 

 

 

 

 

The index_ffs Hint

Oracle Tips by Burleson Consulting

As I noted in earlier chapters, the index fast full scan is used in cases where a query can be resolved without accessing any table rows. Remember not to confuse the index fast full scan with a full-index scan.

When the index_ffs is invoked, the optimizer will scan all of the blocks in the index using multiblock reads and access the index in non-sequence order. You can also make an index fast full scan even faster by combining the index_fss hint with a parallel hint.

The index_ffs hint can also be used to trick the SQL optimizer when you need to select on the values for the second column in a concatenated index. This is because the entire index is accessed, and, depending on the amount of parallelism, an index fast full scan may be faster than an index range scan. Using the index_ffs hint is especially useful for shops where the tables are huge and creating a new index would require gigabytes of extra disk space. In the case of very large tables where no high-level index key exists for the required search column, the fast full-index scan will always be faster than a full-table scan.

For example, consider the following concatenated index on two non-unique columns.

create index
  dept_job_idx
on
   emp
   (deptno, job);

Now, consider the following SQL, and assume that there is no index on the job column.

select
   ename,
   job,
   deptno,
   mgr
from
   emp
where
   job = 'SALESMAN'
;

Here is the execution plan. As we expect, we see a full-table scan on the emp table:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     1
  TABLE ACCESS
FULL                           EMP                                   1

Now, we take the same query and add the fast_ffs hint, making sure to specify the table name and the index name.

select  /*+ index_ffs(emp, dept_job_idx) */
   ename,
   job,
   deptno,
   mgr
from
   emp
where
   job = 'SALESMAN'
;

Here we see that the full-table scan is replaced by the faster fast full-index scan:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                    34
  TABLE ACCESS
BY INDEX ROWID                 EMP                                   1
    INDEX
FULL SCAN                      DEPT_JOB_IDX                          1

Note: The index fast full scan execution plan is the mechanism behind fast index create and recreate.

The use_concat Hint

The use_concat hint requests that a union all execution plan be used for all OR conditions in the query, rewriting the query into multiple queries. The use_concat hint is commonly invoked when a SQL query has a large amount of OR conditions in the where clause.

For example, consider the following query where a B-tree index exists on job, deptno, and sal. It is interesting to note that if the indexes were bitmap indexes, the execution would not perform a full-table scan. This is because Oracle automatically uses bitmap indexes where a query has multiple OR conditions on bitmap index columns.

select
   ename
from
   emp
where
   deptno = 10
or
   sal < 5000
or
   job = ‘CLERK’;

Here we have two choices. Because all of the index columns are low cardinality, we could create three bitmap indexes on deptno, sal, and clerk, causing a bitmap merge execution plan. Our other choice is to invoke use_concat to break the query into three separate B-tree index scans whose result sets will be combined with the union operator.

Here is the execution plan for this query with B-tree indexes. Note that we must perform a full-table scan to satisfy the multiple OR conditions in the where clause:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     1
  TABLE ACCESS
FULL                           EMP                                   1

If our indexes had been bitmap indexes, we would have seen a far faster execution plan:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     4
  TABLE ACCESS
BY INDEX ROWID                 EMP                                   1
    BITMAP CONVERSION
TO ROWIDS                                                            1
      BITMAP OR
                                                                     1
        BITMAP INDEX
SINGLE VALUE                   EMP_DEPTNO_BIT                        1
        BITMAP MERGE
                                                                     2
          BITMAP INDEX
RANGE SCAN                     EMP_SAL_BIT                           1
        BITMAP INDEX
SINGLE VALUE                   EMP_JOB_BIT                           3

Now, returning to our example with three B-tree indexes, let’s add the use_concat hint and see the change to the execution plan.

select /*+ use_concat */
   ename
from
   emp
where
   deptno = 10
or
   sal < 5000
or
   job = ‘CLERK’;

Here we see that the full-table scan has been replaced with a union of three queries, each using the B-tree index for the single columns and the CONCATENATION plan to union the result sets:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     3
  CONCATENATION
    TABLE ACCESS
BY INDEX ROWID                 EMP                                   1
      INDEX
RANGE SCAN                     EMP_JOB                               1
    TABLE ACCESS
BY INDEX ROWID                 EMP                                   2
      INDEX
RANGE SCAN                     EMP_SAL                               1
    TABLE ACCESS
BY INDEX ROWID                 EMP                                   3
      INDEX
RANGE SCAN                     EMP_DEPT                              1

For details on tuning queries with multiple OR conditions, please see Chapter 14. Next let’s take a look at how parallel hints can be added to queries to improve the performance of full-table scans.


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