Skip to main content
added 228 characters in body
Source Link
Andrew Sayer
  • 2.7k
  • 1
  • 7
  • 9

If you want to use ALL_VIEWS in some sort of benchmark then you could materialize it into a table and count that instead.

create table all_views_mat as select * from all_views;

select count(*) from all_views_mat;

If you want to use ALL_VIEWS in some sort of benchmark then you could materialize it into a table and count that instead.

create table all_views_mat as select * from all_views;

select count(*) from all_views_mat;
Source Link
Andrew Sayer
  • 2.7k
  • 1
  • 7
  • 9

SELECT COUNT(*) FROM SYS.ALL_VIEWS;

Is not a simple query. ALL_VIEWS is a very complicated view, it has many joins requiring special logic to determine what the calling user is actually allowed to see.

You can use all_views to see the logic behind all_views, this is how it looks on 19c.

select OWNER, VIEW_NAME, TEXT_LENGTH, TEXT, TEXT_VC,TYPE_TEXT_LENGTH, TYPE_TEXT,
       OID_TEXT_LENGTH, OID_TEXT, VIEW_TYPE_OWNER, VIEW_TYPE, SUPERVIEW_NAME,
       EDITIONING_VIEW, READ_ONLY,  CONTAINER_DATA, BEQUEATH,
       ORIGIN_CON_ID, DEFAULT_COLLATION, CONTAINERS_DEFAULT, CONTAINER_MAP,
       EXTENDED_DATA_LINK, EXTENDED_DATA_LINK_MAP, HAS_SENSITIVE_COLUMN
from int$dba_views
where (OWNER = SYS_CONTEXT('USERENV', 'CURRENT_USER')
       or OBJ_ID(OWNER, VIEW_NAME, 4, OBJECT_ID) in
            (select oa.obj#
             from sys.objauth$ oa
             where oa.grantee# in ( select kzsrorol
                                         from x$kzsro
                                  )
            )
        or /* user has system privileges */
           /* 4 is the type# for Views. See kgl.h for more info */
          exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -397/* READ ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                  )
      )

Simple enough? Well, int$dba_views is actually a view itself, so lets go deeper, this view is defined by

select u.name, u.user#, o.name, o.obj#, o.type#, v.textlength, v.text,
       getlong(1, v.rowid),
       t.typetextlength, t.typetext,
       t.oidtextlength, t.oidtext, t.typeowner, t.typename,
       decode(bitand(v.property, 134217728), 134217728,
              (select sv.name from superobj$ h, sys."_CURRENT_EDITION_OBJ" sv
              where h.subobj# = o.obj# and h.superobj# = sv.obj#), null),
       decode(bitand(v.property, 32), 32, 'Y', 'N'),
       decode(bitand(v.property, 16384), 16384, 'Y', 'N'),

       decode(bitand(v.property/4294967296, 134217728), 134217728, 'Y', 'N'),
       decode(bitand(o.flags,8),8,'CURRENT_USER','DEFINER'),
       case when bitand(o.flags, (65536+131072+4294967296))>0 then 1 else 0 end,
       to_number(sys_context('USERENV', 'CON_ID')),
       nls_collation_name(nvl(o.dflcollid, 16382)),
       -- CONTAINERS_DEFAULT
       decode(bitand(v.property, power(2,72)), power(2,72), 'YES', 'NO'),
       -- CONTAINER_MAP
       decode(bitand(v.property, power(2,80)), power(2,80), 'YES', 'NO'),
       -- EXTENDED_DATA_LINK
       decode(bitand(v.property, power(2,52)), power(2,52), 'YES', 'NO'),
       -- EXTENDED_DATA_LINK_MAP
       decode(bitand(v.property, power(2,79)), power(2,79), 'YES', 'NO'),
       -- HAS_SENSITIVE_COLUMN
       decode(bitand(v.property, power(2,89)), power(2,89), 'YES', 'NO')
from sys."_CURRENT_EDITION_OBJ" o, sys.view$ v, sys.user$ u, sys.typed_view$ t
where o.obj# = v.obj#
  and o.obj# = t.obj#(+)
  and o.owner# = u.user#

Guess what, sys."_CURRENT_EDITION_OBJ" is also a view which has complex filters. I won't keep pasting as you can follow the trail on your own 12c installation (and it will likely be slightly different).

But the important thing to note here is that actual usage of ALL_VIEWS is fast when you are looking up a view by name, it is not fast to compute every single view that you might be able to use (and that is reasonable as it's not something you would ever need to do in a hurry).