Home » SQL & PL/SQL » SQL & PL/SQL » Comparision Usage of DBLINK & MATERIALIZE VIEW (Oracle9i)
Comparision Usage of DBLINK & MATERIALIZE VIEW [message #417793] Tue, 11 August 2009 09:56 Go to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,

USING DBLINK:
 PULLING DATA FROM X1 SEVRER TO X2 SERVER
 ADAM DBLINK IS CREATED ON X2 TO PULL THE DATA FROM X1

 TABLE NAME : XYZ  [TRANSACTIONAL TABLE]
 INDEXES ON TABLE: F1,F2,F3
 RANGE PARTITION BASED ON F2 : MONHTLY BASIS
 EACH PARTITION: HOLDS 40 - 60 MILLIONS OF RECORDS

QUERY
DATA PULLING QUERY SEARCH THROUGH ENTIRE PARTITION, SINCE 2004 TAKES 90 MINUTES
 SELECT * FROM XYZ@ADAM A, (SELECT A1 FROM ABC WHERE A1='N')B
 WHERE A.F1=B.A1
 AND (F2 BETWEEN TO_DATE('01-01-2004 00:00:00','dd-mm-yyyy hh24:mi:ss')
            AND  TO_DATE('01-08-2009 00:00:00','dd-mm-yyyy hh24:mi:ss'))
 AND (F3 BETWEEN TO_DATE('01-08-2009 10:00:00','dd-mm-yyyy hh24:mi:ss')
            AND  TO_DATE('01-08-2009 10:00:00','dd-mm-yyyy hh24:mi:ss'))

IS there any possible option to reduce the timestamp?

Can MATERIALIZE VIEW wil help .....

USING MATERIALIZE VIEW:
 CAN MATERIALIZE VIEW, WILL HELP FOR THE ABOVE SCENARIO TO REDUCE THE TIME STAMP?
 WHAT CONS FROM DATA INTEGRITY ASPECTS?


PLS SUGGEST.
Re: Comparision Usage of DBLINK & MATERIALIZE VIEW [message #417799 is a reply to message #417793] Tue, 11 August 2009 10:08 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell, a "materialized view" approach might be a lot faster than the one via database link.

However, you might try to leave a query as it is, but test how different hints affect query execution. Check DRIVING_SITE and INDEX, for example.
Re: Comparision Usage of DBLINK & MATERIALIZE VIEW [message #417959 is a reply to message #417793] Wed, 12 August 2009 05:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Once you've got the data in the Mview, it will almost certainly be quicker to access it that it would have been across the database link.
However, you will still have the overhead of populating the mview in the first place.
Re: Comparision Usage of DBLINK & MATERIALIZE VIEW [message #418481 is a reply to message #417793] Sun, 16 August 2009 05:23 Go to previous messageGo to next message
bishtoo
Messages: 20
Registered: August 2009
Junior Member
i think MV approach will be better/faster.
anyway we need DB link to refresh MV from remote database.
Re: Comparision Usage of DBLINK & MATERIALIZE VIEW [message #418486 is a reply to message #418481] Sun, 16 August 2009 08:14 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Agree with LF above. Try DRIVING_SITE - especially if one of the date criteria is going to be highly selective:

SELECT /*+ DRIVING_SITE(XYZ)*/ * FROM XYZ@ADAM A, (SEL...


Ross Leishman
Previous Topic: sysdate - sql query
Next Topic: PLS-00402 Alias required for Select list of cursor to avoid dup col names (2merged)
Goto Forum:
  


Current Time: Wed Dec 07 18:22:37 CST 2016

Total time taken to generate the page: 0.14909 seconds