Current Position:Home > Tranforming columns into rows taking to long

Tranforming columns into rows taking to long

Update:11-30Source: network consolidation
Advertisement
Hi,
Please help me in tuning the query. I am trying to transform rows into columns. Its taking lots of time almost 13hrs for 500k records.
select
decode ( r,
          1,Col_1,
          2,Col_2,
          3,Col_3,
          4,Col_4,
          5,Col_5,
          6,Col_6,
          7,Col_7,
          8,Col_8,
          9,Col_9,
          10,Col_10,
          11,Col_11,
          12,Col_12,
          13,Col_13,
          14,Col_14,
          15,Col_15,
          16,Col_16,
          17,Col_17,
          18,Col_18,
          19,Col_19,
          20,Col_20) Col,
R ,
decode ( r,
          1,CASE WHEN SUBSTR(T1.Date_1,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_1,'YYYYDDD') ELSE NULL END,
          2,CASE WHEN SUBSTR(T1.Date_2,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_2,'YYYYDDD') ELSE NULL END,
          3,CASE WHEN SUBSTR(T1.Date_3,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_3,'YYYYDDD') ELSE NULL END,
          4,CASE WHEN SUBSTR(T1.Date_4,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_4,'YYYYDDD') ELSE NULL END,
          5,CASE WHEN SUBSTR(T1.Date_5,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_5,'YYYYDDD') ELSE NULL END,
          6,CASE WHEN SUBSTR(T1.Date_6,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_6,'YYYYDDD') ELSE NULL END,
          7,CASE WHEN SUBSTR(T1.Date_7,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_7,'YYYYDDD') ELSE NULL END,
          8,CASE WHEN SUBSTR(T1.Date_8,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_8,'YYYYDDD') ELSE NULL END,
          9,CASE WHEN SUBSTR(T1.Date_9,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_9,'YYYYDDD') ELSE NULL END,
          10,CASE WHEN SUBSTR(T1.Date_10,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_10,'YYYYDDD') ELSE NULL END,
11,CASE WHEN SUBSTR(T1.Date_11,5) BETWEEN '001'     AND '366'
               THEN TO_DATE(T1.Date_12,'YYYYDDD') ELSE NULL END,
          12,CASE WHEN SUBSTR(T1.Date_12,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_12,'YYYYDDD') ELSE NULL END,
          13,CASE WHEN SUBSTR(T1.Date_13,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_13,'YYYYDDD') ELSE NULL END,
          14,CASE WHEN SUBSTR(T1.Date_14,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_14,'YYYYDDD') ELSE NULL END,
          15,CASE WHEN SUBSTR(T1.Date_15,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_15,'YYYYDDD') ELSE NULL END,
          16,CASE WHEN SUBSTR(T1.Date_16,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_16,'YYYYDDD') ELSE NULL END,
          17,CASE WHEN SUBSTR(T1.Date_17,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_17,'YYYYDDD') ELSE NULL END,
          18,CASE WHEN SUBSTR(T1.Date_18,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_18,'YYYYDDD') ELSE NULL END,
          19,CASE WHEN SUBSTR(T1.Date_19,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_19,'YYYYDDD') ELSE NULL END,
          20,CASE WHEN SUBSTR(T1.Date_20,5) BETWEEN '001' AND '366'
               THEN TO_DATE(T1.Date_20,'YYYYDDD') ELSE NULL END)
Date from Table_1 t1,(select level R from dual connect by level <= 20)
where decode ( r,
          1,Col_1,
          2,Col_2,
          3,Col_3,
          4,Col_4,
          5,Col_5,
          6,Col_6,
          7,Col_7,
          8,Col_8,
          9,Col_9,
          10,Col_10,
          11,Col_11,
          12,Col_12,
          13,Col_13,
          14,Col_14,
          15,Col_15,
          16,Col_16,
          17,Col_17,
          18,Col_18,
          19,Col_19,
          20,Col_20) !=0
thanks
Edited by: user627047 on Jun 23, 2009 12:07 AM

The Best Answer

Advertisement
Here I managed to get the output
PLAN_TABLE_OUTPUT
Plan hash value: 2442671038
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 408 | 274K| 40 (43)| 00:00:01 |
| 1 | NESTED LOOPS | | 408 | 274K| 40 (43)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
|* 3 | CONNECT BY WITHOUT FILTERING| | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 5 | EXTERNAL TABLE ACCESS FULL | EVP0010 | 408 | 269K| 38 (45)| 00:00:01 |
Predicate Information (identified by operation id):
3 - filter(LEVEL<=20)
5 - filter(DECODE("R",1,"C_LOGO_LYLTY_CARD_1",2,"C_LOGO_LYLTY_CARD_2",3,"C_LOGO
_LYLTY_CARD_3",4,"C_LOGO_LYLTY_CARD_4",5,"C_LOGO_LYLTY_CARD_5",6,"C_LOGO_LYLTY_CAR
D_6",7,"C_LOGO_LYLTY_CARD_7",8,"C_LOGO_LYLTY_CARD_8",9,"C_LOGO_LYLTY_CARD_9",10,"C
_LOGO_LYLTY_CARD_10",11,"C_LOGO_LYLTY_CARD_11",12,"C_LOGO_LYLTY_CARD_12",13,"C_LOG
O_LYLTY_CARD_13",14,"C_LOGO_LYLTY_CARD_14",15,"C_LOGO_LYLTY_CARD_15",16,"C_LOGO_LY
LTY_CARD_16",17,"C_LOGO_LYLTY_CARD_17",18,"C_LOGO_LYLTY_CARD_18",19,"C_LOGO_LYLTY_
CARD_19",20,"C_LOGO_LYLTY_CARD_20")<>0)
24 rows selected.
  • Tranforming columns into rows taking to long Update:11-30

    Hi, Please help me in tuning the query. I am trying to transform rows into columns. Its taking lots of time almost 13hrs for 500k records. select decode ( r,           1,Col_1,           2,Col_2,           3,Col_3,           4,Col_4,           5,Col_

  • Interactive Report - Icon View - Dynamic Columns per Rows ? Update:10-11

    Hi all, We use the icon view functionnality in Interactive Report. Is there a way to display the 'columns per row' attribute as an application item and set it dynamical via PL/SQL ? Any suggestions? Thanks in advance for advices, Regards, GrégoryHi,

  • SWAP COLUMNS AND ROW IN AN INTERNAL TABLE to display in ALV Update:10-11

    Hi , I want to swap all the rows in an internal table with the column of the internal table to display it horizontally in ALV grid. e.g 1     2   3  (field names) A    P   X B    Q   Y C    R    Z should look like : D       A   B     C E      P   Q  

  • How to enter a data into the specified column and row in a created table Update:10-11

    Hi, I want to enter some data to specified column and row in a already created table. Please let me know how to do this. Regards Shivakumar SinghA table is just a 2D array of strings. Keep it in a shift register and use "replace array element" t

  • Problem in displaying the data of columns into rows in sap script Update:10-11

    hi, i am working on a sap script and i have to display the dat which is displayed in column into rows but it is not displaying it properly. eg, C     12.1     Si     5.5 it is displaying the data right now like this but i want to display the  data li

  • Filling in values based on the column and row headers Update:10-11

    I have a question that seems simple, but I can't figure out how to do it and I've searched all over the forum to no avail. I have one column that is width increasing by 3 in. increments (column A) I have one row that is height increasing by 3 in. inc

  • How to get the current selected column and row Update:10-11

    Hi, A difficult one, how do i know which column (and row would also be nice) of a JTable is selected? e.g. I have a JButton which is called "Edit" when i select a cell in the JTable and click the button "Edit" a new window must be visi

  • Please help to generate the table from column to rows Update:10-11

    Hello - I have one table with more than 100 columns there are 70 column start with HC1.....HC70 (they are not in sequence) and has some value 0 or 1 Table structure: HICN_ID HC1 HC2 HC4 HC5 HC6.................................HC70 1234A 0 1 1 0 1 1 3

  • [CS2/CS3 JS] Inserting columns or rows in tables Update:10-11

    Anybody here know how to insert or add column(s)/row(s) in tables in Indesign Javascript? Please help... Thanks...Hi Joaquin, you might have to take use of the add()-command: Add a column before the first column: myTable.columns.add( LocationOptions.

  • ALV to select more than one column by row using set_table_for_first_display Update:10-11

    Hello everyone, I am developing an application (ALV OO) to select more than 1 column by row ( one ). This is an a holiday application so the idea is: -One column will be the day, and the row will be the user. So I am trying to select more than one da