Comparision Usage of DBLINK & MATERIALIZE VIEW [message #417793] |
Tue, 11 August 2009 09:56  |
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.
|
|
|
|
|
|
|