Re: Deleting partitioned data

Arup Nanda Thu, 13 Nov 2003 23:10:50 -0800

Jonathan,

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 it has been initially defined on, the partition elimination is done at runtime, not at the parse phase. Consider the following example:

1 create table sales
2 (sales_dt date,
3 product varchar2(20)
4 )
5 partition by range (sales_dt)
6 (
7 partition p1 values less than (to_date('14-nov-2003','dd-mon-yyyy')),
8 partition p2 values less than (to_date('15-nov-2003','dd-mon-yyyy')),
9 partition p3 values less than (to_date('16-nov-2003','dd-mon-yyyy'))
10* )
SQL> /

Table created.

SQL> insert into sales values (sysdate - 1, 'Yesterday');

1 row created.

SQL> insert into sales values (sysdate, 'Today');

1 row created.

SQL> insert into sales values (sysdate+1, 'Tomorrow');

1 row created.

SQL> commit;

Commit complete.

SQL> analyze table sales compute statistics;

Table analyzed.

Now let's examine the various access methods.

SQL> explain plan for select * from sales where sales_dt = to_date('14-nov-2003'
,'dd-mon-yyyy');

Explained.

SQL> select * from table(dbms_xplan.display);

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

| 0 | SELECT STATEMENT | | 1 | 21 | 2 | | |

|* 1 | TABLE ACCESS FULL | SALES | 1 | 21 | 2 | 2 | 2 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("SALES"."SALES_DT"=TO_DATE('2003-11-14 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

Note: cpu costing is off

15 rows selected.

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 predicate information is shown below in the filter section.

SQL> explain plan for select * from sales partition (p2);

Explained.

SQL> select * from table(dbms_xplan.display);

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

| 0 | SELECT STATEMENT | | 1 | 12 | 2 | | |

| 1 | TABLE ACCESS FULL | SALES | 1 | 12 | 2 | 2 | 2 |

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

Note: cpu costing is off

9 rows selected.

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 the third selection option.

SQL> explain plan for select * from sales where sales_dt = '14-nov-03';

Explained.

SQL> select * from table(dbms_xplan.display);

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

| 0 | SELECT STATEMENT | | 1 | 21 | 2 | | |

| 1 | PARTITION RANGE SINGLE| | | | | KEY | KEY |

|* 2 | TABLE ACCESS FULL | SALES | 1 | 21 | 2 | KEY | KEY |

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("SALES"."SALES_DT"='14-nov-03')

Note: cpu costing is off

15 rows selected.

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 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. "(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 can't decide at parse time which partition to use; it uses a KEY iterator.

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.

HTH.

Arup Nanda

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

From: "Jonathan Gennick" <[EMAIL PROTECTED]>

To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>

Sent: Thursday, November 13, 2003 7:34 PM

Subject: Deleting partitioned data

> 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:[EMAIL PROTECTED]
>
> 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
>

Re: Deleting partitioned data

Reply via email to