Jonathan,
You are welcome. Another time for the syntax table (partitoion) comes handy
is while dropping, merging or doing some other partition maintenance work.
This will quickly check is the partition is empty or not, othewise you have
to ge the hig values of the partition and one prior to it and then do a
query based on these two; possible but rather difficult.
HTH.
Arup
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Friday, November 14, 2003 7:40 AM
> Arup, thanks for taking the time to put that together,
> that's a great explanation.
>
> Best regards,
>
> Jonathan Gennick --- Brighten the corner where you are
> http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
>
> Join the Oracle-article list and receive one
> article on Oracle technologies per month by
> email. To join, visit
http://four.pairlist.net/mailman/listinfo/oracle-article,
> or send email to Oracle-article-request_at_gennick.com and
> include the word "subscribe" in either the subject or body.
>
>
> Friday, November 14, 2003, 1:04:25 AM, Arup Nanda (orarup_at_hotmail.com)
wrote:
> AN> Jonathan,
>
> AN> This is explained by a quark in the way partitioning works, especially
in the case of range partitioned tables on date columns. If the partitioning
key is not given clearly in the dsame format as
> AN> it has been initially defined on, the partition elimination is done at
runtime, not at the parse phase. Consider the following example:
>
> AN> 1 create table sales
> AN> 2 (sales_dt date,
> AN> 3 product varchar2(20)
> AN> 4 )
> AN> 5 partition by range (sales_dt)
> AN> 6 (
> AN> 7 partition p1 values less than
(to_date('14-nov-2003','dd-mon-yyyy')),
> AN> 8 partition p2 values less than
(to_date('15-nov-2003','dd-mon-yyyy')),
> AN> 9 partition p3 values less than
(to_date('16-nov-2003','dd-mon-yyyy'))
> AN> 10* )
> SQL>> /
>
> AN> Table created.
>
> SQL>> insert into sales values (sysdate - 1, 'Yesterday');
>
> AN> 1 row created.
>
> SQL>> insert into sales values (sysdate, 'Today');
>
> AN> 1 row created.
>
> SQL>> insert into sales values (sysdate+1, 'Tomorrow');
>
> AN> 1 row created.
>
> SQL>> commit;
>
> AN> Commit complete.
>
> SQL>> analyze table sales compute statistics;
>
> AN> Table analyzed.
>
> AN> Now let's examine the various access methods.
>
> SQL>> explain plan for select * from sales where sales_dt =
to_date('14-nov-2003'
> AN> ,'dd-mon-yyyy');
>
> AN> Explained.
>
> SQL>> select * from table(dbms_xplan.display);
>
> AN> | Id | Operation | Name | Rows | Bytes | Cost |
Pstart| Pstop |
>
AN> ------------------------------------------------------------------------
------------
> AN> | 0 | SELECT STATEMENT | | 1 | 21 | 2 |
| |
> AN> |* 1 | TABLE ACCESS FULL | SALES | 1 | 21 | 2 |
2 | 2 |
>
AN> ------------------------------------------------------------------------
>
> AN> Predicate Information (identified by operation id):
> AN> ---------------------------------------------------
>
> AN> 1 - filter("SALES"."SALES_DT"=TO_DATE('2003-11-14 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
>
> AN> Note: cpu costing is off
>
> AN> 15 rows selected.
>
> AN> As expected, the optimizer decided to look into partiotion p2 only
(partition start=2 and partition stop=2). The choice was made by the
optimizer at step 1, indicated by an asterix and the
> AN> predicate information is shown below in the filter section.
>
> SQL>> explain plan for select * from sales partition (p2);
>
> AN> Explained.
>
> SQL>> select * from table(dbms_xplan.display);
>
> AN> | Id | Operation | Name | Rows | Bytes | Cost |
Pstart| Pstop |
>
AN> ------------------------------------------------------------------------
--------------
> AN> | 0 | SELECT STATEMENT | | 1 | 12 | 2 |
| |
> AN> | 1 | TABLE ACCESS FULL | SALES | 1 | 12 | 2 |
2 | 2 |
>
AN> ------------------------------------------------------------------------
>
> AN> Note: cpu costing is off
>
> AN> 9 rows selected.
>
> AN> It still selected from partition p2 only, as expected. The only
difference is there is no predicate section, as none is required; we
selected from partition directly. So far, so good. Let's see
> AN> the third selection option.
>
> SQL>> explain plan for select * from sales where sales_dt = '14-nov-03';
>
> AN> Explained.
>
> SQL>> select * from table(dbms_xplan.display);
>
> AN> | Id | Operation | Name | Rows | Bytes | Cost |
Pstart| Pstop |
>
AN> ------------------------------------------------------------------------
--------------
> AN> | 0 | SELECT STATEMENT | | 1 | 21 | 2 |
| |
> AN> | 1 | PARTITION RANGE SINGLE| | | | |
KEY | KEY |
> AN> |* 2 | TABLE ACCESS FULL | SALES | 1 | 21 | 2 |
KEY | KEY |
>
AN> ------------------------------------------------------------------------
>
> AN> Predicate Information (identified by operation id):
> AN> ---------------------------------------------------
>
> AN> 2 - filter("SALES"."SALES_DT"='14-nov-03')
>
> AN> Note: cpu costing is off
>
> AN> 15 rows selected.
>
> AN> Well, what happened here? The optimizer couldn't decide the partition
at the parse time, hence it shows KEY as the values of partition start and
stop keys. This occurred since we specified "where
> AN> sales_date = '14-nov-03'" as opposed to "where sales_dt =
to_date('14-nov-2003','dd-mon-yyyy')". The former is not in the same format
as the partition definition, i.e.
> AN> "(to_date('14-nov-2003','dd-mon-yyyy'))", the latter is; hence the
optimizer made a smart choice. When the patterns mentioned in the query and
the partition definition don't match, the optimizer
> AN> can't decide at parse time which partition to use; it uses a KEY
iterator.
>
> AN> I am not sure if the facility provided by Oracle to query a partition
directly is due to the above situation, but it helps there, nevertheless.
>
> AN> HTH.
>
> AN> Arup Nanda
>
>
>
> AN> ----- Original Message -----
> AN> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> AN> Sent: Thursday, November 13, 2003 7:34 PM
>
>
> >> I'd like to ask a question. Consider the two statements
> >> below:
> >>
> >> DELETE
> >> FROM county PARTITION (michigan)
> >> WHERE county_name = 'Alger';
> >>
> >> DELETE
> >> FROM county
> >> WHERE county_name = 'Alger'
> >> AND state = 'MI';
> >>
> >> Is there ever a case where the first option is preferable?
> >> Is there ever a case where Oracle wouldn't be able to
> >> isolate the partition of interest simply by evaluating the
> >> conditions in the WHERE clause? There must be, else why
> >> would Oracle provide the syntax shown in the first
> >> statement? However, I'm having difficulty coming up with a
> >> good example of when that syntax makes sense. Can someone
> >> help me out here?
> >>
> >> Best regards,
> >>
> >> Jonathan Gennick --- Brighten the corner where you are
> >> http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
> >>
> >> Join the Oracle-article list and receive one
> >> article on Oracle technologies per month by
> >> email. To join, visit
http://four.pairlist.net/mailman/listinfo/oracle-article,
> >> or send email to Oracle-article-request_at_gennick.com and
> >> include the word "subscribe" in either the subject or body.
> >>
> >> --
> >> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >> --
> >> Author: Jonathan Gennick
> >> INET: jonathan_at_gennick.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: Jonathan Gennick
> INET: jonathan_at_gennick.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: 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 Fri Nov 14 2003 - 14:59:25 CST
