RE: Documentation contradiction?

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Thu, 10 Aug 2000 03:42:24 +1000
Message-Id: <10584.114197@fatcity.com>

Hi Sean,

The "Planning Tablespaces" tip on my web site takes a more balanced approach.

"In general, it is safest to separate related segments into separate tablespaces. However, not all query execution plans that access two related segments cause repeated alternating access between them. For example, two related tables may reside on the same disks if they are only ever joined using a sort-merge join or hash join, because these query execution plans cause the tables to be accessed in series. The same applies to index merges. However, a nested loops join normally involves repeated access to first the outer table, then the index on the inner table, and then the inner table itself. If any 2, or all 3, of these segments reside on the same disks, head thrashing will occur and will result in poor I/O service times. Similarly, ... Because of these factors, it is safest to always separate related segments into distinct tablespaces, unless you are confident that the optimizer will always choose an execution plan that does not involve repeated alternating access between the segments."

Regards,
Steve Adams

http://www.ixora.com.au/
http://www.oreilly.com/catalog/orinternals/
http://www.christianity.net.au/

-----Original Message-----

From: O'Neill, Sean [mailto:Sean.ONeill_at_organon.ie] Sent: Thursday, 10 August 2000 1:06
To: Multiple recipients of list ORACLE-L Subject: Documentation contradiction?

Hi Folks,

These excerpts from Oracle 8i documentation "appear" to be in contradiction
to me. Seperate data and indexes on separate drives or not!. Am I correct
or confusing apples and oranges so-to-speak.

Separating Tables and Indexes

It is not necessary to separate a frequently used table from its index. During the course of a transaction, the index is read first, and then the
table is read. Because these I/Os occur sequentially, the table and index
can be stored on the same disk without contention.

from
Oracle8i Tuning
Release 8.1.5
A67775-01
20
Tuning I/O

Place Datafiles Appropriately

Tablespace location is determined by the physical location of the datafiles
that constitute that tablespace. Use the hardware resources of your computer
appropriately.

For example, if several disk drives are available to store the database, it
might be helpful to store table data in a tablespace on one disk drive, and
index data in a tablespace on another disk drive. This way, when users query
table information, both disk drives can work simultaneously, retrieving table and index data at the same time.

from
Administrator's Guide
Release 8.1.5
A67772-01
10
Managing Datafiles

Sean :)

######   ######  ######  ######
#    #   #    #  #    #  #    #  Rookie
####     #    #  ######  ######  Data
#   #    #    #  #    #  #    #  Base
#    #  #######  ######  #    #  Adminstrator


-------------------------------- ------------

Organon (Ireland) Ltd.
E-mail: sean.oneill_at_organon.ie [subscribed: Digest Mode]

The only man who never makes a mistake is the man who never does anything.

Theodore Roosevelt

--
Author: O'Neill, Sean
  INET: Sean.ONeill_at_organon.ie

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Wed Aug 09 2000 - 12:42:24 CDT