1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Why Oracle optimizer use full table scan?

Discussion in 'Server Administration and Options' started by Arju, Oct 26, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    The oracle optimizer choose the best plan and execute the query according the plan. It is common to hear that my table has indexes but why oracle does not use indexes rather it is using full table scan.

    There are several reasons behind choosing optimizer full table scans.

    1)The table has no indexes within it.
    Check by,
    Code (Text):
    select index_name from dba_indexes where
    table_name='&tname'  and  owner='&onwer';
    2)Table has indexes but they are not appropriate to queries. For example in the table there is normal B-tree indexes but in the query the column used in the WHERE clause contains function.
    Check it by,
    Code (Text):
    select distinct INDEX_TYPE, INDEX_NAME from dba_indexes
    where table_name='&tname'  and  owner='&onwer';
    3)Query access large amount of data. The table has indexes but query against it select almost all of the rows. In that case optimizer might choose to full access of table.

    4)Index creation order may not appropriate. You have composite indexes on a table but in the where clause the leading column inside indexes are not used rather trailing columns are used.
    Check it by,
    Code (Text):
    SQL> select index_name, column_position, column_name
    from dba_ind_columns where index_owner='&owner' and table_name='&tname';

    5)The table is skewed. For example column gender contains value 'M' 10,000 times but value 'F' only 10 times.

    6)The table is small. If a table can read in a single I/O call, then a full table scan might be cheaper than an index range scan. Single I/O call is defined by DB_FILE_MULTIBLOCK_READ_COUNT parameter and value defined by blocks.
    Check it by,
    SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNT

    NAME TYPE VALUE
    ------------------------------------ --------------------------------- ------------------------------
    db_file_multiblock_read_count integer 16


    7)High degree of parallelism. High degree of parallelism skews the optimizer toward full table scans.
    Check it by,
    Code (Text):
    select  DEGREE from  dba_tables where table_name='&tname'  and  owner='&owner';
    8)In the query if there is no filtering then full table scan is the choice.
     
  2. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Excellent info Arju.

    Also to my knowledge, the index is picked up only if it using the index will return less than or equal to 5% of the rows.

    One more thing to add is in regard to composite indexes. A composite index is considered optimal if its leading columns are more selective than the following ones. Example to that would be:-

    create index abc_idx on abc(a,b,c);

    Here column 'a' should be more selective than 'b' and 'b' should be more selective than 'c'.

    Column 'a' is more selective than column 'b' if it has more number of distinct values(or less skewed).

    Guys, please correct me if i am wrong.

    Regards,
    Prashant
     
  3. sashraf

    sashraf Active Member

    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Hyderbad. India
    Good points.

    One thing to remember is to update stats at regular interval which can help optimizer to make correct decision while creating execution plan.

    ~Ashraf
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    I agree 100%.
     
  5. Punit

    Punit Guest

    Hi Arju,

    Nice example to explain.


    Regards

    Punit
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
  7. sandeepgujje

    sandeepgujje Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    75
    Thanks Prashant.. It helped me lot :)