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?
Received on Tue Mar 20 2007 - 09:37:46 CDT
