Follow Up
Geraldo, January 30, 2020 - 8:25 pm UTC
--DEMO SCRIPTS
DROP USER APPOWNER cascade;
CREATE USER APPOWNER
IDENTIFIED BY appowner
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT CREATE SESSION TO APPOWNER;
GRANT CREATE TABLE TO APPOWNER;
GRANT DEBUG CONNECT SESSION TO APPOWNER;
ALTER USER APPOWNER QUOTA UNLIMITED ON USERS;
--DATABASE A
---TABLE TRACKINGS
CREATE TABLE APPOWNER.TRACKINGS
(
ID NUMBER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 1000 NOORDER NOKEEP) NOT NULL,
TRACKID VARCHAR2(36 BYTE) NOT NULL,
SENDER VARCHAR2(11 BYTE),
DOC VARCHAR2(13 BYTE),
STATUS NUMBER(1),
SENT_DATE DATE,
REGISTRATION_DATE DATE DEFAULT SYSDATE,
LAST_UPDATE_DATE DATE
);
CREATE UNIQUE INDEX APPOWNER.PK_TRACKINGS ON APPOWNER.TRACKINGS
(ID);
CREATE UNIQUE INDEX APPOWNER.UK_TRACKINGS_TRACKID ON APPOWNER.TRACKINGS
(TRACKID);
ALTER TABLE APPOWNER.TRACKINGS ADD (
CONSTRAINT PK_TRACKINGS
PRIMARY KEY
(ID)
USING INDEX APPOWNER.PK_TRACKINGS
ENABLE VALIDATE
, CONSTRAINT UK_TRACKINGS_TRACKID
UNIQUE (TRACKID)
USING INDEX APPOWNER.UK_TRACKINGS_TRACKID
ENABLE VALIDATE);
--TABLE MESSAGES
CREATE TABLE APPOWNER.MESSAGES
(
MESSAGE_ID NUMBER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 1000 NOORDER NOKEEP) NOT NULL,
TRACKING_ID NUMBER NOT NULL,
MESSAGE_CODE NUMBER(10),
MESSAGE VARCHAR2(4000 BYTE)
);
CREATE INDEX APPOWNER.IX_FK_TRACKINGS_MESSAGES ON APPOWNER.MESSAGES
(TRACKING_ID);
CREATE UNIQUE INDEX APPOWNER.PK_MESSAGES ON APPOWNER.MESSAGES
(MESSAGE_ID);
ALTER TABLE APPOWNER.MESSAGES ADD (
CONSTRAINT PK_MESSAGES
PRIMARY KEY (MESSAGE_ID)
USING INDEX APPOWNER.PK_MESSAGES
ENABLE VALIDATE);
ALTER TABLE APPOWNER.MESSAGES ADD (
CONSTRAINT FK_TRACKINGS_MESSAGES
FOREIGN KEY (TRACKING_ID)
REFERENCES APPOWNER.TRACKINGS(ID)
ENABLE VALIDATE);
--DATABASE B
---TABLE HEADERS
CREATE TABLE APPOWNER.HEADERS
(
HEADER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP) NOT NULL,
SENDER VARCHAR2(11 BYTE) NOT NULL,
DOC VARCHAR2(13 BYTE) NOT NULL,
BUYER VARCHAR2(11 BYTE),
TOTAL_AMMOUNT NUMBER(18,2) NOT NULL,
DOC_DATE DATE NOT NULL,
STATUS NUMBER(1) NOT NULL,
SENT_DATE DATE,
REGISTRATION_DATE DATE DEFAULT SYSDATE NOT NULL,
DOC_TYPE NUMBER(2) NOT NULL
);
CREATE UNIQUE INDEX APPOWNER.PK_HEADERS ON APPOWNER.HEADERS
(HEADER_ID);
CREATE UNIQUE INDEX APPOWNER.UK_HEADER_SENDER_DOC ON APPOWNER.HEADERS
(DOC, SENDER);
ALTER TABLE APPOWNER.HEADERS ADD (
CONSTRAINT PK_HEADERS
PRIMARY KEY (HEADER_ID)
USING INDEX APPOWNER.PK_HEADERS
ENABLE VALIDATE);
--TABLE HEADERS2
CREATE TABLE APPOWNER.HEADERS2
(
HEADER2_ID NUMBER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP) NOT NULL,
HEADER_ID NUMBER NOT NULL,
VERSION VARCHAR2(3 BYTE) NOT NULL,
EXPIRIRATION_DATE DATE,
PAYMENT_DATE DATE,
SENDER_LONG_NAME VARCHAR2(150 BYTE) NOT NULL,
SENDER_EMAIL VARCHAR2(80 BYTE),
SENDER_WEBSITE VARCHAR2(50 BYTE),
VENDOR_CODE VARCHAR2(60 BYTE),
SELLING_ZONE VARCHAR2(7 BYTE),
SENDER_DESCRIPTION VARCHAR2(250 BYTE),
SIGNATURE_DATE DATE
);
CREATE INDEX APPOWNER.IX_FK_HEADER_HEADERS2 ON APPOWNER.HEADERS2
(HEADER_ID);
CREATE UNIQUE INDEX APPOWNER.PK_HEADERS2 ON APPOWNER.HEADERS2
(HEADER2_ID);
ALTER TABLE APPOWNER.HEADERS2 ADD (
CONSTRAINT PK_HEADERS2
PRIMARY KEY (HEADER2_ID)
USING INDEX APPOWNER.PK_HEADERS2
ENABLE VALIDATE);
ALTER TABLE APPOWNER.HEADERS2 ADD (
CONSTRAINT FK_HEADER_HEADERS2
FOREIGN KEY (HEADER_ID)
REFERENCES APPOWNER.HEADERS (HEADER_ID)
ENABLE VALIDATE);
--TABLE APPROVALS
CREATE TABLE APPOWNER.APPROVALS
(
APPROVAL_ID NUMBER NOT NULL,
VERSION VARCHAR2(3 BYTE) NOT NULL,
SENDER VARCHAR2(11 BYTE) NOT NULL,
DOC VARCHAR2(13 BYTE) NOT NULL,
SENT_DATE DATE NOT NULL,
TOTAL_AMMOUNT NUMBER(18,2) NOT NULL,
BUYER VARCHAR2(11 BYTE) NOT NULL,
APROVAL_STATUS NUMBER(1) NOT NULL,
APROVAL_DATE DATE NOT NULL,
REGISTRATION_DATE DATE DEFAULT SYSDATE
);
CREATE UNIQUE INDEX APPOWNER.PK_APPROVALS ON APPOWNER.APPROVALS
(APPROVAL_ID);
ALTER TABLE APPOWNER.APPROVALS ADD (
CONSTRAINT PK_APPROVALS
PRIMARY KEY
(APPROVAL_ID)
USING INDEX APPOWNER.PK_APPROVALS
ENABLE VALIDATE);
ALTER TABLE APPOWNER.APPROVALS ADD (
CONSTRAINT FK_HEADER_APPROVALS
FOREIGN KEY (APPROVAL_ID)
REFERENCES APPOWNER.HEADERS (HEADER_ID)
ENABLE VALIDATE);
----INSERTS SECTION
--------HEADERS TABLE
SET DEFINE OFF;
Insert into APPOWNER.HEADERS
(HEADER_ID, SENDER, DOC, BUYER, TOTAL_AMMOUNT,
DOC_DATE, STATUS, SENT_DATE, REGISTRATION_DATE, DOC_TYPE)
Values
(1, '11111', 'Z17000001', '6789', 100,
TO_DATE('1/30/2020', 'MM/DD/YYYY'), 1, TO_DATE('1/29/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 2:59:26 PM', 'MM/DD/YYYY HH:MI:SS AM'), 17);
Insert into APPOWNER.HEADERS
(HEADER_ID, SENDER, DOC, BUYER, TOTAL_AMMOUNT,
DOC_DATE, STATUS, SENT_DATE, REGISTRATION_DATE, DOC_TYPE)
Values
(3, '11111', 'Z17000002', '6789', 50,
TO_DATE('1/30/2020', 'MM/DD/YYYY'), 2, TO_DATE('1/30/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 3:00:11 PM', 'MM/DD/YYYY HH:MI:SS AM'), 17);
Insert into APPOWNER.HEADERS
(HEADER_ID, SENDER, DOC, BUYER, TOTAL_AMMOUNT,
DOC_DATE, STATUS, SENT_DATE, REGISTRATION_DATE, DOC_TYPE)
Values
(21, '11111', 'Z17000003', '6789', 500,
TO_DATE('1/30/2020', 'MM/DD/YYYY'), 2, TO_DATE('1/29/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 3:01:40 PM', 'MM/DD/YYYY HH:MI:SS AM'), 17);
Insert into APPOWNER.HEADERS
(HEADER_ID, SENDER, DOC, BUYER, TOTAL_AMMOUNT,
DOC_DATE, STATUS, SENT_DATE, REGISTRATION_DATE, DOC_TYPE)
Values
(43, '22222', 'Z17000001', '6789', 90,
TO_DATE('1/30/2020', 'MM/DD/YYYY'), 1, TO_DATE('1/29/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 2:59:26 PM', 'MM/DD/YYYY HH:MI:SS AM'), 17);
Insert into APPOWNER.HEADERS
(HEADER_ID, SENDER, DOC, BUYER, TOTAL_AMMOUNT,
DOC_DATE, STATUS, SENT_DATE, REGISTRATION_DATE, DOC_TYPE)
Values
(44, '11111', 'Z17000008', '6789', 90,
TO_DATE('1/30/2020', 'MM/DD/YYYY'), 1, TO_DATE('1/29/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 2:59:26 PM', 'MM/DD/YYYY HH:MI:SS AM'), 17);
Insert into APPOWNER.HEADERS
(HEADER_ID, SENDER, DOC, BUYER, TOTAL_AMMOUNT,
DOC_DATE, STATUS, SENT_DATE, REGISTRATION_DATE, DOC_TYPE)
Values
(45, '22222', 'Z17000002', '6789', 110,
TO_DATE('1/30/2020', 'MM/DD/YYYY'), 2, TO_DATE('1/29/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 2:59:26 PM', 'MM/DD/YYYY HH:MI:SS AM'), 17);
Insert into APPOWNER.HEADERS
(HEADER_ID, SENDER, DOC, BUYER, TOTAL_AMMOUNT,
DOC_DATE, STATUS, SENT_DATE, REGISTRATION_DATE, DOC_TYPE)
Values
(46, '11111', 'Z18000001', '3147', 200,
TO_DATE('1/27/2020', 'MM/DD/YYYY'), 1, TO_DATE('1/29/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 3:16:11 PM', 'MM/DD/YYYY HH:MI:SS AM'), 18);
COMMIT;
--------HEADERS2 TABLE
SET DEFINE OFF;
Insert into APPOWNER.HEADERS2
(HEADER2_ID, HEADER_ID, VERSION, EXPIRIRATION_DATE, PAYMENT_DATE,
SENDER_LONG_NAME, SENDER_EMAIL, SENDER_WEBSITE, VENDOR_CODE, SELLING_ZONE,
SENDER_DESCRIPTION, SIGNATURE_DATE)
Values
(1, 1, '1.0', TO_DATE('2/4/2020', 'MM/DD/YYYY'), TO_DATE('2/29/2020', 'MM/DD/YYYY'),
'SENDER LONG NAME', 'SENDER@SOMETHING.COM', 'WWW.SENDER.COM', '55555', 'AZJ04',
'THIS IS SENDER DESCRIPTION', TO_DATE('1/30/2020', 'MM/DD/YYYY'));
Insert into APPOWNER.HEADERS2
(HEADER2_ID, HEADER_ID, VERSION, EXPIRIRATION_DATE, PAYMENT_DATE,
SENDER_LONG_NAME, SENDER_EMAIL, SENDER_WEBSITE, VENDOR_CODE, SELLING_ZONE,
SENDER_DESCRIPTION)
Values
(3, 43, '1.0', TO_DATE('2/4/2020', 'MM/DD/YYYY'), TO_DATE('2/29/2020', 'MM/DD/YYYY'),
'SENDER2 LONG NAME', 'SENDER2@SOMETHING.COM', 'WWW.SENDER2.COM', '33333', 'AZJ06',
'THIS IS SENDER2 DESCRIPTION');
Insert into APPOWNER.HEADERS2
(HEADER2_ID, HEADER_ID, VERSION, EXPIRIRATION_DATE, PAYMENT_DATE,
SENDER_LONG_NAME, SENDER_EMAIL, SENDER_WEBSITE, VENDOR_CODE, SELLING_ZONE,
SENDER_DESCRIPTION)
Values
(4, 44, '1.0', TO_DATE('2/5/2020', 'MM/DD/YYYY'), TO_DATE('3/10/2020', 'MM/DD/YYYY'),
'SENDER LONG NAME', 'SENDER2@SOMETHING.COM', 'WWW.SENDER2.COM', '33333', 'AZJ06',
'THIS IS SENDER2 DESCRIPTION');
Insert into APPOWNER.HEADERS2
(HEADER2_ID, HEADER_ID, VERSION, EXPIRIRATION_DATE, PAYMENT_DATE,
SENDER_LONG_NAME, SENDER_EMAIL, SENDER_WEBSITE, VENDOR_CODE, SELLING_ZONE)
Values
(5, 46, '1.0', TO_DATE('2/4/2020', 'MM/DD/YYYY'), TO_DATE('2/29/2020', 'MM/DD/YYYY'),
'SENDER LONG NAME', 'SENDER@SOMETHING.COM', 'WWW.SENDER.COM', '77777', 'AZJ09');
COMMIT;
--------TRACKINGS TABLE
SET DEFINE OFF;
Insert into APPOWNER.TRACKINGS
(ID, TRACKID, SENDER, DOC, STATUS,
SENT_DATE, REGISTRATION_DATE, LAST_UPDATE_DATE)
Values
(1, 'mke4d158-k978-451b-ybf3-0d1zddw039at', '11111', 'Z17000001', 1,
TO_DATE('1/29/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 3:19:23 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_DATE('1/30/2020 3:19:23 PM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPOWNER.TRACKINGS
(ID, TRACKID, SENDER, DOC, STATUS,
SENT_DATE, REGISTRATION_DATE, LAST_UPDATE_DATE)
Values
(2, 'mke4d158-k978-451b-ybf3-0d1zddw039ac', '11111', 'Z17000001', 2,
TO_DATE('1/29/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 3:20:58 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_DATE('1/30/2020 3:20:59 PM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPOWNER.TRACKINGS
(ID, TRACKID, SENDER, DOC, STATUS,
SENT_DATE, REGISTRATION_DATE, LAST_UPDATE_DATE)
Values
(3, 'mke4d158-k978-451b-ybf3-0d1zddw039ah', '11111', 'Z17000001', 2,
TO_DATE('1/29/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 3:22:11 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_DATE('1/30/2020 3:22:11 PM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPOWNER.TRACKINGS
(ID, TRACKID, SENDER, DOC, STATUS,
SENT_DATE, REGISTRATION_DATE, LAST_UPDATE_DATE)
Values
(4, 'mke4d158-k978-451b-ybf3-0d1zddw039aw', '11111', 'Z17000002', 2,
TO_DATE('1/30/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 3:23:25 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_DATE('1/30/2020 3:23:26 AM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPOWNER.TRACKINGS
(ID, TRACKID, SENDER, DOC, STATUS,
SENT_DATE, REGISTRATION_DATE, LAST_UPDATE_DATE)
Values
(5, 'mke4d158-k978-451b-ybf3-0d1zddw039aq', '11111', 'Z17000003', 2,
TO_DATE('1/29/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 3:24:24 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_DATE('1/30/2020 3:24:25 PM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPOWNER.TRACKINGS
(ID, TRACKID, SENDER, DOC, STATUS,
SENT_DATE, REGISTRATION_DATE, LAST_UPDATE_DATE)
Values
(6, 'mke4d158-k978-451b-ybf3-0d1zddw039a2', '22222', 'Z17000001', 1,
TO_DATE('1/29/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 3:25:28 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_DATE('1/30/2020 3:25:28 PM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPOWNER.TRACKINGS
(ID, TRACKID, SENDER, DOC, STATUS,
SENT_DATE, REGISTRATION_DATE, LAST_UPDATE_DATE)
Values
(7, 'mke4d158-k978-451b-ybf3-0d1zddw039ag', '11111', 'Z17000008', 1,
TO_DATE('1/29/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 3:26:34 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_DATE('1/30/2020 3:26:34 PM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPOWNER.TRACKINGS
(ID, TRACKID, SENDER, DOC, STATUS,
SENT_DATE, REGISTRATION_DATE, LAST_UPDATE_DATE)
Values
(8, 'mke4d158-k978-451b-ybf3-0d1zddw039ar', '22222', 'Z17000002', 2,
TO_DATE('1/29/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 3:27:25 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_DATE('1/30/2020 3:27:25 PM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into APPOWNER.TRACKINGS
(ID, TRACKID, SENDER, DOC, STATUS,
SENT_DATE, REGISTRATION_DATE, LAST_UPDATE_DATE)
Values
(9, 'mke4d158-k978-451b-ybf3-0d1zddw039as', '11111', 'Z18000001', 1,
TO_DATE('1/29/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 3:27:49 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_DATE('1/30/2020 3:27:49 PM', 'MM/DD/YYYY HH:MI:SS AM'));
COMMIT;
--------MESSAGES TABLE
SET DEFINE OFF;
Insert into APPOWNER.MESSAGES
(MESSAGE_ID, TRACKING_ID, MESSAGE_CODE, MESSAGE)
Values
(1, 8, 1020, 'Field 1 is invalid');
Insert into APPOWNER.MESSAGES
(MESSAGE_ID, TRACKING_ID, MESSAGE_CODE, MESSAGE)
Values
(2, 4, 1020, 'Field 1 is invalid');
Insert into APPOWNER.MESSAGES
(MESSAGE_ID, TRACKING_ID, MESSAGE_CODE, MESSAGE)
Values
(3, 4, 1020, 'Field 1 is invalid');
Insert into APPOWNER.MESSAGES
(MESSAGE_ID, TRACKING_ID, MESSAGE_CODE, MESSAGE)
Values
(4, 2, 1030, 'Field 2 is invalid');
Insert into APPOWNER.MESSAGES
(MESSAGE_ID, TRACKING_ID, MESSAGE_CODE, MESSAGE)
Values
(5, 5, 1020, 'Field 1 is invalid');
COMMIT;
--------APPROVALS TABLE
SET DEFINE OFF;
Insert into APPOWNER.APPROVALS
(APPROVAL_ID, VERSION, SENDER, DOC, SENT_DATE,
TOTAL_AMMOUNT, BUYER, APROVAL_STATUS, APROVAL_DATE, REGISTRATION_DATE)
Values
(1, '1.0', '6789', 'Z170000041', TO_DATE('1/30/2020', 'MM/DD/YYYY'),
100, '11111', 1, TO_DATE('1/30/2020', 'MM/DD/YYYY'), TO_DATE('1/30/2020 4:11:27 PM', 'MM/DD/YYYY HH:MI:SS AM'));
COMMIT;
The real enviromment is:
Database A
1. APPOWNER.TRACKINGS (father)
2. APPOWNER.MESSAGES (child)
Database B:
APPOWNER.HEADERS (father)
APPOWNER.HEADERS2 (child )
APPOWNER.APPROVALS (child)
I want a query to select
1. TRACKID
2. SENDER_LONG_NAME
3. SENDER
4. DOC
5. STATUS
6. SENT_DATE
7. REGISTRATION_DATE
8. LAST_UPDATE_DATE
9. MESSAGE_CODE
10. MESSAGE
11. BUYER
12. A pseudo column with string 'APPROVED' if row exists in APPOWNER.APPROVALS (child table) and APROVAL_STATUS is 1 for any row in APPOWNER.HEADERS (father table) and string 'REJECTED' if APROVAL_STATUS is 2. If no rows exists then 'DO NOT EXIST'
13. APROVAL_DATE
As I posted earlier rows in APPOWNER.TRACKINGS (database A) can be repeated with the combintation (SENDER, DOC). In APPOWNER.HEADERS (database B) rows with combintation (SENDER, DOC) can not be repeated (unique constraint).
I was asked to create that TRACKID column in APPOWNER.HEADERS (with this we guaranteed the matching) to join it with a APPOWNER.TRACKINGS (database A). In order to achieve this, developers will also insert TRACKID on APPOWNER.HEADERS.
What is the best way to construct this query achieving a good performance?
February 03, 2020 - 11:28 am UTC
I'm not sure exactly what your join criteria are.
Ultimately you'll want a query that looks like this (adding the db links as necessary):
with db_a as (
select /*+ no_merge */*
from trackings trck
join messages mess
on ...
), db_b as (
select /*+ no_merge */*
from headers head
join headers2 h2
on ...
left join approvals appr
on ...
)
select * from db_a
join db_b
on ...;
Once you've got a working query, if you find it's too slow post the query along with its execution plan here.
Please watch the video for details on how to get the plan stats from the remote database.