| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: index not in use
On Feb 28, 6:19 pm, "Steve Robin" <ocma..._at_gmail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Even Simple query is also not using this index(It's Bitmap Index).
SQL> explain plan for
2 select * from MCC_USER.SC_ORGANIZATION where
ORGANIZATIONTYPE='3';
Explained.
SQL> @?\rdbms\admin\utlxplp.sql
PLAN_TABLE_OUTPUT
| 0 | SELECT STATEMENT | | 89518 | 24M|1040 |
But I give hint.
SQL> explain plan for
2 select /*+ INDEX(SC_ORGANIZATION IND_SC_ORG_ORG_TYPE) */* from
MCC_USER.SC_ORGANIZATION where ORGANIZATIONTYPE=3;
Explained.
SQL> @?\rdbms\admin\utlxplp.sql
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows |Bytes | Cost |
| 0 | SELECT STATEMENT | | 89518 | 24M| 14108 | | 1 | TABLE ACCESS BY INDEX ROWID | SC_ORGANIZATION | 89518 | 24M| 14108 | | 2 | BITMAP CONVERSION TO ROWIDS| | | | | |* 3 | BITMAP INDEX FULL SCAN | IND_SC_ORG_ORG_TYPE | | | | -------------------------------------------------------------------------------------
I donn't understand BITMAP CONVERSION TO ROWIDS.
SQL> select count(distinct ORGANIZATIONTYPE) from MCC_USER.SC_ORGANIZATION; COUNT(DISTINCTORGANIZATIONTYPE)
3
So I think bitmap index is more helpful. Received on Wed Feb 28 2007 - 07:35:41 CST
 |
 |