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.