Re: Partition pruning not happening

From: Greg Rahn <greg_at_structureddata.org>
Date: Sun, 10 Oct 2010 08:08:30 -0700
Message-ID: <AANLkTik5z5rkxTnudrj1VLezDUz-b_EqAt-wxvghxq50_at_mail.gmail.com>

It is a really good practice to explicitly cast strings to dates like such: invoice_date BETWEEN to_date('01-Sep-10','dd-mon-yy') and to_date('30-Sep-10','dd-mon-yy')

This assumes that INVOICE_DATE is a defined as DATE also.

BTW - KEY does not mean partition pruning does not happen - it means that partitions can not be determined at query compilation time, it will be determined at execution time.

On Sun, Oct 10, 2010 at 7:25 AM, Vladimir Barac <vbarac_at_alghanim.com> wrote:

> Hello, listers
>
>
>
> We have table that is partitioned by date - column name is invoice_date. I
> would expect that query below would use partition pruning - pstart/pstop
> would have partition number instead of "KEY" entry. However, pruning doesn't
> happen. What could be the reason? This seems like ideal query to demonstrate
> pruning. Or am I missing something obvious?
>
>
>
> Thanks in advance,
>
> Vladimir Barac
>
>
>
> system\DMART::ai-dmdb-pr> set autotrace traceonly
>
> system\DMART::ai-dmdb-pr> select * from dm_elec_manager.ele_invoice where
> invoice_date BETWEEN '01-Sep-10' and '30-Sep-10';
>
>
>
> 314624 rows selected.
>
>
>
>
>
> Execution Plan
>
> ----------------------------------------------------------
>
>
>
>
> -----------------------------------------------------------------------------------------------------------------------
>
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
>
>
> -----------------------------------------------------------------------------------------------------------------------
>
> | 0 | SELECT STATEMENT | | 311K| 73M| 1172
> (10)| | | | | |
>
> | 1 | PX COORDINATOR | | | |
> | | | | | |
>
> | 2 | PX SEND QC (RANDOM) | :TQ10000 | 311K| 73M| 1172
> (10)| | | Q1,00 | P->S | QC (RAND) |
>
> | 3 | FILTER | | | |
> | | | Q1,00 | PCWC | |
>
> | 4 | PX BLOCK ITERATOR | | 311K| 73M| 1172
> (10)| KEY | KEY | Q1,00 | PCWC | |
>
> | 5 | TABLE ACCESS FULL| ELE_INVOICE | 311K| 73M| 1172
> (10)| KEY | KEY | Q1,00 | PCWP | |
>
>
> -----------------------------------------------------------------------------------------------------------------------
>

-- 
Regards,
Greg Rahn
http://structureddata.org

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Oct 10 2010 - 10:08:30 CDT