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 [EMAIL PROTECTED] 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:[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. > > > Friday, November 14, 2003, 1:04:25 AM, Arup Nanda ([EMAIL PROTECTED]) 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" <[EMAIL PROTECTED]> > 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:[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). >>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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).
