RE: SQL delete based on aggregate function?

Here are some results from using a true SQL table in OS/400.

create schema lgsql

create table lgsql/wordfile2             
(stockno char(25) not null with default, 
 word char(80) not null with default,    
 fulldesc char(80) not null with default,
 location char(20) not null with default)

insert into lgsql/wordfile2                                 
(stockno, word, fulldesc, location)                         
select stockno, word, fulldesc, location from loyd/wordfile2
94111 rows inserted in WORDFILE2 in LGSQL.                  

select count(*) from (                    
select word from lgsql/wordfile2          
group by word having count(*) = 1) as word

Result: 16524, time < 1 second.

select * from lgsql/wordfile2 where word in
(select word from lgsql/wordfile2          
group by word having count(*) = 1  )       

Query running. 10 records selected, 0 processed.
Query running. 55 records selected, 55 processed.
Query running. 22 records selected, 0 processed.
Query running. 109 records selected, 109 processed.

The result screen appeared in 55 seconds.

This is a different result from before. Before I was using a standard DB2
native, not SQL, table.

create index wf2idx on lgsql/wordfile2 (word)             
Index WF2IDX created in LGSQL on table WORDFILE2 in LGSQL.

select * from lgsql/wordfile2 where word in
(select word from lgsql/wordfile2          
group by word having count(*) = 1  )       

Query running. 16 records selected, 3083225 processed.

Now it has reverted to the original query processing issue.

with temp as (                            
select word, count(*) from lgsql/wordfile2
group by word having count(*) = 1 )       
select * from temp                        

Results returned immediately.

with temp as (                            
select word, count(*) from lgsql/wordfile2
group by word having count(*) = 1 )       
select * from lgsql/wordfile2 a           
join temp on (a.word=temp.word)           

Results returned in 2-3 seconds. Going to the bottom takes 4 seconds.
This is equivalent to performance noted on SQL server.

delete from lgsql/wordfile2 where word in
(select word from lgsql/wordfile2        
group by word having count(*) = 1)       

Query running. 11 records selected, 0 processed.


HOWEFVER, my original question remains. I cannot use the "with temp as"
syntax with a delete. I must use the "where word in" syntax which is very
slow.


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