| 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 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
![]() |
![]() |