Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Joining data tables and system tables

Re: Joining data tables and system tables

From: Mikito Harakiri <mikharakiri_at_yahoo.com>
Date: 27 Dec 2002 16:54:40 -0800
Message-ID: <bdf69bdf.0212271654.62c172f1@posting.google.com>


pbrazier_at_cosmos-uk.co.uk (Paul) wrote in message news:<51d64140.0212270750.236c1839_at_posting.google.com>...
> Can I express this using purely relational operators?:
>
> I want to list all relations (tables) in my database, together with
> the total number of tuples (rows) in each relation.
>
> I can get the list of relations from the system catalog.
> I can use an COUNT aggregate to count the numnber of rows in a given
> relation.
>
> But how do I link the two so the DBMS does the hard work of counting
> each table for me? I know how to use procedural code, "dynamic SQL" or
> other tricks for specific DBMSs; I'm more interested from a
> theoretical viewpoint.
>
> I don't think it's possible in SQL.

It's not a SQL problem, but rather a deficiency of a specific catalog model. Suppose that you have a RDBMS designed like this:

table COLUMNS



COL_ID COL_NAME TAB_NAME
------ -------- --------
1      ENAME    EMP 
2      DEPTNO   EMP 
3      DEPTNO   DEPT

table CELLS



ROW_ID COL_ID VALUE
------ ------ -----
1      1      SMITH
1      2      10
2      1      KING
2      2      20

Now, the query you are after is:

select table, count(1)
from COLUMNS, CELLS
where columns.col_id = cells.col_id
and columns.col_id in (select max(col_id)

                       from COLUMNS
                       group by tab_name)
group by tab_name

The CELLS table is especially handy when you want to query a rare value, but have no idea what column&table might it contain. Why all values in the database can't be combined into a single table/view? Received on Fri Dec 27 2002 - 18:54:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US