Re: Locally Managed Tablespaces ... again!!!

"Marc Blum" <marc_at_marcblum.de> wrote in message news:e2l32vsau6q3k64k919m23s1n2fh815vt7_at_4ax.com...
> On Sat, 11 Jan 2003 09:06:37 +1100, "Howard J. Rogers"
> <howardjr2000_at_yahoo.com.au> wrote:
>
>
> >If you want to perform maintenance on a data file, you offline the
> >tablespace. If you offline a tablespace containing both indexes and
tables,
> >no-one can do any work at all. In separate tablespace, you could offline
the
> >index one, and people could still get at their data, albeit rather more
> >slowly than they're used to.
>
> sure?

Yup. Your own demo shows you retrieving table data when the index tablespace is offlined. It is true that the explain plan can't be the same, and if I implied otherwise I apologise.

>how about that one: the optimizer isn't aware of offlined tablespaces.
> Please enlighten me, how an application with GUI and bla should handle
this one:

How about stored outlines? You create a stored outline when there are no indexes present (or which use hints to force tablescans). When it's time for maintenance (and hence offlining of the index tablespace) you simply issue an 'alter session set used_stored_outlines=TRUE' (or bung it in the init.ora and bounce).

Or, you create a set of outlines for normal index access, and a set for full tablescans. Then you set used_stored_outlines=NORMAL or used_stored_outlines=FULLTS.

For example, consider tablespace TBL containing a copy of dba_objects (and called 'BIGONE'), and a tablespace IDX which contains nothing at the moment:

SQL> alter session set create_stored_outlines=TRUE; Session altered.

SQL> select * from bigone where object_id=37724; no rows selected

(And by virtue of the first command, this statement has just created a stored outline forcing a full scan of the BIGONE table).

SQL> alter session set create_stored_outlines=FALSE; Session altered.

SQL> create index bigidx on bigone(object_id) tablespace idx; Index created.

(Now the index is in place...)

SQL> set autotrace on exp
SQL> select * from bigone where object_id=37724; no rows selected
Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIGONE'    2 1 INDEX (RANGE SCAN) OF 'BIGIDX' (NON-UNIQUE) (...and quite clearly the optimizer thinks it is useful).

SQL> alter tablespace idx offline;
Tablespace altered.

SQL> select * from bigone where object_id=37724; select * from bigone where object_id=37724

              *
ERROR at line 1:
ORA-00376: file 12 cannot be read at this time ORA-01110: data file 12: 'D:\ORACLE\ORADATA\OEMREP\IDX1.DBF'

(And as you say, if the index is offline, we've got trouble).

SQL> alter session set use_stored_outlines=TRUE; Session altered.

(Now switch on the use of stored outlines).

SQL> select * from bigone where object_id=37724; no rows selected
Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=126 Card=334 Bytes=5
          9118)

   1    0   TABLE ACCESS (FULL) OF 'BIGONE' (Cost=126 Card=334 Bytes=5
          9118)

(And lo and behold, we can still get access to the data, albeit via a full scan)

Of course, the use of stored outlines is invisible to the user, so whether he's using a GUI or not, this technique would still work.

Regards
HJR [snip] Received on Sun Jan 12 2003 - 15:31:58 CST