Re: Database Views
If you can't use a DW idea look into Snapshots. If you don't need an up to the second view of the data that is. or schedule the report run time. Most reporting packages have the ability to do so. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 17, 2002 7:20 AM This is why data marts were created. You would be much better off to create a reporting database based on the data you need to see. You won't be happy doing this with views from several databases. It won't perform well. As Rachel pointed out it will be inconsistent. At the very least you could create snapshots of these tables so that all of the data would be locally available. Just my $0.02. Jared On Wednesday 16 January 2002 12:04, [EMAIL PROTECTED] wrote: I would like get your opinion on what would be the best way provide access to data across multiple databases on just on Node (database). One way that I have tried is create a view on one database that reads tables across remaining four databases using Dblinks. Let us say there is a table called CUSTOMER that resides on five databases, in order to make it easier to generate reports, a combined view can be created with dblinks on these tables using UNION ALL on one database. Only concern I have is that when using Dblinks, Oracle might copy the entire remote table to the local database and then retrieve the data based on where clause. This might be a significant performance issue especially if the tables are very BIG. If there are better ways to accomplish this or any ideas on this one will be greatly appreciated. Thank you, Srini -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Database Views
Hello Srini We had a case like this where one of the tables was in a database that reside in a remote site. Our solution was to create a table that combine employees tables from 2 site into one TABLE in the local site. This way the select and join is done ONCE at night. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Wed, January 16, 2002 10:05 PM To: Multiple recipients of list ORACLE-L Subject: Database Views I would like get your opinion on what would be the best way provide access to data across multiple databases on just on Node (database). One way that I have tried is create a view on one database that reads tables across remaining four databases using Dblinks. Let us say there is a table called CUSTOMER that resides on five databases, in order to make it easier to generate reports, a combined view can be created with dblinks on these tables using UNION ALL on one database. Only concern I have is that when using Dblinks, Oracle might copy the entire remote table to the local database and then retrieve the data based on where clause. This might be a significant performance issue especially if the tables are very BIG. If there are better ways to accomplish this or any ideas on this one will be greatly appreciated. Thank you, Srini -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This e-mail was scanned by the eSafe Mail Gateway -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Database Views
that works as long as you can live with possibly inconsistent data in the joint table due to either 1) updates to the employee data on either site during the day not reflected in the joint table until the nightly processing is done 2) nightly process failing and data in the joint table either non-existent or wrong --- àãø_éçéàì [EMAIL PROTECTED] wrote: Hello Srini We had a case like this where one of the tables was in a database that reside in a remote site. Our solution was to create a table that combine employees tables from 2 site into one TABLE in the local site. This way the select and join is done ONCE at night. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Wed, January 16, 2002 10:05 PM To: Multiple recipients of list ORACLE-L Subject:Database Views I would like get your opinion on what would be the best way provide access to data across multiple databases on just on Node (database). One way that I have tried is create a view on one database that reads tables across remaining four databases using Dblinks. Let us say there is a table called CUSTOMER that resides on five databases, in order to make it easier to generate reports, a combined view can be created with dblinks on these tables using UNION ALL on one database. Only concern I have is that when using Dblinks, Oracle might copy the entire remote table to the local database and then retrieve the data based on where clause. This might be a significant performance issue especially if the tables are very BIG. If there are better ways to accomplish this or any ideas on this one will be greatly appreciated. Thank you, Srini -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This e-mail was scanned by the eSafe Mail Gateway -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Database Views
This is why data marts were created. You would be much better off to create a reporting database based on the data you need to see. You won't be happy doing this with views from several databases. It won't perform well. As Rachel pointed out it will be inconsistent. At the very least you could create snapshots of these tables so that all of the data would be locally available. Just my $0.02. Jared On Wednesday 16 January 2002 12:04, [EMAIL PROTECTED] wrote: I would like get your opinion on what would be the best way provide access to data across multiple databases on just on Node (database). One way that I have tried is create a view on one database that reads tables across remaining four databases using Dblinks. Let us say there is a table called CUSTOMER that resides on five databases, in order to make it easier to generate reports, a combined view can be created with dblinks on these tables using UNION ALL on one database. Only concern I have is that when using Dblinks, Oracle might copy the entire remote table to the local database and then retrieve the data based on where clause. This might be a significant performance issue especially if the tables are very BIG. If there are better ways to accomplish this or any ideas on this one will be greatly appreciated. Thank you, Srini -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Database Views
Hello Rachel 1) Yes. You can also change your application to update the new table whenever something change in one of the file. Since personal do not change every day (hopefully) one day lag is OK for us. 2) This is done with SQL program that do delete and then insert and commit. Either the program works and I have a new copy or it didn't work and I am left with yesterday data. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: Rachel Carmichael [SMTP:[EMAIL PROTECTED]] Sent: Thu, January 17, 2002 4:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: Database Views that works as long as you can live with possibly inconsistent data in the joint table due to either 1) updates to the employee data on either site during the day not reflected in the joint table until the nightly processing is done 2) nightly process failing and data in the joint table either non-existent or wrong --- àãø_éçéàì [EMAIL PROTECTED] wrote: Hello Srini We had a case like this where one of the tables was in a database that reside in a remote site. Our solution was to create a table that combine employees tables from 2 site into one TABLE in the local site. This way the select and join is done ONCE at night. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Wed, January 16, 2002 10:05 PM To: Multiple recipients of list ORACLE-L Subject: Database Views I would like get your opinion on what would be the best way provide access to data across multiple databases on just on Node (database). One way that I have tried is create a view on one database that reads tables across remaining four databases using Dblinks. Let us say there is a table called CUSTOMER that resides on five databases, in order to make it easier to generate reports, a combined view can be created with dblinks on these tables using UNION ALL on one database. Only concern I have is that when using Dblinks, Oracle might copy the entire remote table to the local database and then retrieve the data based on where clause. This might be a significant performance issue especially if the tables are very BIG. If there are better ways to accomplish this or any ideas on this one will be greatly appreciated. Thank you, Srini -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This e-mail was scanned by the eSafe Mail Gateway -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing
RE: Database Views
Thank you all for your opinions. The tables that I want to consolidate are 15 Million rows each. So five tables put together will constitute 75 million rows in one table. I will have to consider partitioning after that for optimum performance. Also Materialized view would take a long time to refresh all these tables especially if it is a complete refresh. - Srini -Original Message- Sent: Thursday, January 17, 2002 10:21 AM To: Multiple recipients of list ORACLE-L This is why data marts were created. You would be much better off to create a reporting database based on the data you need to see. You won't be happy doing this with views from several databases. It won't perform well. As Rachel pointed out it will be inconsistent. At the very least you could create snapshots of these tables so that all of the data would be locally available. Just my $0.02. Jared On Wednesday 16 January 2002 12:04, [EMAIL PROTECTED] wrote: I would like get your opinion on what would be the best way provide access to data across multiple databases on just on Node (database). One way that I have tried is create a view on one database that reads tables across remaining four databases using Dblinks. Let us say there is a table called CUSTOMER that resides on five databases, in order to make it easier to generate reports, a combined view can be created with dblinks on these tables using UNION ALL on one database. Only concern I have is that when using Dblinks, Oracle might copy the entire remote table to the local database and then retrieve the data based on where clause. This might be a significant performance issue especially if the tables are very BIG. If there are better ways to accomplish this or any ideas on this one will be greatly appreciated. Thank you, Srini -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
