| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re[2]: Deleting partitioned data
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>> 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).Received on Fri Nov 14 2003 - 06:40:50 CST
 |
 |