Oracle-L: Re: Querying a subpartition????

From: Arup Nanda <orarup_at_hotmail.com>
Date: Sun, 04 May 2003 20:06:37 -0800
Message-ID: <F001.0058F193.20030504200637@fatcity.com>

That is not correct. You can certainly query a subpartition. Here is how:

SELECT * FROM DUMMY SUBPARTITION (SP1) where SP1 is a subpartition in the table. The documentation is not quite clear on this syntax. Most operations to select data from partitions can also be extended to subpartitions as well. For instance, to export a subpartition, you would use the TABLE=DUMMY:SP1 in the paramemter file or in the command line. The similar syntax is available for SQL*Loader too.

Subpartitioning can be only hash or List (in 9i) and can be done only when partitions are range based. This is a functionality; perhaps in the future releases we can see more types.

Hope this helps.

Arup Nanda
www.proligence.com

>From: "Djordje Jankovic" <djordjej_at_rogers.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: Querying a subpartition????
>Date: Sun, 04 May 2003 12:01:36 -0800
>
>Yes, you cannot query a subpartition. The reason for that is that
>subpartitions from the data contents point of view are not significant:
>rows
>are almost randomly (i.e. according to the hash function) spread across
>hash
>partitions - i.e. sub-partitions. As opposed to that, range partitions
>split the data according to a data dependent rule: e.g. all the rows where
>the date column is in a certain range go into one partition.
>
>It does not make a lot of sense to have both partitions and sub-partitions
>range partitioned, as the two range partitioning criterions can be combined
>in one. Hash partitions were introduced to "randomly" split data accross
>multiple partitions, and so usually accross multiple data files / disks.
>
>Djordje
>
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>Sent: Sunday, May 04, 2003 2:11 PM
>
>
> > --=_MAILER_ATTACH_BOUNDARY1_2003540224728184803526
> > Content-Type: text/plain; charset=us-ascii
> >
> > Hello friends,
> >
> >
> > I am a newbie in Oracle and just trying my hands on
>it.
>There is this very bugging problem that is driving me nuts. I have created
>a table with subpartition, say three partition and two subpartitions in
>each
>partition. I can query the partition like
> >
> >
> > sql> select * from dummy partition (p1);
> >
> >
> > but I don't know how to query the subpartition.
> >
> >
> > please help.
> >
> >
> > And yes one more thing.
> >
> >
> > It is not possible to have both partitions and subpartitions to be
>partitioned by range. why????
> >
> >
> >
> >
> >
> > badly in need of help. and thanks in advance.
> > Get Your Private, Free E-mail from Indiatimes at
>http://email.indiatimes.com
> > Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com
> > Bid for Air Tickets @ Re.1 on Air Sahara Flights. Just log on to
>http://airsahara.indiatimes.com and Bid Now !
> >
> > --=_MAILER_ATTACH_BOUNDARY1_2003540224728184803526
> > Content-Type: text/html; charset=us-ascii
> >
> > Hello friends,
> >
>            &
>nbsp;     I am a newbie in Oracle and just trying my
>hands on it. There is this very bugging problem that is driving me
>nuts.  I have created a table with subpartition, say three partition
>and two subpartitions in each partition. I can query the partition like
> > sql> select * from dummy partition (p1);
> > but I don't know how to query the subpartition.
> > please help.
> > And yes one more thing.  
> > It is not possible to have both partitions and subpartitions to be
>partitioned by range. why????
> >  
> > badly in need of help. and thanks in advance.
> > <hr>Get Your Private, Free E-mail from
>Indiatimes at <a href="http://email.indiatimes.com"><font
>face="Arial" size="2">http://email.indiatimes.com</a> Buy The Best
>In
>BOOKS at <A
>href="http://www.bestsellers.indiatimes.com">http://www.bestsellers.indiatim
>es.com</A> Bid for Air Tickets @ Re.1 on Air Sahara Flights. Just
>log on to <a
>href="http://airsahara.indiatimes.com">http://airsahara.indiatimes.com</a>
>and Bid Now !
> >
> > --=_MAILER_ATTACH_BOUNDARY1_2003540224728184803526--
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: amrozkamal
> > INET: amrozkamal_at_indiatimes.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > 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).
> >
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Djordje Jankovic
> INET: djordjej_at_rogers.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>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).
>

The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: orarup_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Sun May 04 2003 - 23:06:37 CDT