By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,636 Members | 1,814 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,636 IT Pros & Developers. It's quick & easy.

help optimizing transpose query

P: 58
Hello All!
Hoping some folks could help me optimize and or choose the best route to do this process.
First off, here is what I am trying to achieve. I have a (fairly large) table of ~34million rows that looks something like this.

ID,TIME,SPD
1,1,35
1,2,25
1,3,34
1,4,25
2,1,25
2,2,11
2,3,56
3,2,22
3,3,25
3,4,22

I am trying to transpose the table by ID into a new table that looks like this (although there will be 96 time fields)

ID,TIME1,TIME2,TIME3,TIME4
1,35,25,34,25
2,25,11,56,null
3,null,22,25,22

This was the original query I used (which worked fine when the table was 1.4 million rows, but now doesn't... it runs out of tempspace (even at 100mb tempspace size)

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE trans_data parallel 8 AS
  2. SELECT /*+ parallel (l,8) */
  3.   l.ID,
  4.   tbl_TIME1.TIME1,tbl_TIME2.TIME2,tbl_TIME3.TIME3,tbl_TIME4.TIME4
  5. FROM org_data l 
  6.   LEFT JOIN
  7.     (
  8.     SELECT
  9.       ID,
  10.       SPD AS TIME1
  11.     FROM
  12.       org_data l
  13.     WHERE
  14.       TIME=1
  15.     )
  16.      tbl_TIME1
  17.   ON
  18.     l.ID=tbl_TIME1.ID
  19.   LEFT JOIN
  20.     (
  21.     SELECT
  22.       ID,
  23.       SPD AS TIME2
  24.     FROM
  25.       org_data l
  26.     WHERE
  27.       TIME=2
  28.     )
  29.      tbl_TIME2
  30.   ON
  31.     l.ID=tbl_TIME2.ID
  32.   LEFT JOIN
  33.     (
  34.     SELECT
  35.       ID,
  36.       SPD AS TIME3
  37.     FROM
  38.       org_data l
  39.     WHERE
  40.       TIME=3
  41.     )
  42.      tbl_TIME3
  43.   ON
  44.     l.ID=tbl_TIME3.ID
  45.   LEFT JOIN
  46.     (
  47.     SELECT
  48.       ID,
  49.       SPD AS TIME4
  50.     FROM
  51.       org_data l
  52.     WHERE
  53.       TIME=4
  54.     )
  55.      tbl_TIME4
  56.   ON
  57.     l.ID=tbl_TIME4.ID;
this code worked about the same as far as speed little bit faster.. but again on the new larger table it runs out of tempspace.

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE CREATE TABLE trans_data parallel 8 AS
  2. SELECT /*+ parallel (l,8) */
  3. a.tmc,
  4. MAX(CASE WHEN a.TIME=1 THEN a.AVG END) AS TIME1,
  5. MAX(CASE WHEN a.TIME=2 THEN a.AVG END) AS TIME2,
  6. MAX(CASE WHEN a.TIME=3 THEN a.AVG END) AS TIME3,
  7. MAX(CASE WHEN a.TIME=4 THEN a.AVG END) AS TIME4
  8. FROM
  9. org_data a
  10. GROUP BY
  11. a.ID;
and here is my latest attempt
I create the table first, populate it with unique ID's then run this

Expand|Select|Wrap|Line Numbers
  1. UPDATE trans_data a SET TIME1 = (SELECT /*+ index (trans_data, ID_INDEX) */ AVG FROM orig_data b WHERE TIME=1 and a.ID=b.ID);
  2. COMMIT;
this works but takes about 8-10 minutes per column
which at 96 columns runs to over 8 hours, trying to shorten up the amount of time taken (if possible!)
Sorry for posting such a long post here, but if anyone can help, I will forever be indebted!
Cheers,
Eric
Mar 12 '08 #1
Share this Question
Share on Google+
2 Replies


100+
P: 153
I like the second approach best personally, creating the pivot table seems is good. Is there no way to restrict on the rows? I mean why do you need to keep redoing the rows you did before? (there is no where clause in the query so your redoing the entire table!)

if you have built a new table from the old can you not add some kind of timestamp to it so you know what you have done already?
Mar 13 '08 #2

P: 58
Thanks for the reply Dave44
was a bit frustrated yesterday and not thinking as clearly as I should have been, your post gave me the much needed inspiration ie adding a where statement.
so just broke up the case statements into 4 temp tables by adding a where time between # and #
and then did a left join of the four temp tables at the end. It ran in less then 5 minutes...
Thanks
Eric
Mar 13 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.