Index is not getting in use if I add one more condition

From: Steve Robin <ocmaman_at_gmail.com>
Date: 20 Mar 2007 00:49:37 -0700
Message-ID: <1174376977.723010.149680@l77g2000hsb.googlegroups.com>

Index is getting use in first query and but if add one more condition then it is not in use. In Second query.
What can be the problem here.

SQL> EXPLAIN PLAN FOR
  2 SELECT COUNT(1) FROM SC_ORG WHERE SC_ORG.ORG_TYPE =2; Explained.

SQL> @?\rdbms\admin\utlxplp.sql

PLAN_TABLE_OUTPUT

---

---

| Id  | Operation                     |  Name                | Rows  |

Bytes | Cost |

---

|   0 | SELECT STATEMENT              |                      |     1
|     2 |     9 |
|   1 |  SORT AGGREGATE               |                      |     1
|     2 |       |
|   2 |   BITMAP CONVERSION COUNT     |                      |
|       |       |
|*  3 |    BITMAP INDEX FAST FULL SCAN| N1_ORG_TYPE  	     |
|       |       |


--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   3 - filter(TO_NUMBER("SC_ORG"."ORG_TYPE")=2)

Note: cpu costing is off

16 rows selected.

SQL> EXPLAIN PLAN FOR
  2 SELECT COUNT(1) FROM SC_ORG WHERE SC_ORG.ORG_TYPE =2 AND (SC_ORG.IsDel = :1)

Explained.

SQL> @?\rdbms\admin\utlxplp.sql

PLAN_TABLE_OUTPUT

---

---

| Id | Operation | Name | Rows | Bytes | Cost |

---

|   0 | SELECT STATEMENT     |                  |     1 |     4 |
1190 |
|   1 |  SORT AGGREGATE      |                  |     1 |     4
|       |
|*  2 |   TABLE ACCESS FULL  | SC_ORG  		| 98925 |   386K|  1190 |


-------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   2 - filter(TO_NUMBER("SC_ORG"."ORG_TYPE")=2 AND

              "SC_ORG"."IsDel"=TO_NUMBER(:Z))

Note: cpu costing is off

16 rows selected.

SQL> select * from dba_indexes where index_name='N1_ORG_TYPE';

OWNER                          INDEX_NAME

INDEX_TYPE

------------------------------ ------------------------------
---------------------------
TABLE_OWNER                    TABLE_NAME

TABLE_TYPE UNIQUENES COMPRESS

------------------------------ ------------------------------
----------- --------- --------
PREFIX_LENGTH TABLESPACE_NAME                 INI_TRANS  MAX_TRANS

INITIAL_EXTENT NEXT_EXTENT

------------- ------------------------------ ---------- ----------

-------------- -----------
MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS
----------- ----------- ------------ ------------- -------------- ---------- ---------------
  PCT_FREE LOG BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY ---------- --- ---------- ----------- -------------

----------------------- -----------------------
CLUSTERING_FACTOR STATUS     NUM_ROWS SAMPLE_SIZE LAST_ANAL DEGREE
----------------- -------- ---------- ----------- ---------
----------------------------------------
INSTANCES                                PAR T G S BUFFER_ USE
DURATION        PCT_DIRECT_ACCESS
---------------------------------------- --- - - - ------- ---
--------------- -----------------
ITYP_OWNER                     ITYP_NAME
------------------------------ ------------------------------

PARAMETERS

---

GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI
--- ------------ ------ -------- ---

MC_UR                       N1_ORG_TYPE            BITMAP
MC_UR                       SC_ORG                TABLE
NONUNIQUE DISABLED
              MC_UR_INDEX                          2
255          65536
          1
2147483645
1               1
        10 NO           1          10
3                       3                       7
               21 VALID            21          21 20-MAR-07 1
1                                        NO  N N N DEFAULT NO


YES                              NO

SQL> SELECT ORG_TYPE,COUNT(1) FROM SC_ORG GROUP BY ORG_TYPE;

ORG_TYPE                 COUNT(1)
------------------------------ ----------
1                                  295142
2                                     796
3                                     895

SQL> Received on Tue Mar 20 2007 - 02:49:37 CDT