Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Tom.

Asked: February 15, 2001 - 8:54 am UTC

Answered by: Tom Kyte - Last updated: June 21, 2012 - 7:39 am UTC

Category: Database - Version: 8i

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Is it still a good idea to keep indexes in their own tablespace? Does this inhance performance or is it more of a recovery issue? Does the answer differ from one platform to another?

Thanks in advance,

Tom

and we said...

Yes, no, maybe.

The idea, born in the 1980's when systems were tiny and user counts were in the single digits, was that you separated indexes from data into separate tablespaces on different disks.

In that fashion, you positioned the head of the disk in the index tablespace and the head of the disk in the data tablespace and that would be better then seeking 2 times on the same disk.

Drives back then were really slow at seeking and typically measured in the 10's to 100's of megabytes (if you were lucky)


Today, with logical volumes, raid, NN gigabyte (nn is rapidly becoming NNN gigabytes) drives, hundreds/thousands of concurrent users, thousands of tables, 10's of thousands of indexes -- this sort of "optimization" is sort of impossible.

What you strive for today is to be able to manage things, to spread IO out evenly avoiding hot spots.

Since I believe all things should be in locally managed tablespaces with UNIFORM extent sizes, I would say that yes, indexes would be in a different tablespace from the data but only because they are a different SIZE then the data. My table with 50 columns and an average row size of 4k might belong in a tablespace that has 5meg extents whereas the index on a single number column might belong in a tablespace with 512k or 1m extents.

I tend to keep my indexes separate from the data but for the above sizing reason. The tablespaces frequently end up on the same exact mount points. You strive for even io across your disks and you may end up with indexes and data on the same devices.

and you rated our response

  (26 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

9.1 documentation suggests seperate because...

November 28, 2002 - 5:12 pm UTC

Reviewer: A reader

Hi

in Oracle 9i documentation Concepts guide chapter 11 where it says HOW INDEXES ARE STORED it states this:

Furthermore, you can improve performance of queries that use an index by storing an index and its table in different tablespaces located on different disk drives, because Oracle can retrieve both index and table data in parallel.



So Oracle can really read both index and data in parallel? if that was the case then we must seperate index from data no?

Tom Kyte

Followup  

November 29, 2002 - 9:28 am UTC

that is a documentation bug. It is not 100% true nor false

it is (well should be) obvious that it cannot access them in parallel for a single session. We read an index to find the table data. Index then data. Index read, then table access by rowid. Not "index+data" at the same time, INDEX leads us to DATA.

Now, in a multi-user system -- we are back to the "old reasons". It was a "poor mans stripe". Put things on different devices to spread the IO out. Someone will be reading the index disk whilst someone else is reading the data disk.

Also -- did it go onto say "of course, you best make sure the tablespaces are on DIFFERENT PHYSICAL DEVICES". if it didn't, then it left you hanging -- without giving you the really important piece of information. That is is trying to spread IO out across all devices. Not that index and data need to be separated like matter and anti-matter.

The SAME exact benefits could have been achieved just by using small extent sizes -- creating one tablespace with datafiles from many disks -- and putting all of the data in there. We tend to allocate extents in a round robin fashion from the available datafiles in the tablespace so the index and data extents would be splayed across the disks. This would achieve the same goal -- to achieve EVEN IO.

That is the goal, the only goal here -- achieve an even distribution of IO across as many devices as you can give us. Achieve that however you want but putting indexes on disk1 and data on disk2 is perhaps the weakest, least effective way to do that!!




how about batch processing

September 04, 2003 - 5:59 pm UTC

Reviewer: Dick Stins from The Netherlands, Amersfoort

Tom,

This is very interesting. I guess that you are right in a multiuser environment (oltp) with lot's of users all doing different kind of things on lot's of different tables and indexes. All movements of the heads on the disks are quiet randomly and it is very hard to tell which constructions are able to reduce the head movements (still bottleneck on mechanical drives).

But quiet often are the machines at night dedicated for batch processing. In this case it is easier to predict and the transactions on the head movements. When you put indexes and data on one disk, than you are guaranteed that the heads will be swapping between those physical index and data sectors on the harddisk.

When you put the indexes and the data on different harddisks, then it might help to reduce the head movements for this specific situation.

Ofcourse it depends on the kind of hardware, cache, ....
So you need to test.

Parallel read of indexes and data might be hard (or not?) but what happens with updates? Indexes and data might be updated in parallel.

Tom Kyte

Followup  

September 05, 2003 - 3:28 pm UTC

NO NO NO.

no you don't.

period.

it won't. it hasn't. it never really did.

if you are using indexes (single block, random io -- as you traverse from ROOT block to branch block to leaf block you'll have read data from 3 TOTALLY different places on the disk), you are accessing the table using single RANDOM block IO as well.


So no, it won't help "keep the heads where they should be" cause the heads are NEVER where they should be after any given read -- they always have to move elsewhwere to do the next read.


so, if you are using indexes, forget about this argument, it doesn't work.


(the updates are not done in parallel and the updates involve tons of single block, random IO once again to walk the index structure -- the heads are flying all over the place)

flying heads

September 06, 2003 - 5:25 pm UTC

Reviewer: Dick Stins from The Netherlands, Amersfoort

Tom,

Thanks for you interesting answer.
You are right you keep head movements for index searches, but when the index is not defragmented in lot's of extents and in the same tablespace, then the heads are still closer to the needed blocks. That saves big head movements so it takes less time to read.

You should know that in batch processing and data warehouses, you not only have index searches but also lots of inserts, updates, deletes, full table searches, hash joins. I can't imagine that your proposition needs some extra refinement.

Concerning my information, you can do parallel updates when the updates are spread over different partitions.

I might have an interesting example. But first I need to do some extra testing to be sure.

Myth vs. Counter-Myth

June 09, 2004 - 10:31 am UTC

Reviewer: Greg Clough from London, Merry olde England

Hi Tom,

Thanks very much for this honest (e.g. "No No No!":) information. I am having the exact same argument with the other DBA's at my site. I think the way you do, but they have come up with a mountain of opposing white papers, etc. Even stuff from the widly respected Mike Ault.

We have 6 x RAID-0+1 disk volumes, and 70Gb of data/index.
* I want to spread the data and index datafiles evenly over the 6 disks
* They want 3 for data and 3 for index

Please help me convince them of the error of their ways. If a response from "Ask Tom" won't do it, then I'm lost... It's 3 vs. 1 at the moment. :-(

Many Thanks.
Greg Clough

Tom Kyte

Followup  

June 09, 2004 - 11:26 am UTC



ask them to tell us why it would be beneficial.

have them read through:

</code> http://tinyurl.com/3hc74 <code>

(Mike even chimed in)

Myth vs. Counter-Myth

June 09, 2004 - 10:31 am UTC

Reviewer: Greg Clough from London, Merry olde England

Hi Tom,

Thanks very much for this honest (e.g. "No No No!":) information. I am having the exact same argument with the other DBA's at my site. I think the way you do, but they have come up with a mountain of opposing white papers, etc. Even stuff from the widly respected Mike Ault.

We have 6 x RAID-0+1 disk volumes, and 70Gb of data/index.
* I want to spread the data and index datafiles evenly over the 6 disks
* They want 3 for data and 3 for index

Please help me convince them of the error of their ways. If a response from "Ask Tom" won't do it, then I'm lost... It's 3 vs. 1 at the moment. :-(

Many Thanks.
Greg Clough

Yes Yes Yes

April 12, 2005 - 3:25 pm UTC

Reviewer: Mike Ault from Alpharetta, Ga

The logical separation of indexes from tables allows tracking of index related IO and minimizes the impact of index creation and management on the tables. If it also allows for IO spread,by placing them on different arrays/spindles, even better (and yes, moving tables can achieve IO spread as well, but we aren't talking about tables.)

Putting the shoe on the other foot, other than a slight increase in tablespace management (come on, that is a bit lame for an object you create once, add to a backup once) what are the bad things about logically separating tables and indexes?

I think the benefits are clear. I assume for your root account you have all sorts of files, temp files and such all in the / directory right? After all, they are on a RAID device so there is no "physical advantage in separating them. Shoot, wouldn't Oracle installs be easier if we just put all the config, executable, data, and other files in one big Oracle directory? Shoot, just put it all in root.

Tom Kyte

Followup  

April 13, 2005 - 8:45 am UTC

striping does all of that Mike, all of that (IO spread)

The myth of table on this disk, index on that disk was because of the single extent myth.

IF instead you had logically just said "ok, small extents, table and index both" and used both disks in a single tablespace (or files from both disks in two tablespaces) you would have achieve even better IO spread, even BETTER

The goal: even IO
The bad attempt: indexes over there, tables over here
The stuff we do today: stripe it all


My point is, separation of indexes from tables was NEVER EVER ABOUT performance, it was always about administration.


The myth was about performance.


No No No Yes Yes Yes

June 19, 2005 - 5:45 am UTC

Reviewer: Dick Stins from The Netherlands, Amersfoort

Tom,

It just depends of your hardware, queries, number of transactions, ..... At avarage, you would probably be right, but for some specific systems you might get huge improvements.

With your randon read theory, would partitioning for performance issues also useless. In the real world, I got huge performance improvements with the same queries.

The goal is not even io.
The goal is faster queries.

You realise this with reducing the number head movements of your disks. This is the most time consuming part.

It would be great when oracle supplies simulation tools to discover this for a specific hardware configuration.


Tom Kyte

Followup  

June 19, 2005 - 7:16 am UTC

point is: if you get improvements from separating index from table, it means one thing:

you had a hot spot.


You had IO contention. What made it faster?

Even IO.

That is what makes it faster. The goal is truly "even IO distribution".

How else could you fix this? Many ways. What is a "weak" way, one that achieves minimal return on investment?

put indexes in /d01/index.dbf
put data in /d02/data.dbf

where d01 and d02 are separate physical devices.

What would achieve the goal, even IO?

create a tablespace "t" with datafiles /d01/t1.dbf and /d02/t2.dbf. create indexes and tables in this tablespace. Use small enough extents so that indexes and tables always have a couple dozen extents -- striped over two disks (N disks).

Or, if you want to have them separated for a logical reason, like admin, backup/recovery, you like it that way, whatever - do the same

create tablespace index /d01/i1.dbf /d02/i2.dbf
create tablespace data /d01/d1.dbf /d02/d2.dbf

and use the small extents again.


You have to go back to "what did moving indexes over there and tables over here" accomplish? It had to of accomplished "better IO distribution" (unless you can come up with something else, that has to be it). How can we achieve better IO distribution -- smearing of data over everything.


This is the entire concept behind raid, ASM in 10g, big SANs and their software and so on.



So, you can keep putting indexes over there, tables over here, I'll stripe.

application tuning

June 19, 2005 - 8:40 am UTC

Reviewer: Dick Stins from The Netherlands, Amersfoort

Tom,

The goals is faster queries=better performance

Striping is just a method to spread io over disks which might produce faster queries.

Striping does not beat the physical behaviour of disks.

When you are able to put often requested data at the faster accessible (striped) parts of the disks, then you win performance.

Only the application designers/developers are able to know which data is needed and in which sequence. Only striping is not good enough. Some SAN has intelligence to do this automatically, but that is not allways successful.

You just confirmed that the oracle database io optimisation can still be improved.

In return to the lack of tools which might proof that database can be better tuned, you advise everybody to use striping....
With a statistically random io behaviour of the disks, you are right with this. But not all applications shows this random behaviour to be able to confirm that striping returns the best performance.

It would be nice when you create a scientific parameterized simulation model for several cases (batch, oltp, x users, x disks, striped, ...) and publish it.

This enables everybody to study this for their own applications and redo this at affordable hardware and every body is then able to get insight at the behaviour of oracle database applications spread over several (striped) disks.

Tom Kyte

Followup  

June 19, 2005 - 11:44 am UTC

so, you are saying "indexes and data separation" is a good way to tune IO patterns????

The science behind "put indexes there and data there" is simply that of "spread IO out".

If you have scientific proof otherwise, pony it up.

quote hot spot

June 19, 2005 - 9:10 am UTC

Reviewer: A reader

You're "Followup:

point is: if you get improvements from separating index from table, it means one
thing:

you had a hot spot."

For your information: several hundreds of disks were all striped. Playing with bigger extents and partitions increased the performance dramatically.

This does proof that randomly equally spread small extents is not for every system optimal!!!!!!

Tom Kyte

Followup  

June 19, 2005 - 11:46 am UTC

what does bigger extents and partitions have to do with anything on this page?????


But, I seriously doubt bigger extents had squat to do with anything -- to get to bigger extents, what did you do??? you rebuilt structures (we are back to 1989 with "put everything in a single extent -- I exported and imported into a single extent and it was better, therefore it was the single extent". No, it wasn't, it was the rebuild of everything and it was short term)

That test has been done over and over and over.



But, quick question to you -- were you using a SAN that has so called "intelligent" algorithms that kick in for read aheads. If so, you might have observed something by segmenting out

o objects that typically do single block IO
o objects that typically do multi-block IO

Meaning it still isn't "indexes over there, tables over here" but rather -

Objects that do mostly single block IO over here (many or even most indexes, many tables)

Objects that do mostly full scans (some tables, some indexes)

Objects that you cannot classify -- to keep them from the rest in order to not mess up the intelligent algorithms




quote hotspot

June 19, 2005 - 2:21 pm UTC

Reviewer: Dick Stins www.zion-it.com from The Netherlands, Amersfoort

Tom,

Thanks for your opinion.



Tom Kyte

Followup  

June 19, 2005 - 3:21 pm UTC

you don't want to tell us if the thing could have been single block IO vs multiblock IO?

Indexes are read just like tables, they are not "magical" in any sense. They are full scanned sometimes, they are single block read sometimes.

Tables are read just like indexes, they are not "magical" in any sense. They are full scanned sometimes, they are single block read sometimes.

Wouldn't you like to know what really is happening on your system? So you can take advantage of that knowledge into the future and really get the benefit of your hardware?

I would, I do.

Backup/recovery, nologging

June 20, 2005 - 4:28 am UTC

Reviewer: Edgar from Latvia

Hi,

IMHO, it is worth idea to consider putting indexes and tables into separate tablespaces because of backup/recovery considerations, rather then because of performance.
Indexes are often buid using "nologging" to minimize maintenance/downtime windows.
It is not a big problem also, if it is impossible to recover index tablespace - you can simply rebuild and analyze missing indexes, no data loss.
And it is possible even to skip index tablespace during point-in-time "user error" recovery.

Tom, can You, please, comment also this part of initial question:
> is it more of a recovery issue?

Sincerely,

Tom Kyte

Followup  

June 20, 2005 - 10:19 am UTC

You use tablespaces to make your life as an admin easier, better.

Separating objects you don't need to recover (which may include tables and indexes) from those you do -- sure.

Breaking them into separate tablespaces just to logically organize things - sure.



Administration purposes

June 20, 2005 - 7:07 am UTC

Reviewer: Paul Guerin from Wollongong, Australia

If all objects were located on a single tablespace - then the tablespace corrupted - then you would need to recover all objects - correct....

If the objects were separated (so different data files), and then the:
1) index corrupted, at least there is a chance that the table would be unaffected - so recovery/rebuild of only one object is needed....
2) table corrupted, then you would recover this object. The data is now available while you rebuild the index....

By separating the objects, you are limiting the possible damage that may occur.... Maybe a good idea to give all critical objects their own tablespace....

Tom Kyte

Followup  

June 20, 2005 - 10:37 am UTC


the units of recovery are basically blocks and files.

No one is arguing that there are not reasons to separate OBJECTS in general (won't say indexes vs data here, just objects)

but you do it to make your life better, easier to admin.

Why smaller extents?

June 07, 2006 - 12:08 am UTC

Reviewer: Aru from NZ

Hi Tom,
In this thread you have many a times talked about using/benefits of smaller extents. When we are talking about i/o how does it matter what size extents are? What we are after is blocks , isen't it? Agreed larger extents have no apparant benefits but I don't see any benefit in extents being smaller also.
Waiting to be rectified.
Regards,
Aru.

Tom Kyte

Followup  

June 07, 2006 - 6:56 am UTC

I did not really talk about the benefits of smaller extents in as much as we said "having many extents is OK, fine, good, not a problem"

But yes, we are interested in reading

a) a single block
b) multi-block read count blocks (say 16) at a time.

we don't read "extents", unless the extent is the size of the multi-block read count

Seperating tables across different tablespaces

April 17, 2008 - 4:30 pm UTC

Reviewer: Shrikant from NJ, USA

Will it help in the ETL performance if we seperate tables across different tablespaces ? By seperarting I mean the high volume/frequently updated tables in single tablespace, tables with low volumn and less updates in seperate tablespace. Will it help in Oracle 10g?
Tom Kyte

Followup  

April 17, 2008 - 4:36 pm UTC

what if all of the tablespaces use files on the same disk?

your goal: even IO distribution over as many devices as you can.

your tools: raid striping, ASM, using many files per tablespace - each file from a different device (poor mans striping)

at the EXTREME - placement of individual tables on separate devices. Typically, in the year 2008 with hundreds/thousands of tables and few very large devices this is neither practical, reasonable or desirable.

April 17, 2008 - 4:50 pm UTC

Reviewer: Shrikant from NJ, USA

We have already ensured even IO distribution by having Oracle filesystem across multiple disks.

And we are thinking of sepearting tables across different tablespaces based on the data volume and update frequency.
The seperation is just for good space management perspect, but would be there any performance gain (or loss) because of this.


Tom Kyte

Followup  

April 17, 2008 - 9:53 pm UTC

The separation wouldn't do much for space management really (not unless you frequently drop or truncate - but then just put things you do that together and things you don't do that to together)

tablespaces are not a performance thing.

tablespaces are a "make my life better, happier because I like the way the data is organized"

think about it - a tablespace is just a bunch of files. Files have extents. Segments are made of extents..... As long as you have even IO over the devices (and tablespaces really don't help you terribly much there - raid does, ASM does) you have already gotten the performance thing down.

Thanks For the explanation Tom.

April 18, 2008 - 10:46 am UTC

Reviewer: A reader


INDEX tablespace VS tableSpace

August 16, 2010 - 1:24 am UTC

Reviewer: INDRANIL from INDIA

Hi Tom,
We all know that - "The index contains not only the indexed value, but also the rowids of rows in the table having that value. "

So, My Question is, why size of the INDEX is always less than the Original Tables ?....

Thanks in advance for your help,
Indranil
Tom Kyte

Followup  

August 19, 2010 - 1:00 am UTC

... So, My Question is, why size of the INDEX is always less than the Original
Tables ?....
...

the answer is: it ISN'T, you can have an index that is larger than the table itself.

but in general, the index is smaller since it contains:

only the columns relevant to the index and the rowid

whereas the table contains

ALL OF THE COLUMNS.


So, if you have a table t ( x int, y char(2000) ) and create an index on x and put a million rows in there (no null values for either X or Y) - then you'll have 1,000,000 4000 byte fields in the table along with 1,000,000 numbers.

In the index, you'll have just 1,000,000 numbers and 1,000,000 rowids - a rowid is a lot smaller than 4000 bytes so 1,000,000 of them would consume less space.

Could not get a consistent answer even from Experts

August 26, 2010 - 12:18 pm UTC

Reviewer: Akash from Singapore

Hi Tom,

Sorry for asking the same question once more, but this time from a data warehousing context.

I am an application developer for a 10 Terra Byte data warehouse on 10g. All data manupulations on this data warehouse are done through ETL jobs. These ETL jobs run in parallel and performs all kinds of operations - mainly SELECT, INSERT and UPDATE on the data.

Our DBAs suggest that we must store the data and indexes in different tablespace as that is a convention followed all over the World. When I asked them why this convention is followed, they informed me that this will improve performance.
When I asked them why this would improve the performance, they told me that Oracle would be able to read index and data simultaneously if we separate them into different tablespace under a single LUN.
When I argued that normally Oracle would read the index first to read the data, they replied, "Yes, but since you are running parallel jobs, may be one job will read the index while some other job will access the data (as more than one job might be accessing the same table). So separating them would enhance the performance."
They further informed me that in OLTP systems, separating index and table data might not be beneficial but in OLAP system, we must separate them for performance.

I have limited knowledge on administration side. I don't understand different storage mechanism like RAID, SAN etc. much. But I do understand Oracle architecture from a developer's point of view. Can you please tell me if my DBAs arguments are correct and if yes, then why?

Regards,
Akash
Tom Kyte

Followup  

August 26, 2010 - 1:49 pm UTC

... Our DBAs suggest that we must store the data and indexes in different
tablespace as that is a convention followed all over the World. ..

not the way I do it, I use a single tablespace for many applications. Makes them self contained and transportable.

Ask them how it improves performance.

When we use an index - we read index root followed by index branch followed by index leaf followed by the table. it is SEQUENTIAL, not parallel. It doesn't matter if that 4th random IO takes place here or there. It is the same. The goal - the GOAL is to have IO evenly distributed over all available devices. Putting indexes there and tables here doesn't necessarily do that.

And when they say "yeah but maybe someone is reading the index and someone is reading the table blah blah blah" say "yeah, and maybe they are not, maybe they are full scanning the table - it would be better then to have the table spread out over lots of devices"

Take all of your devices, create a really big stripe set and put everything in it. now you'll have even IO over all devices because the data is just spread everywhere.


You do not separate for performance reasons. there are none.

Got bogged down...

August 30, 2010 - 7:36 am UTC

Reviewer: Akash from Singapore

...And they pointed me here,
http://www.dba-oracle.com/t_segregate_table_index_data_separate_tablespaces.htm
(especially the last point on I/O balancing).
Also does the following parameter settings can have an effect on the answer of my question?

parallel_adaptive_multi_user = TRUE
parallel_automatic_tuning = FALSE
db_file_multiblock_read_count = 64
optimizer_index_caching = 0

Regards
Akash
Tom Kyte

Followup  

September 09, 2010 - 11:23 am UTC


tell me, why would putting an index for a table on disk 1 and the table data for a table on disk 2 be "better"?

I'd rather take disk 1 and disk 2 and stripe them together and have my index and table data evenly spread over both devices so as to get even IO over both.

What is the benefit to having them on separate devices???? Where is the factual information, the benchmark, the hypothesis as to why this might be true (rhetorical questions all, they provide none on that source)


Look at these paragraphs I quote:


Segregating large indexes and tables into separate tablespaces makes large objects easier to manage,

I ask: why? How so? But - even if it did, why not segregating indexes then as well - this seems to be advice to limit the amount of data you put in a tablespace - there is no reason to make the line you draw to segregate objects by "tables" and "indexes", but rather by overall SIZE

and object segregation allows for easier reporting too.

I ask: Huh??? How so???

There are several compelling reasons for segregating your large tables into separate tablespaces.

I comment: saying something over and over doesn't make it true


The most important benefit from segregation is improved manageability of the table. If a large, active table resides in its own separate tablespace, you can control the I/O by moving the tablespace to different disks.

I ask: How the HECK does putting an object in a tablespace by itself do that - ALTER TABLE T MOVE <some other place>, dbms_redefinition - that is how you would move a single table - neither of them require that table be in a single tablespace


The other reason for table segregation is improved space usage. Having too many tables residing in a single tablespace can make reorganization more risky.

I ask (again): huh? how so, what risk is there - fear, uncertainty, and doubt (fud factors) all.




Something to agree with (sort of)

quote:
For example, if you place all of your indexes on a separate disk, you don't have to worry if the disk of backup becomes corrupt because you can rebuild the indexes to restore the device.

it should really say "if you place all of your indexes in a separate tablespace or set of tablespaces...." since your unit of recovery is the tablespace - not really the disk - when it comes to restoring. You would cut lose the tablespace that suffered a media failure and rebuild the indexes that were in there.

This is a valid reason to segregate the objects, but has nothing to do with PERFORMACE.


as for "better reporting", IO statistics are easily available at the segment level, there is no need to do things at the file level. And even so - doing things at the file level (even if you put indexes in one place and tables in another) does not tell you WHICH index/table is 'hot'. v$segment_statistics on the other hand... does.

the whole IO balancing thing harks back to the late 1980's, early 1990's at best. If you are doing single block IO's (as you are with indexes and indexed reads) you what to have even IO over as many devices as possible. Putting indexes on one set of devices and tables on another does not achieve that - striping everything over all devices - that does. We (and the OS) cannot tell the difference between a single block read of an INDEX and a single block read of a TABLE - they look the same - they are interchangeable.

September 13, 2010 - 3:07 pm UTC

Reviewer: Alexander

There you go again, falling into the "prove it" trap. Prove this prove that blah blah blah. They're experts, they are of a higher mental capacity just accept their advice and handover the sack with the $. Next you're going to tell me beer kills brain cells.

Now if you'll excuse me, I have to go to that place....where my beds and thingys are.....

Truly useful advice – thanks!

September 14, 2010 - 7:52 am UTC

Reviewer: Akash from Singapore

Dear Tom,

Thank you so much! Your comments have helped me to convince my DBAs. But most importantly - the reasonings you have put forward has helped me to clear all my doubts and understand the system better.

Aside to Alexander: You see.. it's easy to comment than to prove. But that's how we differentiate a proposition and a reality. I believe in data. You may believe in expert advice. But I guess we can both believe in beer :)

- Akash
http://www.dwbiconcepts.com

@Akash

September 14, 2010 - 1:58 pm UTC

Reviewer: Alexander

I was being completely sarcastic :) It's somewhat of an inside joke to those familiar with this particular expert's modus operandi. One I'm confident Tom will understand.

Academic vs Experience

March 10, 2011 - 3:49 pm UTC

Reviewer: Scott Hesterman from Fort Mill, SC USA

I've read most of this thread on the topic of separating indexes from tables. Lots of ideas about why it matters, used to matter, doesn't anymore, etc. Interesting reading, but where are the "I tried this and it made a measurable difference" examples? Benchmarks are wonderful things.
By the way, I agree with Tom.
I separate tables from indexes for management purposes. Storage and backup attributes differ.

assm tablespace good for index?

June 15, 2012 - 3:32 am UTC

Reviewer: A reader

hi Tom,

Automatic storage segment management assm is good choice for indexes?

Thank You for answer.
Tom Kyte

Followup  

June 15, 2012 - 7:53 am UTC

yes, you should be using it in general for everything.

assm is a good choice for tablespace index ?

June 20, 2012 - 8:13 am UTC

Reviewer: A reader

Hi Tom,

i read this link about assm performance

http://www.dba-oracle.com/oracle10g_tuning/t_assm_performance.htm

in above link says, that :

§ ASSM will influence index clustering: For row ordered tables, ASSM can adversely affect the clustering_factor for indexes. Bitmap freelists are less likely to place adjacent tows on physically adjacent data blocks, and this can lower the clustering_factor and the cost-based optimizer's propensity to favor an index range scan.

so, i understand that we can not use assm for tablespace index?

Thank You for answer, for help.
Tom Kyte

Followup  

June 21, 2012 - 7:36 am UTC

please don't post links to that site. There are so many things wrong in that writeup. Their pro's conflict with their cons


and the point in this bullet point has to do with the table, not the index. The clustering factor of an index is a metric of how clustered/sorted the TABLE is - not the index. So, they are talking about TABLES in ASSM, not indexes. They are talking about the fact that not all sessions will insert into the same last block - the inserts will likely be spread out over a series of blocks (say about 5 of them) - decreasing contention. that means that rows inserted in some order - might not be stored in that same order on disk in the table - they might be a bit spread out IN THE TABLE. And that will potentially affect the clustering factor.


Please, just use ASSM. It is definitely the preferred method in the year 2012.




assm tablespace good for index?

June 20, 2012 - 8:59 am UTC

Reviewer: A reader

can i use assm for indexes tablespace for database version 9.2.0.8 ?
Tom Kyte

Followup  

June 21, 2012 - 7:39 am UTC

yes. and please stop asking basically the same exact question all over the place.