Data Dictionary access for Oracle

From: Michael Ju. Tokarev <mjt_at_tls.msk.ru>
Date: Mon, 27 Dec 1999 21:59:55 +0300
Message-ID: <3867B72B.90683B2C@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 - 12:59:55 CST