Re: How do I make this SQL more efficient?

On 18 Apr 2002, luke_airig_at_hotmail.com 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?

SQL> select * from t1 order by KEYCOLUMN,gender;

     KEYCOLUMN G

• - 1 F 1 M 1 M 2 F 2 F 2 M 3 M 4 F 5 F 5 M

You want?

     KEYCOLUMN G

• - 1 M 1 M 1 M 2 F 2 F 2 F 3 M 4 F 5 F 5 M

Question: What do you do when they are equal?

Anyways, does the following sql against t1 help you on your way?

First, show that I'm trying to bring back either negative or positive 1.

SQL> select KEYCOLUMN,sum(decode(gender,'F',1,-1)), sum(decode(gender,'M',1,-1))

     from t1
     group by KEYCOLUMN;

 KEYCOLUMN SUM(DECODE(GENDER,'F',1,-1)) SUM(DECODE(GENDER,'M',1,-1))

---------- ---------------------------- ----------------------------
	 1			     -1 			   1
	 2			      1 			  -1
	 3			     -1 			   1
	 4			      1 			  -1
	 5			      0 			   0

Now, put a decode on that to return the correct derived gender.

SQL> select KEYCOLUMN,decode(sum(decode(gender,'F',1,-1)),1,'F','') ||

                      decode(sum(decode(gender,'M',1,-1)),1,'M','')
     from t1
     group by KEYCOLUMN;

 KEYCOLUMN DE
---------- --

	 1 M
	 2 F
	 3 M
	 4 F
	 5

Now, can you use this to change your update?

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.