full table scan
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: full table scan

  1. #1
    Join Date
    Mar 2002
    Posts
    56
    What is full table scan?.
    What actuallt it will do?.
    If I say
    Select * from table1
    Is that it is doing full table scan?.Here it got to read each datablock which had stored any of the rows from table1.

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    **Spam Time**

    Full table scans are good, they are your freind. This will let Oracle look at all the data in a table and memorize it all. This makes queries very fast. Full table scans should only be applied to large and very large tables though, they are not as usefull on smaller tables.

    MH

    Ps. Do not beleive a single word enclosed herin.
    I remember when this place was cool.

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Mr.Hanky has completely opposite views on full table scan.

    Full table scan is good only for small tables or if the size of the table can entirely fit into SGA.

  4. #4
    Join Date
    Feb 2002
    Posts
    28

    Talking

    Mr.Hanky is just being sarcastic here.
    Hey, Mr.Hanky. Don't joke like that. People might take your word for it, they might believe you because you are an "Advisor", and get into troubles.

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    I really could not resist and I did add a disclaimer.

    Just do the opposite of what I said and you will be fine.

    MH
    I remember when this place was cool.

  6. #6
    Join Date
    Mar 2002
    Posts
    56
    but is that full table scan will read each datablock?
    irrespecrtive of whether it is good/bad,for large tables/small tables,
    i would like to know what it actually do?

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Full Table Scan reads all the blocks in the table and places them in the tail end of the LRU list, if the table is defined with NOCACHE option. Those cached data blocks may be flushed out very soon when Oracle finds no space in the SGA for the new blocks from another query.

    Full Table Scan reads all the blocks in the table and places them in the beginning of the LRU list, if the table is defined with CACHE option.

  8. #8
    Join Date
    Mar 2002
    Posts
    56
    so,
    is that only when I say
    Select * from , full table scan will be done?
    OR
    As my openion goes if I say
    Select * from where ,
    then also all the rows (data blocks to be checked) got be read to decide whether corresponding row meets the WHERE condition in the select statement or not.

    Is not it we always do full table scan?


  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Here is an example:
    Table T1
    Columns EMPNAME, EMPID

    There is an index on EMPID column.
    Table and INDEX are analyzed so that CBO uses them for optimal execution paths.

    The Query is:
    Select empid, empname
    from T1
    where empid between 2000 and 4000;

    Assume that 1000 rows are having the empid values between 2000 and 4000, and the table has totally 1300 rows.

    The optimizer calculates the index cost and table cost in order to fetch the relevant rows from the table. If the cost is very high for searching empid values in the index for the range (2000 and 4000), then it will not use the index. Hence, full table scan will be performed on the table.

  10. #10
    Join Date
    Mar 2002
    Posts
    56
    Sorry to disturb you again Mr.selvan

    So,full table scan comes into picture only when you have index created on the table(column) to make comparision.
    (That is whether to make use of index or not).
    If you do not have any index created on the table(column) and the selection criteria is not using that column,then full table scan will be done with select statements (irrespective of where condition).
    Is this what I analyzed is correct Mr.selvan?

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