I want to find a count(*) of a query..Is it better to find using groupby function or find the distinct values and then find the count(*) of each distinct values without using groupby function..Which one is better in terms of performance..We are having more than 20,000,000 records in each table..
Unfortunately, the 2 statements are functionally different.
The first will return the total count of distinct Departments. The second will return a recordset containing a count of employees in each department.
To get equivalent statements, you would need to do
SELECT COUNT(*) FROM
SELECT 1 FROM scott.emp GROUP BY deptno
Now, the difference between the statements becomes that the COUNT(DISTINCT) will use and index, assuming one exists, while the SELECT 1 will do a table scan.
In an OLTP environment, it is usually preferable to go after the index, because it is more likely that more of the index is in memory than the table itself. Plus, it takes up less memory. However, there are multiple other factors, including how much memory you have, how much of the table is usually in memory vs. how much of the index, what your block size is, how big your multi-block count is, etc. The best idea is to test it yourself, unfortunately. In either case, however, it should be very fast. A single table scan is usually nothing to fret over, although you will most likely be crowding other items out of the cache. If in doubt, the COUNT(DISTINCT) is probably your best bet.
I stand corrected, you are right. I should have test my answer before posting it, sorry :(.
As regarding to what is faster: performing a table scan or an index scan, I aggrea with you that it all depends on many factors. IMHO on larger tables it is *generaly* more eficient to scan a table than to do so on index. I do not agrea with you that table scan will flush out many objects from a buffert cache. It will not, because it will use only a small portion of buffer pool and it will roll table rows in that small number of buffers. However if the system will perform an index range-scan on whole index, than this *will* flush out many other objects from cache, eventually it can occupy the whole cache! The exception to all this is if optimizer chooses to perform FFI (fast full index scan) operation, which behaves much like an ordinal table scan.
Also one must be aware how physical I/O operations are performed during a table scan and an index scan. When table is scanned, the reads are done in batches of blocks (DB_FILE_MULTIBLOCK_READ_COUNT) and the disk head movement ids relativly steady as it reads data in extents sequentialy. On the other hand, disk blocks are always read one at the time (again, FFI is an exception), and sequentialy regarding index values, not regarding the block layout! That means that disk head have to jump much more forth and back, resulting in much slower reads.
In most cases on very large datasets FFI is by far the fastest operation when COUNT(*) without a WHERE condition have to be executed. If FFI can not be executed, then usually table scan will perform better than index scan.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Actually, I tried the SELECT (DISTINCT) solution with an INDEX_FFS hint.
- The plan was larger - 3 sort operations (?)
- The first run seemed to do more work than the regular I, leading me to believe that it had to read more of the index than the other statement
NOTE: I am on my own, isolated box with lots of cache and I had already run both of the other statements a few times. Therefore, I would have expected the whole index and table (or as much as necessary) to already be in the cache.
NOTE also that I was testing on a 4 million record table.
- Therefore, this leads me to believe that the regular INDEX scan did not need to read as much as the FFI, since, as I said, I had already done a regular index.
- So, on the first run, the FFI took a good bit longer and did a lot of physical reads. (well, considering it was multi-block, it wasn't *that* many, but still - I expected a 0)
- However, on the second run, the FFI completed faster than either of the other 2. This would further tell me that the FFI did, indeed, pull all that it needed into cache, and not just through a scrolling buffer window. The same thing had occurred with the FULL table scan - ran *much* faster the second time, and with no physical reads.
So, it appeared to me that each statement pulled everything it needed into cache. Therefore, the table would take up the most and crowd out the most. The FFI seemed to pull more than the I, and cost more on the initial run, which is why I didn't really bring that one up - The regular I seemed to do the least, hold the least amount of cache and ran (almost) the fastest.
Mind you, I am not an expert on the cache - I am only extrapolating from the things I saw, so I could certainly be wrong. I'm definitely interested in figuring out more about this, so keep the good info coming!
BTW, for reference, all of them completed in around 1/4 second on the second run - nothing to lose sleep over no matter how you slice it. :)
I'm sure wWhat I have written about occupying space in buffer cache is true for Oracle7, I'm almost certain taht it is more or less the same for Oracle 8.0, but I know they've made significal changes regarding the LRU algorithm in 8i, so my remarks might actualy not be valid for 8i. I'll have to check that myself (when the time permits...). Quite some time ago (it must have been 7.3.4) I've done some tests. I've totaly filled my buffer cache by scaning large portions of various indexes and tables via index-read ROWIDs. Then I've performed a full scan on a large table that have had no block in buffer cache. After this I checked the contents of buffer cache (V$BH) and found out that only a smal portion of cache was filled by blocks of a table I've scaned. As far as I remember I haven't performed full scan on freshly started database, so it might be true that if large amount of buffer cache is free the table scan will simply use all those free buffers.
In any case I don't think on busy system Oracle allows that one huge table full scan could simply flush out complete buffer cache (if the has not been declaerd as CASHEd table). It simply wouldn't be logical performance-wise. They have preventing this to hapen in earlyer release, so I don't think they would allow it in 8i.
But as allways, I could be wrong....
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
I can't believe you're still running 7.x. You poor soul :) - you're too good a dba to be stuck maintaining an antique :(
Interesting info, again - Thank you.
My cache definitely should not be very empty - I am running 700-statement tests all the time. And these statements hit all of the big tables in my database (24,22,21,20 Million rows, etc.). So though the box is only mine, I do at least try to give it a workout now and then :). I'll have to start looking at the V$BH view to get better aquainted with the actual workings of the cache.
What you say makes sense, though - wouldn't be terribly prudent to try to pull a 2G table into cache, for example, so some kind of re-use is obviously necessary - I just need to figure out how that jives with my observed behavior.
Thanks again for the excellent info,
BTW - I'm thinking MSDWSAM123 got a whole lot more info that he was counting on :)
Replacement algorithm for buffer cache in 8.1.3
The new replacement algorithm for managing buffers in the cache is no
longer LRU based. Instead, it is a variation of the "touch count", or
"clock frequency" scheme. It is described below assuming that all
are linked on a single list (different from implementation, which has
several lists, for keeping track of buffers that are absolutely useless,
those that need to be written, etc).
Basic description assuming all buffers on a single list
Buffer's have a "touch count" field that keeps track of the number of
touches (hits) a DBA has encountered while it is in the cache. Hits that
are very "close" (within _db_aging_touch_time seconds) are counted as 1
You can see the current number of touches per buffer by dumping the
You might alternatively query x$bh as it has been updated to show the
SQL> SELECT TCH FROM SYS.X$BH WHERE FILE#=4 AND DBABLK=2794;
1 row selected
What happens when a buffer is touched (hit) in cache?
On a hit, the following steps occur:
- Assuming that _db_aging_touch_time seconds has passed since we last
incremented the touch count, the touch count is increased by 1.
- The buffer is NOT moved from its current position in the list. That is
it stays where it is.
- Incrementing the touch count is done without any latching activity.
Thus, we may miss an increment to touch count occasionally as a
So basically, we increment touch count based on the time elapsed since
we last incremented it.
How is a victim selected for replacement?
If we need to read a buffer into the cache, we must first identify a
"victim" to be replaced:
- Victims are selected by scanning the list from the tail of the list.
IF ( touch count of scanned buffer > _db_aging_hot_criteria ) THEN
Give buffer another chance (do not select as a victim)
IF (_db_aging_stay_count >= _db_aging_hot_criteria) THEN
Halve the buffer's touch count
Set the buffer's touch count to _db_aging_stay_count
Select buffer as a victim
Where is a new buffer placed in the list?
Unlike LRU, where a buffer with a new DBA is always moved to the top of
the list (except for long table scans), with this scheme a buffer is
inserted in the "middle" of the cache. "Middle" is specified as a
percentage of the list, and is set for the 3 buffer pools using the
To understand this, think of the list being divided up into a "hot"
portion (buffers above the "middle" point) and "cold" buffers (those
that are below the middle point). Here, "hot" and "cold" are very loose
terms since it is possible for buffers with a very high touch count to
trickle down to the cold region, if they are not touched since their
series of touches.
Thus, a buffer read into the default cache will (by default) be
in the middle of the cache (_db_percent_hot_default = 50 by default) .
The reasoning for putting the buffer in the middle, as opposed to the
top, is to make this buffer earn its touch count by getting a few hits
before getting a chance to go to the top of the cache.
Actual Implementation Details
The actual implementation is a variation of the algorithm to account
for maintaining buffers to be written in separate lists (regular
writes versus ping writes versus writes due to reuse range and reuse
Also, once buffers are written, they are usually "useless", in the sense
that they have aged out. Such buffers are kept aside on a AUXiliary list
rather than the main list, provided there are no waiters (or
waiting for this buffer. If there are foregrounds that want to access
these buffers, then they are moved to the "middle" portion of the main
after re-setting their touch count.
The victim selection described earlier always prefers a buffer in the
auxiliary list over a buffer on the main list. If there are no buffers
on the auxiliary list, then it uses the algorithm described earlier for
the buffers on the main list.
Also, there is some special processing for CR buffers, which allows
buffers to be "cooled", and put at the tail of the main list after
their temperature below the "threshold" (parameters _db_aging_freeze_cr
Specific cases - full table (long) scans
From testing it appears that blocks read by a full scan of a long table
(see <Parameter:small_table_threshold>) will be placed at the end of the
list. Thus, these buffers will imediately become victims for
It was observed that during such a scan, every
db_file_multiblock_read_count's worth of blocks were placed at the end
of the list (LRU flag "moved_to_tail" bit was set) and were replaced by
the next db_file_multiblock_read_count's batch of blocks.
This effectively mimics the pre-8i behaviour for full table (long) scans
and avoids the cache being flushed by a huge table scan.
Specific cases - full table (short) scans and cached tables
The buffers are not moved to the tail of the list, and thus do not
immediate candidates for replacement.
Summary of parameters
Parameter name Default Description
_db_aging_hot_criteria 2 Used to decide victim selection
_db_aging_stay_count 99 Touch count set to this if
(low value) low value < threshold during
_db_percent_hot_default 50 % Divides default cache into hot
(middle point) and cold regions; specifies
in the cache a new buffer is to
_db_percent_hot_keep 0 % Same as above, but for keep pool
_db_percent_hot_recycle 0 % Same as above, but for recycle
_db_aging_touch_time 3 secs Touch count not incremented if
(small interval) the buffer is touched within
_db_aging_touch_time seconds of
the last touch