-
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.
-
**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.
-
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.
-
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.
-
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.
-
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.
Is not it we always do full table scan?
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|