Skip to Main Content
  • Questions

  • Faster way to Pivot the data and insert into another table

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Lalitha.

Asked: July 20, 2022 - 12:05 pm UTC

Last updated: July 28, 2022 - 2:21 pm UTC

Version: Oracle 12.1

Viewed 10K+ times! This question is

You Asked

Hi All,

I have a table to 1.8b records and need to pivot this table and insert it into another table. The performance of this SQL is very bad and it took around 8hr but no results.

Please find the sql below.

v_sql1 := q'{    INSERT  INTO SCPOMGR.FCSTWIDE (
 dmdunit,
 dmdgroup, loc, startdate, TYPE, fcstid, model, dmdcal, 
 period1, period2, period3, period4, period5, period6, period7, period8, period9, period10, period11, period12, period13, period14, period15, period16, period17, period18, period19, period20, 
 period21, period22, period23, period24, period25,period26, period27, period28, period29, period30, period31, period32, period33, period34, period35, period36, period37, period38, period39, period40,
 period41, period42, period43, period44, period45, period46, period47, period48, period49, period50, period51, period52, period53, period54, period55, period56, period57, period58, period59, period60, 
 period61, period62, period63, period64, period65, period66, period67, period68, period69, period70, period71, period72, period73, period74, period75, period76, period77, period78, period79, period80, 
 period81, period82, period83, period84, period85, period86, period87, period88, period89, period90, period91, period92, period93, period94, period95, period96, period97, period98, period99, period100,
 period101, period102, period103, period104, period105, period106, period107, period108, period109, period110, period111, period112, period113, period114, period115, period116, period117, period118, period119, period120,
 period121, period122, period123, period124, period125, period126, period127, period128, period129, period130, period131, period132, period133, period134, period135, period136, period137, period138, period139, period140,
 period141, period142, period143, period144, period145, period146, period147, period148, period149, period150, period151, period152, period153, period154, period155, period156, period157, period158, period159, period160,
 period161, period162, period163, period164, period165, period166, period167, period168, period169, period170, period171, period172, period173, period174, period175, period176, period177, period178, period179, period180,
 period181, period182, period183, period184, period185, period186, period187, period188, period189, period190, period191, period192, period193, period194, period195, period196, period197, period198, period199, period200,
 period201, period202, period203, period204, period205, period206, period207, period208, period209, period210, period211, period212, period213, period214, period215, period216, period217, period218, period219, period220,
 period221, period222, period223, period224, period225, period226, period227, period228, period229, period230, period231, period232, period233, period234, period235, period236, period237, period238, period239, period240, 
 period241, period242, period243, period244, period245, period246, period247, period248, period249, period250, period251, period252, period253, period254, period255, period256, period257, period258, period259, period260,
 period261, period262, period263, period264, period265, period266, period267, period268, period269, period270, period271, period272, period273, period274, period275, period276, period277, period278, period279, period280,
 period281, period282, period283, period284, period285, period286, period287, period288, period289, period290, period291, period292, period293, period294, period295, period296, period297, period298, period299, period300,
 period301, period302, period303, period304, period305, period306, period307, period308, period309, period310, period311, period312, period313, period314, period315, period316, period317, period318, period319, period320,
 period321, period322, period323, period324, period325, period326, period327, period328, period329, period330, period331, period332, period333, period334, period335, period336, period337, period338, period339, period340,
 period341, period342, period343, period344, period345, period346, period347, period348, period349, period350, period351, period352, period353, period354, period355, period356, period357, period358, period359, period360,
 period361, period362, period363, period364)}';

 
 v_sql2 := q'{SELECT 
 dmdunit,dmdgroup,loc,startdate,TYPE,fcstid,model,dmdcal,
 NVL(period1,0), NVL(period2,0), NVL(period3,0), NVL(period4,0), NVL(period5,0), NVL(period6,0), NVL(period7,0), NVL(period8,0), NVL(period9,0), NVL(period10,0), 
 NVL(period11,0), NVL(period12,0), NVL(period13,0), NVL(period14,0), NVL(period15,0), NVL(period16,0), NVL(period17,0), NVL(period18,0), NVL(period19,0), NVL(period20,0), 
 NVL(period21,0), NVL(period22,0), NVL(period23,0), NVL(period24,0), NVL(period25,0),NVL(period26,0), NVL(period27,0), NVL(period28,0), NVL(period29,0), NVL(period30,0),
 NVL(period31,0), NVL(period32,0), NVL(period33,0), NVL(period34,0), NVL(period35,0), NVL(period36,0), NVL(period37,0), NVL(period38,0), NVL(period39,0), NVL(period40,0),
 NVL(period41,0), NVL(period42,0), NVL(period43,0), NVL(period44,0), NVL(period45,0), NVL(period46,0), NVL(period47,0), NVL(period48,0), NVL(period49,0), NVL(period50,0), 
 NVL(period51,0), NVL(period52,0), NVL(period53,0), NVL(period54,0), NVL(period55,0), NVL(period56,0), NVL(period57,0), NVL(period58,0), NVL(period59,0), NVL(period60,0), 
 NVL(period61,0), NVL(period62,0), NVL(period63,0), NVL(period64,0), NVL(period65,0), NVL(period66,0), NVL(period67,0), NVL(period68,0), NVL(period69,0), NVL(period70,0), 
 NVL(period71,0), NVL(period72,0), NVL(period73,0), NVL(period74,0), NVL(period75,0), NVL(period76,0), NVL(period77,0), NVL(period78,0), NVL(period79,0), NVL(period80,0), 
 NVL(period81,0), NVL(period82,0), NVL(period83,0), NVL(period84,0), NVL(period85,0), NVL(period86,0), NVL(period87,0), NVL(period88,0), NVL(period89,0), NVL(period90,0), 
 NVL(period91,0), NVL(period92,0), NVL(period93,0), NVL(period94,0), NVL(period95,0), NVL(period96,0), NVL(period97,0), NVL(period98,0), NVL(period99,0), NVL(period100,0),
 NVL(period101,0), NVL(period102,0), NVL(period103,0), NVL(period104,0), NVL(period105,0), NVL(period106,0), NVL(period107,0), NVL(period108,0), NVL(period109,0), NVL(period110,0), 
 NVL(period111,0), NVL(period112,0), NVL(period113,0), NVL(period114,0), NVL(period115,0), NVL(period116,0), NVL(period117,0), NVL(period118,0), NVL(period119,0), NVL(period120,0),
 NVL(period121,0), NVL(period122,0), NVL(period123,0), NVL(period124,0), NVL(period125,0), NVL(period126,0), NVL(period127,0), NVL(period128,0), NVL(period129,0), NVL(period130,0),
 NVL(period131,0), NVL(period132,0), NVL(period133,0), NVL(period134,0), NVL(period135,0), NVL(period136,0), NVL(period137,0), NVL(period138,0), NVL(period139,0), NVL(period140,0),
 NVL(period141,0), NVL(period142,0), NVL(period143,0), NVL(period144,0), NVL(period145,0), NVL(period146,0), NVL(period147,0), NVL(period148,0), NVL(period149,0), NVL(period150,0), 
 NVL(period151,0), NVL(period152,0), NVL(period153,0), NVL(period154,0), NVL(period155,0), NVL(period156,0), NVL(period157,0), NVL(period158,0), NVL(period159,0), NVL(period160,0),
 NVL(period161,0), NVL(period162,0), NVL(period163,0), NVL(period164,0), NVL(period165,0), NVL(period166,0), NVL(period167,0), NVL(period168,0), NVL(period169,0), NVL(period170,0),
 NVL(period171,0), NVL(period172,0), NVL(period173,0), NVL(period174,0), NVL(period175,0), NVL(period176,0), NVL(period177,0), NVL(period178,0), NVL(period179,0), NVL(period180,0),
 NVL(period181,0), NVL(period182,0), NVL(period183,0), NVL(period184,0), NVL(period185,0), NVL(period186,0), NVL(period187,0), NVL(period188,0), NVL(period189,0), NVL(period190,0),
 NVL(period191,0), NVL(period192,0), NVL(period193,0), NVL(period194,0), NVL(period195,0), NVL(period196,0), NVL(period197,0), NVL(period198,0), NVL(period199,0), NVL(period200,0),
 NVL(period201,0), NVL(period202,0), NVL(period203,0), NVL(period204,0), NVL(period205,0), NVL(period206,0), NVL(period207,0), NVL(period208,0), NVL(period209,0), NVL(period210,0),
 NVL(period211,0), NVL(period212,0), NVL(period213,0), NVL(period214,0), NVL(period215,0), NVL(period216,0), NVL(period217,0), NVL(period218,0), NVL(period219,0), NVL(period220,0),
 NVL(period221,0), NVL(period222,0), NVL(period223,0), NVL(period224,0), NVL(period225,0), NVL(period226,0), NVL(period227,0), NVL(period228,0), NVL(period229,0), NVL(period230,0),
 NVL(period231,0), NVL(period232,0), NVL(period233,0), NVL(period234,0), NVL(period235,0), NVL(period236,0), NVL(period237,0), NVL(period238,0), NVL(period239,0), NVL(period240,0), 
 NVL(period241,0), NVL(period242,0), NVL(period243,0), NVL(period244,0), NVL(period245,0), NVL(period246,0), NVL(period247,0), NVL(period248,0), NVL(period249,0), NVL(period250,0),
 NVL(period251,0), NVL(period252,0), NVL(period253,0), NVL(period254,0), NVL(period255,0), NVL(period256,0), NVL(period257,0), NVL(period258,0), NVL(period259,0), NVL(period260,0),
 NVL(period261,0), NVL(period262,0), NVL(period263,0), NVL(period264,0), NVL(period265,0), NVL(period266,0), NVL(period267,0), NVL(period268,0), NVL(period269,0), NVL(period270,0),
 NVL(period271,0), NVL(period272,0), NVL(period273,0), NVL(period274,0), NVL(period275,0), NVL(period276,0), NVL(period277,0), NVL(period278,0), NVL(period279,0), NVL(period280,0),
 NVL(period281,0), NVL(period282,0), NVL(period283,0), NVL(period284,0), NVL(period285,0), NVL(period286,0), NVL(period287,0), NVL(period288,0), NVL(period289,0), NVL(period290,0),
 NVL(period291,0), NVL(period292,0), NVL(period293,0), NVL(period294,0), NVL(period295,0), NVL(period296,0), NVL(period297,0), NVL(period298,0), NVL(period299,0), NVL(period300,0),
 NVL(period301,0), NVL(period302,0), NVL(period303,0), NVL(period304,0), NVL(period305,0), NVL(period306,0), NVL(period307,0), NVL(period308,0), NVL(period309,0), NVL(period310,0),
 NVL(period311,0), NVL(period312,0), NVL(period313,0), NVL(period314,0), NVL(period315,0), NVL(period316,0), NVL(period317,0), NVL(period318,0), NVL(period319,0), NVL(period320,0),
 NVL(period321,0), NVL(period322,0), NVL(period323,0), NVL(period324,0), NVL(period325,0), NVL(period326,0), NVL(period327,0), NVL(period328,0), NVL(period329,0), NVL(period330,0),
 NVL(period331,0), NVL(period332,0), NVL(period333,0), NVL(period334,0), NVL(period335,0), NVL(period336,0), NVL(period337,0), NVL(period338,0), NVL(period339,0), NVL(period340,0),
 NVL(period341,0), NVL(period342,0), NVL(period343,0), NVL(period344,0), NVL(period345,0), NVL(period346,0), NVL(period347,0), NVL(period348,0), NVL(period349,0), NVL(period350,0),
 NVL(period351,0), NVL(period352,0), NVL(period353,0), NVL(period354,0), NVL(period355,0), NVL(period356,0), NVL(period357,0), NVL(period358,0), NVL(period359,0), NVL(period360,0),
 NVL(period361,0), NVL(period362,0), NVL(period363,0), NVL(period364,0)}';


 v_sql3 := q'{          
    FROM (SELECT /*+ INDEX(bf TMP_UDT_BY_FCST_WEEKLY_IDX1)*/ 
                bf.dmdunit  AS dmdunit,
    ' '        AS dmdgroup,
    bf.loc       AS loc,
    '}'||v_dmdpostdate||q'{' AS startdate,
    1            AS TYPE,
    ' '          AS fcstid,
    'LDE'        AS model,
    'DMDDAY'     AS dmdcal,
    bf.prediction_day,
    bf.mean,
    (ROW_NUMBER() OVER (partition by prediction_day order by bf.loc)) as rownumber
     FROM scpomgr.udt_by_fcst_weekly bf,scpomgr.dfu d
    WHERE d.dmdunit = bf.dmdunit AND d.loc = bf.loc AND bf.loc= '}'||c_loc||     
         q'{')
    PIVOT (SUM (mean) FOR prediction_day IN (
 TO_DATE ('}'||v_dmdpostdate||q'{','YYYY/MM/DD')+ 0  AS period1, TO_DATE ('}'||v_dmdpostdate||q'{','YYYY/MM/DD')+ 1  AS period2, TO_DATE ('}'||v_dmdpostdate||q'{','YYYY/MM/DD')+ 2  AS period3,
 TO_DATE ('}'||v_dmdpostdate||q'{','YYYY/MM/DD')+ 3  AS period4, TO_DATE ('}'||v_dmdpostdate||q'{','YYYY/MM/DD')+ 4  AS period5, TO_DATE ('}'||v_dmdpostdate||q'{','YYYY/MM/DD')+ 5  AS period6,
 TO_DATE ('}'||v_dmdpostdate||q'{','YYYY/MM/DD')+ 6  AS period7, TO_DATE ('}'||v_dmdpostdate||q'{','YYYY/MM/DD')+ 7  AS period8, TO_DATE ('}'||v_dmdpostdate||q'{','YYYY/MM/DD')+ 8  AS period9,
 TO_DATE ('}'||v_dmdpostdate||q'{','YYYY/MM/DD')+ 9  AS period10, TO_DATE ('}'||v_dmdpostdate||q'{','YYYY/MM/DD')+ 10  AS period11, TO_DATE ('}'||v_dmdpostdate||q'{','YYYY/MM/DD')+ 11  AS period12,....
 TO_DATE ('}'||v_dmdpostdate||q'{','YYYY/MM/DD')+ 363  AS period364,.....

execute immediate v_sql1||v_sql2||v_sql3;


Please let me know if there any better way to handle this.

and Chris said...

Hard to say without more details - really we see the execution plan to understanding what's going on here.

You can get this by running:

alter session set statistics_level = all;
set serveroutput off

<your SQL>

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


That said, some observations:

- If you're processing 1,800,000,000 rows, using index lookups is almost certainly the slowest way to process the data by far. Remove the INDEX hint.
- It looks like you're using dynamic SQL to generate the PIVOT list of values, but there are the days from v_dmdpostdate over the next year.

You should be able to replace this with a static SQL statement by mapping each day to a day number first. Then pivoting these values.

For example:

with rws as (
  select t.*,
         dense_rank () over ( partition by trunc ( prediction_day ) rn
  from   ... t
  where  prediction_day >= v_dmdpostdate
)
  select * from rws
  pivot ( 
     ... for rn in ( 1, 2, 3, ..., 364, 365, 366 )
  );


This does assume there's at least one row for each day. If days could be missing you'll have to generate these (e.g. by joining to a list of all the dates).

Is this answer out of date? If it is, please let us know via a Comment
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

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.