Re: Advantages/disadvantages of having tables within 'SYSTEM' TS

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3f02bcb6$0$18489$ed9e5944_at_reading.news.pipex.net...
> Tablespaces give the following benefits that the "one big pot" approach
> doesn't.
>
> It does give space management benefits because of quotas.

Why on earth would I give different quotas per each tablespace? For example, user Scott has 1G quota on SYSTEM tablespace and 20G on USERS -- that sounds stupid to me. Any realistic motivation?

> It enables physical separation of logically separate entities.

There is no such thing as physical locality. For example, we might think that objects are collocated on disk, but striping invalidates it. Besides, files do exactly that. If motivation was providing file surrogate for raw devices, why didn't they just call it "file"?

> It enables differential backups of data objects with different backup
> requirements.

What if DBA initially created all-encompassing tablespace USERS, but then realized that he have to reorganize it into 2 just because there are different backup requirements? Is tablespace reorganization easy? Wouldn't it be more straightforward to allow DBA to backup objects that meet a certain criteria?

> In particular it means that a runaway user object cannot stop the db
> entirely from working for simple space management reasons.

Wouldn't it normally be solved by figuring out what datafiles is runaway object is, and putting those datafiles offline?

OK, you may have a point here, tablespaces is just some grouping of objects that might have some utility. However, given numerous other storage artifacts like extents, segments, blocks, partitions, etc, the whole storage design looks ridiculously complex.

> > Practical comment. System tablespace has some separate logic that you
> might
> > be not aware of. For example, until recently skip-scan index scans were
> > disabled on system tablespace.
>
> I have a hard time believing this. AFAIK Oracle has *no* method for
> disabling access paths on a tablespace basis. If you can demonstrate this
> I'd be more convinced. Generally the SYSTEM and SYS *schemas* aren't
> analyzed so some access paths won't be considered as the RBO is used, this
> is not the same as disabling them for a tablespace.

You may verify it in 9.2:

select --+ INDEX_SS(o i_OBJ2)
* from sys.obj$ o where name = 'EMP'

(hint would trigger CBO). Received on Wed Jul 02 2003 - 13:43:17 CDT