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

Daniel

Which point would you like to prove:

Table T1 is in tablespace USERS
It has three indexes, in tablespace INDX You'll have to trust me that I played fair with these statistics, on an idle database, as honest differences between before and after snapshots. (See my website for information about one of the better ways of taking stats like these).

Demo 1:
In an update-intensive environment the
"INDEX" tablespace is likely to get hammered much harder than the "TABLE" tablespace.

alter system checkpoint;

begin
 for i in 10001..10010 loop
  insert into t1 values(
   i,i,mod(i,400),rpad('x',20),rpad('x',100)   );
 end loop;
 commit;
end;
/

alter system checkpoint;

Writes      Blocks   Avg Csecs    Max Tablespace
------      ------   ---------    --- -------------------
     2           2        .500    457 USERS
    12          12       5.333    133 INDX

Test 2
In a read-intensive environment the "TABLE" tablespace is more likely to get hammered than the "INDEX" tablespace

alter tablespace users offline;
alter tablespace users online;
alter tablespace indx offline;
alter tablespace indx online;

select count(v1)
from t1
where
 n2 = 50
;

Reads      Blocks   Avg Csecs    Tablespace
-----      ------   ---------    -------------------
   25          25        .480    USERS
    2           2       1.500    INDX

As we agreed on this newsgroup many moons ago, it's not so much that the table/index split is absolutely wrong - it's just too simple-minded to be of general value, but sounds sufficiently rational to persuade people that they've done the best thing.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation


(see http://www.jlcomp.demon.co.uk/tutorial.html )


Next Seminar dates:


(see http://www.jlcomp.demon.co.uk/seminar.html )


____England______January 21/23
____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



DA Morgan wrote in message <3E29B221.7C93D59_at_exesolutions.com>...

>Well said. I am in complete agreement. I think Tom, Howard, et al
have made
>their point eloquently.
>
>I'm now trying to put together a demo for my students and that is a
bit more
>challenging.
>
>Daniel Morgan
>