i don't believe that external tables can be indexed yet. you've got parallel query, though...
> ----- Original Message ----- > From: <[EMAIL PROTECTED]> > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Friday, November 08, 2002 4:23 AM > Subject: RE: Data Purging Strategy > > > > Another poor man's solution would be to unload the tables into flat files > and attach > > to them as needed using Oracle's external table feature from 9i. That > solution > > should hold for quite a while into the future since the external table > function is very > > much like SQL*Loader, which is so integral to so many systems that Oracle > is not > > going to think about making it 'go away'. You would still run into > problems if there is > > some substantive change that makes the external tables from 9i invalid, > but that still > > leaves you with flat files that you can load back into the DB with > SQL*Loader. > > > > Chris Gait > > > > > > On 6 Nov 2002 at 6:43, Conboy, Jim wrote: > > > > Date sent: Wed, 06 Nov 2002 06:43:38 -0800 > > To: Multiple recipients of list ORACLE-L <ORACLE- > > [EMAIL PROTECTED]> > > Send reply to: [EMAIL PROTECTED] > > Organization: Fat City Network Services, San Diego, California > > > > > A poor man's solution might be to load the offline database with > appropriate data, then do a tablespace export and store the results on CD > labelled by date. Restoring needed data would entail a tablespace import of > stuff from the appropriate CD into the offline DB. I'm sure here's some > > gotchas involved but some variation on that theme might work. > > > > > > Jim > > > > > > > > > -----Original Message----- > > > Sent: Wednesday, November 06, 2002 8:49 AM > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > This is a data-archival requirement, not a data-purge requirement. It > only resembles a purge requirement based on the multiple-database-migration > strategy you outlined. There are alternatives... > > > > > > Depending on the volume of data in your database and your availability > requirements, implementing table- and index-partitioning will likely be > crucial. One strategy is to have the most-active tables partitioned by a > date column and have different sets of these partitions reside in > time-variant > > tablespaces. With this arrangement, you can archive data to tape by > simply setting the archived tablespaces to READ ONLY and then migrating them > to tape-based (instead of disk-based) file-systems and bringing them back > online. Legato has this file-system technology (recently purchased) and > > there is a share-ware product called SAMFS which is an HSM (hierarchical > storage mgmt) filesystem used by some vendors (i.e. StorageTek, etc). By > setting tablespaces to READ ONLY it becomes very easy to move them from disk > to tape while retaining them within the same original database, > > simplifying the task of later retrieval (which is really important). > > > > > > Of course, Oracle's partitioning option is enormously expensive, but in > this case it is a matter of the upfront license costs (with reduced > downstream implementation costs due to simplicity) versus a large downstream > application-development cost. In this situation, I think roughly offsets > > everything. Since I'm not spending the money, I can afford such a > calculation... :-) > > > > > > With the various storage technologies available, a single database can > straddle several simultaneously, optimizing performance or cost as needed. > Some files might reside on solid-state NVRAM "disk", some on SAN-based disk, > some on NAS-based storage, and then finally reside in archive media > > file-systems such as tape or magneto-optical based HSM file-systems. > > > > > > ----- Original Message ----- > > > To: Multiple recipients of list ORACLE-L <mailto:ORACLE-L@;fatcity.com> > > > Sent: Wednesday, November 06, 2002 2:13 AM > > > > > > > > > Dear List, > > > > > > I need some inputs from you all regarding purging data from the > database. > > > > > > This is the requirement > > > > > > > > > We define a retention period for all the data in the system. > > > When the retention period is reached, the data should be deleted, but > then at a later time, some user might request for this purged data. So it > must be possible to retrieve this data. > > > > > > This is the strategy we have designed for this. > > > > > > When the retention period is reached, move the data from the main > database to an offline database. Then delete the data from the main > database. > > > > > > In the offline database, we cannot again keep it from long, so it has to > moved to tapes. Now my question, how can we move this data to tapes and at > the same time retrieve data from the tapes based on dates. > > > i.e, the user will ask for the data on a particular date, so it must be > possible to retrieve data from the tapes based on a date and load it to the > database tables. > > > > > > Regards > > > Prem > > > > > > > > > > > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > > San Diego, California -- Mailing list and web hosting services > > --------------------------------------------------------------------- > > 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: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
