|
-
if the table is too small and can fit into one single block then i think it will go for full table scan no matter all columns are indexed
This isn't entirely true; at least in 10gR1 (and I'm pretty confident I've seen the same behaviour in 9i too).
First of all the 10gR1 manaul states that the minimum database blocks in the initial extent of a segment is 2 e.g. you cannot create a table that will fit into one single block, only 2 (http://download-uk.oracle.com/docs/c...tm#sthref3591).
Secondly, in the example below, an index is still used to retrieve a single row from a 2 block table:
Code:
SQL> alter tablespace test offline;
Tablespace altered.
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
SQL> create tablespace test datafile
2 '/u01/oradata/DV/test_01.dbf' size 1M
3 extent management local uniform size 8k;
Tablespace created.
SQL> alter user austin quota unlimited on test;
User altered.
SQL> Create table t (x number) tablespace test;
Table created.
SQL> select blocks from user_segments where segment_name = 'T';
BLOCKS
----------
2
SQL> create index i on t (x);
Index created.
SQL> insert into t values (1);
1 row created.
SQL> exec dbms_stats.gather_table_stats ('AHACKET2', 'T');
PL/SQL procedure successfully completed.
SQL> explain plan for select * from t where x = 1;
Explained.
SQL> select * from plan;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3928333288
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I | 1 | 3 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"=1)
If you repeat my example, but add another column to the table, you'll probably see that a FTS is used instead. On otherwords, if oracle can get all the data it needs from just the index it will tend to favour the index even on a small table.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|