SQL Operators: Access Paths

A row source is a set of rows returned by a step in the execution plan. A SQL operator acts on a row source.
A unary operator acts on one input, as with access paths. A binary operator acts on two outputs, as with joins.
An access path is a technique used by a query to retrieve rows from a row source.

We will be discussing on Major Access path i.e Table Access Path and Index Access Path in this article.

1=> Table Access Paths

Below are popular access path for Table .

=> Full Table Scan -(No Index Exists)

A full table scan reads all rows from a table, and then filters out those rows that do not meet the selection criteria.

Because there is no Index on table ,Access is being done thriugh Full table scan.

This is the most common access path for optimizer when there is No INDEX on source table.

Optimizer tries to fetch all records from table and do filters based on condition.

As below example doesn’t contain any index, it’s got accessed through FULL TABLE Scan.

SQL> set lines 400
SQL> set pages 400
SQL>  select * from table(dbms_xplan.display_cursor('2nhvn3vpjadzv',0));

PLAN_TABLE_OUTPUT
-------------------------
SQL_ID	2nhvn3vpjadzv, child number 0
-------------------------------------
select	/* compare */ * from DEMO_TAB where PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID =0
Plan hash value: 76495054
------------------------------------------------------------------------------
| Id  | Operation	  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     |	     |	     |	  35 (100)|	     |
|*  1 |  TABLE ACCESS FULL| DEMO_TAB |	   2 |	  28 |	  35   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("CONS_ID"=0 AND "SHOP_ID"=0 AND "PRODUCT"='WHEELS')
19 rows selected.

=> Full Table Scan -(Index Exists But Table Column Contains NULL )

The optimizer cannot use the index to count the number of table rows because the index cannot contain null entries.
Whenever there is NULL in INDEX Columns values Optimizer will go for FULL TABLE scan if there is NO specific predicate supplied to Query.
When we use predicate INDEX data got filtered and access path may got changed and to select only NON NULL data from INDEX as in below example.

SQL> select count(*) from emp_null where EMP_NO IS NULL;

  COUNT(*)
----------
	 7

SQL> col INDEX_NAME for a20
SQL> col TABLE_NAME for a20
SQL> col COLUMN_NAME for a20
SQL> select index_name,table_name,column_name from dba_ind_columns where table_name='EMP_NULL';

INDEX_NAME	     TABLE_NAME 	  COLUMN_NAME
-------------------- -------------------- --------------------
IDX_NULL	     EMP_NULL		  EMP_NO

SQL> desc emp_null
 Name		Null?	Type
 -----------------------------
 EMP_NO 				NUMBER
 EMP_AGE				NUMBER

SQL> explain plan for select EMP_NO from EMP_NULL;

Explained.

SQL> set lines 300
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-----------------------
Plan hash value: 2944648810
------------------------------------------------------------------------------
| Id  | Operation	  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     |	3621K|	  17M|	2029   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP_NULL |	3621K|	  17M|	2029   (1)| 00:00:01 |
------------------------------------------------------------------------------

8 rows selected.

SQL> explain plan for select EMP_NO from EMP_NULL where EMP_NO< 100;
Explained.

SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------
Plan hash value: 338935661
-----------------------------------------------------------------------------
| Id  | Operation	 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |	    |  3774 | 18870 |	 16   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_NULL |  3774 | 18870 |	 16   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------
   1 - access("EMP_NO"<100)
13 rows selected.
SQL> 


=> Full table Scan –(High Degree of Parallelism).

SQL> select count(*) from EMP_TEST;

  COUNT(*)
----------
      2000
SQL> desc EMP_TEST
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID						    NUMBER
 GENDER 					    VARCHAR2(1)
SQL> SELECT DISTINCT GENDER FROM emp_test;                          

Gender
-------
M
F
N
S
V
T
6 rows selected.
SQL> CREATE INDEX GENDER_IDX ON EMP_TEST(GENDER);
Index created.

SQL> EXPLAIN PLAN FOR SELECT * FROM EMP_TEST WHERE GENDER='S';
Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
-------------------
Plan hash value: 889310681

--------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		 |   199 |  1194 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP_TEST	 |   199 |  1194 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN		    | GENDER_IDX |   199 |	 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------
   2 - access("GENDER"='S')
14 rows selected.

SQL> ALTER TABLE EMP_TEST PARALLEL (DEGREE 15);
Table altered.

SQL> EXPLAIN PLAN FOR SELECT * FROM EMP_TEST WHERE GENDER='S';
Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
------------------
Plan hash value: 1161765406

--------------------------------------------------------------------------------------------------------------
| Id  | Operation	     | Name	| Rows	| Bytes | Cost (%CPU)| Time	|    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |		|   199 |  1194 |     2   (0)| 00:00:01 |	 |	|	     |
|   1 |  PX COORDINATOR      |		|	|	|	     |		|	 |	|	     |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |   199 |  1194 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |		|   199 |  1194 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |	     |
|*  4 |     TABLE ACCESS FULL| EMP_TEST |   199 |  1194 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |	     |
--------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-----------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("GENDER"='S')
Note
-----
   - Degree of Parallelism is 15 because of table property
20 rows selected.
SQL> 

=> Full table Scan –(Query is Unselective).

If the optimizer determines that the query requires most of the blocks in the table, then it uses a full table scan, even though indexes are available. Full table scans can use larger I/O calls.
Making fewer large I/O calls is cheaper than making many smaller calls.

SQL> SELECT COUNT(*) FROM EMP_NAMES;

  COUNT(*)
----------
      6500

SQL> SELECT EMP_LNAME,COUNT(*) FROM EMP_NAMES GROUP BY EMP_LNAME;     

EMP_LNAME    COUNT(*)
---------- ----------
REDDY		  300
SINGH		 4200
DIWAN		 1500
TIWARI		  500

SQL> SELECT INDEX_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='EMP_NAMES';

INDEX_NAME  COLUMN_NAME
-----------
IDX_SELECTIVE  EMP_LNAME

SQL> EXPLAIN PLAN FOR SELECT * FROM EMP_NAMES WHERE EMP_LNAME='REDDY';
Explained.

SQL> SET LINES 300
SQL> SET PAGES 300
SQL> EXPLAIN PLAN FOR SELECT * FROM EMP_NAMES WHERE EMP_LNAME='REDDY';

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
----------------
Plan hash value: 4062229589
-----------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		    |	284 |  2840 |	  3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP_NAMES     |	284 |  2840 |	  3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN		    | IDX_SELECTIVE |	286 |	    |	  2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMP_LNAME"='REDDY')

14 rows selected.

SQL>  EXPLAIN PLAN FOR SELECT * FROM EMP_NAMES WHERE EMP_LNAME='SINGH';
Explained.

SQL>  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------
Plan hash value: 2618431616
-------------------------------------------------------------------------------
| Id  | Operation	  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	      |  4167 | 41670 |     7	(0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP_NAMES |  4167 | 41670 |     7	(0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("EMP_LNAME"='SINGH')
13 rows selected.

=> Table accessed by ROWID

A rowid is an internal representation of the storage location of data.
The rowid of a row specifies the data file and data block containing the row and the location of the row in that block.
Locating a row by specifying its rowid is the fastest way to retrieve a single row because it specifies the exact location of the row in the database.

First Rowid got fetched from INDEX and then data fetched from TABLE using Rowid.
Batched search is used to reduce I/O traversing. i.e Index got scanned in bulk and then trimmed to access ROWID

SQL> EXPLAIN PLAN FOR SELECT * FROM EMP_NAMES WHERE EMP_LNAME='REDDY';
Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------
Plan hash value: 4062229589

-----------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		    |	284 |  2840 |	  3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP_NAMES     |	284 |  2840 |	  3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN		    | IDX_SELECTIVE |	286 |	    |	  2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMP_LNAME"='REDDY')

14 rows selected.

2=> Index Access Paths

B-Tree Structure

B-Tree contains branch blocks & Leaf Blocks
Brach Block is used for Searching/Traversing where as Leaf Block stores actual data.
The Leaf block are interconnected to each other and have doubly-linked relation with each other.
Leaf block store actual rows with row-id to locate in table.

Below mentioned are B-Tree INDEX Access path through which optimizer build row-set.

=> INDEX UNIQUE SCAN

A unique index access path returns maximum 1 row.
A unique index access path need “=” as predicate operator.
All columns of UNIQUE INDEX have to be part of EQUALITY operator.
To satisfy this access path there has to be PRIMARY KEY INDEX or UNIQUE KEY INDEX where UNIQUENESS has to be “UNIQUE” in USER_INDEX table.
No other INDEX should exist on the same table column with NONUNIQUE nature.

Let we create Index having UNIQUENESS and UNIQ in USER_INDEX column as below.

SQL> create table UNIQ_TEST (emp_id number ,emp_name varchar2(20));

Table created.
SQL> insert into UNIQ_TEST values (1,'JEFF');
1 row created.
SQL>  insert into UNIQ_TEST values (2,'RAU'); 
1 row created.
SQL> insert into UNIQ_TEST values (3,'DIPTI');
1 row created.
SQL> COMMIT;
Commit complete.


SQL> CREATE UNIQUE INDEX UNQ_IDX_NON_P ON UNIQ_TEST(EMP_ID);
Index created.

SQL> explain plan for select * from UNIQ_TEST where EMP_ID=2;
Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
----------------------
Plan hash value: 2732305634
---------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		    |	  1 |	 25 |	  1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| UNIQ_TEST     |	  1 |	 25 |	  1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN	    | UNQ_IDX_NON_P |	  1 |	    |	  0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMP_ID"=2)
14 rows selected.

Let we Check Uniqueness for this INDEX as below.

SQL> select INDEX_NAME,TABLE_NAME,UNIQUENESS from user_indexes where table_name='UNIQ_TEST';
INDEX_NAME                                                             TABLE_NAME          UNIQUENES
-------------------------------------------------------------------------------------------------------
UNQ_IDX_NON_P                                                             UNIQ_TEST          UNIQUE
SQL>


Let we have another example with having Primary Key as below,

SQL> create table UNIQ_TEST (emp_id number primary key,emp_name varchar2(20));
Table created.
SQL> insert into UNIQ_TEST values (1,'JEFF');
1 row created.
SQL>  insert into UNIQ_TEST values (2,'RAU'); 
1 row created.
SQL> insert into UNIQ_TEST values (3,'DIPTI');
1 row created.
SQL> COMMIT;
Commit complete.

SQL> SET LINES 400 
SQL> SET PAGES 400 
SQL> COL TABLE_NAME FOR A20 
SQL> COL COLUMN_NAME FOR A20
SQL> SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = '&TABLE_NAME'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;
Enter value for table_name: UNIQ_TEST
old   3: WHERE cols.table_name = '&TABLE_NAME'
new   3: WHERE cols.table_name = 'UNIQ_TEST'

TABLE_NAME	     COLUMN_NAME	    POSITION STATUS   OWNER
-------------------- -------------------- ---------- -------- -----
UNIQ_TEST	     EMP_ID			       1    ENABLED  ABHI_TEST

SQL> select INDEX_NAME,TABLE_NAME,UNIQUENESS from user_indexes where table_name='UNIQ_TEST';

INDEX_NAME						  TABLE_NAME	      UNIQUENES
---------------------------------------------------------------------------------------
SYS_C007723							UNIQ_TEST	      UNIQUE

SQL> explain plan for select * from UNIQ_TEST where EMP_ID=2;
Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
--------------------
Plan hash value: 4089452479
-------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		  |	1 |    25 |	1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| UNIQ_TEST   |	1 |    25 |	1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN	    | SYS_C007723 |	1 |	  |	1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMP_ID"=2)
14 rows selected.

Let we have another example with having NON-UNIQUE INDEX as below,INDEX RANGE SCAN  is used due to NON-UNIQUE Nature.

SQL> ALTER TABLE UNIQ_TEST DROP CONSTRAINTS SYS_C007723;
Table altered.

SQL> CREATE INDEX NON_UNQ_IDX ON UNIQ_TEST(EMP_ID);
Index created.

SQL> explain plan for select * from UNIQ_TEST where EMP_ID=2;
Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
------------------------
Plan hash value: 289486799
---------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		  |	1 |    25 |	2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| UNIQ_TEST   |	1 |    25 |	2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN		    | NON_UNQ_IDX |	1 |	  |	1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMP_ID"=2)
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

18 rows selected.

=> INDEX RANGE SCAN

Index range scan is an Ordered Scan of Index values.(DEFAULT)
Optimizer choose index for Query when it’s have high selectivity.
Leading columns of Concatenated index must be part of conditions
Predicate operator could be >,>=,<,<=
Wild-card searches of the form col1 LIKE '%ASD' must not be in a leading position.
Selectivity of INDEX must be less that FTS or any other index to be eligible to INDEX RANGE SCAN.
If “=” operator is used then INDEX Uniqueness has to be NONUNIQUE.

SQL> CREATE INDEX NON_UNQ_IDX ON UNIQ_TEST(EMP_ID);
Index created.

SQL> explain plan for select * from UNIQ_TEST where EMP_ID=2;
Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 289486799
---------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		  |	1 |    25 |	2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| UNIQ_TEST   |	1 |    25 |	2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN		    | NON_UNQ_IDX |	1 |	  |	1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMP_ID"=2)

18 rows selected.

SQL> select INDEX_NAME,TABLE_NAME,UNIQUENESS from user_indexes where table_name='UNIQ_TEST';

INDEX_NAME			 TABLE_NAME	      UNIQUENES
--------------------------------------------------
NON_UNQ_IDX			 UNIQ_TEST	      NONUNIQUE
SQL>

=> INDEX FULL SCAN

INDEX Full Scan read entire INDEX in ORDERED way.
It eliminates extra work of sorting INDEX by ORDER.
When a Predicate references a COLUMN from Index.
A query includes an ORDER BY on indexed columns, which does NOT CONTAINS ANY NULL value
If NO PREDICATE is specified, then ALL of the BELOW condition has to meet.
 a.All columns in the table and in the query are in the index.
 b.At least one indexed column is not null.

How It reads actually.
The database reads the root block, and then navigates down the left hand side of the index (or right if doing a descending full scan) until it reaches a leaf block.
Then the database reaches a leaf block, the scan proceeds across the bottom of the index, one block at a time, in sorted order.
The database uses single-block I/O rather than multiblock I/O.

SQL>  EXPLAIN PLAN FOR SELECT SAT_NAME,SAT_ID FROM SATTELITE ORDER BY SAT_ID;
Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
----------------------
Plan hash value: 3333187628
---------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		    |  6499 | 77988 |	  3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SATTELITE     |  6499 | 77988 |	  3   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN	    | FULL_IDX_SCAN |  6499 |	    |	  2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
9 rows selected.
SQL>

=> INDEX FAST FULL SCAN

An index fast full scan reads the index blocks in unsorted order, as they exist on disk.
This scan does not use the index to probe the table, but reads the index instead of the table, essentially using the index itself as a table.
The INDEX_FFS(table_name index_name) hint forces a fast full index scan.
The database uses multiblock I/O to read the root block and all of the leaf and branch blocks.
The databases ignores the branch and root blocks and reads the index entries on the leaf blocks.

Let we continue with above example and changing Columns in Select .

SQL> set lines 400
SQL> set pages 400
SQL>  EXPLAIN PLAN FOR SELECT  SAT_ID FROM SATTELITE ORDER BY SAT_ID;
Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 3834974934
---------------------------------------------------------------------------------------
| Id  | Operation	      | Name	      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | 	      |  6499 | 25996 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY	      | 	      |  6499 | 25996 |     7  (15)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| FULL_IDX_SCAN |  6499 | 25996 |     6	(0)| 00:00:01 |
---------------------------------------------------------------------------------------
9 rows selected.
SQL> 

=> INDEX SKIP SCAN

An index skip scan occurs when the initial column of a composite index is "skipped" or not specified in the query.
This type of scan happens on Multi-Column INDEX.
The leading column of a composite index is not specified in the query predicate. 
For example, the query predicate does not reference the cust_gender column, and the composite index key is (cust_gender,cust_email).
Many distinct values exist in the nonleading key of the index and relatively few distinct values exist in the leading key. 
For example, if the composite index key is (cust_gender,cust_email), then the cust_gender column has only two distinct values, but cust_email has thousands.

SQL> SELECT COUNT(*) FROM SATTELITE;

  COUNT(*)
----------
      6499
SQL> SELECT DISTINCT SAT_CAT FROM SATTELITE;
S
-
Y
X
SQL> SELECT SAT_LOCATION FROM SATTELITE WHERE ROWNUM<10;
SAT_LOCATI
----------
X4
X5
X8
X10
X12
X14
X16
X18
X20

9 rows selected.

SQL> SELECT SAT_LOCATION FROM SATTELITE WHERE  SAT_LOCATION IS NULL;
no rows selected

SQL> SELECT SAT_CAT FROM SATTELITE WHERE  SAT_LOCATION IS NULL;
no rows selected

SQL> CREATE INDEX IDX_SKP_SCN ON SATTELITE(SAT_CAT,SAT_LOCATION);
Index created.

SQL> @gather_stats.sql
Enter value for owner: ABHI_TEST
Enter value for name: SATTELITE
old   2: DBMS_STATS.GATHER_TABLE_STATS(ownname => '&OWNER', tabname =>'&name', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE);
new   2: DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'SATTELITE', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> EXPLAIN PLAN FOR SELECT * FROM SATTELITE WHERE SAT_LOCATION='X190';

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------------------
Plan hash value: 2657963293
---------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		  |	1 |    24 |	4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SATTELITE   |	1 |    24 |	4   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN		    | IDX_SKP_SCN |	1 |	  |	3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SAT_LOCATION"='X190')
       filter("SAT_LOCATION"='X190')
15 rows selected.
SQL> 

=> INDEX JOIN SCAN

An index join scan is a hash join of multiple indexes that together return all columns requested by a query.
The database does not need to access the table because all data is retrieved from the indexes.
The cost of retrieving rows from the table is higher than reading the indexes without retrieving rows from the table.
An index join is often expensive. For example, when scanning two indexes and joining them, it is often less costly to choose the most selective index, and then probe the table.
You can specify an index join with the INDEX_JOIN(table_name) hint.

SQL>  EXPLAIN PLAN FOR SELECT /*+ INDEX_JOIN(SATTELITE) */ SAT_ID,SAT_LOCATION  FROM SATTELITE WHERE SAT_LOCATION='X18' ORDER BY SAT_ID;
Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
-------
Plan hash value: 2745389282
--------------------------------------------------------------------------------------------
| Id  | Operation		| Name		   | Rows  | Bytes | Cost (%CPU)| Time	   |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	|		   |	 1 |	10 |	35   (3)| 00:00:01 |
|   1 |  SORT ORDER BY		|		   |	 1 |	10 |	35   (3)| 00:00:01 |
|   2 |   VIEW			| index$_join$_001 |	 1 |	10 |	34   (0)| 00:00:01 |
|*  3 |    HASH JOIN		|		   |	   |	   |		|	   |
|   4 |     INDEX FAST FULL SCAN| FULL_IDX_SCAN    |	 1 |	10 |	19   (0)| 00:00:01 |
|*  5 |     INDEX FAST FULL SCAN| IDX_SKP_SCN	   |	 1 |	10 |	24   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(ROWID=ROWID)
   5 - filter("SAT_LOCATION"='X18')

18 rows selected.