/** * Tests two access nodes, each with access patterns, but one already * satisfied by user criteria - the other should be made dependent * (same query written slightly different). */ @Test public void testNodesBothHaveAccessPatterns1a() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select pm4.g1.e1 from pm4.g2, pm4.g1 where pm4.g2.e1 = pm4.g1.e1 and pm4.g2.e5 = 'abc'", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm4.g1 AS g_0 WHERE g_0.e1 IN (<dependent values>)", "SELECT g_0.e1 FROM pm4.g2 AS g_0 WHERE g_0.e5 = 'abc'"}, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ TestDependentJoins.checkDependentGroups(plan, new String[] {"pm4.g1"}); //$NON-NLS-1$ }
/** * Test that access node with unsatisfied access pattern is made dependent * (Same query written slightly different way) */ @Test public void testMakeDependentAccessPattern1a() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select pm1.g1.e1 from pm4.g1, pm1.g1 where pm4.g1.e1 = pm1.g1.e1", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm4.g1 AS g_0 WHERE g_0.e1 IN (<dependent values>)", "SELECT g_0.e1 FROM pm1.g1 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ checkDependentGroups(plan, new String[] {"pm4.g1"}); //$NON-NLS-1$ }
/** * Test that second access pattern of access node is chosen to make * dependent with */ @Test public void testMakeDependentAccessPattern3() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select pm1.g1.e1 from pm1.g1, pm4.g2 where pm1.g1.e1 = pm4.g2.e5", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT g_0.e5 FROM pm4.g2 AS g_0 WHERE g_0.e5 IN (<dependent values>)", "SELECT g_0.e1 FROM pm1.g1 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ checkDependentGroups(plan, new String[] {"pm4.g2"}); //$NON-NLS-1$ }
@Test public void testMakeDependentOverUnionProjectingLiterals() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select pm1.g2.e1 from pm1.g2 inner join /*+ makedep */ " + "(select 1 as x, 'a' as y, e1 from pm1.g1 union all select 2, 'b', e1 from pm2.g1) v on y = pm1.g2.e1 and x = pm1.g2.e2", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT 2 FROM pm2.g1 AS g_0", "SELECT g_0.e1, g_0.e2 FROM pm1.g2 AS g_0", "SELECT 1 FROM pm1.g1 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ checkDependentGroups(plan, new String[] {}); //$NON-NLS-1$ }
/** * Tests two access nodes, each with access patterns, but one already * satisfied by user criteria - the other should be made dependent */ @Test public void testNodesBothHaveAccessPatterns1() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select pm4.g1.e1 from pm4.g1, pm4.g2 where pm4.g2.e5 = 'abc' and pm4.g1.e1 = pm4.g2.e1", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm4.g1 AS g_0 WHERE g_0.e1 IN (<dependent values>)", "SELECT g_0.e1 FROM pm4.g2 AS g_0 WHERE g_0.e5 = 'abc'"}, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ TestDependentJoins.checkDependentGroups(plan, new String[] {"pm4.g1"}); //$NON-NLS-1$ }
/** * Test that access node with unsatisfied access pattern is made dependent */ @Test public void testMakeDependentAccessPattern1() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select pm1.g1.e1 from pm1.g1, pm4.g1 where pm1.g1.e1 = pm4.g1.e1", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm4.g1 AS g_0 WHERE g_0.e1 IN (<dependent values>)", "SELECT g_0.e1 FROM pm1.g1 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ checkDependentGroups(plan, new String[] {"pm4.g1"}); //$NON-NLS-1$ }
/** * Test that access node with unsatisfied access pattern is made dependent */ @Test public void testMakeDependentAccessPattern2() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select pm1.g1.e1 from pm1.g1, pm4.g1 where pm4.g1.e2 = 1 and pm1.g1.e1 = pm4.g1.e1", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm4.g1 AS g_0 WHERE (g_0.e2 = 1) AND (g_0.e1 IN (<dependent values>))", "SELECT g_0.e1 FROM pm1.g1 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ checkDependentGroups(plan, new String[] {"pm4.g1"}); //$NON-NLS-1$ }
@Test public void testGlobalHintSetQuery() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("(Select e1 from pm1.g1 Intersect select abc.e1 from (select pm1.g2.e1 from pm1.g2, pm1.g3 where pm1.g2.e2 = pm1.g3.e2) abc) option makedep @abc.g2", TestOptimizer.example1(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm1.g1 AS g_0", "SELECT g_0.e2, g_0.e1 FROM pm1.g2 AS g_0 WHERE g_0.e2 IN (<dependent values>)", "SELECT g_0.e2 FROM pm1.g3 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access 1, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy 2, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project 0, // Select 0, // Sort 0 // UnionAll }); checkDependentGroups(plan, new String[] {"pm1.g2"}); //$NON-NLS-1$ }
@Test public void testGlobalHint() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select * FROM vm1.g4 option makedep @g4.g2", TestOptimizer.example1(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm1.g2 AS g_0 WHERE g_0.e1 IN (<dependent values>)", "SELECT g_0.e1 FROM pm1.g1 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.checkNodeTypes(plan, new int[] { 1, // Access 1, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy 1, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project 0, // Select 0, // Sort 0 // UnionAll }); checkDependentGroups(plan, new String[] {"pm1.g2"}); //$NON-NLS-1$ }
@Test public void testDepJoinHintForceRight() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select * FROM vm1.g4 option makedep pm1.g2", TestOptimizer.example1(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm1.g2 AS g_0 WHERE g_0.e1 IN (<dependent values>)", "SELECT g_0.e1 FROM pm1.g1 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.checkNodeTypes(plan, new int[] { 1, // Access 1, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy 1, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project 0, // Select 0, // Sort 0 // UnionAll }); checkDependentGroups(plan, new String[] {"pm1.g2"}); //$NON-NLS-1$ }
@Test public void testDepJoinHintForceLeft() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select * FROM vm1.g4 option makedep pm1.g1", TestOptimizer.example1(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm1.g1 AS g_0 WHERE g_0.e1 IN (<dependent values>)", "SELECT g_0.e1 FROM pm1.g2 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.checkNodeTypes(plan, new int[] { 1, // Access 1, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy 1, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project 0, // Select 0, // Sort 0 // UnionAll }); checkDependentGroups(plan, new String[] {"pm1.g1"}); //$NON-NLS-1$ }
@Test public void testDepJoinMultiGroupForceOther() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select vm1.g4.*, pm1.g3.e1 FROM vm1.g4, pm1.g3 where pm1.g3.e1=vm1.g4.e1 option makedep pm1.g2", TestOptimizer.example1(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm1.g3 AS g_0", "SELECT g_0.e1 FROM pm1.g2 AS g_0 WHERE g_0.e1 IN (<dependent values>)", "SELECT g_0.e1 FROM pm1.g1 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access 1, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy 2, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project 0, // Select 0, // Sort 0 // UnionAll }); checkDependentGroups(plan, new String[] {"pm1.g2"}); //$NON-NLS-1$ }
@Test public void testGlobalHint1() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select * FROM vm1.g4 as x option makedep @x.g2", TestOptimizer.example1(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm1.g2 AS g_0 WHERE g_0.e1 IN (<dependent values>)", "SELECT g_0.e1 FROM pm1.g1 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.checkNodeTypes(plan, new int[] { 1, // Access 1, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy 1, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project 0, // Select 0, // Sort 0 // UnionAll }); checkDependentGroups(plan, new String[] {"pm1.g2"}); //$NON-NLS-1$ }
@Test public void testDepJoinMultiGroupBaseline() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select vm1.g4.*, pm1.g3.e1 FROM vm1.g4, pm1.g3 where pm1.g3.e1=vm1.g4.e1", TestOptimizer.example1(), //$NON-NLS-1$ new String[] { "SELECT pm1.g1.e1 FROM pm1.g1", //$NON-NLS-1$ "SELECT pm1.g2.e1 FROM pm1.g2", //$NON-NLS-1$ "SELECT pm1.g3.e1 FROM pm1.g3" }, TestOptimizer.getGenericFinder(false), ComparisonMode.CORRECTED_COMMAND_STRING ); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, new int[] { 3, // Access 0, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy 2, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project 0, // Select 0, // Sort 0 // UnionAll }); checkDependentGroups(plan, new String[0]); }
/** * This case actually tests the dead-tie case - either access node could * be made dependent, but merge join is used since no access pattern * needs to be fulfilled and there is no cost info available for either source */ @Test public void testPushSelectAndMakeDependentAccessPattern1() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select pm1.g1.e1 from pm1.g1, pm4.g1 where pm4.g1.e1 = 'abc' and pm1.g1.e1 = 'abc' and pm1.g1.e2 = pm4.g1.e2", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT pm1.g1.e2, pm1.g1.e1 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'", "SELECT pm4.g1.e2 FROM pm4.g1 WHERE pm4.g1.e1 = 'abc'" }, TestOptimizer.getGenericFinder(false), ComparisonMode.CORRECTED_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ checkDependentGroups(plan, new String[0]); TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access 0, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy 1, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project 0, // Select 0, // Sort 0 // UnionAll }); }
/** * Tests that it is enforced if an access node can't be made dependent * because of it's (already-satisfied) access pattern - merge join is used */ @Test public void testPushSelectAndMakeDependentAccessPattern2() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select pm1.g1.e1 from pm1.g1, pm4.g1 where pm4.g1.e1 = 'abc' and pm1.g1.e2 = pm4.g1.e2", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT pm1.g1.e2, pm1.g1.e1 FROM pm1.g1", "SELECT pm4.g1.e2 FROM pm4.g1 WHERE pm4.g1.e1 = 'abc'" }, TestOptimizer.getGenericFinder(false), ComparisonMode.CORRECTED_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ checkDependentGroups(plan, new String[0] ); TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access 0, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy 1, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project 0, // Select 0, // Sort 0 // UnionAll }); }
/** * This case actually tests the dead-tie case - either access node could * be made dependent, but merge join is used since no access pattern * needs to be fulfilled and there is no cost info available for either source * (Same query written slightly different) */ @Test public void testPushSelectAndMakeDependentAccessPattern1a() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select pm1.g1.e1 from pm4.g1, pm1.g1 where pm4.g1.e2 = pm1.g1.e2 and pm4.g1.e1 = 'abc' and pm1.g1.e1 = 'abc'", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT pm1.g1.e2, pm1.g1.e1 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'", "SELECT pm4.g1.e2 FROM pm4.g1 WHERE pm4.g1.e1 = 'abc'" }, TestOptimizer.getGenericFinder(false), ComparisonMode.CORRECTED_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ checkDependentGroups(plan, new String[0]); TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access 0, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy 1, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project 0, // Select 0, // Sort 0 // UnionAll }); }
@Test public void testDepJoinMultiGroupForceOther_NotDep() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select vm1.g4.*, pm1.g3.e1 FROM vm1.g4, pm1.g3 where pm1.g3.e1=vm1.g4.e1 option makedep pm1.g2 makenotdep pm1.g1, pm1.g3", TestOptimizer.example1(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm1.g3 AS g_0", "SELECT g_0.e1 FROM pm1.g2 AS g_0 WHERE g_0.e1 IN (<dependent values>)", "SELECT g_0.e1 FROM pm1.g1 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access 1, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy 2, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project 0, // Select 0, // Sort 0 // UnionAll }); checkDependentGroups(plan, new String[] {"pm1.g2"}); //$NON-NLS-1$ checkNotDependentGroups(plan, new String[] {"pm1.g1", "pm1.g3"}); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testDepJoinHintForceLeft_NotDep() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select * FROM vm1.g4 option makedep pm1.g1 makenotdep pm1.g2", TestOptimizer.example1(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm1.g1 AS g_0 WHERE g_0.e1 IN (<dependent values>)", "SELECT g_0.e1 FROM pm1.g2 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.checkNodeTypes(plan, new int[] { 1, // Access 1, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy 1, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project 0, // Select 0, // Sort 0 // UnionAll }); checkDependentGroups(plan, new String[] {"pm1.g1"}); //$NON-NLS-1$ checkNotDependentGroups(plan, new String[] {"pm1.g2"}); //$NON-NLS-1$ }
@Test public void testDepJoinHintForceRight_NotDep() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select * FROM vm1.g4 option makedep pm1.g2 makenotdep pm1.g1", TestOptimizer.example1(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm1.g2 AS g_0 WHERE g_0.e1 IN (<dependent values>)", "SELECT g_0.e1 FROM pm1.g1 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.checkNodeTypes(plan, new int[] { 1, // Access 1, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy 1, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project 0, // Select 0, // Sort 0 // UnionAll }); checkDependentGroups(plan, new String[] {"pm1.g2"}); //$NON-NLS-1$ checkNotDependentGroups(plan, new String[] {"pm1.g1"}); //$NON-NLS-1$ }