Re: How to use Partition in Query

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:01:41 CDT