Re: Re[2]: Deleting partitioned data

In addition to the effect on the optimizer, there is also the issue of the granularity of object locking.

Explicitly naming a partition in a DML statement places a "TM" enqueue (a.k.a. DML lock) on just the partition. So, conflicting operations such as INSERT /*+ APPEND */ against other partitions in the table can run without waiting for the DML to be committed or rolled back.

Not naming the parition explicitly in a DML statement places the "TM" enqueue against the entire partitioned table, not just the partition involved. So, an INSERT /*+ APPEND */ or SQL*Loader direct=true operation running against the table (even inserting rows into another partition entirely) will "hang", waiting for the other operation to complete.

I believe that this is one of the primary reasons for the existence of the explicit paritition naming syntax in DML statements.

on 11/14/03 5:40 AM, Jonathan Gennick at jonathan_at_gennick.com wrote:

> 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: Tim Gorman
  INET: tim_at_sagelogix.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).