Subquery

[lee]

Hi,
I have the following query:

DELETE FROM RDS_SECURITY RS
WHERE (TRAN_NO) in
(select TRAN_NO from RDS_TRANSACTION
where PROD_TYP = 'LMCH'
AND ACCT_STA_TYP = 'PROCESSED')


RDS_TRANSACTION is a parent table and
RDS_SECURITY is a child table.

The explain plan was:
It was doing an index scan(I do not remember which one!!) on rds_transaction and a full table scan on rds_security table.

After analyzing the tables with estimate option, it is doing a full table scan on both the tables. There is a primary key index on rds_security on tran_no column.
There is a primary key index on rds_transaction on tran_no. Also, there is an index on prod_typ column.

The total no of records in rds_security is: 9935829
The total no of records in rds_transaction is: 12915822.

Why? Please let em know. Is there a way I can improve this query? It is creating a lot of performance problems.

Please help. It's urgent!!

Thanks.

[pando]

well I guess the index on prod_type is not selective or you are deleting many rows

[gd_1976]

Hi Leo,
Using "IN" clause might be the reason for Full table scan. Use "exists" clause.

Try to use some dummy clause in where statement like tran_id <> 0 . Most of the time, try to use the primary key as the dummy where clause.


Hop it is very useful.

Thanks.

GD_1976.

[pando]

Originally posted by gd_1976

Using "IN" clause might be the reason for Full table scan. Use "exists" clause.

well it's often the other way round, exists forces at least one FTB, the outer query, in this case if the index is selective it would do a range scan

[pando]

look how many rows

select TRAN_NO from RDS_TRANSACTION
where PROD_TYP = 'LMCH'
AND ACCT_STA_TYP = 'PROCESSED'

returns, if it returs a large result set then you might go for exists, if it was small use in
if the result set is small but it's doing full scan you may want to index ACCT_STA_TYP and use in

[lee]

The inner query returns 1200 rows only.
I inserted tran_no <> 0 in the inner query but it still does a full table scan on both the tables.

Then I tried the foll query:
delete from rds_security rs
where exists(select rt.tran_no from rds_transaction
where rs.tran_no = rt.tran_no
and rt.prod_typ = 'LMCH'
and rt.acct_sta_typ = 'PROCESSED')

It is doing a full scan on rds_security table and an index unique scan on pk_rds_transaction(tran_no).

What should I do?

[gd_1976]

Maybe due to this condition 'where PROD_TYP = 'LMCH'
AND ACCT_STA_TYP = 'PROCESSED' ' ,is doing a full table scan.
I think that might be the reason. Can you do a fqvor, Just comment those lines and run thw query (Select query). it might go for a index scan (use dummy where condition).
"select rt.tran_no from rds_transaction
where rs.tran_no = rt.tran_no"


Thanks
GD_1976,

[lee]

I checked the following:

select rt.tran_no from rds_transaction
where rs.tran_no = rt.tran_no

It does a full table scan on rds_security and a full index scan on pk_rds_transaction table.

[pando]

well did you index ACCT_STA_TYP