This question is specific to B Tree index.
Following are the type of index scans I know
1. Index Unique Scan
This happens on a unique index and when we need to pick single record. And this happens when ever there is a "=" operation in the where clause.
2. Index Range Scan
This happens when we select a range of records. By giving > or < like that.
3. Index fast full scan
This happen when oracle does not have to go to the table to fetch the data and all the required information is available in the index itself.
This is what all I know about this in a brief.
But what I would like to know is how the scanning happens for all the above three in a b tree index. Dose the traversing happens the same way for all the type of scan or there is any difference. Can you give an example and explain how its scans through the index. I searched for this and couldn¿t find much.
And also can you throw some light on index rebuild. I have been hearing for some time now these two controversial statements.
1. Index rebuild will improve performance.
2. Statement 1 is just a myth.
A reference from this site
http://www.miracleas.dk/images/upload/Docs/Richard%20Foote.pdf Says B Tree index is always balanced. But I can¿t make much out of the examples. Can you let me know how can I identify that my index is balanced and how may levels are there in that index.
Than you.
1) wrong about the '='
ops$tkyte%ORA11GR1> create table t ( x int, y int );
Table created.
ops$tkyte%ORA11GR1> create unique index t_idx on t(x,y);
Index created.
ops$tkyte%ORA11GR1> exec dbms_stats.set_table_stats( user, 'T', numrows => 100000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR1> @at
ops$tkyte%ORA11GR1> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA11GR1> set autotrace traceonly explain
ops$tkyte%ORA11GR1> select * from t where x = 5;
Execution Plan
----------------------------------------------------------
Plan hash value: 2946670127
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 26000 | 2 (0)| 00:00:01
|* 1 | INDEX RANGE SCAN| T_IDX | 1000 | 26000 | 2 (0)| 00:00:01
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"=5)
2) or equals as above, or use a non-unique index in any case.
3) index range/unique/skip/join will do that too. An index fast full scan is generally used when we can avoid a full scan of the base table - yes.
If you have access to Effective Oracle by Design or Expert Oracle Database architecture, I go into index access paths in some detail.
the index SCANS use single block IO - we in general read the root block of the index, navigate to a branch block, navigate to a leaf block - and then start reading leaf blocks (they are linked together from left to right like a linked list). SCANS like this read the index "in order", in sorted key order.
the index FULL SCAN uses multi-block IO. We read N blocks at a time, we ignore root and branch blocks and just process leaf blocks (where the data is). the data is read as it exists on disk - in whatever order we hit it (the data is not read in key order)
as for the rebuild stuff - search for rebuild on this site - we've discussed this many times in the past.
a rebuild (or coalesce even better) is called for so infrequently - it is the exception to have to do that, not the rule. A rebuild/coalesce sometimes is positive, most of the time a waste of energy.
Your b*tree index is always balanced, always. So, to identify that one is balanced, you just "know it to be true".
As for the levels, you gather statistics on the index and blevel will be the height minus one (in user_indexes)