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

Jonathan Lewis wrote:

> 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
> >

Thanks. This is some of what I am looking for.

I am in complete agreement on the table-index split and given the huge volume of mythology out there want my students to see, first hand, that it is as you suggest simplistic. These demos are a definite help in doing that.

Thanks again.

Daniel Morgan Received on Sun Jan 19 2003 - 09:28:01 CST