Re: Speed of DBA_TRIGGER_COLS view

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Tue, 11 Jan 2000 15:26:19 +0100
Message-ID: <85feia$66h$1@news3.isdnet.net>

select distinct c.name
from sys.trigger$ t, sys.obj$ o, sys.user$ u, sys.user$ u2,

     sys.col$ c, sys.obj$ o2, sys.triggercol$ tc
where t.obj# = tc.obj#          -- find corresponding trigger definition
  and o.obj# = t.obj#           --    and corresponding trigger name
  and c.obj# = t.baseobject     -- and corresponding row in COL$ of
  and c.col# = tc.col#          --    the referenced column
  and o2.obj# = t.baseobject    -- and name of the table containing the trigger
  and u2.user# = o2.owner#      -- and name of the user who owns the table
  and u.user# = o.owner#        -- and name of user who owns the trigger

  and u.name='FRED' and o.name='FREDSTRIGGER';

is ten times faster but it needs to have select any table privilege or select privilege on the sys tables.

--
Have a nice day
Michel

Jim Katsos <jkatsos_at_oz.quests.com> a écrit dans le message : rKze4.19227$lv5.75732_at_news-server.bigpond.net.au...
> I am running a query like this:
>
> select column_name from dba_trigger_cols
> where trigger_owner = 'FRED'
> and trigger_name = 'FREDSTRIGGER';
>
> This takes quite a long time. I tried removing the hint from the view to no
> avail. Does anyone know how I can speed up this query.
>
> --
> Jim Katsos
> Quest Software Pty Ltd
> http://www.quests.com
> tel +61 3 9811 8068
> mob 0411088578
>
>
Received on Tue Jan 11 2000 - 08:26:19 CST