Re: newbie index question

"Phillip" <tienp_at_wholefoods.com> wrote in message news:3A8B0E1E.F2B02F22_at_wholefoods.com...
> If I have an index on multiple columns, one of which is has a not null
> constraint, can I find out how many rows are in the index by simply
> doing a select count(*) on the table? My thinking is that since the
> column doesn't allow nulls, finding out how many rows in the table will
> tell me how many rows are in the index. Please advise...
>
> --
> Phillip
>

analyze index index_name validate structure /
select * from index_stats
will return the *exact* number of keys (amongst others) This is usually quick

If you really want to go your route
a select count(*) will result in a full table scan, which will search until the highwater mark of the table. Likely to be slower. You'd better
select count(*)
from foo
where <mandatory column> > 0 or > chr(0) which will use the index.

Hth,

Sybrand Bakker, Oracle DBA Received on Wed Feb 14 2001 - 17:23:10 CST