Re: A join question

"Stan Brown" <stanb_at_panix.com> wrote in message news:9ljt02$d36$1_at_panix1.panix.com...
> In <9ljqab$n4g$1_at_panix3.panix.com> stanb_at_panix.com (Stan Brown) writes:
>
> >I am trying to obtain the "check" constraints on a given table using an
SQL
> >statement. So far I have this:
>
> >select
> >C.table_name,
> >CC.COLUMN_NAME ,
> >SEARCH_CONDITION
> >from
> >USER_CONSTRAINTS C ,
> >USER_CONS_COLUMNS CC
> >where CONSTRAINT_TYPE = 'C'
> >AND CC.TABLE_NAME = 'BRKR'
> >AND CC.TABLE_NAME = C.TABLE_NAME
> >AND CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME
> >ORDER BY CC.TABLE_NAME
>
> >Which seems to return what I need BUT for a table that shold only have 2
or
> >3 of these it returns 500+ rows (and takes a long time).
>
> >I suspect that I am dooing the join incoreclty. Any guidance oon this?
>
>
> OK, this just gets wierer. In playing around, I have determined that there
> are 551 different constraint names on this one table! Now realy there are
> only a couple here is the table create statement:
>
> CREATE TABLE brkr (
> brkr VARCHAR(5) NOT NULL ,
> brkr_level NUMBER (2,0) NOT NULL ,
> descript VARCHAR(80) NOT NULL ,
> gen NUMBER(1,0) NOT NULL ,
> bought NUMBER(1,0) NOT NULL ,
> consume NUMBER(1,0) NOT NULL ,
> meter_id NUMBER (2,0),
> CONSTRAINT brkr_index
> PRIMARY KEY (brkr, brkr_level)
> USING INDEX
> TABLESPACE CONFIG_TS ,
> FOREIGN KEY (meter_id) REFERENCES meter,
> CHECK (gen IN(0,1)),
> CHECK (bought IN(0,1)),
> CHECK (consume IN(0,1))
> )
> TABLESPACE CONFIG_TS ;
>
> And there are only 62 rows in the table, how can there be that many
> cobstraints on it?
>

You're now getting a cartesian product!
and
Look, here it is

AND CC.TABLE_NAME = 'BRKR'
AND CC.TABLE_NAME = C.TABLE_NAME should be
AND C.TABLE_NAME = 'BRKR'
AND CC.TABLE_NAME = C.TABLE_NAME Hth,

Sybrand Bakker,
Senior Oracle DBA Received on Fri Aug 17 2001 - 16:34:32 CDT