thanks Tom,

This is just one time process after that t2.dt and t3.dt cease to exist.


I was asking for the WITH clause, because in the update, if you have something like

update t1 set col1 = (
--begin
select xx
from term t2
where <join cond>
--end
)
where exists (
--begin
select xx
from term t2
where <join cond>
--end
)

I was thinking, I would put the query part between the "--begin" and "--end" in thw "WITH" clause and refer them once. but, it doesn't seem to be right as it has correlation in this particular case.


BTW, the site looks excellent with the new changes. I like the new menu very much.

I found subquery factoring (named subqueries) useful both as a documentation tool and in UPDATEs. As documentation, a factored subquery is sometimes more easily understood than nested subqueries (and may replace temp tables). Works nicely in UPDATE statements too.

update meta_admin.attribute A
set ( business_nm ) =
(
with UPDT_WITH_SINGLE as
(
select S.business_nm AS SHOULD_BE_BUSINESS_NM
, B.business_nm AS UPDT_BUSINESS_NM
from single_business_nm S
inner join
updt_business_nm B
on ( S.huon_desc = B.huon_desc )
)
select SHOULD_BE_BUSINESS_NM
from UPDT_WITH_SINGLE X
where X.UPDT_BUSINESS_NM = A.business_nm
)
/
oraTEST1:meta_user> /

164086 rows updated.

Whe have an application wich processes rows obtained from excel-like data entry forms,
part of the logic includes calculating a summary row

CREATE TABLE anexo_etes_cpn (
ano_eje number(4),
mes_eje number(2),
entidad number(4),
codigo_formato number(4),
codigo_suma varchar2(4),
codigo_rubro varchar2(4),
indicador_suma varchar2(1), -- 'S' | 'N'
mto_01 number,
mto_02 number,
mto_03 number )

Primary key:
ano_eje,mes_eje,entidad,codigo_formato,codigo_rubro

Rows:
ANO_EJE MES_EJE ENT COD CODIGO_SUMA CODIGO_RUBRO I MTO_01 MTO_02 MTO_03
---------- ---------- --- --- -------------------- -------------- - ---------- ---------- ----------
2003 13 101 181 01 01 S 544581826 28879476 -3024258
2003 13 101 181 0111 0111 N
2003 13 101 181 0110 0110 N 2947665 -1579089
2003 13 101 181 0109 0109 N
2003 13 101 181 0108 0108 N 14116289 7147392
2003 13 101 181 0107 0107 N
2003 13 101 181 0106 0106 N
2003 13 101 181 0105 0105 N 19538000 1537586 -10299
2003 13 101 181 0102 0102 N 270623993 2624587
2003 13 101 181 0101 0101 N 41652132 -648968
2003 13 101 181 0103 0103 N 175122614 17332106 -305085
2003 13 101 181 0104 0104 N 20581133 237805 -480817

The first row (the one with indicador_suma='S') is the summary of the ones with indicador_suma = 'N',
it's obtained with:

UPDATE anexo_etes_cpn a
SET a.mto_01 =
( SELECT SUM(NVL( b.mto_01 , 0 ))
FROM anexo_etes_cpn b
WHERE b.ano_eje = a.ano_eje
AND b.mes_eje = a.mes_eje
AND b.entidad = a.entidad
AND b.codigo_formato = :b1
AND b.codigo_suma LIKE a.codigo_suma || '%'
AND b.ind_suma = 'N' ),
a.mto_02 =
( SELECT SUM(NVL( b.mto_02 , 0 ))
FROM anexo_etes_cpn b
WHERE b.ano_eje = a.ano_eje
AND b.mes_eje = a.mes_eje
AND b.entidad = a.entidad
AND b.codigo_formato = :b1
AND b.codigo_suma LIKE a.codigo_suma || '%'
AND b.ind_suma = 'N' ),
a.mto_03 =
( SELECT SUM(NVL( b.mto_03 , 0 ))
FROM anexo_etes_cpn b
WHERE b.ano_eje = a.ano_eje
AND b.mes_eje = a.mes_eje
AND b.entidad = a.entidad
AND b.codigo_formato = :b1
AND b.codigo_suma LIKE a.codigo_suma || '%'
AND b.ind_suma = 'N' )
WHERE a.ano_eje = :b4
AND a.mes_eje = :b3
AND a.entidad = :b2
AND a.codigo_formato = :b1
AND a.ind_suma = 'S'
AND a.codigo_suma IS NOT NULL

The problem is that the table is read many times (actually there are 12 computed columns, not 3
as is shown here). We know that iIt's posible to compute the summatory once and store the results
on variables and after that perform the update:
SELECT SUM(NVL( b.mto_01 , 0 )), SUM(NVL( b.mto_02 , 0 )), SUM(NVL( b.mto_03 , 0 ))
INTO v_s_1, v_s_2, v_s_3
FROM ....
UPDATE ... SET mto_01 = v_s_1, mto_02 = v_s_2, ....
But is there a way to perform the same update with only one sentence? we tried with
subquery factoring with no luck.

Tom,

I may be experiencing the strangest Oracle behavior I have seen to date. I'm looking at a query from an Oracle product called OBIEE that's using subquery factoring. For whatever reason, the timings and execution plans totally change when you run the query in succession. I literally logged in, ran the query, it returned 1 row in about a second. ran it again right after, it ran for 7 minutes. The trace showed different plans and also, the slower of the two showed a global temporary table being created behind the scenes.

The plans are a bit large to post. But I'm just curious to get your gut feeling because I have no possible idea what could cause this since we can run this over and over, and it will flip flop. There are no bind variables, it's all literals.

WITH
SAWITH0 AS (select T116391.ORG_HIER11_NAME as c2,
     sum(T158903.HEADCOUNT) as c3,
     T116391.ORG_HIER11_NUM as c4,
     T104908.PER_NAME_YEAR as c5
from
     W_POSITION_DH T116081 /* Dim_W_POSITION_DH_Position_Hierarchy */ ,
     W_INT_ORG_DH T116391 /* Dim_W_INT_ORG_DH_Employee_Org */ ,
     WC_ORG_LIST_D T304800 /* Dim_WC_ORG_LIST_D_OrgSecurity */ ,
     W_EMPLOYEE_D T68497 /* Dim_W_EMPLOYEE_D */ ,
     W_YEAR_D T104908 /* Dim_W_YEAR_D */ ,
     W_WRKFC_EVT_MONTH_F T158903 /* Fact_W_WRKFC_EVT_MONTH_F_Snapshot */ ,
     WC_COLUMN_SECURITY_D T304709 /* Dim_WC_COLUMN_SECURITY_D_Professional */ ,
     W_POSITION_DH T296816 /* Dim_Vis_W_POSITION_DH_Employee_Security */ ,
     WC_COLUMN_SECURITY_D T304704 /* Dim_WC_COLUMN_SECURITY_D_Personal */
where  ( T116081.ROW_WID = T158903.EMP_POSTN_DH_WID
   and T116391.ORG_WID = T158903.HR_ORG_WID
   and T104908.ROW_WID = T158903.EVENT_YEAR_WID
   and T68497.ROW_WID = T158903.EMPLOYEE_WID
   and T68497.VIS_PR_POSTN_DH_WID = T296816.ROW_WID
   and T68497.X_POPULATION_GROUP = T304704.POPULATION_GROUP
   and T68497.X_POPULATION_GROUP = T304709.POPULATION_GROUP
   and T116391.BASE_ORG_NUM = T304800.ORGANIZATION_ID
   and T158903.SNAPSHOT_IND = 1
   and T304704.COL_SECURITY_GROUP = 'Manager'
   and T304704.DATA_GROUP = 'PERSONAL'
   and T116081.CURRENT_LVL12ANC_LOGIN = 'N0176839'
   and T304709.COL_SECURITY_GROUP = 'Manager'
   and T304709.DATA_GROUP = 'PROFESSIONAL'
   and case  when T104908.PER_NAME_YEAR = '2012' then T116391.HIERARCHY_NAME end  = 'LMIG Org Level Hierarchy'
   and T158903.DELETE_FLG <> 'Y'
   and T296816.CURRENT_BASE_LOGIN <> 'N0176839'
   and T104908.CAL_YEAR_START_DT >= TO_DATE('2001-01-01 00:00:00' , 'YYYY-MM-DD HH24:MI:SS')
   and (T158903.SNAPSHOT_MONTH_END_IND in (1) or T158903.EFFECTIVE_END_DATE >= TO_DATE('2012-04-10 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'))
   and (T158903.LAST_MONTH_IN_YEAR_IND in (1) or T158903.EFFECTIVE_END_DATE >= TO_DATE('2012-04-10 00:00:00' , 'YYYY-MM-DD HH24:MI:SS'))
   and (T116391.ROW_WID in (0) or T116391.W_HIERARCHY_CLASS in ('HR-ORG'))
   and (T116391.ROW_WID in (0) or T116391.HR_ORG_FLG in ('Y'))
   and (T116391.ROW_WID in (0) or T116391.CURRENT_VER_HIER_FLG in ('Y'))
   and (T116391.ROW_WID in (0) or T304800.SECURITY_PROFILE_ID in (-1.0))
   and T158903.EFFECTIVE_START_DATE <= TO_DATE('2012-04-10 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') )
group by T104908.PER_NAME_YEAR, T116391.ORG_HIER11_NUM, T116391.ORG_HIER11_NAME),
SAWITH1 AS (select distinct SAWITH0.c2 as c1,
     LAST_VALUE(SAWITH0.c3 IGNORE NULLS) OVER (PARTITION BY SAWITH0.c4, SAWITH0.c2 ORDER BY SAWITH0.c4 NULLS FIRST, SAWITH0.c2 NULLS FIRST,
SAWITH0.c5 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING
   and UNBOUNDED FOLLOWING) as c2
from
     SAWITH0)
select SAWITH1.c1 as c1
from
     SAWITH1
order by c1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.85       0.85          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     28.71     419.04     112539    7391807          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     29.56     419.90     112539    7391807          0           1


and the better stats:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.49       0.51          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.39       0.39          0      21962          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.88       0.90          0      21962          0           1

Thanks I'll check that out. Didn't know about that feature. Someone updated the stats on the schema so now we're getting consistent results so I can't check the plan for that.