On Mar 20, 7:37 pm, "Valentin Minzatu" <valentinminz..._at_yahoo.com>
wrote:
> On Mar 20, 3:49 am, "Steve Robin" <ocma..._at_gmail.com> wrote:
>
>
>
>
>
> > Index is getting use in first query and but if add one more condition
> > then it is not in use. In Second query.
> > What can be the problem here.
>
> > SQL> EXPLAIN PLAN FOR
> > 2 SELECT COUNT(1) FROM SC_ORG WHERE SC_ORG.ORG_TYPE =2;
>
> > Explained.
>
> > SQL> @?\rdbms\admin\utlxplp.sql
>
> > PLAN_TABLE_OUTPUT
> > ---------------------------------------------------------------------------ญญ-------------------------
>
> > ---------------------------------------------------------------------------ญญ-----------
> > | Id | Operation | Name | Rows |
> > Bytes | Cost |
> > ---------------------------------------------------------------------------ญญ-----------
> > | 0 | SELECT STATEMENT | | 1
> > | 2 | 9 |
> > | 1 | SORT AGGREGATE | | 1
> > | 2 | |
> > | 2 | BITMAP CONVERSION COUNT | |
> > | | |
> > |* 3 | BITMAP INDEX FAST FULL SCAN| N1_ORG_TYPE |
> > | | |
> > ---------------------------------------------------------------------------ญญ-----------
>
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
>
> > 3 - filter(TO_NUMBER("SC_ORG"."ORG_TYPE")=2)
>
> > Note: cpu costing is off
>
> > 16 rows selected.
>
> > SQL> EXPLAIN PLAN FOR
> > 2 SELECT COUNT(1) FROM SC_ORG WHERE SC_ORG.ORG_TYPE =2 AND
> > (SC_ORG.IsDel = :1)
>
> > Explained.
>
> > SQL> @?\rdbms\admin\utlxplp.sql
>
> > PLAN_TABLE_OUTPUT
> > ---------------------------------------------------------------------------ญญ-------------------------
>
> > -------------------------------------------------------------------------
> > | Id | Operation | Name | Rows | Bytes |
> > Cost |
> > -------------------------------------------------------------------------
> > | 0 | SELECT STATEMENT | | 1 | 4 |
> > 1190 |
> > | 1 | SORT AGGREGATE | | 1 | 4
> > | |
> > |* 2 | TABLE ACCESS FULL | SC_ORG | 98925 | 386K| 1190 |
> > -------------------------------------------------------------------------
>
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
>
> > 2 - filter(TO_NUMBER("SC_ORG"."ORG_TYPE")=2 AND
> > "SC_ORG"."IsDel"=TO_NUMBER(:Z))
>
> > Note: cpu costing is off
>
> > 16 rows selected.
>
> > SQL> select * from dba_indexes where index_name='N1_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
> > --- ------------ ------ -------- ---
> > MC_UR N1_ORG_TYPE BITMAP
> > MC_UR SC_ORG TABLE
> > NONUNIQUE DISABLED
> > MC_UR_INDEX 2
> > 255 65536
> > 1
> > 2147483645
> > 1 1
> > 10 NO 1 10
> > 3 3 7
> > 21 VALID 21 21 20-MAR-07 1
> > 1 NO N N N DEFAULT NO
>
> > YES NO
>
> > SQL> SELECT ORG_TYPE,COUNT(1) FROM SC_ORG GROUP BY ORG_TYPE;
>
> > ORG_TYPE COUNT(1)
> > ------------------------------ ----------
> > 1 295142
> > 2 796
> > 3 895
>
> > SQL>
>
> Is the column in the added condition (IsDel) part of the index?- Hide quoted text -
>
> - Show quoted text -
I apologies, but I don't remember any condition like this(IsDel).
Would you give me any idea, any site or anything, which can help me to
understand it.
Received on Tue Mar 20 2007 - 21:00:37 CDT
