solution
Dear BXR, 23rd June 2001 15:48 hrs chennai
The first query works out for you better the reason is as follows.
consistent gets - number of the data blocks which were accessed in the buffer cache for SQL statements, that do not modify data - just SELECT statements.
db block gets - number of the data blocks which were accessed in the buffer cache for SQL statements that modify data - INSERT, UPDATE, DELETE and SELECT FOR UPDATE statements.
physical reads - number of the data blocks that where read from the disk.
The sum of consistent gets and db block gets is the number of the logical reads.
The buffer hit ratio is:
(logical reads - physical reads) / logical reads
or
(consistent gets + db block gets - physical reads) / (consistent gets + db block gets)
or
Hit Ratio= 1-(physical reads/(db block gets+consistent gets)
The total of DB block gets and consistent gets gives the total number of requests for data from memory.
Hit ratio value includes requests satisfied by access to buffers in memory and request that cause physical I/O.
So more requests satisfied from memory is better than physical reads to be more.
You can check this with the above formula to see both the conditions what is the hit ratio for your Queries.
Which ever gives you high hit ratio is the better Query to Use and i am sure you would have seen the first query to be best with high Hit ratio.
Cheers
Padmam
Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it