Re: A join question

"Stan Brown" <stanb_at_panix.com> wrote in message news:9lk4sr$2o2$1_at_panix1.panix.com...
> In <998084079.11287.0.pluto.d4ee154e_at_news.demon.nl> "Sybrand Bakker"
<postbus_at_sybrandb.demon.nl> writes:
>
>
> >"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
>
> Ah, stupid of me.
>
> Will this address teh multiplicity of constraint names mentioned in my
> other thread on this?
>

I think so. A cartesian product is the result of all possible combinations of resultsets from individual tables.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Fri Aug 17 2001 - 18:07:39 CDT