Message-ID: <4034087C.6090606@earthlink.net>
Date: Wed, 18 Feb 2004 19:51:08 -0500
From: Sean Shanny <shannyconsulting@earthlink.net>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040113
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Need some help with crafting a query to do major update
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

To all,

This is part of a data warehouse.  Made the mistake of using a natural 
key in one of the fact tables.  :-(  The f_test_pageviews is a simple 
testing table while I work this out.  The real table has an identical 
schema.

I have built a mapping table, d_user, to allow the replacement of the 
text based (32 characters wide) subscriber_key in f_test_pageviews with 
an int4 mapping key.  I need to replace all of the 
f_test_pageviews.subscriber_key values with the d_user.id value putting 
it in f_test_pageviews.sub_key column.

I have tried this sql:

update f_test_pageviews set sub_key = t2.id from f_test_pageviews t1, 
d_user t2 where t1.subscriber_key = t2.user_id;

but it is taking forever to complete. I would appreciate it if anyone 
could tell me a faster way to do this.  I have to update 250 million 
plus rows over 4 tables. (We break the page view tables into calendar 
months)

Thanks.

--sean


Table "public.d_user"
 Column  |  Type   |                       Modifiers
---------+---------+--------------------------------------------------------
 id      | integer | not null default nextval('public.d_user_id_seq'::text)
 user_id | text    | not null
Indexes:
    "d_user_pkey" primary key, btree (id)
    "d_user_user_id_key" unique, btree (user_id)


 Table "public.f_test_pageviews"
         Column         |  Type   | Modifiers
------------------------+---------+-----------
 id                     | integer |
 date_key               | integer |
 time_key               | integer |
 content_key            | integer |
 location_key           | integer |
 session_key            | integer |
 subscriber_key         | text    |
 persistent_cookie_key  | integer |
 ip_key                 | integer |
 referral_key           | integer |
 servlet_key            | integer |
 tracking_key           | integer |
 provider_key           | text    |
 marketing_campaign_key | integer |
 orig_airport           | text    |
 dest_airport           | text    |
 commerce_page          | boolean |
 job_control_number     | integer |
 sequenceid             | integer |
 url_key                | integer |
 useragent_key          | integer |
 web_server_name        | text    |
 cpc                    | integer |
 referring_servlet_key  | integer |
 first_page_key         | integer |
 newsletterid_key       | text    |
 sub_key                | integer |
Indexes:
    "idx_temp_pageviews_id" unique, btree (id)