ORACLE FAST FULL INDEX SCAN vs. INDEX RANGE SCANS

      Today ,My colleague give me a question.
      Actually,he had wrote a effective sql .But,There is a small mistake in this sql.So I write a different sql using clause exists.Duing about 400 seconds,it can run out a result. A then we correct the mistake   which there was in that effective sql.
      so ,I compared  effective sql with ineffective sql -:)
      Look this effective sql:
     

select bt.num
from uin,  bt
where    bt.num = uin.phnum(+)
and uin.usin_phnum is null
and bt.num is not null

     In the plan of this sql,we found Fast full index scans.In the document of oracle official(10g):

Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least .e column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.

You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.

A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.

  
      But ,the plan of my ineffective sql uses index range scans.The de.ion of index range scan:
 

An index range scan is a common operation for accessing selective data. It can be bounded (bounded . both sides) or unbounded (on .e or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.

If data must be sorted by order, then use the ORDER BY clause, and do not rely . an index. If an index can be used to satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort.

        Through those de.ions ,We can catch . the merits and shortcomings in ffs and irs respectively.In fact ,that effective sql in the where clause:bt.num = uin.phnum(+),the coloumn "phumn" is  null and there is index . it .Maybe ,Someone will ask this question:"Why did the ORACLE Optimizer brake the rule that ' at least .e column in the index key has the NOT NULL constraint'?"
        In my guess ,outer join is the real reason.Because,the optimizer convert the outer join into two phase,first is not null and then union all the "null" operation.
        All above, There is a huge benefit to not reading the table rows, but there are some requirements for Oracle to invoke the fast full-index scan.
     

1.All of the columns required must be specified in the index. That is, all columns in the select and where clauses must exist in the index.
2.The query returns more than 10 percent of the rows within the index. This 10 percent figure depends . the degree of multi-block reads and the degree of parallelism.
3.You are counting the number of rows in a table that meet a specific criterion. The fast full-index scan is almost always used for count(*) operations.

       By the way,the outer join need also think over.
       -----------------------------
       Oh,finish! it took my about two hours for this blog. You konw,We need experiment many times -:)