Re: Data Dictionary access for Oracle

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.
Received on Tue Dec 28 1999 - 07:25:40 CST