Advantages of Row Source Aliases inside Views
来源:互联网 发布:如何分析数据得出问题 编辑:程序博客网 时间:2024/04/29 08:49
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.
Now lets look at an execution plan of a query that joins the views.
From just the execution plan it is impossible to tell which INDEX SCAN is from which view.
But the predicate information helps us. The nested loop is driven from the reference to T in the view V to the table T.
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.
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?
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.
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.
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 tSELECT rownum, 2*rownumFROM dba_objectsWHERE 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 FORSELECT t.a, v.bFROM t, vWHERE t.a = v.bAND 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.
- Advantages of Row Source Aliases inside Views
- 17.1.2.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication
- 17.1.2.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication
- advantages of connection poolings
- All Aliases of PowerShell
- the advantages of event delegation
- the advantages of using frameworks
- Advantages of Stainless Steel Rebar
- Advantages of aluminium lighting poles
- Advantages of AMQP over JMS
- 17.1.2.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication 基于语句和行的复制的优势和劣势
- Advantages of VPS over Shared Hosting
- The Advantages Of Fiber Media Converters
- Advantages of Kosmix's KFS vs. HDFS
- The advantages of the archiving of online redo log files
- Ubuntu: 安装Source Inside 3.5
- [.NET]advantages and disadvantages of using c++ interop
- 植物性饮食的妙处 The Advantages of Plant-based Diet
- [Android]使用achartengine开发曲线图相关的Android应用程序
- 【data structure】之查找链表中倒数第N个元素
- C++引用与指针的比较
- ico资源下载
- Linux设备文件(转自:http://lamp.linux.gov.cn/Linux/device_files.html)
- Advantages of Row Source Aliases inside Views
- Androd之获取网络图片
- How to Create QuickAction Dialog in Android
- 安卓个人学习笔记---使用URL访问网络资源
- 向身边的巨人学习如何写好代码,刻意去提升自己
- shell 的test命令
- 算是冬至的感慨
- 关于C#中Thread.Join()的一点理解
- Poor performance of PSPMSESSIONS_VW view affects Performance Monitor System Monitor Component