Re: index not in use

On Feb 28, 1:35 pm, "Steve Robin" <ocma..._at_gmail.com> wrote:
> 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

Please use dbms_metadata.get_ddl in the future so we don't have to read this fully *unreadable* junk anymore. More importantly, you forgot the most crucial information: the datatype of organizationtype.
If the datatype is number, Oracle will implicitly replace organizationtype = '2'
by
to_char(organizationtype) = '2'
and NO index.

--
Sybrand Bakker
Senior Oracle DBA