Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> How do I make this SQL more efficient?
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 COUNTReceived on Thu Apr 18 2002 - 17:54:52 CDT
, 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 ) ;
![]() |
![]() |