Hi Experts,
Oracle Version:- 19C
I am working on an Store proc for an API,
Requirement is to return all contacts irrespective whether they have an account or not, but data is not properly normalized or clean.
My right table has around 47 Million records and left table has 1.8 Million. The output should have 1.8 Million records, I am able to use that with distinct but that kills the query (50 seconds). Is there any alternate way.
I tried to replicate the scenario below:
create table contacts_tb (id number,act_no number,fname varchar2(10),lname varchar2(10), email varchar2(20));
create table acct_tb ( act_no number, act_nm varchar2(10), legal_entity_id number);
select * from contacts_tb;
--drop table acct_tb;
--drop table contacts_tb;
insert into contacts_tb values (10,NULL,'101_fn','101_ln','101@gmail.com');
insert into contacts_tb values (10,NULL,'102_fn','102_ln','102@gmail.com');
insert into contacts_tb values (10,NULL,'103_fn','103_ln','103@gmail.com');
insert into contacts_tb values (10,NULL,'104_fn','104_ln','104@gmail.com');
insert into contacts_tb values (20,1234,'201_fn','201_ln','201@gmail.com');
insert into acct_tb values (123,'123company',10);
insert into acct_tb values (456,'123company',10);
insert into acct_tb values (111,'111company',20);
select a.id,a.act_no,a.fname,a.lname,a.email,b.act_nm from contacts_tb a left join acct_tb b on (a.id = b.legal_entity_id);
<img src="https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/DDM7B05X18FT/capture-jpg.jpg" alt="Capture.JPG">
With distinct, output looks fine, but the only issue is the huge volume of data in right table.