Re: Alternative to select count(*) from table ?

From: Ashish Mittal <mittalashish_at_yahoo.com>
Date: Thu, 14 Mar 2002 15:49:22 GMT
Message-ID: <6E3k8.38692$q2.5462_at_sccrnsc01>


Another "better" alternative is to create a materialized view - refresh on commit - as the number of rows. Then there should be no locking issues since the database will maintain this count for you.

Ashish
"Ashish Mittal" <mittalashish_at_yahoo.com> wrote in message news:CC3k8.38684$q2.5792_at_sccrnsc01...
> Is it possible to create a table with the max number of rows, with a field
> "active/inactive", and allow onlyupdates - no inserts?
>
> That should get rid of the check.
>
> Ashish
> "Johan" <jox123_at_hotmail.com> wrote in message
> news:7257f2d6.0203130700.24eb027a_at_posting.google.com...
> > Hi all,
> >
> > Hope that someone can help me with this question.
> >
> > I have a table where I need to keep track of the number of rows it
> > contains, because there is a limit on the maximum number of rows
> > allowed in it. Now I do a select count(*) from table; before every
> > insert. With 5 million rows in the table the count takes around 10
> > seconds. (I can see from the execution plan that it is counting the
> > number of rows in the primary index of the table).
> >
> > So, I was wondering if there is a better (and faster) alternative to
> > my select count(*) available? I would rather not maintain a counter by
> > myself in another table for example. The table in question could
> > contain up to millions of rows.
> >
> > Is the number of rows in a table to be found as metadata from
> > somewhere for example?
> >
> > I'm using Oracle 8.1.7 Standard Edition on Solaris 8.
> >
> > Any help is greatly appreciated.
> >
> > /Johan
>
>
Received on Thu Mar 14 2002 - 16:49:22 CET

Original text of this message