Re:partitioning question

From: <dgoulet_at_vicr.com>
Date: Tue, 9 May 2000 15:35:35 -0400
Message-Id: <10492.105273@fatcity.com>

Assuming you have the partitioning option loaded in the first place there is two ways to get the optimizer to use only the particular partition.

1. the query where clause has the partition key as a part thereof
2. you add the partition qualifier onto the from clause

As in having the emp table partitioned by deptno:

    create table emp( deptno number, whatever_else)

      partition by range(deptno)
      (partition emp_rng1 values less than 5,
       partition emp_rng2 values less than 10);

Now to only query partition #1 you need:

    select * from emp where deptno <= 4; or

    select * from emp partition(emp_rng1);

    Works like a charm around here.

Dick Goulet
Senior Oracle DBA
Vicor Corporation

____________________Reply Separator____________________

Subject: partitioning question
Author: Thomas.Nigel_at_viaginterkom.de
Date: 5/9/00 9:14 AM

Hi all,

Can anybody give a definitive answers to these questions:

Will 8.1.5 running under the RULE based optimiser recognise that a table or index has been partitioned, and thus access only the some partitions to get the required data for the query?

IF NOT Will I have to use a hint combined with statistics to make use of this facility?

Regards

Nigel T

-- 
Author: 
  INET: Thomas.Nigel_at_viaginterkom.de

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
also send the HELP command for other information (like subscribing).

Received on Tue May 09 2000 - 14:35:35 CDT