Home » SQL & PL/SQL » SQL & PL/SQL » minus query using dblink (Oracle 12)
minus query using dblink [message #676169] Sat, 18 May 2019 23:31 Go to next message
sivakumar121
Messages: 29
Registered: April 2009
Junior Member
Hi

I am executing the below query in Oracle and it is running for ever, because of DBLink and also tables has 50 million rows with good number of columns.
Since I am using the dblinks, obviously the query performance will go down and it is running for ever.
I am not having access to create a table in this environment and do minus.

Any thoughts how I can execute this query and see the results?

select * from abc@dblink
minus
select * from abc

Thank you
Re: minus query using dblink [message #676171 is a reply to message #676169] Sun, 19 May 2019 01:30 Go to previous messageGo to next message
John Watson
Messages: 7933
Registered: January 2010
Location: Global Village
Senior Member
You could re-write it to use a NOT EXISTS predicate. That would give you an anti join which is sometimes more efficient than the sorting needed for MINUS.
Re: minus query using dblink [message #676175 is a reply to message #676171] Sun, 19 May 2019 03:31 Go to previous messageGo to next message
sivakumar121
Messages: 29
Registered: April 2009
Junior Member
Actually I want to compare all the columns data for any difference.
Re: minus query using dblink [message #676176 is a reply to message #676175] Sun, 19 May 2019 03:46 Go to previous messageGo to next message
John Watson
Messages: 7933
Registered: January 2010
Location: Global Village
Senior Member
Well, go on: write it as I suggested.
Re: minus query using dblink [message #676200 is a reply to message #676176] Mon, 20 May 2019 11:50 Go to previous message
Bill B
Messages: 1868
Registered: December 2004
Senior Member
To do what you want, you need to pull the entire table over the database link and then do the minus against the local temp table. If the initial table is big, it will take a while to pull it over into your temp tablespace
Previous Topic: Loop after fetch on generic SYS_REFCURSOR
Next Topic: Sql query taking long time to execut
Goto Forum:
  


Current Time: Mon Jun 17 14:15:53 CDT 2019