Re: is it possible in pl/sql?

On Thu, 10 Feb 2005 09:17:15 -0500, Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us> wrote:
> David,
>
> Just try running an explain plan against your select count statement. Run
> it against a table with a primary key, and one without one. Run it against
> a table with a unique key but no primary key.
>
> What you will find is that Oracle will pick the quickest way to get the
> result. If there is a PK or Unique Key, it will scan the index - because
> the column is NOT NULL, it will have an entry for every row.
>
> If the table does not have any unique index to use, it will count the rows
> in the table.

on a similar note it will make use of a bitmap index and potentially return the result very fast on very large tables if it can. user @ orcl>drop table t1;

Table dropped.

user @ orcl>create table t1
  2 as
  3 select rownum id,a.*
  4 from all_objects a, all_objects b
  5* where rownum < 1000001;

Table created.

user @ orcl>alter table t1
  2 add constraint pk_t1 primary key (id);

Table altered.

user @ orcl>create bitmap index btmp_idx   2 on t1(object_type);

Index created.

user @ orcl>exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

user @ orcl>explain plan for
  2 select count(*)
  3 from t1;

Explained.

user @ orcl>set lines 120
user @ orcl>@show_plan_9i

PLAN_TABLE_OUTPUT

| Id  | Operation                     | Name     | Rows  | Bytes |
Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |       |   
22   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |          |     1 |       |    
       |          |
|   2 |   BITMAP CONVERSION COUNT     |          |  1008K|       |   
22   (0)| 00:00:01 |
|   3 |    BITMAP INDEX FAST FULL SCAN| BTMP_IDX |       |       |    
       |          |
------------------------------------------------------------------------------------------

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l