Re: How to use Partition in Query

Hi Royatman,
Thanks a lot for your reply.I am using a range partition and the partition column is a date column.
My main problem is I need to fetch 45 days of data.This will cover 2 partitions.I am using the condition as
Where Date_Column >=[sydate-45 just a rough idea] for today it should give me February and March data.
I checked the Explain plan and optimizer is doing full table scan.It's not pruning the partition.

How can I solve this kind of situation.Any idea? Once again thanks alot for your suggestion.

roytmaned_at_yahoo.com wrote:
> 1. As far as I know you can fetch from one partition
>
> SELECT COUNT(*) FROM table PARTITION (partitionname);
>
> 2. If you need from 2 partitions you can use union
> SELECT COUNT(*) FROM table PARTITION (partitionname1);
> union all
> SELECT COUNT(*) FROM table PARTITION (partitionname2);
>
> 3. Depending on your method of partitioning(range, hash, etc), if you
> structure your query right, the ptimizer will prune the partitions for
> you, ie say if your table partitioned by date and you select where
> dates between first and second partition data range it will prune your
> pratitions and will only select from 2 that it needs to query no
> external interraction needed........
>
> hope it helps,
> Eugene Roytman
>
> Lucky wrote:
> > Hi All,
> >
> > I have a table which has more than 10 partitions.Now I want to query
> > the table but don't want a full table scan.I want to fetch results from
> > 2 partiitions (suppose partition A and B).How can I do that.Can I use
> > multiple partition name in my query.
> >
> > All the reply will be highly appreciated.
> >
> > Thanks in Advance.
Received on Mon Apr 10 2006 - 16:15:30 CDT