Hi,
I'm trying to find out in Partition wise join (PWJ) is possible with interval partitioning - i can't find an explicit statement that it isn't but i can't get it to work - i've produced a simple test case to lllustrate the issue.
below i have 2 create table scripts - 1 for interval case and 1 for the hash case - i then have a simple query over those 2 objects which should produce a PWJ.
In the hash case it works fine (see 2nd screenshot with one set of slaves), the first screen shot shows the interval case where i end up with 2 sets of slaves and no PWJ.
Any idea if this is possible and i just missed something?
(for the test case choose appropriate schema/tablespaces names for your system)
Oh and version (i nearly forgot...:-) ) - is 11.2.0.4.1 on SLES 11
Cheers,
Rich
-- interval case
CREATE TABLE "SB_DWH_IN"."TEST1"
TABLESPACE "SB_DWH_INTEGRATION"
PARTITION BY RANGE ("OBJECT_ID") INTERVAL (10000)
(PARTITION "LESS_THAN_ZERO" VALUES LESS THAN (0) TABLESPACE "SB_DWH_INTEGRATION" )
as select * from DBA_OBJECTS where object_id is not null;
CREATE TABLE "SB_DWH_IN"."TEST2"
TABLESPACE "SB_DWH_INTEGRATION"
PARTITION BY RANGE ("OBJECT_ID") INTERVAL (10000)
(PARTITION "LESS_THAN_ZERO" VALUES LESS THAN (0) TABLESPACE "SB_DWH_INTEGRATION" )
as select * from DBA_OBJECTS where object_id is not null;
--hash case
CREATE TABLE "SB_DWH_IN"."TEST1"
TABLESPACE "SB_DWH_INTEGRATION"
PARTITION BY HASH ("OBJECT_ID") partitions 8
store in ( "SB_DWH_INTEGRATION" )
as select * from DBA_OBJECTS where object_id is not null;
CREATE TABLE "SB_DWH_IN"."TEST2"
TABLESPACE "SB_DWH_INTEGRATION"
PARTITION BY HASH ("OBJECT_ID") partitions 8
store in ( "SB_DWH_INTEGRATION" )
as select * from DBA_OBJECTS where object_id is not null;
--query to run
select /*+ PARALLEL(TEST2,8) PARALLEL(TEST1,8) */ *
from "SB_DWH_IN"."TEST2","SB_DWH_IN"."TEST1"
where TEST1.object_id=test2.object_id
