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@xxxxxxxxxxxx> 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