Full Table Scan

[ramuv]

hI ,

I AM trying to avoid full table scan on a query, though i am indexing all the columns used in the statement, it is still doing full table scan.
please can you let me know how do i proceed to stop full table scan.

ram

[chduarte]

Try to set your session to RBO and try again:

SQL> Alter session set optimizer_mode = rule;

SQL> explain plan for YOUR_QUERY;

If work by index now try to delete statistics.

[slimdave]

How many rows are you selecting, and how many rows are in the table?

[Bore]

Put the query and the execution plan here

[Kamkan]

Hi,
u can guide the optimizer to use index using the following parameters. This works even with 20 rows.

Alter session set optimizer_index_caching=90;

Alter session set optimizer_index_cost_adj=30;

Value 30 for optimizer_index_cost_adj is just to start with. Exact value could be arrived from the value of following events:

(db file scattered read/db file sequential read)*100

After setting this, execute and let us know the result.

Thanks

[suvashish]

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

[hacketta1]

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:

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.