Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Geraldo.

Asked: January 19, 2020 - 12:26 pm UTC

Last updated: February 07, 2020 - 9:45 am UTC

Version: 18.6

Viewed 1000+ times

You Asked

Hello, Ask Tom Team.

I have to create a report to show some business data. The data to show is stored in two different Oracle databases. I have to write a SQL query joining two the databases (dblink). The two databases were designed for very different purposes.

Environment:
Database A can have unprocessed data and a combination of two business (tableA.columA, tableA.ColumB) field can be repeated. Database B has processed data (this means that the app validates the data with some business rules) and the combination of two business fields can be repeated. Database A has a table with a GUID (varchar 36); in database B does not exist that GUID field and the combination of two business fields (tableA.columA, tableA.ColumB) can not be repeated, it is also a key.

I was asked to create that GUID column in a table of database B (with this we guaranteed the matching) to join it with a table in Database A.

My question:
1. Despite indexes will be created on GUIDs columns, How can I have good performance writing a SQL join by GUID and using a dblink?
2. What other approach do I have?

Thanks in advanced.

and Chris said...

A GUID is just a value. Tuning joins on this is the same as tuning joins on any other values.

But...

Surely you want to join on columns A and B?

I'm working on the assumption that each row in database A's table has a unique GUID. As you have duplicate (A, B) values, how do you know which row/GUID the single row in database B relates to?

In any case, so we can help you effectively please provide a demo script showing what you're trying to do. This must include:

- create tables
- insert intos
- your query

If you need advice on tuning queries over database links, I discuss options in this Ask TOM Office Hours video:


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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?




Chris Saxon
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.

Follow Up

A reader, February 06, 2020 - 1:19 pm UTC

Ok, Chris. I'll be work on the query.

But, is it good to create the trackid column on HEADERS table in database B in order to do the join with it? Because I need to guaranteed the same row in both databases.

Any other approach?
Chris Saxon
February 06, 2020 - 1:22 pm UTC

If you have a 1:M relationship and you need to join the 1 to a specific row in the M, then you need a column in the 1 stating which row to join to.

So yes, you need track_id or similar.


Follow Up

A reader, February 06, 2020 - 1:29 pm UTC

Ok. Thanks, Chris.

Just to know, how will be performance knowing that the trackid column is a GUID (varchar 36) and will be million and millions rows? How will the index behave?



Chris Saxon
February 07, 2020 - 9:45 am UTC

Depends on your query. Test it and find out!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.