is it possible in pl/sql? 2005-02-10 - By Niall Litchfield
On Thu, 10 Feb 2005 09:17:15 -0500, Mercadante, Thomas F
<thomas.mercadante@(protected)> 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>@(protected)
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
|
|
