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.