On Feb 28, 5:43 pm, "sybrandb" <sybra..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
I apologies for incomplete information, In future I will provide this
information in starting.
But it is varchar2.
Received on Wed Feb 28 2007 - 07:19:33 CST
