Re: ORACLE Data Partitioning?

Alan J. Heckman (aheckman_at_us.oracle.com) wrote:
: Joe Minnich (jminnich_at_bugaboo.ssnet.com) wrote:
: > In article <3lc1a9$5q1_at_usenet.rpi.edu>, hoffma_at_rpi.edu (Adam Hoffman) writes:
: > |> Does anyone out there know if any version of ORACLE can physically
: > |> partition a single table into seperate disks based on a data rule?
: > |>
: > |> Example:
: > |>
: > |> A very large table which contains 4 fiscal years worth of transactions
: > |> would be partitioned onto 4 seperate disks based on fiscal year
: > |> with the current fiscal year on the fastest one. When a query is
: > |> made against this table using the fiscal year, only the disk where
: > |> the applicable fiscal year resides is accessed.
: > |>
: > |> I know that DB2 can do this but I would like to know if ORACLE can?
: > |> Any comments would be appreciated. Thanks.
 

: > i do not know of a way in which oracle will currently do this
: > type of partitioning for you. you can however strip your tablespace
: > across n disks and use the parallel loader to load the individual
: > files. indexed access would then achieve what you are looking for.
: > i think that oracle is going to continue to improve in this area.
: > it is only going to get better. we have done quite alot of work in
: > this area - 714 has some "features" that will be enhanced in 716 and 72x.
 

: > --
 

: > Thanks,
: > -------------------------------------------------------
: > Joe Minnich jminnich_at_bugaboo.ssnet.com
: > System Administrator 302-791-8662
: > Electronic Payment Services
 

: You can "partition" by partitioning the data into 4 files readable by
: SQLLOAD, creating a tablespace with 4 data files, one on each disk if
: you insist (Although I would still stripe them !!!), and directing
: SQLLOAD to load the data from each source file to a specific data file
: within the tablespace (see the file parameter).

Whilst this is true, it does not meet the stated objective of restricting query access to the relevant disk.

Table scans (parallel or otherwise) would scan all four disks.

An index beginning with fiscal year would restrict the scan, but you would need either a fifth spindle for the index, the whole index on one of the four spindles, or distributed across the four spindles in a way which did not match the data partitioning.

In any case, use of the index would inhibit parallel query.

--
graeme
--
Disclaimer:	The author's opinions are his own, and not necessarily
		those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL   Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.