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.
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.
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.
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?
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.
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.
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.
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?
Reply With Quote
Bookmarks