Re: Loading 300 million records

AlexK wrote:
> All tables
> are on the same database, nothing is being accessed across a dblink.
...
> A view was also mentioned in the posts. This data is being used by a
> web application that performs some analytics on it. I'm not sure of
> the details of the app. The data needs to be in a table as the source
> tables do change at times. If the table was a view, the results
> generated by the web app could change which is not what the users want.
> Also, I think the query runs too slowly to be used as a view.

It sounds like all the tables are in the same database and you are generating some sort of a static snapshot of a view of the data. If this is the case, then you really need to look at Materialized Views. This is exactly the problem that snapshots/mviews were invented to solve.

In fact a materialized view is even capable of performing incremental updates to your data if it meets certain criteria... meaning that you can initially populate the table with data (suppose this takes 10 hours) and the have the DB track changes to the source tables, and nightly/weekly/monthly/whatever you can do an incremental update that will go *much* faster (it might take less than an hour).

Also, you can specify a schedule for refreshing Materialized Views and then the database will take care of updating the data automatically. If this is applicable in your situation it's probably worth consideration!

> What is CTAS, I'm not quite familiar with that acronym.

CREATE TABLE ... AS SELECT ... Received on Mon Jun 13 2005 - 13:50:11 CDT