Skip to Main Content
  • Questions

  • 'Reverse' transposition SQL optimisation

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Alex.

Asked: October 11, 2003 - 10:55 pm UTC

Last updated: November 05, 2006 - 5:53 pm UTC

Version: 9.2.0.3

Viewed 1000+ times

You Asked

Hello Tom...

We have both your books in our team... thank you, thank you, thank you!


In your expert-one on one you have some helpful instructions on pivotting... but we have a reverse situation where we want to pivot from columns to rows!

If we could take you through a following script example...



12:31:13 ep@EP> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for 32-bit Windows: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production

set serveroutput on
set arraysize 500


==================================================

Ok.. first thing... create sample data...

==================================================

Drop table TABLE_OFMANYCOLUMNS;



CREATE TABLE TABLE_OFMANYCOLUMNS
(
PK_ID NUMBER(9),
AKEYLOOKUP_ID NUMBER(9),
AKEYLOOKUP_DATE DATE,
P0030 NUMBER(17,4),
P0100 NUMBER(17,4),
P0130 NUMBER(17,4),
P0200 NUMBER(17,4),
P0230 NUMBER(17,4),
P0300 NUMBER(17,4),
P0330 NUMBER(17,4),
P0400 NUMBER(17,4),
P0430 NUMBER(17,4),
P0500 NUMBER(17,4),
P0530 NUMBER(17,4),
P0600 NUMBER(17,4),
P0630 NUMBER(17,4),
P0700 NUMBER(17,4),
P0730 NUMBER(17,4),
P0800 NUMBER(17,4),
P0830 NUMBER(17,4),
P0900 NUMBER(17,4),
P0930 NUMBER(17,4),
P1000 NUMBER(17,4),
P1030 NUMBER(17,4),
P1100 NUMBER(17,4),
P1130 NUMBER(17,4),
P1200 NUMBER(17,4),
P1230 NUMBER(17,4),
P1300 NUMBER(17,4),
P1330 NUMBER(17,4),
P1400 NUMBER(17,4),
P1430 NUMBER(17,4),
P1500 NUMBER(17,4),
P1530 NUMBER(17,4),
P1600 NUMBER(17,4),
P1630 NUMBER(17,4),
P1700 NUMBER(17,4),
P1730 NUMBER(17,4),
P1800 NUMBER(17,4),
P1830 NUMBER(17,4),
P1900 NUMBER(17,4),
P1930 NUMBER(17,4),
P2000 NUMBER(17,4),
P2030 NUMBER(17,4),
P2100 NUMBER(17,4),
P2130 NUMBER(17,4),
P2200 NUMBER(17,4),
P2230 NUMBER(17,4),
P2300 NUMBER(17,4),
P2330 NUMBER(17,4),
P2400 NUMBER(17,4)
)
TABLESPACE EP
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;


CREATE UNIQUE INDEX LOOKUP_IDX ON TABLE_OFMANYCOLUMNS
(AKEYLOOKUP_ID, AKEYLOOKUP_DATE)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE UNIQUE INDEX PK_PK_ID ON TABLE_OFMANYCOLUMNS
(PK_ID)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


ALTER TABLE TABLE_OFMANYCOLUMNS ADD (
CONSTRAINT PK_PK_ID PRIMARY KEY (PK_ID)
USING INDEX
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));





Declare
cnt_sequence number := 0;
cnt_date date ;

Begin
for n in 1..200 loop

cnt_date :=to_date('01/01/2003','dd/mm/yyyy');
for o in 1..365 loop
cnt_sequence := cnt_sequence + 1;

insert into TABLE_OFMANYCOLUMNS
(PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE,
P0030,P0100,P0130,P0200,P0230,P0300,P0330,P0400,P0430,P0500,
P0530,P0600,P0630,P0700,P0730,P0800,P0830,P0900,P0930,P2400)
values
(cnt_sequence,
n,
to_date(cnt_date,'dd/mm/yy'),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual),
(select dbms_random.value(0,200) from dual)
);
cnt_date := cnt_date +1;
end loop;
end loop;
End;
/



PL/SQL procedure successfully completed.


commit;

Commit complete.






==================================================

Now.. we want to query this table (normally.. there are a lot more (hundreds of thousands)
rows with more fields i the table with indexed lookups etc!

We have the following query whch transposes the data.. but it is VERY expensive and requires
a select for every column want values pivotted for)

==================================================



analyze table TABLE_OFMANYCOLUMNS compute statistics;

Table analyzed.


timing start SeeHowLongThisTakes
timing show
set autotrace traceonly;



timing for: SeeHowLongThisTakes
Elapsed: 00:00:00.00

I



(select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0030' as P_NO, P0030 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0100' as P_NO, P0100 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0130' as P_NO, P0130 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0200' as P_NO, P0200 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0230' as P_NO, P0230 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0300' as P_NO, P0300 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0330' as P_NO, P0330 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0400' as P_NO, P0400 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0430' as P_NO, P0430 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0500' as P_NO, P0530 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0600' as P_NO, P0600 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0630' as P_NO, P0630 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0700' as P_NO, P0700 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0730' as P_NO, P0730 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0800' as P_NO, P0800 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0830' as P_NO, P0830 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0900' as P_NO, P0900 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P0930' as P_NO, P0930 as P_VALUE
from TABLE_OFMANYCOLUMNS t
union all
select PK_ID, AKEYLOOKUP_ID, AKEYLOOKUP_DATE, 'P2400' as P_NO, P2400 as P_VALUE
from TABLE_OFMANYCOLUMNS t)
order by AKEYLOOKUP_ID, AKEYLOOKUP_DATE, P_NO;






1387000 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23157 Card=1387000 B
ytes=26353000)

1 0 SORT (ORDER BY) (Cost=779 Card=1387000 Bytes=26353000)
2 1 UNION-ALL
3 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

4 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

5 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

6 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

7 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

8 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

9 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

10 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

11 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

12 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

13 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

14 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

15 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

16 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

17 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

18 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

19 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

20 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)

21 2 TABLE ACCESS (FULL) OF 'TABLE_OFMANYCOLUMNS' (Cost=41
Card=73000 Bytes=1387000)





Statistics
----------------------------------------------------------
990 recursive calls
47 db block gets
31239 consistent gets
8673 physical reads
0 redo size
24139554 bytes sent via SQL*Net to client
31548 bytes received via SQL*Net from client
2775 SQL*Net roundtrips to/from client
22 sorts (memory)
1 sorts (disk)
1387000 rows processed


timing show

timing for: SeeHowLongThisTakes
Elapsed: 00:00:20.09










==================================================

Tom...is there a beter way to construct this query...
possibly returning the pivotted result with one pass!


Thanks!






and Tom said...

Two ways -- one is a pipelined function, the other just "sql".

first the pipelined function -- we start with an object type that describes our output. Think of this as you would a "view" definition -- just a template. YES, it must be done as a create type -- it cannot be "hidden" in plsql, it must be a SQL type:

ops$tkyte@ORA920> create or replace type myScalarType as object
2 ( pk_id number,
3 akeylookup_id number,
4 akeylookup_date date,
5 datum number
6 )
7 /

Type created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace type myTableType as table of myScalarType
2 /

Type created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> CREATE TABLE TABLE_OFMANYCOLUMNS
2 (
3 PK_ID NUMBER(9),
4 AKEYLOOKUP_ID NUMBER(9),
5 AKEYLOOKUP_DATE DATE,
6 P0030 NUMBER(17,4),
7 P0100 NUMBER(17,4),
8 P0130 NUMBER(17,4),
9 P0200 NUMBER(17,4)
10 )
11 /

Table created.

ops$tkyte@ORA920> exec gen_data( 'TABLE_OFMANYCOLUMNS', 100 );

PL/SQL procedure successfully completed.

for demonstration purposes, i'll use 4 columns -- you can have as many as you like..

ops$tkyte@ORA920> create or replace
2 function reverse_pivot( p_cursor in sys_refcursor )
3 return myTableType
4 pipelined
5 as
6 type array is table of table_ofManyColumns%rowtype index by binary_integer;
7 l_data array;
8 l_rec myScalarType := myScalarType(null,null,null,null);
9 begin
10 loop
11 fetch p_cursor bulk collect into l_data limit 100;
12 for i in 1 .. l_data.count
13 loop
14 l_rec.pk_id := l_data(i).pk_id;
15 l_rec.akeyLookup_date := l_data(i).akeyLookup_date;
16 l_rec.akeylookup_id := l_data(i).akeyLookup_id;
17 l_rec.datum := l_data(i).p0030;
18 pipe row( l_rec );
19 l_rec.datum := l_data(i).p0100;
20 pipe row( l_rec );
21 l_rec.datum := l_data(i).p0130;
22 pipe row( l_rec );
23 l_rec.datum := l_data(i).p0200;
24 pipe row( l_rec );
25 end loop;
26 exit when p_cursor%notfound;
27 end loop;
28 close p_cursor;
29 return;
30 end;
31 /

Function created.

there is the procedure to pivot. it takes a result set as input (a set of rows from table of many columns). It bulk fetches 100 rows at a time and for each row -- it outputs N rows (n = number of columns to unpivot)

ops$tkyte@ORA920>
ops$tkyte@ORA920> select *
2 from TABLE( reverse_pivot( cursor( select * from table_ofManyColumns where rownum = 1 ) ) )
3 /

PK_ID AKEYLOOKUP_ID AKEYLOOKU DATUM
---------- ------------- --------- ----------
644717704 283491708 09-OCT-04 5.0958E+12
644717704 283491708 09-OCT-04 4.9144E+12
644717704 283491708 09-OCT-04 4.9737E+12
644717704 283491708 09-OCT-04 2.3809E+12


and there is how to call it. You would put whatever query with whatever binds you want in the CURSOR() call and we'll unpivot it.

You can use SQL as well - we need a table to unpivot with


ops$tkyte@ORA920>
ops$tkyte@ORA920> create table tall_dual ( x primary key ) organization index
2 as
3 select rownum from all_objects where rownum <= 100;

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select pk_id, akeyLookup_id, akeyLookup_date,
2 decode( x, 1, p0030, 2, p0100, 3, p0130, 4, p0200 ) datum
3 from ( select * from table_ofManyColumns where rownum = 1 ) a,
4 ( select x from tall_dual where x <= 4 ) b
5 /

PK_ID AKEYLOOKUP_ID AKEYLOOKU DATUM
---------- ------------- --------- ----------
644717704 283491708 09-OCT-04 5.0958E+12
644717704 283491708 09-OCT-04 4.9144E+12
644717704 283491708 09-OCT-04 4.9737E+12
644717704 283491708 09-OCT-04 2.3809E+12

a simple cartesian product will turn each row in A into N rows from B. Decode picks off the column we want and wah-lah, you are done

Benchmark/test each approach in your environment to see which is most appealing to you.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Pipe function vs Decode!

Alex, October 16, 2003 - 10:36 am UTC


Hello Tom,

And thank you for the great reply!

We have not followed up earlier is because we have been exploring carefully both alternatives you suggested.


We were a litle surprised by the results!

At first.. we thought the idea of a pipeline function was very elegant so we explored that avenue first. However we soon came to a roadblock - when I first posted our query I possibly over simplified the issue.

In the original post I outlined that there was a number some lookup tables which needed to be joined together with a single table with nuumerous columns to be transposed to rows. There is actually 2 tables...


Looking at our original View:

-- ----------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW EP.EP_V_PERIODMAP_CURVE_R_OLD
(EPCM_CALENDAR_ID, EPCM_SEASON_ID, EPCM_DAY_ID, EPCM_DATE, CURVE_ID,
P_NO, PERIOD_ID, P_VALUE)
AS
select cm.EPCM_CALENDAR_ID, cm.EPCM_SEASON_ID, cm.EPCM_DAY_ID, cm.EPCM_DATE, cd.CURVE_ID, 'P0030' as P_NO, pm.P0030 as PERIOD_ID, cd.P0030 as P_VALUE
from ep_calendar_map cm,
ep_period_map pm,
ep_curvedetails cd
where cm.EPCM_CALENDAR_ID = pm.EPPM_CALENDAR_ID
and cm.EPCM_SEASON_ID = pm.EPPM_SEASON_ID
and cm.EPCM_DAY_ID = pm.EPPM_DAY_ID
and cm.EPCM_DATE = cd.curvedetails_date
union all
select cm.EPCM_CALENDAR_ID, cm.EPCM_SEASON_ID, cm.EPCM_DAY_ID, cm.EPCM_DATE, cd.CURVE_ID, 'P0100' as P_NO, pm.P0100 as PERIOD_ID, cd.P0100 as P_VALUE
from ep_calendar_map cm,
ep_period_map pm,
ep_curvedetails cd
where cm.EPCM_CALENDAR_ID = pm.EPPM_CALENDAR_ID
and cm.EPCM_SEASON_ID = pm.EPPM_SEASON_ID
and cm.EPCM_DAY_ID = pm.EPPM_DAY_ID
and cm.EPCM_DATE = cd.curvedetails_date
union all

...

union all
select cm.EPCM_CALENDAR_ID, cm.EPCM_SEASON_ID, cm.EPCM_DAY_ID, cm.EPCM_DATE, cd.CURVE_ID, 'P2400' as P_NO, pm.P2400 as PERIOD_ID, cd.P2400 as P_VALUE
from ep_calendar_map cm,
ep_period_map pm,
ep_curvedetails cd
where cm.EPCM_CALENDAR_ID = pm.EPPM_CALENDAR_ID
and cm.EPCM_SEASON_ID = pm.EPPM_SEASON_ID
and cm.EPCM_DAY_ID = pm.EPPM_DAY_ID
and cm.EPCM_DATE = cd.curvedetails_date;

-- ----------------------------------------------------------------------------------

For the record... this is has been perform! (7 seconds is too too long!)

ep@EP> select * from EP_V_PERIODMAP_CURVE_READINGS where curve_id = 120403
and EPCM_DATE between '01-Jul-2003' and '10-Jul-2003';




-- ----------------------------------------------------------------------------------



But what I did not tell you as you ca derive from the view structure is that the fields pm.P0030 as PERIOD_ID to pm.P2400 as PERIOD_ID also requires transposition!


So... we we came up with the following interpretation of your sample code... you will note that we have created Type ty_cal_map IS RECORD and we manage both the values (from our UNION query):

pm.P0230 as PERIOD_ID
cd.P0230 as P_VALUE

as

l_rec.p_no := 'P0030';
l_rec.period_id := ty_output_values(i).p0030;
l_rec.p_value := ty_output_values(i).c0030;

as you can see in function EE_PeriodMapCurveReadings...



-- ----------------------------------------------------------------------------------

create or replace type myCurvePeriodMapValues as object
(EPCM_calendar_id number,
EPCM_season_id number,
EPCM_day_id number,
EPCM_date date,
Curve_id number,
p_no varchar2(6),
period_id number(9),
p_value number);
/
/* Source of TYPE BODY MYCURVEPERIODMAPVALUES is not available */
/
create or replace type ty_PeriodMap_Curvedetails as table of myCurvePeriodMapValues;
/
create or replace function EE_PeriodMapCurveReadings(p_cursor in sys_refcursor) return ty_PeriodMap_Curvedetails

PIPELINED IS
l_rec myCurvePeriodMapValues := myCurvePeriodMapValues(null,null,null,null,null,null,null,null);


Type ty_cal_map IS RECORD
(Cal_id NUMBER,
Season_Id NUMBER,
Day_Id NUMBER,
C_Date DATE,
curve_id NUMBER,
p0030 number, p0100 number, p0130 number, p0200 number, p0230 number, p0300 number,
p0330 number, p0400 number, p0430 number, p0500 number, p0530 number, p0600 number,
p0630 number, p0700 number, p0730 number, p0800 number, p0830 number, p0900 number,
p0930 number, p1000 number, p1030 number, p1100 number, p1130 number, p1200 number,
p1230 number, p1300 number, p1330 number, p1400 number, p1430 number, p1500 number,
p1530 number, p1600 number, p1630 number, p1700 number, p1730 number, p1800 number,
p1830 number, p1900 number, p1930 number, p2000 number, p2030 number, p2100 number,
p2130 number, p2200 number, p2230 number, p2300 number, p2330 number, p2400 number,
c0030 number, c0100 number, c0130 number, c0200 number, c0230 number, c0300 number,
c0330 number, c0400 number, c0430 number, c0500 number, c0530 number, c0600 number,
c0630 number, c0700 number, c0730 number, c0800 number, c0830 number, c0900 number,
c0930 number, c1000 number, c1030 number, c1100 number, c1130 number, c1200 number,
c1230 number, c1300 number, c1330 number, c1400 number, c1430 number, c1500 number,
c1530 number, c1600 number, c1630 number, c1700 number, c1730 number, c1800 number,
c1830 number, c1900 number, c1930 number, c2000 number, c2030 number, c2100 number,
c2130 number, c2200 number, c2230 number, c2300 number, c2330 number, c2400 number);


Type ty_RP_CalPer_Map is table of ty_cal_map index by binary_integer;

ty_output_values Ty_Rp_Calper_Map;


begin
loop
fetch p_cursor bulk collect into ty_output_values limit 100; -- _data, qq_data limit 100;
for i in 1 .. ty_output_values.count
loop

l_rec.epcm_calendar_id := ty_output_values(i).Cal_id;
l_rec.epcm_season_id := ty_output_values(i).Season_id;
l_rec.epcm_day_id := ty_output_values(i).Day_id;
l_rec.epcm_date := ty_output_values(i).c_date;
l_rec.curve_id := ty_output_values(i).curve_id;
l_rec.p_no := 'P0030';
l_rec.period_id := ty_output_values(i).p0030;
l_rec.p_value := ty_output_values(i).c0030;
pipe row( l_rec );
l_rec.p_no := 'P0100';
l_rec.period_id := ty_output_values(i).p0100;
l_rec.p_value := ty_output_values(i).c0100;
pipe row( l_rec );
l_rec.p_no := 'P0130';
l_rec.period_id := ty_output_values(i).p0130;
l_rec.p_value := ty_output_values(i).c0130;
pipe row( l_rec );

...
l_rec.p_no := 'P2400';
l_rec.period_id := ty_output_values(i).p2400;
l_rec.p_value := ty_output_values(i).c2400;

end loop;
exit when p_cursor%notfound;
end loop;
close p_cursor;
return;
-- end;




end EE_PeriodMapCurveReadings;
/


-- ----------------------------------------------------------------------------------

This produced the vastly improved results:



timing for: new_RP_functions
Elapsed: 00:00:00.00
ep@EP>
ep@EP> set autotrace traceonly;
ep@EP>
ep@EP>
ep@EP> select * from TABLE(EE_PeriodMapCurveReadings(cursor(
2 select cm.EPCM_CALENDAR_ID, cm.EPCM_SEASON_ID, cm.EPCM_DAY_ID, CM.EPCM_DATE, cd.curve_id,
3 pm.P0030, pm.P0100,pm.P0130, pm.P0200,pm.P0230, pm.P0300,pm.P0330, pm.P0400,pm.P0430, pm.P0
500,
4 pm.P0530, pm.P0600,pm.P0630, pm.P0700,pm.P0730, pm.P0800,pm.P0830, pm.P0900,pm.P0930, pm.P1
000,
5 pm.P1030, pm.P1100,pm.P1130, pm.P1200,pm.P1230, pm.P1300,pm.P1330, pm.P1400,pm.P1430, pm.P1
500,
6 pm.P1530, pm.P1600,pm.P1630, pm.P1700,pm.P1730, pm.P1800,pm.P1830, pm.P1900,pm.P1930, pm.P2
000,
7 pm.P2030, pm.P2100,pm.P2130, pm.P2200,pm.P2230, pm.P2300,pm.P2330, pm.P2400,
8 cd.P0030, cd.P0100,cd.P0130, cd.P0200,cd.P0230, cd.P0300,cd.P0330, cd.P0400,cd.P0430, cd.P0
500,
9 cd.P0530, cd.P0600,cd.P0630, cd.P0700,cd.P0730, cd.P0800,cd.P0830, cd.P0900,cd.P0930, cd.P1
000,
10 cd.P1030, cd.P1100,cd.P1130, cd.P1200,cd.P1230, cd.P1300,cd.P1330, cd.P1400,cd.P1430, cd.P1
500,
11 cd.P1530, cd.P1600,cd.P1630, cd.P1700,cd.P1730, cd.P1800,cd.P1830, cd.P1900,cd.P1930, cd.P2
000,
12 cd.P2030, cd.P2100,cd.P2130, cd.P2200,cd.P2230, cd.P2300,cd.P2330, cd.P2400
13 from ep_calendar_map cm,
14 ep_period_map pm,
15 ep_curvedetails cd
16 where cm.EPCM_CALENDAR_ID = pm.EPPM_CALENDAR_ID
17 and cm.EPCM_SEASON_ID = pm.EPPM_SEASON_ID
18 and cm.EPCM_DAY_ID = pm.EPPM_DAY_ID
19 and cm.EPCM_DATE = cd.curvedetails_date
20 and cd.curve_id = 120403
21 and cd.curvedetails_date between '01-Jul-2003' and '10-Jul-2003'
22 )));

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=8168 Bytes=56
19584)

1 0 VIEW (Cost=4 Card=8168 Bytes=5619584)
2 1 COLLECTION ITERATOR (PICKLER FETCH) OF 'EE_PERIODMAPCURV
EREADINGS'





Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
7309 consistent gets
0 physical reads
0 redo size
204772 bytes sent via SQL*Net to client
7770 bytes received via SQL*Net from client
663 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9917 rows processed

ep@EP>
ep@EP> timing show
timing for: new_RP_functions
Elapsed: 00:00:01.01
ep@EP> spool off



TKPROF: Release 9.2.0.3.0 - Production on Thu Oct 16 23:50:54 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: ep_ora_2880.trc
Sort options: fchela exeela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

select * from TABLE(EE_PeriodMapCurveReadings(cursor(
select cm.EPCM_CALENDAR_ID, cm.EPCM_SEASON_ID, cm.EPCM_DAY_ID, CM.EPCM_DATE, cd.curve_id,
pm.P0030, pm.P0100,pm.P0130, pm.P0200,pm.P0230, pm.P0300,pm.P0330, pm.P0400,pm.P0430, pm.P0500,
pm.P0530, pm.P0600,pm.P0630, pm.P0700,pm.P0730, pm.P0800,pm.P0830, pm.P0900,pm.P0930, pm.P1000,
pm.P1030, pm.P1100,pm.P1130, pm.P1200,pm.P1230, pm.P1300,pm.P1330, pm.P1400,pm.P1430, pm.P1500,
pm.P1530, pm.P1600,pm.P1630, pm.P1700,pm.P1730, pm.P1800,pm.P1830, pm.P1900,pm.P1930, pm.P2000,
pm.P2030, pm.P2100,pm.P2130, pm.P2200,pm.P2230, pm.P2300,pm.P2330, pm.P2400,
cd.P0030, cd.P0100,cd.P0130, cd.P0200,cd.P0230, cd.P0300,cd.P0330, cd.P0400,cd.P0430, cd.P0500,
cd.P0530, cd.P0600,cd.P0630, cd.P0700,cd.P0730, cd.P0800,cd.P0830, cd.P0900,cd.P0930, cd.P1000,
cd.P1030, cd.P1100,cd.P1130, cd.P1200,cd.P1230, cd.P1300,cd.P1330, cd.P1400,cd.P1430, cd.P1500,
cd.P1530, cd.P1600,cd.P1630, cd.P1700,cd.P1730, cd.P1800,cd.P1830, cd.P1900,cd.P1930, cd.P2000,
cd.P2030, cd.P2100,cd.P2130, cd.P2200,cd.P2230, cd.P2300,cd.P2330, cd.P2400
from ep_calendar_map cm,
ep_period_map pm,
ep_curvedetails cd
where cm.EPCM_CALENDAR_ID = pm.EPPM_CALENDAR_ID
and cm.EPCM_SEASON_ID = pm.EPPM_SEASON_ID
and cm.EPCM_DAY_ID = pm.EPPM_DAY_ID
and cm.EPCM_DATE = cd.curvedetails_date
and cd.curve_id = 120403
and cd.curvedetails_date between '01-Jul-2003' and '10-Jul-2003'
)))

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.37 0.41 0 125 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 42 1.12 1.24 0 14834 0 19834
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 46 1.50 1.65 0 14959 0 19834

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 57 (EP)

Rows Row Source Operation
------- ---------------------------------------------------
9917 VIEW (cr=7527 r=0 w=0 time=494932 us)
9917 COLLECTION ITERATOR PICKLER FETCH (cr=7527 r=0 w=0 time=431058 us)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
9917 VIEW
9917 COLLECTION ITERATOR (PICKLER FETCH) OF
'EE_PERIODMAPCURVEREADINGS'


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 42 0.00 0.00
SQL*Net message from client 42 0.00 0.12
SQL*Net more data to client 118 0.00 0.06
********************************************************************************

SELECT "A4"."EPCM_CALENDAR_ID" "EPCM_CALENDAR_ID","A4"."EPCM_SEASON_ID"
"EPCM_SEASON_ID","A4"."EPCM_DAY_ID" "EPCM_DAY_ID","A4"."EPCM_DATE"
"EPCM_DATE","A2"."CURVE_ID" "CURVE_ID","A3"."P0030" "P0030","A3"."P0100"
"P0100","A3"."P0130" "P0130","A3"."P0200" "P0200","A3"."P0230" "P0230",
"A3"."P0300" "P0300","A3"."P0330" "P0330","A3"."P0400" "P0400","A3"."P0430"
"P0430","A3"."P0500" "P0500","A3"."P0530" "P0530","A3"."P0600" "P0600",
"A3"."P0630" "P0630","A3"."P0700" "P0700","A3"."P0730" "P0730","A3"."P0800"
"P0800","A3"."P0830" "P0830","A3"."P0900" "P0900","A3"."P0930" "P0930",
"A3"."P1000" "P1000","A3"."P1030" "P1030","A3"."P1100" "P1100","A3"."P1130"
"P1130","A3"."P1200" "P1200","A3"."P1230" "P1230","A3"."P1300" "P1300",
"A3"."P1330" "P1330","A3"."P1400" "P1400","A3"."P1430" "P1430","A3"."P1500"
"P1500","A3"."P1530" "P1530","A3"."P1600" "P1600","A3"."P1630" "P1630",
"A3"."P1700" "P1700","A3"."P1730" "P1730","A3"."P1800" "P1800","A3"."P1830"
"P1830","A3"."P1900" "P1900","A3"."P1930" "P1930","A3"."P2000" "P2000",
"A3"."P2030" "P2030","A3"."P2100" "P2100","A3"."P2130" "P2130","A3"."P2200"
"P2200","A3"."P2230" "P2230","A3"."P2300" "P2300","A3"."P2330" "P2330",
"A3"."P2400" "P2400","A2"."P0030" "P0030","A2"."P0100" "P0100","A2"."P0130"
"P0130","A2"."P0200" "P0200","A2"."P0230" "P0230","A2"."P0300" "P0300",
"A2"."P0330" "P0330","A2"."P0400" "P0400","A2"."P0430" "P0430","A2"."P0500"
"P0500","A2"."P0530" "P0530","A2"."P0600" "P0600","A2"."P0630" "P0630",
"A2"."P0700" "P0700","A2"."P0730" "P0730","A2"."P0800" "P0800","A2"."P0830"
"P0830","A2"."P0900" "P0900","A2"."P0930" "P0930","A2"."P1000" "P1000",
"A2"."P1030" "P1030","A2"."P1100" "P1100","A2"."P1130" "P1130","A2"."P1200"
"P1200","A2"."P1230" "P1230","A2"."P1300" "P1300","A2"."P1330" "P1330",
"A2"."P1400" "P1400","A2"."P1430" "P1430","A2"."P1500" "P1500","A2"."P1530"
"P1530","A2"."P1600" "P1600","A2"."P1630" "P1630","A2"."P1700" "P1700",
"A2"."P1730" "P1730","A2"."P1800" "P1800","A2"."P1830" "P1830","A2"."P1900"
"P1900","A2"."P1930" "P1930","A2"."P2000" "P2000","A2"."P2030" "P2030",
"A2"."P2100" "P2100","A2"."P2130" "P2130","A2"."P2200" "P2200","A2"."P2230"
"P2230","A2"."P2300" "P2300","A2"."P2330" "P2330","A2"."P2400" "P2400"
FROM
"EP_CALENDAR_MAP" "A4","EP_PERIOD_MAP" "A3","EP_CURVEDETAILS" "A2" WHERE
"A4"."EPCM_CALENDAR_ID"="A3"."EPPM_CALENDAR_ID" AND "A4"."EPCM_SEASON_ID"=
"A3"."EPPM_SEASON_ID" AND "A4"."EPCM_DAY_ID"="A3"."EPPM_DAY_ID" AND
"A4"."EPCM_DATE"="A2"."CURVEDETAILS_DATE" AND "A2"."CURVE_ID"=120403 AND
"A2"."CURVEDETAILS_DATE">='01-Jul-2003' AND "A2"."CURVEDETAILS_DATE"<=
'10-Jul-2003'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 6 0.31 0.29 0 14594 0 422
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 0.31 0.29 0 14594 0 422

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 57 (EP) (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
211 NESTED LOOPS (cr=7297 r=0 w=0 time=133946 us)
7250 MERGE JOIN CARTESIAN (cr=43 r=0 w=0 time=54967 us)
10 TABLE ACCESS BY INDEX ROWID EP_CURVEDETAILS (cr=12 r=0 w=0 time=1660 us)
365 INDEX RANGE SCAN EP_CUREVEDETAILS_ID_INDEX (cr=6 r=0 w=0 time=700 us)(object id 37565)
7250 BUFFER SORT (cr=31 r=0 w=0 time=31077 us)
725 TABLE ACCESS FULL EP_PERIOD_MAP (cr=31 r=0 w=0 time=1616 us)
211 INDEX UNIQUE SCAN EP_CALENDAR_MAP_IDX (cr=7254 r=0 w=0 time=41198 us)(object id 38588)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
211 NESTED LOOPS
7250 MERGE JOIN (CARTESIAN)
10 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'EP_CURVEDETAILS'
365 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'EP_CUREVEDETAILS_ID_INDEX' (NON-UNIQUE)
7250 BUFFER (SORT)
725 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EP_PERIOD_MAP'
211 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'EP_CALENDAR_MAP_IDX'
(UNIQUE)

********************************************************************************



My first question is... did we approach this implementation of the pipe function appropriately?


We wonder because... it was NOT as fast as the Decode or Case approach!!!!


Here is the structure and benchmark results:

-- --------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW EP.EP_V_PERIODMAP_CURVE_READINGS
(EPCM_CALENDAR_ID, EPCM_SEASON_ID, EPCM_DAY_ID, EPCM_DATE, CURVE_ID,
P_NO, PERIOD_ID, P_VALUE)
AS
select cm.EPCM_CALENDAR_ID, cm.EPCM_SEASON_ID, cm.EPCM_DAY_ID, cm.EPCM_DATE, EP.CURVE_ID,
decode(x, 1, 'P0030', 2, 'P0100', 3, 'P0130', 4, 'P0200', 5, 'P0230', 6, 'P0300', 7, 'P0330', 8, 'P0400',
9, 'P0430', 10, 'P0500', 11, 'P0530', 12, 'P0600', 13, 'P0630', 14, 'P0700', 15, 'P0730', 16, 'P0800',
17, 'P0830', 18, 'P0900', 19, 'P0930', 20, 'P1000', 21, 'P1030', 22, 'P1100', 23, 'P1130', 24, 'P1200',
25, 'P1230', 26, 'P1300', 27, 'P1330', 28, 'P1400', 29, 'P1430', 30, 'P1500', 31, 'P1530', 32, 'P1600',
33, 'P1630', 34, 'P1700', 35, 'P1730', 36, 'P1800', 37, 'P1830', 38, 'P1900', 39, 'P1930', 40, 'P2000',
41, 'P2030', 42, 'P2100', 43, 'P2130', 44, 'P2200', 45, 'P2230', 46, 'P2300', 47, 'P2330', 48, 'P2400') P_no,

decode(x, 1, pm.P0030, 2, pm.P0100, 3, pm.P0130, 4, pm.P0200, 5, pm.P0230, 6, pm.P0300, 7, pm.P0330, 8, pm.P0400,
9, pm.P0430, 10, pm.P0500, 11, pm.P0530, 12, pm.P0600, 13, pm.P0630, 14, pm.P0700, 15, pm.P0730, 16, pm.P0800,
17, pm.P0830, 18, pm.P0900, 19, pm.P0930, 20, pm.P1000, 21, pm.P1030, 22, pm.P1100, 23, pm.P1130, 24, pm.P1200,
25, pm.P1230, 26, pm.P1300, 27, pm.P1330, 28, pm.P1400, 29, pm.P1430, 30, pm.P1500, 31, pm.P1530, 32, pm.P1600,
33, pm.P1630, 34, pm.P1700, 35, pm.P1730, 36, pm.P1800, 37, pm.P1830, 38, pm.P1900, 39, pm.P1930, 40, pm.P2000,
41, pm.P2030, 42, pm.P2100, 43, pm.P2130, 44, pm.P2200, 45, pm.P2230, 46, pm.P2300, 47, pm.P2330, 48, pm.P2400) Period_ID,

decode(x, 1, ep.P0030, 2, EP.P0100, 3, EP.P0130, 4, EP.P0200, 5, EP.P0230, 6, EP.P0300, 7, EP.P0330, 8, EP.P0400,
9, EP.P0430, 10, EP.P0500, 11, EP.P0530, 12, EP.P0600, 13, EP.P0630, 14, EP.P0700, 15, EP.P0730, 16, EP.P0800,
17, EP.P0830, 18, EP.P0900, 19, EP.P0930, 20, EP.P1000, 21, EP.P1030, 22, EP.P1100, 23, EP.P1130, 24, EP.P1200,
25, EP.P1230, 26, EP.P1300, 27, EP.P1330, 28, EP.P1400, 29, EP.P1430, 30, EP.P1500, 31, EP.P1530, 32, EP.P1600,
33, EP.P1630, 34, EP.P1700, 35, EP.P1730, 36, EP.P1800, 37, EP.P1830, 38, EP.P1900, 39, EP.P1930, 40, EP.P2000,
41, EP.P2030, 42, EP.P2100, 43, EP.P2130, 44, EP.P2200, 45, EP.P2230, 46, EP.P2300, 47, EP.P2330, 48, EP.P2400) P_value


from ( select x from tall_dual where x <= 48) b,
ep_calendar_map cm,
ep_period_map pm,
ep_curvedetails ep
where cm.EPCM_CALENDAR_ID = pm.EPPM_CALENDAR_ID
and cm.EPCM_SEASON_ID = pm.EPPM_SEASON_ID
and cm.EPCM_DAY_ID = pm.EPPM_DAY_ID
and cm.EPCM_DATE = ep.curvedetails_date;




timing for: Benchmark
Elapsed: 00:00:00.00
ep@EP>
ep@EP> select * from EP_V_PERIODMAP_CURVE_READINGS where curve_id = 120403 and EPCM_DATE between '01-Jul-2003' and '10-Jul-2003';

10128 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=315 Bytes=83
790)

1 0 MERGE JOIN (CARTESIAN) (Cost=12 Card=315 Bytes=83790)
2 1 NESTED LOOPS (Cost=11 Card=1 Bytes=253)
3 2 MERGE JOIN (CARTESIAN) (Cost=11 Card=594 Bytes=140778)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'EP_CURVEDETAILS' (
Cost=9 Card=1 Bytes=41)

5 4 INDEX (RANGE SCAN) OF 'EP_CUREVEDETAILS_ID_INDEX'
(NON-UNIQUE) (Cost=3 Card=286)

6 3 BUFFER (SORT) (Cost=2 Card=725 Bytes=142100)
7 6 TABLE ACCESS (FULL) OF 'EP_PERIOD_MAP' (Cost=2 Car
d=725 Bytes=142100)

8 2 INDEX (UNIQUE SCAN) OF 'EP_CALENDAR_MAP_IDX' (UNIQUE)
9 1 BUFFER (SORT) (Cost=12 Card=408 Bytes=5304)
10 9 INDEX (RANGE SCAN) OF 'SYS_IOT_TOP_38649' (UNIQUE) (Co
st=1 Card=408 Bytes=5304)





Statistics
----------------------------------------------------------
4073 recursive calls
0 db block gets
8076 consistent gets
0 physical reads
0 redo size
129722 bytes sent via SQL*Net to client
719 bytes received via SQL*Net from client
22 SQL*Net roundtrips to/from client
44 sorts (memory)
0 sorts (disk)
10128 rows processed

ep@EP>
ep@EP> set echo off

10128 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=315 Bytes=83
790)

1 0 MERGE JOIN (CARTESIAN) (Cost=12 Card=315 Bytes=83790)
2 1 NESTED LOOPS (Cost=11 Card=1 Bytes=253)
3 2 MERGE JOIN (CARTESIAN) (Cost=11 Card=594 Bytes=140778)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'EP_CURVEDETAILS' (
Cost=9 Card=1 Bytes=41)

5 4 INDEX (RANGE SCAN) OF 'EP_CUREVEDETAILS_ID_INDEX'
(NON-UNIQUE) (Cost=3 Card=286)

6 3 BUFFER (SORT) (Cost=2 Card=725 Bytes=142100)
7 6 TABLE ACCESS (FULL) OF 'EP_PERIOD_MAP' (Cost=2 Car
d=725 Bytes=142100)

8 2 INDEX (UNIQUE SCAN) OF 'EP_CALENDAR_MAP_IDX' (UNIQUE)
9 1 BUFFER (SORT) (Cost=12 Card=408 Bytes=5304)
10 9 INDEX (RANGE SCAN) OF 'SYS_IOT_TOP_38649' (UNIQUE) (Co
st=1 Card=408 Bytes=5304)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7333 consistent gets
0 physical reads
0 redo size
129722 bytes sent via SQL*Net to client
719 bytes received via SQL*Net from client
22 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10128 rows processed

timing for: Benchmark
Elapsed: 00:00:01.05




TKPROF: Release 9.2.0.3.0 - Production on Fri Oct 17 00:18:20 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: ep_ora_1272.trc
Sort options: fchela exeela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
****
Tom Kyte
October 16, 2003 - 3:08 pm UTC

I don't see anything obviously wrong in your implementation -- but that is why i gave you two methods -- benchmark them and see which works best in your case.


Most of the times, if you can do it in straight sql, thats best. there are cases where plsql can edge it out, but here -- the massive assignments probably killed us.

Pipe function vs Decode (cont'd)

Alex, October 16, 2003 - 10:39 am UTC

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
211 NESTED LOOPS
7250 MERGE JOIN (CARTESIAN)
10 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'EP_CURVEDETAILS'
365 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'EP_CUREVEDETAILS_ID_INDEX' (NON-UNIQUE)
7250 BUFFER (SORT)
725 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EP_PERIOD_MAP'
211 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'EP_CALENDAR_MAP_IDX'
(UNIQUE)

********************************************************************************



My first question is... did we approach this implementation of the pipe function appropriately?


We wonder because... it was NOT as fast as the Decode or Case approach!!!!


Here is the structure and benchmark results:

-- --------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW EP.EP_V_PERIODMAP_CURVE_READINGS
(EPCM_CALENDAR_ID, EPCM_SEASON_ID, EPCM_DAY_ID, EPCM_DATE, CURVE_ID,
P_NO, PERIOD_ID, P_VALUE)
AS
select cm.EPCM_CALENDAR_ID, cm.EPCM_SEASON_ID, cm.EPCM_DAY_ID, cm.EPCM_DATE, EP.CURVE_ID,
decode(x, 1, 'P0030', 2, 'P0100', 3, 'P0130', 4, 'P0200', 5, 'P0230', 6, 'P0300', 7, 'P0330', 8, 'P0400',
9, 'P0430', 10, 'P0500', 11, 'P0530', 12, 'P0600', 13, 'P0630', 14, 'P0700', 15, 'P0730', 16, 'P0800',
17, 'P0830', 18, 'P0900', 19, 'P0930', 20, 'P1000', 21, 'P1030', 22, 'P1100', 23, 'P1130', 24, 'P1200',
25, 'P1230', 26, 'P1300', 27, 'P1330', 28, 'P1400', 29, 'P1430', 30, 'P1500', 31, 'P1530', 32, 'P1600',
33, 'P1630', 34, 'P1700', 35, 'P1730', 36, 'P1800', 37, 'P1830', 38, 'P1900', 39, 'P1930', 40, 'P2000',
41, 'P2030', 42, 'P2100', 43, 'P2130', 44, 'P2200', 45, 'P2230', 46, 'P2300', 47, 'P2330', 48, 'P2400') P_no,

decode(x, 1, pm.P0030, 2, pm.P0100, 3, pm.P0130, 4, pm.P0200, 5, pm.P0230, 6, pm.P0300, 7, pm.P0330, 8, pm.P0400,
9, pm.P0430, 10, pm.P0500, 11, pm.P0530, 12, pm.P0600, 13, pm.P0630, 14, pm.P0700, 15, pm.P0730, 16, pm.P0800,
17, pm.P0830, 18, pm.P0900, 19, pm.P0930, 20, pm.P1000, 21, pm.P1030, 22, pm.P1100, 23, pm.P1130, 24, pm.P1200,
25, pm.P1230, 26, pm.P1300, 27, pm.P1330, 28, pm.P1400, 29, pm.P1430, 30, pm.P1500, 31, pm.P1530, 32, pm.P1600,
33, pm.P1630, 34, pm.P1700, 35, pm.P1730, 36, pm.P1800, 37, pm.P1830, 38, pm.P1900, 39, pm.P1930, 40, pm.P2000,
41, pm.P2030, 42, pm.P2100, 43, pm.P2130, 44, pm.P2200, 45, pm.P2230, 46, pm.P2300, 47, pm.P2330, 48, pm.P2400) Period_ID,

decode(x, 1, ep.P0030, 2, EP.P0100, 3, EP.P0130, 4, EP.P0200, 5, EP.P0230, 6, EP.P0300, 7, EP.P0330, 8, EP.P0400,
9, EP.P0430, 10, EP.P0500, 11, EP.P0530, 12, EP.P0600, 13, EP.P0630, 14, EP.P0700, 15, EP.P0730, 16, EP.P0800,
17, EP.P0830, 18, EP.P0900, 19, EP.P0930, 20, EP.P1000, 21, EP.P1030, 22, EP.P1100, 23, EP.P1130, 24, EP.P1200,
25, EP.P1230, 26, EP.P1300, 27, EP.P1330, 28, EP.P1400, 29, EP.P1430, 30, EP.P1500, 31, EP.P1530, 32, EP.P1600,
33, EP.P1630, 34, EP.P1700, 35, EP.P1730, 36, EP.P1800, 37, EP.P1830, 38, EP.P1900, 39, EP.P1930, 40, EP.P2000,
41, EP.P2030, 42, EP.P2100, 43, EP.P2130, 44, EP.P2200, 45, EP.P2230, 46, EP.P2300, 47, EP.P2330, 48, EP.P2400) P_value


from ( select x from tall_dual where x <= 48) b,
ep_calendar_map cm,
ep_period_map pm,
ep_curvedetails ep
where cm.EPCM_CALENDAR_ID = pm.EPPM_CALENDAR_ID
and cm.EPCM_SEASON_ID = pm.EPPM_SEASON_ID
and cm.EPCM_DAY_ID = pm.EPPM_DAY_ID
and cm.EPCM_DATE = ep.curvedetails_date;




timing for: Benchmark
Elapsed: 00:00:00.00
ep@EP>
ep@EP> select * from EP_V_PERIODMAP_CURVE_READINGS where curve_id = 120403 and EPCM_DATE between '01-Jul-2003' and '10-Jul-2003';

10128 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=315 Bytes=83
790)

1 0 MERGE JOIN (CARTESIAN) (Cost=12 Card=315 Bytes=83790)
2 1 NESTED LOOPS (Cost=11 Card=1 Bytes=253)
3 2 MERGE JOIN (CARTESIAN) (Cost=11 Card=594 Bytes=140778)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'EP_CURVEDETAILS' (
Cost=9 Card=1 Bytes=41)

5 4 INDEX (RANGE SCAN) OF 'EP_CUREVEDETAILS_ID_INDEX'
(NON-UNIQUE) (Cost=3 Card=286)

6 3 BUFFER (SORT) (Cost=2 Card=725 Bytes=142100)
7 6 TABLE ACCESS (FULL) OF 'EP_PERIOD_MAP' (Cost=2 Car
d=725 Bytes=142100)

8 2 INDEX (UNIQUE SCAN) OF 'EP_CALENDAR_MAP_IDX' (UNIQUE)
9 1 BUFFER (SORT) (Cost=12 Card=408 Bytes=5304)
10 9 INDEX (RANGE SCAN) OF 'SYS_IOT_TOP_38649' (UNIQUE) (Co
st=1 Card=408 Bytes=5304)





Statistics
----------------------------------------------------------
4073 recursive calls
0 db block gets
8076 consistent gets
0 physical reads
0 redo size
129722 bytes sent via SQL*Net to client
719 bytes received via SQL*Net from client
22 SQL*Net roundtrips to/from client
44 sorts (memory)
0 sorts (disk)
10128 rows processed

ep@EP>
ep@EP> set echo off

10128 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=315 Bytes=83
790)

1 0 MERGE JOIN (CARTESIAN) (Cost=12 Card=315 Bytes=83790)
2 1 NESTED LOOPS (Cost=11 Card=1 Bytes=253)
3 2 MERGE JOIN (CARTESIAN) (Cost=11 Card=594 Bytes=140778)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'EP_CURVEDETAILS' (
Cost=9 Card=1 Bytes=41)

5 4 INDEX (RANGE SCAN) OF 'EP_CUREVEDETAILS_ID_INDEX'
(NON-UNIQUE) (Cost=3 Card=286)

6 3 BUFFER (SORT) (Cost=2 Card=725 Bytes=142100)
7 6 TABLE ACCESS (FULL) OF 'EP_PERIOD_MAP' (Cost=2 Car
d=725 Bytes=142100)

8 2 INDEX (UNIQUE SCAN) OF 'EP_CALENDAR_MAP_IDX' (UNIQUE)
9 1 BUFFER (SORT) (Cost=12 Card=408 Bytes=5304)
10 9 INDEX (RANGE SCAN) OF 'SYS_IOT_TOP_38649' (UNIQUE) (Co
st=1 Card=408 Bytes=5304)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7333 consistent gets
0 physical reads
0 redo size
129722 bytes sent via SQL*Net to client
719 bytes received via SQL*Net from client
22 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10128 rows processed

timing for: Benchmark
Elapsed: 00:00:01.05




TKPROF: Release 9.2.0.3.0 - Production on Fri Oct 17 00:18:20 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: ep_ora_1272.trc
Sort options: fchela exeela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

select * from EP_V_PERIODMAP_CURVE_READINGS where curve_id = 120403
and EPCM_DATE between '01-Jul-2003' and '10-Jul-2003'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.25 0.30 0 743 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 44 0.59 0.55 0 14666 0 20256
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 48 0.84 0.85 0 15409 0 20256

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 57 (EP)

Rows Row Source Operation
------- ---------------------------------------------------
10128 MERGE JOIN CARTESIAN (cr=7333 r=0 w=0 time=175006 us)
211 NESTED LOOPS (cr=7332 r=0 w=0 time=119713 us)
7250 MERGE JOIN CARTESIAN (cr=59 r=0 w=0 time=54276 us)
10 TABLE ACCESS BY INDEX ROWID EP_CURVEDETAILS (cr=28 r=0 w=0 time=1592 us)
365 INDEX RANGE SCAN EP_CUREVEDETAILS_ID_INDEX (cr=14 r=0 w=0 time=783 us)(object id 37565)
7250 BUFFER SORT (cr=31 r=0 w=0 time=30287 us)
725 TABLE ACCESS FULL EP_PERIOD_MAP (cr=31 r=0 w=0 time=1565 us)
211 INDEX UNIQUE SCAN EP_CALENDAR_MAP_IDX (cr=7273 r=0 w=0 time=34521 us)(object id 38588)
10128 BUFFER SORT (cr=1 r=0 w=0 time=17773 us)
48 INDEX RANGE SCAN SYS_IOT_TOP_38649 (cr=1 r=0 w=0 time=91 us)(object id 38650)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
10128 MERGE JOIN (CARTESIAN)
211 NESTED LOOPS
7250 MERGE JOIN (CARTESIAN)
10 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'EP_CURVEDETAILS'
365 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'EP_CUREVEDETAILS_ID_INDEX' (NON-UNIQUE)
7250 BUFFER (SORT)
725 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EP_PERIOD_MAP'
211 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'EP_CALENDAR_MAP_IDX' (UNIQUE)
10128 BUFFER (SORT)
48 INDEX (RANGE SCAN) OF 'SYS_IOT_TOP_38649' (UNIQUE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 44 0.00 0.00
SQL*Net message from client 44 0.00 0.11
SQL*Net more data to client 120 0.00 0.05
********************************************************************************




-- ----------------------------------------------------------------------

So Decode was a bit slower.. but not by much! And in other tests it was actually faster - when returning larger date ranges!


Ultimately we have achieved outstanding results but I was hoping you might provide some more effective analysis of our pipe function...

Thank you so very much!


PS.. any plans to support HTML on your site.. so we can provide some colour formatting to make posts easier to read?






Number of columns

A reader, November 04, 2006 - 11:22 pm UTC

Oracle 9.2.0.6

Suppose I use a "generic" external table definition to parse/read a CSV file. My external table definition has 50 varchar2(4000) columns named c1..c50.

But if the CSV file being loaded has only 10 columns populated, how can I get at this number "10"? In other words, I would like to read the first row in the CSV file, pivot the row into columns and determine that only the first 10 are non-blank and return this number 10. 10 would be used in subsequent iterators and stuff.

Ideas? Thanks

Tom Kyte
November 05, 2006 - 9:11 am UTC

read the first row in the csv file and pivot the row into columns?!?!?!

the row is already full of columns, not sure what you mean.


select nvl2( c1, 1, 0 )+nvl2(c2, 1, 0) )+....nvl2(c50,1,0) into l_cnt from t
where rownum = 1;

would achieve what appears to be the stated goal.

Number of columns

A reader, November 05, 2006 - 10:47 am UTC

select nvl2( c1, 1, 0 )+nvl2(c2, 1, 0) )+....nvl2(c50,1,0) into l_cnt from t
where rownum = 1;

Doh, of course yes. But that would tolerate "holes" in the file i.e. empty columns. Any way to detect/avoid that?

Thanks

Tom Kyte
November 05, 2006 - 11:01 am UTC

but, umm, well - anything would tolerate "holes" - not really at all sure what you mean.

If you mean "first record looks like this:

1,,3,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

we would count 3, not four but even if we just looked for the "last column" without a null, you'd have to look at the entire file because row two could be:

1,2,3,4,5,6,7,8,9,10,,,,,,,,,,,,,,,,,


in that file - you have an intractable problem here. Unless you want to read the ENTIRE file.

so maybe you look for a different approach.


Agreed

A reader, November 05, 2006 - 5:29 pm UTC

Yes, I see what you are saying. I agree.

Taking this one step further. Suppose these columns in the CSV file need to be fed as arguments to a stored procedure using named parameter notation.

I would "build" the stored procedure string putting in a parameter for every non-null value in the CSV record.

l_string := 'begin sp(p_one=>:1,p_two=>:2,...p_fifty=>:50);end;'

Now, how would I execute this string using NDS?

EXECUTE IMMEDIATE l_string USING .... what?

The USING clause needs a static pre-determined list of values to bind into the :1,:2 bind variables. But the values I am supplying are dynamic, there could be 2 non-null values or 50

Ideas? Thanks

Tom Kyte
November 05, 2006 - 5:53 pm UTC

why would you dynamically call a stored procedure like that????

Generic code is....

bad code, in many many many cases.

And this one, well, this is looking really bad.


MAYBE what you want to do is this:

instead of writing "really generic code to do stuff", you write a code generator that you say "hey, when you process file X, it'll have 27 columns (because YOU MUST KNOW THIS, YOU JUST MUST)" and your code would produce the code that processes file X.

Generic code

A reader, November 05, 2006 - 8:53 pm UTC

Tom, as always, thanks a lot. You are right.

The allure of writing "generic" code and structures is sometimes too great. It is, after all, the holy grail of IT. Code and structures that can adapt and handle anything thrown at them by changing at most, some table/config parameters.

But, once you start to think about it, it becomes clear that it is a bad idea. Only the original author of the code can fathom what the heck it does.

Thanks again.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library