Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> How do I make this SQL more efficient?

How do I make this SQL more efficient?

From: Luke Airig <luke_airig_at_hotmail.com>
Date: 18 Apr 2002 15:54:52 -0700
Message-ID: <a066cf23.0204181454.4a3590ad@posting.google.com>


I am an Oracle neophyte and I need some advice on how to improve some sql that I have written to do some data clean up. The problem is that one of our primary tables (1M+ rows) has multiple rows with the same key and different genders indicated. I want to derive the 'majority' gender occurrences and then update the rows with the derived gender. The following sql works but runs for 4 hours. I have included interim result sets for one sample key as comments. Can anyone suggest alternatives that would be more efficient?

TIA update table1 z

   set gender =

( select max ( a.gender )  as  majority_gender  -- MAJORITY_GENDER
                                                -- -
                                                -- F
--
--
    from ( select keycolumn_1                   --                       G COUNT

, keycolumn_2 -- ------- ---------- -- - -----
, keycolumn_3 -- 8016209 05-02-1993 02 F 6
, gender -- 8016209 05-02-1993 02 M 2
, count ( gender ) as count
from table1 where keycolumn_1 is not null and keycolumn_2 is not null group by keycolumn_1
, keycolumn_2
, keycolumn_3
, gender
) a , ( select max ( gender_count ) as count -- COUNT from ( select keycolumn_1 -- ---------- , keycolumn_2 -- 6 , keycolumn_3 , gender , count ( gender ) gender_count from table1 where keycolumn_1 is not null and keycolumn_2 is not null group by keycolumn_1 , keycolumn_2 , keycolumn_3 , gender ) group by keycolumn_1
, keycolumn_2
, keycolumn_3
) b where a.count = b.count and z.keycolumn_1 = a.keycolumn_1 and z.keycolumn_2 = a.keycolumn_2 and z.keycolumn_3 = a.keycolumn_3 group by a.keycolumn_1 , a.keycolumn_2 , a.keycolumn_3 , a.count ) ;
Received on Thu Apr 18 2002 - 17:54:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US