Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> index not in use
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_NAMECOLUMN_NAME
OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANSINITIAL_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 DURATIONPCT_DIRECT_ACCESS ITYP_OWNER
--------------------------- ------------------------------ ------------------------------ ----------- --------- -------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- --- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- --------- ---------------------------------------- ---------------------------------------- --- - - - ------- --- --------------- ----------------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 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
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