Re: Views

From: Guy Harrison <gharriso_at_werple.net.au>
Date: 1997/04/03
Message-ID: <01bc407b$527b5220$792a11cb@gharriso>#1/1

Michael Ho <infoage_at_hk.super.net> wrote

> COUNT(*) don't use index, it use full access instead.

The cost based optimizer will definitely use an index to perform a count(*) if it calculates that the index retrieval will be quicker. The rule based optimizer will not use an index unless a WHERE clause requires it to.

-- 
Guy Harrison

gharriso@werple.net.au || http://werple.net.au/~gharriso || 613 419377964



Michael Ho <infoage_at_hk.super.net> wrote in article
<3343DEBA.384F_at_hk.super.net>...


> Guy Harrison wrote:

> > 

> > Ken,

> > 

> > What might be happening is that the optimizer is using a unique index

 to


> > perform the count(*) queries on table1 and table2.  When you create a

 union


> > view,  the optimizer can't resolve this using just the indexes and so

 has


> > to perform full table scans instead of full index scans.  I did a quick

> > test and this is what happens on my 7.2.3 system.

> > 

> 

> COUNT(*) don't use index, it use full access instead.

>

Received on Thu Apr 03 1997 - 00:00:00 CST