Reporting View Hierarchies

It is a characteristic of PeopleSoft that it uses lots of views to present data within the application, and frequently views reference other views.  There are examples of this design going 5 levels deep in HR and deeper in Financials.  When faced with a SQL execution plan for such a view you often wonder which view referenced which table.

However, the Oracle database describes "dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links" in the view ALL_DEPENDENCIES.

This hierarchical query (depend_heir.sql) on this view will report the structure of views within views. 

REM depend_hier.sql
undefine view_name
set pages 999 lines 176 long 50000
break on name skip 1 on owner 
ttitle 'Dependency Hierarchy'
column my_level format a5 heading 'Level'
column owner format a12
column name format a18
column type format a7
column referenced_type format a7 heading 'Refd|Type'
column referenced_owner format a6 heading 'Refd|Owner'
column referenced_name format a18 heading 'Refd|Name'
column referenced_link_name format a10 heading 'Refd|Link'
column dependency_type heading 'Dep|Type'
column text heading 'View Text' format a80 wrap on
spool depend_hier.&&view_name..lst
with d as (
  select * from all_dependencies
  union all
  select null, null, null, owner, view_name, 'VIEW', null, null
  from all_views 
  where owner = 'SYSADM' and view_name = UPPER('&&view_name')
)
select LPAD(TO_CHAR(level),level,'.') my_level
, d.type, d.owner, d.name
, d.referenced_type, d.referenced_owner, d.referenced_name, d.referenced_link_name
, d.dependency_type
, v.text
from d
  left outer join all_views v 
    on  v.owner = d.referenced_owner
    and v.view_name = d.referenced_name
  connect by nocycle
        d.name = prior d.referenced_name
  and   d.owner = prior d.referenced_owner
start with d.owner IS NULL and d.name IS NULL
/
spool off
ttitle off

For example, this is the report for PS_POSN_HISTORY3 from a demo HCM database. It is only three levels deep. "POSN_HISTORY3 is the third of three nested views which retrieve position incumbent history.  It selects job records with effective dates before position exits to obtain exit salaries."

Wed Aug 11                                                                                                                                                             page    1
                                                                              Dependency Hierarchy

                                              Refd    Refd   Refd               Refd       Dep
Level TYPE    OWNER        NAME               Type    Owner  Name               Link       Type View Text
----- ------- ------------ ------------------ ------- ------ ------------------ ---------- ---- --------------------------------------------------------------------------------
1                                             VIEW    SYSADM PS_POSN_HISTORY3                   SELECT A.Position_Nbr ,A.Position_Entry_Dt ,A.Emplid ,A.EMPL_RCD ,B.EFFDT ,B.EFF
                                                                                                SEQ ,B.Sal_Admin_Plan ,B.Grade ,B.Step ,B.Comprate ,B.Comp_Frequency ,B.Currency
                                                                                                _Cd ,' ' ,' ' ,' ' ,' ' FROM PS_POSN_HISTORY2 A ,PS_JOB B WHERE B.EmplID = A.Emp
                                                                                                lID AND B.EMPL_RCD = A.EMPL_RCD AND B.Position_Nbr = A.Position_Nbr AND B.EffDt
                                                                                                = ( SELECT MAX(C.EffDt) FROM PS_JOB C WHERE C.EmplID = B.EmplID AND C.EMPL_RCD =
                                                                                                 B.EMPL_RCD AND (C.EffDt < A.Position_End_Dt OR (C.EffDt = A.Position_End_Dt AND
                                                                                                 C.EffSeq = A.EffSeq - 1))) AND B.Effseq = ( SELECT MAX(C.Effseq) FROM PS_JOB C
                                                                                                WHERE C.EmplID = B.EmplID AND C.EMPL_RCD = B.EMPL_RCD AND (C.EffDt < A.Position_
                                                                                                End_Dt OR (C.EffDt = A.Position_End_Dt AND C.EffSeq = A.EffSeq - 1)))


.2    VIEW    SYSADM       PS_POSN_HISTORY3   TABLE   SYSADM PS_JOB                        HARD
.2    VIEW                                    VIEW    SYSADM PS_POSN_HISTORY2              HARD SELECT A.Position_Nbr , A.Position_Entry_Dt , A.Emplid , A.EMPL_RCD , B.EffDt ,
                                                                                                B.EffSeq , B.Action FROM PS_POSN_HISTORY A , PS_JOB B WHERE A.EmplID = B.EmplID
                                                                                                AND A.Empl_Rcd = B.Empl_Rcd AND B.EffDt = ( SELECT MIN(C.EffDt) FROM PS_JOB C WH
                                                                                                ERE C.EmplID = B.EmplID AND C.Empl_Rcd = B.Empl_Rcd AND (C.EffDt > A.Position_En
                                                                                                try_Dt OR (C.EffDt = A.Position_Entry_Dt AND C.EffSeq > A.EffSeq)) AND ((C.Posit
                                                                                                ion_Nbr <> A.Position_Nbr) OR (C.HR_STATUS <> 'A'))) AND B.EffDt<=TO_DATE(TO_CHA
                                                                                                R(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') AND B.EffSeq = ( SELECT MIN(D.EffSeq) FROM
                                                                                                 PS_JOB D WHERE D.EmplID = B.EmplID AND D.Empl_Rcd = B.Empl_Rcd AND D.EffDt = B.
                                                                                                EffDt AND ((D.Position_Nbr <> A.Position_Nbr) OR (D.HR_STATUS <> 'A')))


..3   VIEW    SYSADM       PS_POSN_HISTORY2   TABLE   SYSADM PS_JOB                        HARD
..3   VIEW                                    VIEW    SYSADM PS_POSN_HISTORY               HARD SELECT A.Position_Nbr ,A.Position_Entry_Dt ,A.Emplid ,A.EMPL_RCD ,A.EFFSEQ ,A.EF
                                                                                                FDT ,A.Sal_Admin_Plan ,A.Grade ,A.Step ,A.CompRate ,A.Comp_Frequency ,A.Currency
                                                                                                _Cd ,' ' ,' ' ,' ' ,' ' FROM PS_Job A WHERE A.Position_Entry_Dt = A.Effdt AND A.
                                                                                                Effseq = ( SELECT MIN(B.Effseq) FROM PS_Job B WHERE B.Emplid = A.Emplid AND B.EM
                                                                                                PL_RCD = A.EMPL_RCD AND B.Effdt = A.Effdt AND B.Position_Nbr = A.Position_Nbr)


...4  VIEW    SYSADM       PS_POSN_HISTORY    TABLE   SYSADM PS_JOB                        HARD

We can see from the report that view PS_POSN_HISTORY3 calls view PS_POSN_HISTORY2 that in turn calls view PS_POSN_HISTORY. 

Each of the views also contains multiple references to PS_JOB that perform various effective date/sequence sub-queries. Where there are multiple references to the same object, there is still only one dependency.

The script is available on Github as a part of my psscripts repository.

PeopleSoft and Invalid Views in the Oracle Database

I was listening to the section on Invalid Views in PSADMIN Podcast #117 (@19:00). Essentially, when you drop and recreate a view that is referenced by a second view, the status on the second view in the database goes invalid. This is not a huge problem because as soon as you query the second view it is compiled. However, you would like to know whether any change to a view prevents any dependent views from compiling, although you would expect have teased these errors out before migration to production.
The PeopleSoft solution to this is to include all the dependent views in the Application Designer project. However, as pointed out, in the podcast you are now releasing code, possibly unintentionally releasing code changes and certainly updating last change dates on record definitions when really you just need to compile the database objects.   PeopleSoft does this because it is a platform generic solution, but really this is using the PeopleSoft Application Designer to solve a database management issue.
A similar problem also occurs in the Oracle database with dependent PL/SQL procedures and packages where you sometimes get referential loops. Oracle provides a procedure DBMS_UTILITY.COMPILE_SCHEMA that recompiles all invalid objects in a schema and reports any errors to the ALL_ERRORS view.  I think this is a much safer option.

Here is a very simple (non-PeopleSoft) example

drop table t purge;
drop view a;
drop view b;

create table t (a number);
insert into t values (1); 
create view a as select a from t; 
create view b as select a from a; 

column object_name format a12
select object_type, object_name, status 
from user_objects where object_name IN('T','A','B')
/

OBJECT_TYPE             OBJECT_NAME  STATUS
----------------------- ------------ -------
TABLE                   T            VALID
VIEW                    B            VALID
VIEW                    A            VALID

Dropping and recreating view A renders view B invalid.

drop view a;
create view a as select a from t; 

select object_type, object_name, status 
from user_objects
where object_name IN('T','A','B');

OBJECT_TYPE             OBJECT_NAME  STATUS
----------------------- ------------ -------
TABLE                   T            VALID
VIEW                    B            INVALID
VIEW                    A            VALID

Just querying B makes it valid again.

select * from b;
select object_type, object_name, status 
from user_objects where object_name IN('T','A','B');

OBJECT_TYPE             OBJECT_NAME  STATUS
----------------------- ------------ -------
TABLE                   T            VALID
VIEW                    B            VALID
VIEW                    A            VALID

Let's make B invalid again by rebuild A, but this time I will change the name of the column in view A from A to T so that view B cannot compile without an error.  I can recompile every invalid object in the schema by calling DBMS_UTILITY_COMPILE_SCHEMA.  However, B remains invalid because there is an error.

drop view a;
create view a (t) as select a from t;  

EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'SCOTT');
select object_type, object_name, status 
from user_objects where object_name IN('T','A','B');

OBJECT_TYPE             OBJECT_NAME  STATUS
----------------------- ------------ -------
TABLE                   T            VALID
VIEW                    B            INVALID
VIEW                    A            VALID

I can query the errors from USER_ERRORS.  So now I have recompiled all invalid objects and have a report of the exceptions that I can work on fixing.

NAME
---------------------------------------------------
TYPE           SEQUENCE       LINE   POSITION
------------ ---------- ---------- ----------
TEXT
---------------------------------------------------
ATTRIBUTE MESSAGE_NUMBER
--------- --------------
B
VIEW                  1          0          0
ORA-00904: "A": invalid identifier
ERROR                  0

N.B.: if you use CREATE OR REPLACE VIEW, then the view is not left invalid unless there is an error. Unfortunately, Application Designer always drops and recreates views.

Advantages of Row Source Aliases inside Views

Here is a simple idea that could make it easier to read the execution plan of queries that have multiple references to the same table, possibly via views: make the row source aliases sufficiently distinctive that they would identify the view. Perhaps even have the alias include the name of view.
This is not exclusive to PeopleSoft, except that PeopleSoft uses views very heavily.

First here is a very simple test example. I'll create a table and a view on the same table.

CREATE TABLE t
(a NUMBER NOT NULL CONSTRAINT t_pk PRIMARY KEY
,b NUMBER NOT NULL);

INSERT INTO t
SELECT rownum, 2*rownum
FROM   dba_objects
WHERE  rownum <= 100; 

CREATE OR REPLACE VIEW v AS 
SELECT v.a, v.b FROM t v 
WHERE  v.a <= 42;

Now lets look at an execution plan of a query that joins the views.

EXPLAIN PLAN FOR
SELECT t.a, v.b
FROM   t, v
WHERE  t.a = v.b
AND    t.b >= 24;

SELECT * FROM TABLE(dbms_xplan.display);

From just the execution plan it is impossible to tell which INDEX SCAN is from which view.

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    42 |   420 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |      |    42 |   420 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |    42 |   210 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_PK |    42 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |     5 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | T_PK |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

But the predicate information helps us. The nested loop is driven from the reference to T in the view V to the table T.

Predicate Information (identified by operation id):
---------------------------------------------------
  3 - access("V"."A"<=42)
  4 - filter("T"."B">=24)
  5 - access("T"."A"="V"."B")

The row source alias of the object appears in the predicate, but only if it is specified on that column in either the select list of the criteria. But now we can determine which operation relates to whivj reference.

Here is part of a simple SQL statement generated by nVIsion.

SELECT ...
FROM ps_pr_jrnl_hdr_vw a, ps_pr_jrnl_ln_vw b, 
     ps_xx_rt_rate_qvw c, ps_xx_rt_rate_q_vw d …;

Both PS_XX_RT_RATE_QVW and PS_XX_RT_RATE_Q_VW are views on PS_RT_RATE_TBL. We can see from the execution plan that Oracle has chosen to use one index in one view and another index in another. But which in which?

----------------------------------------------------------------------------------
| Id  | Operation                      |  Name           | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |     1 |   160 |   549 |
|   1 |  SORT GROUP BY                 |                 |     1 |   160 |   549 |
|   2 |   NESTED LOOPS                 |                 |     1 |   160 |   547 |
|   3 |    NESTED LOOPS                |                 |     1 |   132 |   545 |
|*  4 |     HASH JOIN                  |                 |     1 |    66 |   541 |
|*  5 |      INDEX RANGE SCAN          | PSJJRNL_HEADER  |  4066 |   150K|    87 |
|*  6 |      INDEX FAST FULL SCAN      | PSFRT_RATE_TBL  |     1 |    28 |   452 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| PS_JRNL_LN      |     1 |    66 |     4 |
|*  8 |      INDEX RANGE SCAN          | PSFJRNL_LN      |     1 |       |     3 |
|*  9 |    INDEX RANGE SCAN            | PSERT_RATE_TBL  |     1 |    28 |     2 |
----------------------------------------------------------------------------------

But I have used the name of the query as the row source alias for the table PS_RT_RATE_TBL in these views, and that alias now appears in the Predicate Information.

Predicate Information (identified by operation id):
---------------------------------------------------
…
  6 - filter(TO_NUMBER(TO_CHAR("XX_RT_RATE_QVW"."EFFDT",'YYYY'))=2007 AND ...
…
  9 - access("JL"."CURRENCY_CD"="XX_RT_RATE_Q_VW"."FROM_CUR" AND ...

So it now very obvious which view in the from clause is responsible for which access of the rate table at operations 6 and 9 in this plan.