| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Documenation for count(*) and table scans
On Sat, 18 Apr 1998 18:09:10 -0500, "Jim" <jimmorgan_at_csi.com> wrote:
>Finally, a useful suggestion. It makes since that is how to force the use
>of an index with any query operation (I have used dummy WHERE col > 0
>clauses just to force use of an index before). It just never occurred to me
>to do the same thing with a count(*) query -- I'll have to give it a try.
>
>Still no one has referred me to any Oracle documentation. How can so many
>people offer opinions on this subject but not one can point to some Oracle
>doc and say 'See, it says so right here'? I'm not faulting you, you found a
>workaround because you couldn't find any doc either. But others are telling
>me how to do it and I know at least some of those people are dead wrong.
>
>There seems to be enough interest in this topic...has anyone found any
>documentation from Oracle on the subject? Surely there must be SOMETHING...
I don't know if this kind of queries are explained anywhere in the docs explicitely, but if you know how both RULE based optimizer and COST based optimizer works (this *is* explained in the docs), then you should know when oracle can and when it can't use indexes with "SELECT COUNT(*)..." type of queries.
With RBO, oracle *can't* use existing index if leading column of the index is not properly referenced in the WHERE clause. So if you are using RBO, it'll allways take full table scan if you don't use WHERE clause (no mater if there are PK, UK, or any other indexes available).
With CBO and properly analyzed tables, oracle will use primary key's index scan with SELECT COUNT(*)... even if you don't provide any WHERE clause. IF PK is not available (there should always be, though), then it might or might not use any other available index, depending on the NULL constraint's of the indexed columns.
So the most important isue with your SELECT COUNT(*) is whether you use CBO or RBO. Again, as Andy Tasker already pointed out, RBO will use a primary key automatically if there is one! Here is a simple demonstration of this fact with table SCOTT.EMP, having primary key on column EMPNO:
SQL> SET AUTOTRACE TRACEONLY EXPLAIN;
SQL> ALTER SESSION SET OPTIMIZER_GOAL = CHOOSE;
Session altered.
SQL> ANALYZE TABLE emp DELETE STATISTICS --Force RBO 2 /
Table analyzed.
SQL> SELECT COUNT(*) FROM emp --RBO will use FULL TABLE SCAN! 2 /
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP'
SQL> SELECT COUNT(empno) FROM emp --RBO will still not use index!
2 /
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP'
SQL> SELECT COUNT(*) FROM emp
2 WHERE empno >= 0 --Now RBO will bw able to use index!
3 /
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'PK_EMP'
SQL> ANALYZE TABLE emp COMPUTE STATISTICS --Force CBO
2 /
Table analyzed.
SQL> SELECT COUNT(*) FROM emp --CBO will use PK index even
2 --there is no WHERE clause!3 /
Execution Plan
0 SELECT STATEMENT Cost=1 Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'PK_EMP'
SQL> SELECT COUNT(empno) FROM emp -- Again, CBO will use index!
2 /
Execution Plan
0 SELECT STATEMENT Cost=1 Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'PK_EMP'
>I'm curious...do you KNOW the index was used by using a database tool of
>some sort or did you surmise that because of the performance improvement? I
>think that would be a reasonable assumption to make, but I have also noticed
>that count(*) queries do not always run in the same amount of time (I assume
>the difference is because of caching). In any case, I'm going to try
>forcing the use of an index via the WHERE clause.
The most basic tool for this is a simple EXPLAIN PLAN command. Look into your SQL Reference Guide. With SQL*Plus 3.3 there is a very convinient comand to make it easier to show the access path and other statistics of your queries - it's called AUTOTRACE. Just enter "SET AUTOTRACE" to see it's syntax.
>--
>Regards,
>Jim
Regards,
Jurij Modic Republic of Slovenia jurij.modic_at_mf.sigov.mail.si Ministry of Finance ============================================================The above opinions are mine and do not represent any official standpoints of my employer Received on Sun Apr 19 1998 - 16:34:18 CDT
 |
 |