RE: SQL delete based on aggregate function? -- MIDRANGE-L

Based on Don's suggestion, I ran

Select word, count(*) from wordfile2
Group by word having count(*) = 1

Return results in a second, went to the bottom of the list in a second.

However, try this:

Select * from wordfile2 where word in 
(select word from wordfile2
Group by word having count(*) = 1)

Receive messages:
Building access path for file WORDFILE2 in LOYD
Query running. 3 records selected, 1707500 processed.
Query running. 11 reecords selected, 3159909 processed.

Etc.

Created an index.
Create index wf2idx on loyd/wordfile2 (word)

Then run select again.

Query running. 3 records selected, 1703281 processed.
Query running. 73 records selected, 73 processed.
Query running, 13 records selected, 4540123 processed.

The optimizer is building a comparison table for each row being processed.

Substitute "delete" for "select *" in the statement above, the same results.
In the delete statement, once you delete a row, the subselect would by
definition change.

Creating copy of file WORDFILE in LOYD.
Building access path for file WORDFILE2 in LOYD.
Query running. 6 records selected, 2215503 processed.

Here's what running in debug mode had to say:
All access paths were considered for file WORDFILE2. The reason code was 4.
The cost to use this access path, as determined by the optimizer, was higher
than the cost associated with the chosen access method. 
Arrival sequence access was used for file WORDFILE2.
Unable to retrive query options file.
**** Ending debug message for query.

However, no suggestions were given to create an access path.


The following query is interesting, as it gives me only those records that
have multiple occurrences of a word.

select * from wordfile2 a 
join (select word, count(*) from wordfile2 
group by word 
having count(*) > 1 ) as b on (a.word=b.word)
order by a.word 


I can run this with output to a file. It's too bad the CA download screen
won't let me enter this syntax.


Loyd Goodbar
Senior programmer/analyst
BorgWarner
E/TS Water Valley
662-473-5713

-----Original Message-----
From: DeLong, Eric [mailto:EDeLong@xxxxxxxxxxxxxxx] 
Sent: Thursday, January 20, 2005 09:18
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL delete based on aggregate function?

No, I don't think that is so.  The SQL optimizer recognizes that the
subquery never changes, so it builds the subquery result set and simply
checks each row for a word that is in the list.  

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Goodbar, Loyd (ETS - Water Valley)
[mailto:LGoodbar@xxxxxxxxxxxxxx]
Sent: Thursday, January 20, 2005 8:36 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL delete based on aggregate function?


This does work but will take forever. For each row being processed (all
123,000), a temporary list (the where word in... clause) must be built. So,
123,000 * 123,000 records are being processed for this query. I was looking
for something quicker.

Thanks,
Loyd

Loyd Goodbar
Senior programmer/analyst
BorgWarner
E/TS Water Valley
662-473-5713