[Dbix-class] DBIx::Class and Oracle Partitions

[Chris Hampson]

Hello Everyone,

I've been using DBIx::Class a lot over the past couple of years but
this problem has me completely stumped, I'm appealing to those wiser
than me for a little advice ;-)

Essentially I've got a custom result source defined (I've simplified
the SQL for this example) which uses Oracles partitioning
functionality to speed up searching a couple of rather large tables.

What I'd like to try to do is specify the partition that Oracle
searches in in the from clause when specifying a table. After alot of
faffing, and then discovering that I can't use bind variables in a
from clause, ideally what I'd like to implement is the equivalent of

...
from dat partition(?) inner join....
...

But I just can't seem to pull this off, any advice would be very
greatly appreciated :-)

Many thanks in advance,

Chris H


Here is the basis of what I have so far...

package zelot::DUDB::summaryquery;

use strict;
use warnings;

use base 'DBIx::Class';
__PACKAGE__->load_components("Core");

__PACKAGE__->table("dummy");
__PACKAGE__->add_columns( "did", { data_type => "Number", is_nullable
=> 1, size => 8 });
__PACKAGE__->add_columns( "name", { data_type => "VARCHAR2",
is_nullable => 1, size => 400 });
__PACKAGE__->add_columns( "vid", { data_type => "Number"});

__PACKAGE__->result_source_instance->name( \<<SQL );

( select distinct(dir.did),
  volumes.filer || ':' || volumes.name AS name,
  dir.vid

  from dat partition(vid0) inner join dir partition(vid0) on dir.did =
dat.did, volumes
  where dir.pid = 0 and dir.vid = dat.vid and volumes.vid = dir.did
  group by dir.did, dir.vid
)

SQL
1;