Re: Data Dictionary access for Oracle

Oracle already maintains similar information in user_dependencies. That view probably is optimized.
Maybe you should look into using that.
Other than that as the dictionary is using RBO probably you FROM list needs to be
> USER_CONS_COLUMNS rcol
> USER_CONS_COLUMNS col,
> USER_CONSTRAINTS rcon,
> USER_CONSTRAINTS con,

Which is driving table last.

Hth,

--
Sybrand Bakker, Oracle DBA
Michael Ju. Tokarev <mjt_at_tls.msk.ru> wrote in message news:3867B72B.90683B2C_at_tls.msk.ru...
> Hi here!
>
> Just one question -- if someone knows how to do this.
>
> For some app I need to access information about cross-table references
> (foreign keys). This info stored in data dictionary (interesting views
> are user_constraints and user_cons_columns). Exactly I need a list of
> tables that references to given one, together with columns.
> For easier access, I created view like this (I used USER_xxx here just for
> simplicity):
>
> CREATE OR REPLACE VIEW USER_TABLE_REFERENCES
> AS
> SELECT
> rcon.CONSTRAINT_NAME, -- name of constraint
> con.TABLE_NAME REFERENCED_TABLE_NAME, -- what foreign table is
> col.COLUMN_NAME REFERENCED_COLUMN_NAME, -- to what column this reference
is
> rcol.TABLE_NAME REFERENCES_TABLE_NAME, -- what table made reference to
other one
> rcol.COLUMN_NAME REFERENCES_COLUMN_NAME -- and what column
> FROM
> USER_CONSTRAINTS con,
> USER_CONS_COLUMNS col,
> USER_CONSTRAINTS rcon,
> USER_CONS_COLUMNS rcol
> WHERE
> rcon.CONSTRAINT_TYPE = 'R' and
> rcon.R_CONSTRAINT_NAME = con.CONSTRAINT_NAME and
> col.TABLE_NAME = con.TABLE_NAME and
> col.CONSTRAINT_NAME = con.CONSTRAINT_NAME and
> rcol.TABLE_NAME = rcon.TABLE_NAME and
> rcol.CONSTRAINT_NAME = rcon.CONSTRAINT_NAME and
> rcol.POSITION = col.POSITION
> ;
>
> Typical query is "SELECT * FORM USER_TABLE_REFERENCES WHERE TABLE_NAME =
:tname".
>
> That all is perfect and solves my task, but with a "little exception":
> it is just too slow. Query given in example executes about 2 secs,
> and select without WHERE part (I have about 150 tables in my schema)
> executes about 15(!) minutes ! -- this is unacceptable behavor.
>
> So -- is it possible to speed up this view (while preserving information
> it returns)? I know about underlying tables (sys.con$, sys.col$ etc) on
that
> USER_xxx views are based, and already have implementation based directly
> on this, and it gives me good timing results, but this is a bit ugly at
least --
> to use sys's objects (and this objects are not constant in different
> Oracle versions). And I don't want to give access to sys objects...
>
> The same question is about primary key constraints -- it is too slow if
based on
> USER_CONSTRAINTS and USER_CONS_COLUMNS, it is fast if based on sys.xxx
tables.
> Also for unique constraints...
>
> Ideas?
>
> Regards,
> Michael.
Received on Mon Dec 27 1999 - 14:53:11 CST