"Daniel Roy" <danielroy10junk_at_hotmail.com> wrote in message
news:3722db.0309260603.17dd078a_at_posting.google.com...
> > I rather doubt there is one for Oracle. If there is a primary key or
> > otherwise not null index, it could be used in the count(*) and make
> > it faster than a table scan.
>
> I created a test case to see if Oracle would use an index to count the
> number of records, and it seems that we need to give a hint, otherwise
> it just uses a FTS. Note that I use CBO on 9.2:
>
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
>
> SQL> create table tst (x char(1));
>
> Table created.
>
> Elapsed: 00:00:01.02
> SQL> set timing off
> SQL> truncate table plan_table;
>
> Table truncated.
>
> SQL> explain plan for
> 2 select count(*) from tst;
>
> Explained.
>
> SQL> select * from table(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------
>
> -------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)|
> -------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | | 3
(34)|
> | 1 | SORT AGGREGATE | | 1 | |
> |
> | 2 | TABLE ACCESS FULL | TST | 41 | | 3
> (34)|
> -------------------------------------------------------------------------
>
> 8 rows selected.
>
> SQL> alter table tst add primary key(x);
>
> Table altered.
>
> SQL> truncate table plan_table;
>
> Table truncated.
>
> SQL> explain plan for
> 2 select count(*) from tst;
>
> Explained.
>
> SQL> select * from table(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------
>
> -------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)|
> -------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | | 3
(34)|
> | 1 | SORT AGGREGATE | | 1 | |
> |
> | 2 | TABLE ACCESS FULL | TST | 41 | | 3
> (34)|
> -------------------------------------------------------------------------
>
> 8 rows selected.
>
> SQL> truncate table plan_table;
>
> Table truncated.
>
> SQL> select index_name from user_indexes where table_name = 'TST';
>
> INDEX_NAME
> ------------------------------
> SYS_C003106
>
> SQL> explain plan for
> 2 select /*+ index(tst, SYS_C003106) */ count(*) from tst;
>
> Explained.
>
> SQL> select * from table(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------
>
> --------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)|
> --------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | | 27
(4)|
> | 1 | SORT AGGREGATE | | 1 | |
> |
> | 2 | INDEX FULL SCAN | SYS_C003106 | 41 | | 27
> (4)|
> --------------------------------------------------------------------------
>
> 8 rows selected.
>
> Therefore, if you have an index on non-null field (such as a one
> associated to a PK), it's probably faster to include it in a hint as I
> did, in order to replace the FTS by an index full scan. Note also that
> presumably the "sort aggregate" step would be avoided if the index is
> defined as unique (I didn't test that).
>
Hi Daniel,
Perhaps the following modifications to your test case might prove
constructive:
- Create a table that has a decent number of rows. The CBO is going to look
favourably to FTS of minute tables.
- Analyze the tables to give the CBO half a decent chance
SQL> create table bowie as select * from dba_source;
Table created.
SQL> alter table bowie modify (owner constraint bowie_nn not null);
Table altered.
SQL> create index bowie_idx on bowie(owner);
Index created.
SQL> analyze table bowie compute statistics;
Table analyzed.
SQL> set autotrace traceonly
SQL> select count(*) from bowie;
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'BOWIE_IDX' (NON-UNIQUE) (Cost
=34 Card=145249)
And not a hint in site ....
Beware of assuming action A caused result B. It could will be that unknown
or unsuspecting action or effect C, D, E, ......
Generally, the CBO will choose the cheapest path, give a chance (be it FTS,
Index Scan, whatever )
Cheers
Richard
Received on Fri Sep 26 2003 - 09:47:05 CDT
