Re: Record row count and execution time into table?

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: 3 Oct 2005 01:42:02 -0700
Message-ID: <1128328922.777377.51590@g49g2000cwa.googlegroups.com>

Niall Litchfield wrote:
> > Since NULLs are not stored in an index, the results might not be 100%
> > accurate if the index is used to generate the COUNT.
>
>
> If the index can't be used to satisfy the query - it won't be used.
> B*Tree indexes on nullable columns therefore won't be considered (or it
> is a bug if they are). B*Tree indexes on NOT NULL columns can be
> considred. Bitmap indexes do indicate null values.
>
> You'll have to wait till later on today for a demo - can't believe I
> trashed that database on this machine.

and the proof. my comments prefixed by *** otherwise unedited.

SQL> create table t
  2 as select * from all_source;

Table created.

SQL>
SQL> desc t;

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 OWNER                                              VARCHAR2(30)
 NAME                                               VARCHAR2(30)
 TYPE                                               VARCHAR2(12)
 LINE                                               NUMBER
 TEXT                                               VARCHAR2(4000)

SQL> alter table t modify name not null;

Table altered.

SQL> create index idx1 on t(owner);

Index created.

SQL> @gather_stats
SP2-0310: unable to open file "gather_stats.sql" SQL> @gather_user_stats

PL/SQL procedure successfully completed.

• now we have stats on the table and index.
• the index is on a nullable column

SQL> set autotrace on explain
SQL> select count(*) from t;

  COUNT(*)

---

    378898

1 row selected.

Execution Plan

---

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4276 Card=1)    1 0 SORT (AGGREGATE)

   2    1     TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=4276 Card=37789
          5)

• FTS as we might expect

SQL> create index idx2 on t(name) compute statistics;

Index created.

SQL> select count(*) from t;

  COUNT(*)

---

    378898

1 row selected.

Execution Plan

---

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=296 Card=1)    1 0 SORT (AGGREGATE)

   2    1     INDEX (FAST FULL SCAN) OF 'IDX2' (INDEX) (Cost=296 Card=
          377895)

• This time because the index is on a non-null column the count
• can use the index.

SQL> create bitmap index idx3 on t(type) compute statistcis; create bitmap index idx3 on t(type) compute statistcis

                                            *

ERROR at line 1:
ORA-00905: missing keyword

SQL> create bitmap index idx3 on t(type) compute statistics;

Index created.

SQL> select count(*) from t;

  COUNT(*)

---

    378898

1 row selected.

Execution Plan

---

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=1)    1 0 SORT (AGGREGATE)

   2    1     BITMAP CONVERSION (COUNT) (Cost=13 Card=377895)
   3    2       BITMAP INDEX (FAST FULL SCAN) OF 'IDX3' (INDEX (BITMAP
          ))

• and bitmap access is even better if available.

SQL> desc t;

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 OWNER                                              VARCHAR2(30)
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE                                               VARCHAR2(12)
 LINE                                               NUMBER
 TEXT                                               VARCHAR2(4000)

SQL>

• note that the nullability precludes the owner column
• but not the type column from being used.

Cheers

Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com Received on Mon Oct 03 2005 - 03:42:02 CDT