Re: How do I make this SQL more efficient?

I have no idea what you mean by "key" because having the same primary key is absolutely impossible.

I can see lots of things about your SQL that I find abhorent starting with aliasing a column whose only purpose is that it is being utilized in an update:

    set gender = ( select max ( a.gender ) as majority_gender

What this is supposed to do?

But it is equally likely that your performance problem has as much to do with your database as with your SQL. Does it run on some hardware with an operating system? Does it have a version number? Do indexes exist corresponding with your WHERE clauses? Are they being used? Have you run an explain plan? Do you have the cost based optimizer? Have you collected statistics?

Had you provided that information I might be more inclined to look at why you are nesting inline views within inline views with all of this happening within a single table. It seems like you have made a simple query as complex and convoluted as possible.

Daniel Morgan

Luke Airig wrote:

> 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 - 18:31:54 CDT