Thread: Directing Partitioned Table Searches

In a partitioned table, is it possible to specify the partition for a
query to search using a variable instead of a constant?

EXAMPLE: Join another table to the partitioned one
Table: clu     (partitioned by state)
ogc_fid bigint
cluid char(16)
state bpchar(2)
constraint: state='mi' (or 'co', 'ks', etc. for each partition)

Table: farms
link bigint
zone bpchar(2)
farmid char(7)
...
This selection will result in partitions being searched
SELECT cluid, farmid
FROM clu JOIN farms ON ogc_fid=link

This selection will result in only the 'mi' (Michigan) partition being
searched
SELECT cluid, farmid
FROM clu JOIN farms ON ogc_fid=link
WHERE state='mi'

And this selection will result in ALL partitions being searched. But why?
SELECT cluid, farmid
FROM clu JOIN farms ON ogc_fid=link
WHERE state=zone

I'd like to be able to run some queries w/o the overhead of searching
partitions unnecessarily. Can it be done?

Regards,

--

*Bill Thoen*
GISnet - www.gisnet.com <http://www.gisnet.com/>
1401 Walnut St., Suite C
Boulder, CO 80302
303-786-9961 tel
303-443-4856 fax
bthoen@gisnet.com

On Fri, Feb 4, 2011 at 7:35 PM, Bill Thoen <bthoen@gisnet.com> wrote:
> And this selection will result in ALL partitions being searched. But why?
> SELECT cluid, farmid
> FROM clu JOIN farms ON ogc_fid=link
> WHERE state=zone

The constraint exclusion code does not execute your constraints to
decide whether to look at your partition; it examines the query and
the constraint and does a "proof" to try to exclude the partition. If
it cannot do that proof, it will scan that table.

> I'd like to be able to run some queries w/o the overhead of searching
> partitions unnecessarily. Can it be done?

Your best bet is to know which partition you need and write your query
that way dynamically, rather than trying to use a generic query and
have the DB do the constraint exclusion.  In your above case, if you
know that 'zone' will limit you to just the MI table, then specify the
MI table instead of the base clu table.

Vick Khera wrote:
> Your best bet is to know which partition you need and write your query
> that way dynamically, rather than trying to use a generic query and
> have the DB do the constraint exclusion.  In your above case, if you
> know that 'zone' will limit you to just the MI table, then specify the
> MI table instead of the base clu table.
>
Thanks. That sounds simple enough. Since I want to automate this, I
guess the next step is to learn how to create and  execute a "dynamic"
query.  I think I know how to do that.


--

*Bill Thoen*
GISnet - www.gisnet.com <http://www.gisnet.com/>
1401 Walnut St., Suite C
Boulder, CO 80302
303-786-9961 tel
303-443-4856 fax
bthoen@gisnet.com

On Mon, Feb 7, 2011 at 1:17 PM, Bill Thoen <bthoen@gisnet.com> wrote:
> Thanks. That sounds simple enough. Since I want to automate this, I guess
> the next step is to learn how to create and  execute a "dynamic" query.  I
> think I know how to do that.
>

In perl, it looks something like this:

$part = compute_partition($value);
$sth = $dbh->prepare("select * from table_$part where foo=?");
$sth->execute($value);

you just interpolate the parts you need in the query string.