9.1 documentation suggests seperate because...
A reader, November 28, 2002 - 5:12 pm UTC
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?
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
Dick Stins, September 04, 2003 - 5:59 pm UTC
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.
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
Dick Stins, September 06, 2003 - 5:25 pm UTC
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
Greg Clough, June 09, 2004 - 10:31 am UTC
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
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
Greg Clough, June 09, 2004 - 10:31 am UTC
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
Mike Ault, April 12, 2005 - 3:25 pm UTC
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.
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
Dick Stins, June 19, 2005 - 5:45 am UTC
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.
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
Dick Stins, June 19, 2005 - 8:40 am UTC
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.
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
A reader, June 19, 2005 - 9:10 am UTC
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!!!!!!
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
Dick Stins www.zion-it.com, June 19, 2005 - 2:21 pm UTC
Tom,
Thanks for your opinion.
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
Edgar, June 20, 2005 - 4:28 am UTC
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,
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
Paul Guerin, June 20, 2005 - 7:07 am UTC
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....
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?
Aru, June 07, 2006 - 12:08 am UTC
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.
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
Shrikant, April 17, 2008 - 4:30 pm UTC
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?
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.
Shrikant, April 17, 2008 - 4:50 pm UTC
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.
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.
A reader, April 18, 2008 - 10:46 am UTC
INDEX tablespace VS tableSpace
INDRANIL, August 16, 2010 - 1:24 am UTC
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
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
Akash, August 26, 2010 - 12:18 pm UTC
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
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...
Akash, August 30, 2010 - 7:36 am UTC
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.
Alexander, September 13, 2010 - 3:07 pm UTC
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!
Akash, September 14, 2010 - 7:52 am UTC
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
Alexander, September 14, 2010 - 1:58 pm UTC
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
Scott Hesterman, March 10, 2011 - 3:49 pm UTC
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?
A reader, June 15, 2012 - 3:32 am UTC
hi Tom,
Automatic storage segment management assm is good choice for indexes?
Thank You for answer.
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 ?
A reader, June 20, 2012 - 8:13 am UTC
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.
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?
A reader, June 20, 2012 - 8:59 am UTC
can i use assm for indexes tablespace for database version 9.2.0.8 ?
June 21, 2012 - 7:39 am UTC
yes. and please stop asking basically the same exact question all over the place.