1--echo # WL#8244 Hints for subquery execution 2 3CREATE TABLE t1 (a INTEGER NOT NULL, b INT, PRIMARY KEY (a)); 4CREATE TABLE t2 (a INTEGER NOT NULL, KEY (a)); 5CREATE TABLE t3 (a INTEGER NOT NULL, b INT, KEY (a)); 6INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40); 7INSERT INTO t2 VALUES (2), (3), (4), (5); 8INSERT INTO t3 VALUES (10,3), (20,4), (30,5); 9 10ANALYZE TABLE t1, t2, t3; 11 12--echo This query will normally use Table Pull-out 13EXPLAIN 14SELECT * FROM t2 WHERE t2.a IN (SELECT a FROM t1); 15--echo Check that we can disable SEMIJOIN transformation 16EXPLAIN 17SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1); 18--echo Same with hint in outer query 19EXPLAIN 20SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2 21WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1); 22 23--echo Query with two sub-queries 24EXPLAIN 25SELECT * FROM t3 26WHERE t3.a IN (SELECT a FROM t1 tx) 27 AND t3.b IN (SELECT a FROM t1 ty); 28--echo No SEMIJOIN transformation for first subquery 29EXPLAIN 30SELECT * FROM t3 31WHERE t3.a IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 tx) 32 AND t3.b IN (SELECT a FROM t1 ty); 33EXPLAIN 34SELECT /*+ NO_SEMIJOIN(@subq1) */ * FROM t3 35WHERE t3.a IN (SELECT /*+ QB_NAME(`subq1`) */ a FROM t1 tx) 36 AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); 37--echo No SEMIJOIN transformation for latter subquery 38EXPLAIN 39SELECT * FROM t3 40WHERE t3.a IN (SELECT a FROM t1 tx) 41 AND t3.b IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 ty); 42EXPLAIN 43SELECT /*+ NO_SEMIJOIN(@`subq2`) */ * FROM t3 44WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) 45 AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); 46--echo No SEMIJOIN transformation for any subquery 47EXPLAIN 48SELECT * FROM t3 49WHERE t3.a IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 tx) 50 AND t3.b IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 ty); 51EXPLAIN 52SELECT /*+ NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2) */ * FROM t3 53WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) 54 AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); 55 56--echo Query with nested sub-queries 57EXPLAIN 58SELECT * FROM t3 59WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx 60 WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); 61--echo No SEMIJOIN transformation for outer subquery 62EXPLAIN 63SELECT /*+ NO_SEMIJOIN(@subq1) */ * FROM t3 64WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx 65 WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); 66--echo No SEMIJOIN transformation for inner-most subquery 67EXPLAIN 68SELECT /*+ NO_SEMIJOIN(@subq2) */ * FROM t3 69WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx 70 WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); 71--echo No SEMIJOIN transformation at all 72EXPLAIN 73SELECT /*+ NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2) */ * FROM t3 74WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx 75 WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); 76 77--echo This query does not support SEMIJOIN. SEMIJOIN hint is ignored 78EXPLAIN 79SELECT /*+ SEMIJOIN(@subq) */ * FROM t2 80WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a); 81 82--echo This query will get LooseScan by default 83EXPLAIN 84SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 85--echo Let's turn off LooseScan, FirstMatch is then SELECTed 86EXPLAIN 87SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2 88WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 89--echo Let's also turn off FirstMatch, MatLookup is then used 90EXPLAIN 91SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2 92WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 93--echo Let's also turn off Materialization, DuplicateWeedout should then be used 94EXPLAIN 95SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, MATERIALIZATION) */ * 96FROM t2 97WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 98--echo If we turn off all strategies, DuplicateWeedout should still be used 99EXPLAIN 100SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, MATERIALIZATION, 101 DUPSWEEDOUT) */ * 102FROM t2 103WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 104--echo Turn off non-used strategies, nothing should change. Still Loosescan 105EXPLAIN 106SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ * 107FROM t2 108WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 109 110--echo Test same query with SEMIJOIN hint 111--echo Forcing LooseScan, should not change anything 112EXPLAIN 113SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2 114WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 115--echo Force FirstMatch 116EXPLAIN 117SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t2 118WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 119--echo Force Materialization 120EXPLAIN 121SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ * FROM t2 122WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 123--echo Force DuplicateWeedout 124EXPLAIN 125SELECT /*+ SEMIJOIN(@subq1 DUPSWEEDOUT) */ * FROM t2 126WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 127--echo If LooseScan is among candidates, it will be used 128EXPLAIN 129SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, DUPSWEEDOUT) */ * FROM t2 130WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 131EXPLAIN 132SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 133WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 134EXPLAIN 135SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, MATERIALIZATION, 136 DUPSWEEDOUT) */ * FROM t2 137WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 138--echo Drop LooseScan from list of strategies, FirstMatch will be used 139EXPLAIN 140SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 141WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 142--echo Drop FirstMatch, MatLookup is next 143EXPLAIN 144SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 145WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 146 147--echo For this query LooseScan and Materialization is not applicable 148EXPLAIN 149SELECT * FROM t1 150WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); 151--echo Turn off all applicable strategies. DuplicateWeedout should be used 152EXPLAIN 153SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, DUPSWEEDOUT) */ * FROM t1 154WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); 155--echo Similar with SEMIJOIN hint 156EXPLAIN 157SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION) */ * FROM t1 158WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); 159 160--echo Test multiple subqueries. 161--echo Default for this query is Loosecan for first and FirstMatch for latter 162EXPLAIN 163SELECT * FROM t1 164WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 165 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 166--echo Forcing the default strategy should not change anything 167EXPLAIN 168SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 169WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 170 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 171--echo Forcing a strategy for one, may change the other due to cost changes 172EXPLAIN 173SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1 174WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 175 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 176--echo Forcing same strategy for both 177EXPLAIN 178SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 179WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 180 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 181--echo Loosescan for both is not possible, ends up with DuplicateWeedout 182EXPLAIN 183SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1 184WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 185 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 186--echo Swap strategies compared to default 187EXPLAIN 188SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1 189WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 190 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 191--echo Different subsets of strategies for different subqueries 192EXPLAIN 193SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) 194 SEMIJOIN(@subq2 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t1 195WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 196 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 197--echo Vice versa 198EXPLAIN 199SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) 200 SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1 201WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 202 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 203--echo Another combination 204EXPLAIN 205SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, FIRSTMATCH) 206 SEMIJOIN(@subq2 LOOSESCAN, DUPSWEEDOUT) */ * FROM t1 207WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 208 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 209--echo Turn off default 210EXPLAIN 211SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN) 212 NO_SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 213WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 214 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 215--echo Also turn off 2nd choice. Gives DuplicateWeedout over both 216EXPLAIN 217SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH) 218 NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1 219WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 220 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 221--echo Also turn off DuplicateWeedout. Materialization is only one left. 222EXPLAIN 223SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, DUPSWEEDOUT) 224 NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */ * FROM t1 225WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 226 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 227--echo Force materialization with SEMIJOIN hints instead 228EXPLAIN 229SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) 230 SEMIJOIN(@subq2 MATERIALIZATION) */ * FROM t1 231WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 232 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 233--echo This query gives DuplicateWeedout over both since combining 234--echo DuplicateWeedout with another strategy does not seem possible. 235EXPLAIN 236SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) 237 SEMIJOIN(@subq2 DUPSWEEDOUT) */ * FROM t1 238WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 239 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 240--echo More alternatives for 2nd subquery gives Materialization for first 241EXPLAIN 242SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) 243 SEMIJOIN(@subq2 LOOSESCAN, FIRSTMATCH, DUPSWEEDOUT) */ * FROM t1 244WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 245 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 246 247--echo A query with nested subqueries which by default is materialized together 248EXPLAIN 249SELECT * FROM t1 250WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 251 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 252--echo Let's turn off Materialization, DuplicateWeedout is then SELECTed 253EXPLAIN 254SELECT /*+ NO_SEMIJOIN(@subq1 MATERIALIZATION) */ * FROM t1 255WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 256 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 257--echo Let's also turn off DuplicateWeedout, FirstMatch is then used 258EXPLAIN 259SELECT /*+ NO_SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t1 260WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 261 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 262--echo Also turn off FirstMatch. LooseScan not usable. Back to DuplicateWeedout 263EXPLAIN 264SELECT /*+ NO_SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT, FIRSTMATCH) */ * 265FROM t1 266WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 267 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 268--echo If we turn off all strategies, DuplicateWeedout should still be used 269EXPLAIN 270SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, MATERIALIZATION, 271 DUPSWEEDOUT) */ * 272FROM t1 273WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 274 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 275 276--echo Test same query with SEMIJOIN hint 277--echo Force Materialization, should not change anything 278EXPLAIN 279SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ * FROM t1 280WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 281 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 282--echo Force LooseScan, will use DuplicateWeedout 283EXPLAIN 284SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t1 285WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 286 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 287--echo Force FirstMatch 288EXPLAIN 289SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1 290WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 291 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 292--echo Force DuplicateWeedout 293EXPLAIN 294SELECT /*+ SEMIJOIN(@subq1 DUPSWEEDOUT) */ * FROM t1 295WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 296 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 297--echo If Materialization is among candidates, it will be used 298EXPLAIN 299SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t1 300WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 301 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 302EXPLAIN 303SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, LOOSESCAN, DUPSWEEDOUT) */ * FROM t1 304WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 305 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 306EXPLAIN 307SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, FIRSTMATCH, LOOSESCAN, 308 DUPSWEEDOUT) */ * FROM t1 309WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 310 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 311--echo Drop Materialization. DuplicateWeedout will be used 312EXPLAIN 313SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */ * FROM t1 314WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 315 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 316--echo Drop DuplicateWeedout, FirstMatch is next 317EXPLAIN 318SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t1 319WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 320 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 321--echo Strategy hints on inner-most query is ignored since sj-nests are merged 322EXPLAIN 323SELECT /*+ NO_SEMIJOIN(@subq2 MATERIALIZATION) */ * FROM t1 324WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 325 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 326--echo Ditto 327EXPLAIN 328SELECT /*+ SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 329WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 330 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 331 332--echo Turn off semijoin for outer subquery. FirstMatch is used for inner 333EXPLAIN 334SELECT /*+ NO_SEMIJOIN(@subq1) */ * FROM t1 335WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 336 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 337--echo Do not use FirstMatch for inner 338EXPLAIN 339SELECT /*+ NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 340WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 341 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 342--echo Do not use FirstMatch nor Materialization for inner 343EXPLAIN 344SELECT /*+ NO_SEMIJOIN(@subq1) 345 NO_SEMIJOIN(@subq2 FIRSTMATCH, MATERIALIZATION) */ * FROM t1 346WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 347 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 348--echo LooseScan is last resort 349EXPLAIN 350SELECT /*+ NO_SEMIJOIN(@subq1) 351 NO_SEMIJOIN(@subq2 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ * 352FROM t1 353WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 354 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 355--echo Allow all stragies except default 356EXPLAIN 357SELECT /*+ NO_SEMIJOIN(@subq1) 358 SEMIJOIN(@subq2 MATERIALIZATION, DUPSWEEDOUT, LOOSESCAN) */ * 359FROM t1 360WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 361 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 362--echo Force a particular strategy 363EXPLAIN 364SELECT /*+ NO_SEMIJOIN(@subq1) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1 365WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 366 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 367 368--echo Turn off semijoin for inner-most subquery. FirstMatch is used for outer 369EXPLAIN 370SELECT /*+ NO_SEMIJOIN(@subq2) */ * FROM t1 371WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 372 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 373--echo Do not use FirstMatch for outer 374EXPLAIN 375SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH) NO_SEMIJOIN(@subq2) */ * FROM t1 376WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 377 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 378--echo Do not use FirstMatch nor Materialization for outer 379EXPLAIN 380SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION) 381 NO_SEMIJOIN(@subq2) */ * FROM t1 382WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 383 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 384--echo LooseScan can not be used since index scan would not be "covering" 385EXPLAIN 386SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) 387 NO_SEMIJOIN(@subq2) */ * FROM t1 388WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 389 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 390--echo Allow all stragies except default 391EXPLAIN 392SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT, LOOSESCAN) 393 NO_SEMIJOIN(@subq2) */ * FROM t1 394WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 395 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 396--echo Force a particular strategy 397EXPLAIN 398SELECT /*+ SEMIJOIN(@subq1 DUPSWEEDOUT) NO_SEMIJOIN(@subq2) */ * FROM t1 399WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 400 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 401--echo Turn off semijoin for both subqueries 402EXPLAIN 403SELECT /*+ NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2) */ * FROM t1 404WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 405 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); 406 407--echo Test hints with prepared statements 408PREPARE stmt1 FROM "EXPLAIN 409SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) 410 NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1 411WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 412 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)"; 413EXECUTE stmt1; 414EXECUTE stmt1; 415DEALLOCATE PREPARE stmt1; 416--echo Another Prepared Statement test 417PREPARE stmt1 FROM "EXPLAIN 418SELECT /*+ NO_SEMIJOIN(@subq1) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1 419WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 420 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2))"; 421EXECUTE stmt1; 422EXECUTE stmt1; 423DEALLOCATE PREPARE stmt1; 424 425SET optimizer_switch = default; 426 427--echo Tests with non-default optimizer_switch settings 428 429SET optimizer_switch = 'semijoin=off'; 430--echo No table pull-out for this query 431EXPLAIN 432SELECT * FROM t2 WHERE t2.a IN (SELECT a FROM t1); 433--echo This should not change anything 434EXPLAIN 435SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2 436WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1); 437--echo Force semijoin 438EXPLAIN 439SELECT /*+ SEMIJOIN(@subq) */ * FROM t2 440WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1); 441--echo Setting strategy should still force semijoin 442--echo Strategy is ignored since table pull-out is done 443EXPLAIN 444SELECT /*+ SEMIJOIN(@subq FIRSTMATCH) */ * FROM t2 445WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1); 446--echo Query with two sub-queries 447EXPLAIN 448SELECT * FROM t3 449WHERE t3.a IN (SELECT a FROM t1 tx) 450 AND t3.b IN (SELECT a FROM t1 ty); 451--echo SEMIJOIN transformation for first subquery 452EXPLAIN 453SELECT /*+ SEMIJOIN(@subq1) */ * FROM t3 454WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) 455 AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); 456--echo SEMIJOIN transformation for latter subquery 457EXPLAIN 458SELECT /*+ SEMIJOIN(@subq2) */ * FROM t3 459WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) 460 AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); 461--echo SEMIJOIN transformation for both subqueries 462EXPLAIN 463SELECT /*+ SEMIJOIN(@subq1) SEMIJOIN(@subq2) */ * FROM t3 464WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) 465 AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); 466--echo Query with nested sub-queries 467EXPLAIN 468SELECT * FROM t3 469WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx 470 WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); 471--echo SEMIJOIN transformation for outer subquery 472EXPLAIN 473SELECT /*+ SEMIJOIN(@subq1) */ * FROM t3 474WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx 475 WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); 476--echo SEMIJOIN transformation for inner-most subquery 477EXPLAIN 478SELECT /*+ SEMIJOIN(@subq2) */ * FROM t3 479WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx 480 WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); 481--echo SEMIJOIN transformation for both 482EXPLAIN 483SELECT /*+ SEMIJOIN(@subq1) SEMIJOIN(@subq2) */ * FROM t3 484WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx 485 WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); 486 487--echo Test strategies when some are disabled by optimizer_switch 488SET optimizer_switch='semijoin=on'; 489 490SET optimizer_switch='loosescan=off'; 491--echo This query will get LooseScan by default. FirstMatch now. 492EXPLAIN 493SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 494--echo Let's turn off LooseScan also by hint, FirstMatch should still be SELECTed 495EXPLAIN 496SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2 497WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 498--echo Let's also turn off FirstMatch, MatLookup should then be used 499EXPLAIN 500SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t2 501WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 502--echo Let's also turn off Materialization, DuplicateWeedout should then be used 503EXPLAIN 504SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION) */ * 505FROM t2 506WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 507 508--echo Let's force LooseScan back on 509EXPLAIN 510SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2 511WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 512--echo Forcing another strategy 513EXPLAIN 514SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ * FROM t2 515WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 516--echo If LooseScan is among candidates, it is used even if originally disabled 517EXPLAIN 518SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, DUPSWEEDOUT) */ * FROM t2 519WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 520EXPLAIN 521SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 522WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 523EXPLAIN 524SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, MATERIALIZATION, 525 DUPSWEEDOUT) */ * FROM t2 526WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 527 528--echo Disable another strategy 529SET optimizer_switch='firstmatch=off'; 530 531--echo Turn on FirstMatch, but not LooseScan on with hint 532EXPLAIN 533SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 534WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 535--echo Drop all remaining strategies with hint, should use DuplicateWeedout 536EXPLAIN 537SELECT /*+ NO_SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 538WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 539 540--echo For this query LooseScan and Materialization is not applicable 541--echo Should use DuplicateWeedout since FirstMatch is disabled 542EXPLAIN 543SELECT * FROM t1 544WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); 545--echo Turn off all applicable strategies. DuplicateWeedout should still be used 546EXPLAIN 547SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, DUPSWEEDOUT) */ * FROM t1 548WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); 549--echo Reverse which strategies are allowed with hint 550EXPLAIN 551SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH) */ * FROM t1 552WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); 553 554--echo Default for this query is Loosecan for first and FirstMatch for latter 555--echo Since both strategies are disabled, will now use DuplicateWeedout 556EXPLAIN 557SELECT * FROM t1 558WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 559 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 560--echo Allowing LooseScan and FirstMatch and optimizer_switch is ignored 561EXPLAIN 562SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH) 563 SEMIJOIN(@subq2 LOOSESCAN, FIRSTMATCH) */ * FROM t1 564WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 565 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 566--echo Forcing a disabled strategy for one 567EXPLAIN 568SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1 569WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 570 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 571--echo Forcing same strategy for both 572EXPLAIN 573SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 574WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 575 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 576--echo Swap strategies compared to default 577EXPLAIN 578SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1 579WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 580 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 581--echo Different subsets of strategies for different subqueries 582EXPLAIN 583SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) 584 SEMIJOIN(@subq2 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t1 585WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 586 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 587--echo Turn off DuplicateWeedout for both. Materialization is left 588EXPLAIN 589SELECT /*+ NO_SEMIJOIN(@subq1 DUPSWEEDOUT) 590 NO_SEMIJOIN(@subq2 DUPSWEEDOUT) */ * FROM t1 591WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 592 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 593--echo Forcing materialization should have same effect 594EXPLAIN 595SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) 596 SEMIJOIN(@subq2 MATERIALIZATION) */ * FROM t1 597WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 598 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 599--echo Turn off DuplicateWeedout for first. MatLookup is used for both 600EXPLAIN 601SELECT /*+ NO_SEMIJOIN(@subq1 DUPSWEEDOUT) */ * FROM t1 602WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 603 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 604--echo Turn off DuplicateWeedout for second. Same effect. 605EXPLAIN 606SELECT /*+ NO_SEMIJOIN(@subq2 DUPSWEEDOUT) */ * FROM t1 607WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 608 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 609 610--echo Enable all strategies except DuplicateWeedout 611SET optimizer_switch='firstmatch=on,loosescan=on,materialization=on,duplicateweedout=off'; 612 613--echo If we turn off all other strategies, DuplicateWeedout will be used 614EXPLAIN 615SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, MATERIALIZATION) */ * 616FROM t2 617WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 618--echo LooseScan and Materialization is not applicable, FirstMatch is used 619EXPLAIN 620SELECT * FROM t1 621WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); 622--echo Turn off all applicable strategies. DuplicateWeedout should be used 623EXPLAIN 624SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1 625WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); 626--echo Similar with SEMIJOIN hint 627EXPLAIN 628SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION) */ * FROM t1 629WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); 630 631--echo Disable all strategies 632SET optimizer_switch='firstmatch=off,loosescan=off,materialization=off,duplicateweedout=off'; 633--echo DuplicateWeedout is then used 634EXPLAIN 635SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 636--echo Turning off extra strategies should not change anything 637EXPLAIN 638SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, DUPSWEEDOUT) */ * 639FROM t2 640WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 641--echo Turning on some strategies should give one of those 642EXPLAIN 643SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION) */ * 644FROM t2 645WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); 646--echo For this query that cannot use LooseScan or Materialization, 647--echo turning those on will still give DupliateWeedout 648EXPLAIN 649SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION) */ * FROM t1 650WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); 651--echo Turning on FirstMatch should give FirstMatch 652EXPLAIN 653SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH) */ * FROM t1 654WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); 655 656SET optimizer_switch = default; 657 658--echo Test that setting optimizer_switch after prepare will change strategy 659PREPARE stmt1 FROM "EXPLAIN 660SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) 661 NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1 662WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 663 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)"; 664EXECUTE stmt1; 665SET optimizer_switch = 'duplicateweedout=off'; 666--echo Will now use materialization 667EXECUTE stmt1; 668SET optimizer_switch = 'duplicateweedout=on'; 669--echo Turn DuplicateWeedout back on 670EXECUTE stmt1; 671DEALLOCATE PREPARE stmt1; 672 673SET optimizer_switch = default; 674 675--echo Specifying two SEMIJOIN/NO_SEMIJOIN for same query block gives warning 676--echo First has effect, second is ignored 677EXPLAIN 678SELECT * FROM t2 679WHERE t2.a IN (SELECT /*+ NO_SEMIJOIN() SEMIJOIN() */ a FROM t1); 680--echo Try opposite order 681EXPLAIN 682SELECT * FROM t2 683WHERE t2.a IN (SELECT /*+ SEMIJOIN() NO_SEMIJOIN() */ a FROM t1); 684--echo Specify at different levels, hint inside block has effect 685EXPLAIN 686SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2 687WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SEMIJOIN() */ a FROM t1); 688--echo Specify at different levels, opposite order 689EXPLAIN 690SELECT /*+ SEMIJOIN(@subq) */ * FROM t2 691WHERE t2.a IN (SELECT /*+ QB_NAME(subq) NO_SEMIJOIN() */ a FROM t1); 692--echo Duplicate hints also gives warning, but hint has effect 693EXPLAIN 694SELECT /*+ SEMIJOIN(@subq) */ * FROM t2 695WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SEMIJOIN() */ a FROM t1); 696EXPLAIN 697SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2 698WHERE t2.a IN (SELECT /*+ QB_NAME(subq) NO_SEMIJOIN() */ a FROM t1); 699--echo Multiple subqueries with conflicting hints 700EXPLAIN 701SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 702WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) NO_SEMIJOIN() */ a FROM t3) 703 AND t1.b IN (SELECT /*+ QB_NAME(subq2) SEMIJOIN(LOOSESCAN) */ a FROM t2); 704EXPLAIN 705SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 706WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) NO_SEMIJOIN(LOOSESCAN) */ a FROM t3) 707 AND t1.b IN (SELECT /*+ QB_NAME(subq2) SEMIJOIN(LOOSESCAN) */ a FROM t2); 708--echo Conflicting hints in same hint comment 709EXPLAIN 710SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1 711WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 712 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 713EXPLAIN 714SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) NO_SEMIJOIN(@subq1 LOOSESCAN) */ * 715FROM t1 716WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 717 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 718EXPLAIN 719SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN) NO_SEMIJOIN(@subq1 FIRSTMATCH) */ * 720FROM t1 721WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 722 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 723 724--echo Non-supported strategies should give warnings 725EXPLAIN 726SELECT /*+ SEMIJOIN(@subq1 INTOEXISTS) NO_SEMIJOIN(@subq2 INTOEXISTS) */ * 727FROM t1 728WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 729 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); 730 731--echo SUBQUERY tests 732--echo SUBQUERY should disable SEMIJOIN and use specified subquery strategy 733EXPLAIN 734SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1); 735EXPLAIN 736SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2 737WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1); 738--echo Query with two subqueries 739EXPLAIN 740SELECT /*+ SUBQUERY(@subq1 INTOEXISTS) SUBQUERY(@subq2 MATERIALIZATION) */ * 741FROM t3 742WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) 743 AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); 744--echo Query with nested sub-queries 745EXPLAIN 746SELECT /*+ SUBQUERY(@subq1 INTOEXISTS) SUBQUERY(@subq2 MATERIALIZATION) */ * 747FROM t3 748WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx 749 WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); 750EXPLAIN 751SELECT /*+ SUBQUERY(@subq1 MATERIALIZATION) SUBQUERY(@subq2 INTOEXISTS) */ * 752FROM t3 753WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx 754 WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); 755 756--echo This query does not support SEMIJOIN. Materialization is default 757EXPLAIN 758SELECT * FROM t2 759WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a); 760--echo Use In-to-exists instead 761EXPLAIN 762SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ * FROM t2 763WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a); 764 765--echo For this query In-to-exists is default 766EXPLAIN 767SELECT a, a IN (SELECT a FROM t1) FROM t2; 768--echo Force Subquery Materialization 769EXPLAIN 770SELECT a, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2; 771EXPLAIN 772SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ a, 773 a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1) FROM t2; 774 775--echo This query does not support Subquery Materialization due to type mismatch 776EXPLAIN 777SELECT * FROM t2 778WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ sum(b) FROM t1 group by a); 779--echo Trying to force Subquery Materialization will not change anything 780EXPLAIN 781SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2 782WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ sum(b) FROM t1 group by a); 783 784--echo Test hints with prepared statements 785PREPARE stmt1 FROM "EXPLAIN 786SELECT /*+ SUBQUERY(@subq1 MATERIALIZATION) 787 SUBQUERY(@subq2 INTOEXISTS) */ * FROM t1 788WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) 789 AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)"; 790EXECUTE stmt1; 791EXECUTE stmt1; 792DEALLOCATE PREPARE stmt1; 793 794--echo Test optimizer_switch settings with SUBQUERY hint 795SET optimizer_switch='materialization=off'; 796--echo This query will now use In-to-exist 797EXPLAIN 798SELECT * FROM t2 799WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a); 800--echo Force it to use Materialization 801EXPLAIN 802SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2 803WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a); 804 805SET optimizer_switch='materialization=on,subquery_materialization_cost_based=off'; 806--echo This query will now use materialization 807EXPLAIN 808SELECT a, a IN (SELECT a FROM t1) FROM t2; 809--echo Force In-to-exists 810EXPLAIN 811SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ a, 812 a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1) FROM t2; 813 814--echo Specifying both strategies should give a warning 815EXPLAIN 816SELECT /*+ SUBQUERY(@subq1 MATERIALIZATION, INTOEXISTS) 817 SUBQUERY(@subq2 MATERIALIZATION, INTOEXISTS) */ * 818FROM t3 819WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) 820 AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); 821--echo Non-supported strategies should give warnings 822EXPLAIN 823SELECT /*+ SUBQUERY(@subq1 FIRSTMATCH) SUBQUERY(@subq2 LOOSESCAN) */ * 824FROM t3 825WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) 826 AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); 827 828SET optimizer_switch= default; 829 830--echo Specifying two SUBQUERY for same query block gives warning 831--echo First has effect, second is ignored 832EXPLAIN 833SELECT * FROM t2 834WHERE t2.a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) SUBQUERY(INTOEXISTS) */ a 835FROM t1); 836--echo Try opposite order 837EXPLAIN 838SELECT * FROM t2 839WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) SUBQUERY(MATERIALIZATION) */ a 840FROM t1); 841--echo Specify at different levels, hint inside block has effect 842EXPLAIN 843SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2 844WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SUBQUERY(INTOEXISTS) */ a FROM t1); 845--echo Specify at different levels, opposite order 846EXPLAIN 847SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ * FROM t2 848WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SUBQUERY(MATERIALIZATION) */ a FROM t1); 849 850--echo Specifying combinations of SUBQUERY and SEMIJOIN/NO_SEMIJOIN 851--echo for same query block gives warning 852--echo First has effect, second is ignored 853EXPLAIN 854SELECT * FROM t2 855WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) SEMIJOIN() */ a FROM t1); 856--echo Try opposite order 857EXPLAIN 858SELECT * FROM t2 859WHERE t2.a IN (SELECT /*+ NO_SEMIJOIN() SUBQUERY(MATERIALIZATION) */ a FROM t1); 860--echo Specify at different levels, hint inside block has effect 861EXPLAIN 862SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2 863WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SEMIJOIN() */ a FROM t1); 864EXPLAIN 865SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ * FROM t2 866WHERE t2.a IN (SELECT /*+ QB_NAME(subq) NO_SEMIJOIN() */ a FROM t1); 867EXPLAIN 868SELECT /*+ SEMIJOIN(@subq FIRSTMATCH) */ * FROM t2 869WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SUBQUERY(@subq INTOEXISTS) */ a FROM t1); 870 871 872 873 874drop table t1, t2, t3; 875 876