Hi All,
Just created a table to test the scan functionality over indexed column. The table holds 1 million records with 16 distinct values on a column named TNAME and TNUMBER holds unique values.
TNAME is a not null column with an index over it.
As I queried against the table with condition TNAME='MYDATA10', the select count(*) query goes for INDEX range scan and select * query executes with Full table scan. Although the MYDATA10 is just 15% of the total rows.
But again, when I queried the same against the condition TNAME='MYDATATEST' both count(*) and * goes for an Index Range scan, but I could see that the total rows are too minimal for MYDATATEST when compared to my MYDATA10.
Since I have made the TNAME column as NOT NULL and as I have indexed it, I was expecting it to go for Index Fast full scan rather Full table scan.
Why this is happening. Could somebody clear me??
Thank you!
ETLTEST@orcl> select * from v$version where banner like 'Oracle%';
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
ETLTEST@orcl> DESC TEST12
Name Null? Type
----------------- -------- ------------------------
TNAME NOT NULL VARCHAR2(25)
TNUMBER NUMBER
ETLTEST@orcl> SELECT COUNT(*) FROM TEST12;
COUNT(*)
----------
1000000
Elapsed: 00:00:00.03
ETLTEST@orcl> SELECT DISTINCT TNAME, COUNT(*)
2 FROM TEST12
3 GROUP BY TNAME
4 ORDER BY TNAME
5 /
TNAME COUNT(*)
------------------------- ----------
MYDATA0 66666
MYDATA1 66667
MYDATA10 66667
MYDATA11 66666
MYDATA12 66666
MYDATA13 66666
MYDATA14 66297
MYDATA2 66667
MYDATA3 66667
MYDATA4 66294
MYDATA5 66667
MYDATA6 66667
MYDATA7 66667
MYDATA8 66667
MYDATA9 66298
MYDATATEST 1111
16 rows selected.
Elapsed: 00:00:00.48
ETLTEST@orcl> SELECT INDEX_NAME, COLUMN_NAME
2 FROM USER_IND_COLUMNS
3 WHERE TABLE_NAME='TEST12';
INDEX_NAME COLUMN_NAME
------------------------------
TEST12_IDX TNAME
Elapsed: 00:00:00.03
ETLTEST@orcl> EXPLAIN PLAN
2 FOR
3 SELECT COUNT(*) FROM TEST12 WHERE TNAME='MYDATA10';
Explained.
Elapsed: 00:00:00.01
ETLTEST@orcl> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 4254084898
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 106 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX RANGE SCAN| TEST12_IDX | 76837 | 675K| 106 (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TNAME"='MYDATA10')
14 rows selected.
Elapsed: 00:00:00.12
ETLTEST@orcl> EXPLAIN PLAN
2 FOR
3 SELECT * FROM TEST12 WHERE TNAME='MYDATA10';
Explained.
Elapsed: 00:00:00.01
ETLTEST@orcl> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 1497227925
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 76837 | 975K| 592 (7)| 00:00:08 |
|* 1 | TABLE ACCESS FULL| TEST12 | 76837 | 975K| 592 (7)| 00:00:08 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TNAME"='MYDATA10')
13 rows selected.
Elapsed: 00:00:00.03
ETLTEST@orcl> EXPLAIN PLAN FOR
2 SELECT COUNT(*) FROM TEST12 WHERE TNAME='MYDATATEST';
Explained.
Elapsed: 00:00:00.01
ETLTEST@orcl> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 4254084898
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX RANGE SCAN| TEST12_IDX | 927 | 8343 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TNAME"='MYDATATEST')
14 rows selected.
Elapsed: 00:00:00.01
ETLTEST@orcl> EXPLAIN PLAN FOR
2 SELECT * FROM TEST12 WHERE TNAME='MYDATATEST';
Explained.
Elapsed: 00:00:00.00
ETLTEST@orcl> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 2226945981
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 927 | 12051 | 21 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST12 | 927 | 12051 | 21 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST12_IDX | 927 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TNAME"='MYDATATEST')
14 rows selected.
Elapsed: 00:00:00.01
Edited by: PrazY on Sep 14, 2009 4:44 PM
Added version detail