Having an index that includes the 4 keys and status increases performance.
CREATE index vkchild1 on vkchild (k1, k2, k3, k4, status);
Using an EXISTS check can be efficient and beneficial.
Especially later if you need to run an update or delete on the main table and require all the logic in the where clause.
You want to make sure to provide all the key fields in the where clause of the subselect matching the field of the outer table.
Including the C.keys=p.keys and the "Select 1" together allows the system to perform close to a "Setll" and "If %Equal" check on the detail table. This is even more efficient if there is an index the system can use directly.
select p.k1, p.k2, p.k3, p.k4
from vkparent p
where not exists ( select 1
from vkchild c where c.status = 'A'
and C.k1 = p.k1
and C.k2 = p.k2
and C.k3 = p.k3
and C.k4 = p.k4
Fetch First 1 Rows Only
) ;
The system will sometimes include the "Fetch First 1 Rows Only" clause during optimization when the "Select 1" is used.
The exception join performs well, but I would be interested to see how it compares with the EXISTS check on the huge database.
I've seen the "NOT IN" subselect perform worse than the "NOT EXISTS" check on large databases.
Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob Berendt
Sent: Friday, December 20, 2019 6:06 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Help with SQL
I think you're right. That STATUS column throws a wrinkle in it.
create table vkparent (
k1 char(1),
k2 char(1),
k3 char(1),
k4 char(1),
primary key(k1, k2, k3, k4)
)
;
create table vkchild (
k1 char(1),
k2 char(1),
k3 char(1),
k4 char(1),
status char(1),
k5 char(1),
d1 char(1),
primary key(k1, k2, k3, k4, k5)
);
insert into vkparent values('A', 'A', 'A', 'A'); insert into vkparent values('B', 'B', 'B', 'B'); insert into vkparent values('C', 'C', 'C', 'C'); insert into vkparent values('D', 'D', 'D', 'D'); insert into vkchild values('A', 'A', 'A', 'A', 'A', '1', 'X'); insert into vkchild values('B', 'B', 'B', 'B', 'I', '1', 'X'); insert into vkchild values('D', 'D', 'D', 'D', 'I', '1', 'X'); insert into vkchild values('D', 'D', 'D', 'D', 'A', '2', 'Y');
select p.k1, p.k2, p.k3, p.k4
from vkparent p
where (p.k1, p.k2, p.k3, p.k4) not in ( select c.k1, c.k2, c.k3, c.k4 from vkchild c where c.status = 'A') ; This line won't get what you want because 'D' has both an active and an inactive row.
select p.k1, p.k2, p.k3, p.k4, c.status
from vkparent p left outer join vkchild c on
p.k1=c.k1 and p.k2=c.k2 and p.k3=c.k3 and p.k4=c.k4 where c.status is null or c.status<>'A'
;
l
Rob Berendt
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Vinay Gavankar
Sent: Friday, December 20, 2019 7:14 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Help with SQL
CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.
Hi,
We have a Table with K1, K2, K3, K4 as Primary keys with about 800 million
records. A child table, which is partitioned, has K1, K2, K3, K4, K5 (Seq
Number) as Primary keys and a Status field (value of A or I) with about 10
billion records. Child table having no record for the 4 keys is possible,
though highly unlikely.
I need to get a list of records (just the key fields) from the main Table,
which has no record in the child table with Status = 'A' (may or may not
have an 'I' Status record).
Can someone please help me with some efficient sql code to achieve this?
TIA.
Vinay
midrange.com
