SELECT over DB link
A reader, April 06, 2017 - 6:01 am UTC
Consider scenario of Primary DB & Archival DB
Below SQL is going to be executed on Archival DB
1. Table - tb_card_request is created on Archival DB
2. Synonyms created on Archival DB which will point to Primary DB table e.g.
CREATE SYNONYM tb_card_request_details on tb_card_request_details@db_link;
CREATE SYNONYM tb_form_details on tb_form_details@db_link;
select cad.field_value
from tb_card_request_details cad, -- this will fetch records over dblink using synonym
tb_card_request ca, -- this will fetch records from Archival DB
tb_form_details fdt -- this will fetch records over dblink using synonym
where ca.request_id = 123456789
and ca.created_on = ’01-Jan-2009’
and cad.fk_tb_card_request(+) = ca.pk_tb_card_request
and ca.rec_status = 'AC'
and cad.fk_tb_form_details = fdt.pk_tb_form_details
and fdt.rec_status = 'AC';
Is it a good practice to fetch data over DB link?
Above is just a sample SQL, there are queries which includes many tables in joins which will fetch data from other DB using DB link.
April 07, 2017 - 2:16 am UTC
There is nothing "wrong" with joins across a database link, but you do need to be very aware of the implications of doing so.
Joins are basically done "locally" - by that I mean you are likely to see one of three possible scenarios:
a) all data pushed from local site to the remote site, join done remotely
b) all data pulled from remote site to the local site, join done locally
c) nested loop style, for each row in local, do a remote lookup
Its fairly self-evident that all of those approaches have potential risks depending on the size of the data being pushed back and forth.
This is why sometimes people will have materialized views etc to pay that cross-database penalty "in the background" so that subsequent queries can run locally.
Synonym
A reader, April 11, 2017 - 7:29 am UTC
I have a table created on Primary DB as well as On Arcihval DB:
e.g. tb_card_request_details
Now on Primary DB, I have create synonym with the table name like below whic is pointing to Archival DB via DB link.
CREATE PUBLIC SYNONYM tb_card_request_details on tb_card_request_details@db_link;
When I run SELECT * FROM tb_card_request_details, it gives result from Primary itself though I have created it for Archival DB using DB link in CREATE SYNONYM command.
Can you please help to understand?
April 12, 2017 - 5:42 am UTC
Names are resolved from "inside out", ie
- does the object exist in my schema ? Yes, use that, otherwise:
- do I have a private synonym to somewhere else ? Yes, use that, otherwise:
- do I have a public synonym ? Yes, use that.