Full Table Scan
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Full Table Scan

  1. #1
    Join Date
    Sep 2002
    Posts
    59

    Full Table Scan

    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

  2. #2
    Join Date
    Dec 2006
    Location
    Brazil
    Posts
    36
    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.
    Regards,

    Carlos Duarte
    Oracle Applications DBA

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How many rows are you selecting, and how many rows are in the table?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Put the query and the execution plan here

  5. #5
    Join Date
    Jun 2006
    Location
    Chennai, INDIA
    Posts
    72

    Wink

    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

  6. #6
    Join Date
    May 2003
    Posts
    4
    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

  7. #7
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    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
  •  


Click Here to Expand Forum to Full Width