index not in use

From: Steve Robin <ocmaman_at_gmail.com>
Date: 28 Feb 2007 04:35:49 -0800
Message-ID: <1172666149.564870.166110@8g2000cwh.googlegroups.com>

SQL> SELECT * FROM DBA_IND_COLUMNS WHERE TABLE_NAME='SC_ORGANIZATION' AND INDEX_OWNER='MCC_USER'
  2 AND COLUMN_NAME='ORGANIZATIONTYPE';

INDEX_OWNER                    INDEX_NAME
TABLE_OWNER                    TABLE_NAME

COLUMN_NAME
------------------------------ ------------------------------

• ------------------------------

  ---

  ---

  MCC_USER IND_SC_ORG_ORG_TYPE MCC_USER SC_ORGANIZATION ORGANIZATIONTYPE

SQL> SELECT * FROM DBA_INDEXES WHERE INDEX_NAME='IND_SC_ORG_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
------------------------------ ------------------------------

--------------------------- ------------------------------
------------------------------ ----------- --------- --------
------------- ------------------------------ ---------- ----------
-------------- ----------- ----------- ----------- ------------
------------- -------------- ---------- --------------- ---------- ---
---------- ----------- ------------- -----------------------
----------------------- ----------------- -------- ----------
----------- --------- ----------------------------------------
---------------------------------------- --- - - - ------- ---
--------------- ----------------- ------------------------------
------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MCC_USER                       IND_SC_ORG_ORG_TYPE
BITMAP                      MCC_USER
SC_ORGANIZATION                TABLE       NONUNIQUE
DISABLED               MCC_USER_DATA
2        255          65536                       1
2147483645                                            1
1         10

NO
VALID
1

1                                        NO  N N N DEFAULT
NO
NO                               NO

SQL> SHOW PARAMETER INDEX

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
optimizer_index_caching              integer     50
optimizer_index_cost_adj             integer     40


But still..............................

SQL> SELECT * FROM MCC_USER.SC_ORGANIZATION WHERE ORGANIZATIONTYPE='2'; 738 rows selected.

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1040 Card=89518 Byte
          s=25512630)

   1    0   TABLE ACCESS (FULL) OF 'SC_ORGANIZATION' (Cost=1040 Card=8
          9518 Bytes=25512630)

Statistics

---

          0  recursive calls
          0  db block gets
      10863  consistent gets
      10742  physical reads
          0  redo size
      92143  bytes sent via SQL*Net to client
       1046  bytes received via SQL*Net from client
         51  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        738  rows processed

I am not able to use this index.

Database : 9.2.0.8
OS : Windows XP Received on Wed Feb 28 2007 - 06:35:49 CST