Re: Count(*)ing nested tables


"John Currier" <md4curvs@xxxxxxxxx> wrote in message
news:1122386424.873791.220540@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> > When you mention * in the select-list of a table with nest tables, it
> > tries to expand all the fields in nested table(s) too, which results in
> > a messy multi-dimensional result set for each row, and seems to be
> > ambiguous in the context of COUNT().
> >
> > I don't have any direct experience with nested tables, but I'm supposing
> > that you could try "SELECT COUNT(1) FROM WHATEVER". That should be a
> > non-NULL value for each row of the non-nested table, without attempting
> > to reference any of the nested tables within it. The value of 1 is
> > arbitrary; any other non-NULL constant value would work equally well,
> > since we're only counting them.
> >
> > You could probably also do COUNT(pk) where pk is the name of a non-NULL
> > column in the table. But for when you don't know the name of a non-NULL
> > column (if any exists), stick with COUNT(1).
>
> Interesting. Your comments are making me think about how to optimize
> that select.
>
> So, what's the most efficient way to get the number of rows in an
> arbitrary table, given that I know the primary key and index details?
>
> Thanks,
> John
> http://schemaspy.sourceforge.net
>
Benchmark it. It will differ by RDBMS. In all likelihood using the primary
key in the count would probably be most efficient. Usually there is an
index on the primary key and the optimizer might use the index to get the
row count.
Jim


.

Relevant Pages

  • Re: flagship sunk
    ... The only way to expand the herd is to shag the cow. ... columns in a table except the primary key could/should be nullable. ... and you don't have a parent row for it, just do an insert into the parent ...
    (comp.databases.informix)
  • Re: modeling either/or relationship...
    ... >> identifier (VIN), and add two mutually exclusive sub-classes, Sport ... > allowing a reference to different columns than those of a primary key? ...
    (comp.databases.theory)
  • Re: pro- foreign key propaganda?
    ... Foreign keys are a must and I'm sure your database uses ... Is there simlarly a difference between a primary key and a primary key ... reference a table, one of them is chosen for all of the references. ... be called "declaring a primary key" in some environments. ...
    (comp.databases.theory)
  • Re: Database design, Keys and some other things
    ... >I am putting forward for discussion that Codd's Information Principle ... >immutable reference for it, ... >guarantees primary key mutability, but is externalised from the data, ... >references to prevent orphan tuples. ...
    (comp.databases.theory)
  • Re: OIDs vs Relational Keys?
    ... You are getting confused as to the purpose and usefulness of a surrogate key, we use auto-numbering schemes like the IDENTITY 'property' to easily create a surrogate key - the natural 'primary key' is just 'meta' data within the table, it should not be duplicated around the database schema because if it should change you run into all sorts of serious situations within the database and also within the application, here are some issues.... ... Serious locking throughout the schema which will most likely cause deadlocks and severe locking contention ... If the sector_code should ever change you need only tackle one table, the rest remains as is; the application will use sector.id internally in drop downs and for reference back to the database; the suer will never see this 'id' instead the meta data of the primary key will be displayed. ...
    (comp.databases.theory)