RE: Why "Separating Data and Indexes improves performance" is a myth?

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Sat, 24 Apr 2004 22:02:35 +0100
Message-ID: <CFECD6EA683C524BB5FD22C0F1F34ACB01936C87@bristol43.audit-commission.gov.uk>

hey someone reads my site :).

Ok better type this quite fast in case Laura discovers me doing work email on a Sat night :(

Wolfgang nails the myth well when he states that the index is accessed at the same time as the table. Nuno nails the more mature approach quite well when he states find out if you have io contention, and if so fix it.

A couple of other thoughts

That presentation always starts with don't believe everything you hear, test it. Naturally that applies in spades to the presenter in question.
Why don't people create 6 tablespaces by default. one for normal tables one for indexes, one for master tables and one for child tables (and another 2 for the associated indexes) I mean master and child are always accessed together right?
I've never personally been convinced by the indexes need less protection argument - it is true that they do not contain data which cannot be recreated by a rebuild - but presumably they are there for correct operation of the system. A 10m row table always accessed by primary key would become somewhat of a resource hog if the key were lost. Recovery by rebuilding might be faster (who knows without testing) but you'd still want to recover sharpish.
Time for beer and gloating as liverpool dump Man Utd out of the title race..

NB: Feedback on the website via the link is always welcomed.

cheers

Niall

	-----Original Message----- 
	From: oracle-l-bounce_at_freelists.org on behalf of Juan Cachito Reyes Pacheco 
	Sent: Fri 23/04/2004 17:13 
	To: oracle-l_at_freelists.org 
	Cc: 
	Subject: Why "Separating Data and Indexes improves performance" is a myth?
	
	

	Hi
	Does any one kwnows please,
	Why "Separating Data and Indexes improves performance" is a myth?
	
	If they are both accesed at the same time, it will improve performance or
	not,
	I don't understand exactly what is this myth about?
	
	http://www.niall.litchfield.dial.pipex.com/OracleMyths.zip
	
	Thanks
	
	
	Juan Carlos Reyes Pacheco
	OCP
	Database 9.2 Standard Edition
	
	
	----------------------------------------------------------------
	Please see the official ORACLE-L FAQ: http://www.orafaq.com
	----------------------------------------------------------------
	To unsubscribe send email to:  oracle-l-request_at_freelists.org
	put 'unsubscribe' in the subject line.
	--
	Archives are at http://www.freelists.org/archives/oracle-l/
	FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
	-----------------------------------------------------------------
	



**********************************************************************

This email contains information intended for the addressee only. It may be confidential and may be the subject of legal and/or
professional privilege. Any dissemination, distribution, copyright or use of this
communication without prior permission of the sender is strictly prohibited.

Binary/unsupported file stripped by Ecartis --
Type: application/ms-tnef
File: winmail.dat

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Sat Apr 24 2004 - 15:59:53 CDT