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: 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).
