-
"select count(*)" is poor on cached table
Hi all,
Any idea why "select count(*)" is poor on cached table?
Thanks
SQL>alter table EBUSS1.RATE_ENGINE cache ;
Table altered.
Elapsed: 00:00:00.48
SQL>select count(*) from EBUSS1.RATE_ENGINE ;
COUNT(*)
----------
792856
1 row selected.
Elapsed: 00:03:39.66
SQL>alter table EBUSS1.RATE_ENGINE nocache ;
Table altered.
Elapsed: 00:00:00.48
SQL>select count(*) from EBUSS1.RATE_ENGINE ;
COUNT(*)
----------
792856
1 row selected.
Elapsed: 00:01:54.57
Giani
-
I'm not sure, but the reason the second one is faster may be because it does not have to reparse the SQL?
-
CACHE is not a magic go-faster command. Here is what I think happened ...
i) You set the table to CACHE, requesting that blocks me kept in memory after they have been read.
ii) You performed a full table scan, requiring that the blocks be read from disk, hence the long query time, but Oracle obviously will not then purge the blocks from memory straight away.
iii) You set the table to NOCACHE
iv) The next full scan found the blocks in memory, hence the short query time
-
I think Slimdave is probably right (this happens often).
He is also right to say "CACHE is not a magic go-faster command". It only increases the PROBABILITY of data from a full table scan staying in buffers. If you need ALL 3/4 mio rows kept in memory you might want to rethink design . . . ?
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Also CACHING is meant for small lookup tables, no point on flooding the Buffer Cache with unneeded information via a Full Table Scan on a large table. I also agree with Slim Dave, and if it wasn't inthe Buffer Cache (Which it was), it'd be in the Unix Buffer Cache or Disk Cache.
OCP 8i, 9i DBA
Brisbane Australia
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
|