Consistent gets vs physical reads
588770Jul 7 2010 — edited Jul 14 2010Hello,
I am doing some autotrace on one of my queries. Oracle seems to favor a full table scan which has a lower consistent gets rating and some physical reads.
If I force it to use a index I get double the amount of consistent gets and 0 physical gets. Which should I prefer?
Yes, as the blocks get cahced the full table scan might lower the physical gets but this is more for my knowledge, how many consistent gets is a physical read "worth" ?
This table might have 10 million-100 million rows in it. And I want to retrieve 100 - 100k rows from it using this query (join with a temporary table). Can the optimizer use different plans when expected number of rows varies even if the SQL syntax is the same?