| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Data Dictionary access for Oracle
Sybrand Bakker wrote:
>
> Oracle already maintains similar information in user_dependencies. That view
> probably is optimized.
Nope, this is how objects depend on other objects for DDL. For example, view depends on underlying tables, procedure depends on tables/views/procedures it references and so on. There is nothing about foreign keys here: SQL> descr user_dependencies
Name Null? Type ------------------------- -------- -------------------------------- NAME NOT NULL VARCHAR2(30) TYPE VARCHAR2(12) REFERENCED_OWNER VARCHAR2(30) REFERENCED_NAME VARCHAR2(64) REFERENCED_TYPE VARCHAR2(12) REFERENCED_LINK_NAME VARCHAR2(128) SCHEMAID NUMBER DEPENDENCY_TYPE VARCHAR2(4)
SQL> select distinct type from user_dependencies TYPE
Yes, it "helps" -- query will take 14min and 30 secs instead of 15 mins :^) that maybe due to other activities on server or other user's activities. (My data dictionary is all in cache, and oracle eats only cpu time - on PentiumII/400 mhz machine; there are about 14000 records in dba_objects).
> Hth,
>
> --
> Sybrand Bakker, Oracle DBA
Anyway, thanks for a try...
> 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 - 15:12:23 CST
 |
 |