| 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
Michael Ju. Tokarev wrote:
>
> Hi!
> This is a reply to my own post...
>
> Here is a little conclusion (solution) for this problem.
> Seemed that dictionary views can't be used for this purpose -- it is veeery slow.
> The quick and dirty hack -- create this views based on sys objects, just
> like other views created by catalog.sql, and grant access to them to the same
> users that other views granted (to public). For each database version where should
> be one version of that views, just like other catalog's views...
> This views based on sys.con$, sys.col$, sys.obj$ tables are a bit complicated,
> but uncomparable faster (about 1000 times faster!).
>
> "Michael Ju. Tokarev" wrote:
> >
> > 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.
I doubt that the "$" tables will ever lose columns from version to version, maybe just gain them...I've seen the X$ tables change remarkably but things like OBJ$ etc seem "fairly" static...
... nice and vague I know...
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Wed Dec 29 1999 - 00:06:00 CST
 |
 |