|
|
--echo # WL#8017 Infrastructure for Optimizer Hints
|
|
|
|
|
|
CREATE TABLE t1(f1 INT, f2 INT);
|
|
|
INSERT INTO t1 VALUES
|
|
|
(1,1),(2,2),(3,3);
|
|
|
|
|
|
CREATE TABLE t2(f1 INT NOT NULL, f2 INT NOT NULL, f3 CHAR(200), KEY(f1, f2));
|
|
|
INSERT INTO t2 VALUES
|
|
|
(1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty'),
|
|
|
(2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'), (2,4, 'qwerty'),(2,5, 'qwerty'),
|
|
|
(3,1, 'qwerty'),(3,4, 'qwerty'),
|
|
|
(4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), (4,4, 'qwerty'),
|
|
|
(1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty'),
|
|
|
(2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'), (2,4, 'qwerty'),(2,5, 'qwerty'),
|
|
|
(3,1, 'qwerty'),(3,4, 'qwerty'),
|
|
|
(4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), (4,4, 'qwerty');
|
|
|
|
|
|
CREATE TABLE t3 (f1 INT NOT NULL, f2 INT, f3 VARCHAR(32),
|
|
|
PRIMARY KEY(f1), KEY f2_idx(f1), KEY f3_idx(f3));
|
|
|
INSERT INTO t3 VALUES
|
|
|
(1, 1, 'qwerty'), (2, 1, 'ytrewq'),
|
|
|
(3, 2, 'uiop'), (4, 2, 'poiu'), (5, 2, 'lkjh'),
|
|
|
(6, 2, 'uiop'), (7, 2, 'poiu'), (8, 2, 'lkjh'),
|
|
|
(9, 2, 'uiop'), (10, 2, 'poiu'), (11, 2, 'lkjh'),
|
|
|
(12, 2, 'uiop'), (13, 2, 'poiu'), (14, 2, 'lkjh');
|
|
|
INSERT INTO t3 SELECT f1 + 20, f2, f3 FROM t3;
|
|
|
INSERT INTO t3 SELECT f1 + 40, f2, f3 FROM t3;
|
|
|
|
|
|
ANALYZE TABLE t1;
|
|
|
ANALYZE TABLE t2;
|
|
|
ANALYZE TABLE t3;
|
|
|
|
|
|
|
|
|
--echo # NO_RANGE_OPTIMIZATION hint testing
|
|
|
set optimizer_switch=default;
|
|
|
|
|
|
--echo # Check statistics with no hint
|
|
|
FLUSH STATUS;
|
|
|
SELECT f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
|
|
|
SHOW STATUS LIKE 'handler_read%';
|
|
|
|
|
|
--echo # Check statistics with hint
|
|
|
FLUSH STATUS;
|
|
|
SELECT /*+ NO_RANGE_OPTIMIZATION(t3) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
|
|
|
SHOW STATUS LIKE 'handler_read%';
|
|
|
|
|
|
EXPLAIN SELECT f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
|
|
|
--echo # Turn off range access for PRIMARY key
|
|
|
--echo # Should use range access by f2_idx key
|
|
|
EXPLAIN SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
|
|
|
--echo # Turn off range access for PRIMARY & f2_idx keys
|
|
|
--echo # Should use skip scan for f3_idx index
|
|
|
EXPLAIN SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
|
|
|
--echo # Turn off range access for all keys
|
|
|
--echo # Should use index access
|
|
|
EXPLAIN SELECT /*+ NO_RANGE_OPTIMIZATION(t3) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
|
|
|
--echo # Turn off range access for PRIMARY & f2_idx keys
|
|
|
--echo # Should use index access
|
|
|
EXPLAIN SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY) NO_RANGE_OPTIMIZATION(t3 f2_idx) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
|
|
|
|
|
|
--echo # NO_ICP hint testing
|
|
|
set optimizer_switch='index_condition_pushdown=on';
|
|
|
|
|
|
EXPLAIN SELECT f2 FROM
|
|
|
(SELECT f2, f3, f1 FROM t3 WHERE f1 > 27 AND f3 = 'poiu') AS TD
|
|
|
WHERE TD.f1 > 27 AND TD.f3 = 'poiu';
|
|
|
|
|
|
EXPLAIN SELECT /*+ NO_ICP(t3@qb1 f3_idx) */ f2 FROM
|
|
|
(SELECT /*+ QB_NAME(QB1) */ f2, f3, f1 FROM t3 WHERE f1 > 27 AND f3 = 'poiu') AS TD
|
|
|
WHERE TD.f1 > 27 AND TD.f3 = 'poiu';
|
|
|
|
|
|
EXPLAIN SELECT /*+ NO_ICP(t3@qb1) */ f2 FROM
|
|
|
(SELECT /*+ QB_NAME(QB1) */ f2, f3, f1 FROM t3 WHERE f1 > 27 AND f3 = 'poiu') AS TD
|
|
|
WHERE TD.f1 > 27 AND TD.f3 = 'poiu';
|
|
|
|
|
|
--echo # Expected warning for f1_idx key, unresolved name.
|
|
|
EXPLAIN SELECT f2 FROM
|
|
|
(SELECT /*+ NO_ICP(t3 f3_idx, f1_idx, f2_idx) */ f2, f3, f1 FROM t3 WHERE f1 > 27 AND f3 = 'poiu') AS TD
|
|
|
WHERE TD.f1 > 27 AND TD.f3 = 'poiu';
|
|
|
|
|
|
--echo # ICP should still be used.
|
|
|
EXPLAIN SELECT f2 FROM
|
|
|
(SELECT /*+ NO_ICP(t3 f1_idx, f2_idx) */ f2, f3, f1 FROM t3 WHERE f1 > 27 AND f3 = 'poiu') AS TD
|
|
|
WHERE TD.f1 > 27 AND TD.f3 = 'poiu';
|
|
|
|
|
|
--echo # BKA & NO_BKA hint testing
|
|
|
set optimizer_switch=default;
|
|
|
set optimizer_switch='batched_key_access=off,mrr_cost_based=off';
|
|
|
|
|
|
--echo # Check statistics without hint
|
|
|
FLUSH STATUS;
|
|
|
SELECT t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
SHOW STATUS LIKE 'handler_read%';
|
|
|
|
|
|
--echo # Check statistics with hint
|
|
|
FLUSH STATUS;
|
|
|
SELECT /*+ BKA() */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
SHOW STATUS LIKE 'handler_read%';
|
|
|
|
|
|
EXPLAIN SELECT t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
EXPLAIN SELECT /*+ BKA(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
EXPLAIN SELECT /*+ BKA() */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
EXPLAIN SELECT /*+ BKA(t1, t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
EXPLAIN SELECT /*+ BKA(t1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
EXPLAIN SELECT /*+ QB_NAME(QB1) BKA(t2@QB1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
set optimizer_switch='batched_key_access=off,mrr_cost_based=on';
|
|
|
|
|
|
EXPLAIN SELECT t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
EXPLAIN SELECT /*+ BKA(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
EXPLAIN SELECT /*+ BKA() */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
EXPLAIN SELECT /*+ BKA(t1, t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
EXPLAIN SELECT /*+ QB_NAME(QB1) BKA(t2@QB1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
set optimizer_switch='batched_key_access=on,mrr_cost_based=off';
|
|
|
|
|
|
EXPLAIN SELECT t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
set optimizer_switch='mrr=off';
|
|
|
EXPLAIN SELECT t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
--echo # MRR switch should not affect BKA.
|
|
|
--echo # BKA should be used for table t2.
|
|
|
EXPLAIN SELECT /*+ BKA(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
set optimizer_switch='mrr=on';
|
|
|
|
|
|
EXPLAIN SELECT /*+ NO_BKA(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
EXPLAIN SELECT /*+ NO_BKA() */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
EXPLAIN SELECT /*+ NO_BKA(t1, t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
EXPLAIN SELECT /*+ QB_NAME(QB1) NO_BKA(t2@QB1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
--echo # UPDATE|DELETE|INSERT|REPLACE hint testing
|
|
|
set optimizer_switch='batched_key_access=off,mrr_cost_based=off';
|
|
|
|
|
|
EXPLAIN UPDATE t3
|
|
|
SET f3 = 'mnbv' WHERE f1 > 30 AND f1 < 33 AND (t3.f1, t3.f2, t3.f3) IN
|
|
|
(SELECT t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
|
|
|
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
|
|
|
|
|
|
--echo # Turn off range access for PRIMARY key.
|
|
|
--echo # Range access should be used for f2_idx key.
|
|
|
EXPLAIN UPDATE /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY) */ t3
|
|
|
SET f3 = 'mnbv' WHERE f1 > 30 AND f1 < 33 AND (t3.f1, t3.f2, t3.f3) IN
|
|
|
(SELECT /*+ BKA(t2) NO_BNL(t1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
|
|
|
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
|
|
|
|
|
|
EXPLAIN DELETE FROM t3
|
|
|
WHERE f1 > 30 AND f1 < 33 AND (t3.f1, t3.f2, t3.f3) IN
|
|
|
(SELECT /*+ QB_NAME(qb1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
|
|
|
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
|
|
|
|
|
|
--echo # Turn off range access. Range access should not be used.
|
|
|
--echo # Turn off BNL. BNL should not be used.
|
|
|
EXPLAIN DELETE /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) NO_BNL(t1@QB1) */ FROM t3
|
|
|
WHERE f1 > 30 AND f1 < 33 AND (t3.f1, t3.f2, t3.f3) IN
|
|
|
(SELECT /*+ QB_NAME(qb1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
|
|
|
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
|
|
|
|
|
|
EXPLAIN INSERT INTO t3(f1, f2, f3)
|
|
|
(SELECT t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
|
|
|
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
|
|
|
|
|
|
--echo # Turn off ICP. ICP should not be used.
|
|
|
EXPLAIN INSERT INTO t3(f1, f2, f3)
|
|
|
(SELECT /*+ NO_ICP(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
|
|
|
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
|
|
|
|
|
|
--echo # Turn off ICP. ICP should not be used.
|
|
|
EXPLAIN INSERT /*+ NO_ICP(t2@QB1 f1) */ INTO t3(f1, f2, f3)
|
|
|
(SELECT /*+ QB_NAME(qb1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
|
|
|
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
|
|
|
|
|
|
EXPLAIN REPLACE INTO t3(f1, f2, f3)
|
|
|
(SELECT t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
|
|
|
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
|
|
|
|
|
|
--echo # Turn off ICP. ICP should not be used.
|
|
|
EXPLAIN REPLACE INTO t3(f1, f2, f3)
|
|
|
(SELECT /*+ NO_ICP(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
|
|
|
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
|
|
|
|
|
|
--echo # Turn off ICP for nonexistent table. ICP should be used.
|
|
|
EXPLAIN REPLACE /*+ NO_ICP(t2@qb1) */ INTO t3(f1, f2, f3)
|
|
|
SELECT /*+ QB_NAME(qb2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
|
|
|
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
--echo # Turn off ICP. ICP should not be used.
|
|
|
EXPLAIN REPLACE /*+ NO_ICP(t2@qb1) */ INTO t3(f1, f2, f3)
|
|
|
SELECT /*+ QB_NAME(qb1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
|
|
|
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
--echo # Misc tests
|
|
|
|
|
|
--echo # Should issue warning
|
|
|
EXPLAIN SELECT /*+ QB_NAME(qb1) QB_NAME(qb1 ) */ * FROM t2;
|
|
|
--echo # Should issue warning
|
|
|
EXPLAIN SELECT /*+ BKA(@qb1) QB_NAME(qb1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
--echo # Should not crash
|
|
|
PREPARE stmt1 FROM "SELECT /*+ BKA(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1";
|
|
|
EXECUTE stmt1;
|
|
|
EXECUTE stmt1;
|
|
|
DEALLOCATE PREPARE stmt1;
|
|
|
|
|
|
--echo # Check use of alias
|
|
|
EXPLAIN SELECT tbl2.f1, tbl2.f2, tbl2.f3 FROM t1 tbl1,t2 tbl2
|
|
|
WHERE tbl1.f1=tbl2.f1 AND tbl2.f2 BETWEEN tbl1.f1 and tbl1.f2 and tbl2.f2 + 1 >= tbl1.f1 + 1;
|
|
|
--echo # Turn on BKA for multiple tables. BKA should be used for tbl2.
|
|
|
EXPLAIN SELECT /*+ BKA(tbl1, tbl2) */ tbl2.f1, tbl2.f2, tbl2.f3 FROM t1 tbl1,t2 tbl2
|
|
|
WHERE tbl1.f1=tbl2.f1 AND tbl2.f2 BETWEEN tbl1.f1 and tbl1.f2 and tbl2.f2 + 1 >= tbl1.f1 + 1;
|
|
|
|
|
|
--echo # Print warnings for nonexistent names
|
|
|
EXPLAIN
|
|
|
SELECT /*+ BKA(t2) NO_BNL(t1) BKA(t3) NO_RANGE_OPTIMIZATION(t3 idx1) NO_RANGE_OPTIMIZATION(t3) */
|
|
|
t2.f1, t2.f2, t2.f3 FROM t1,t2 WHERE t1.f1=t2.f1 AND
|
|
|
t2.f2 BETWEEN t1.f1 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1;
|
|
|
|
|
|
--echo # Check illegal syntax
|
|
|
EXPLAIN SELECT /*+ BKA(qb1 t3@qb1) */ f2 FROM
|
|
|
(SELECT /*+ QB_NAME(qb1) */ f2, f3, f1 FROM t3 WHERE f1 > 2 AND f3 = 'poiu') AS TD
|
|
|
WHERE TD.f1 > 2 AND TD.f3 = 'poiu';
|
|
|
|
|
|
--echo # Check illegal syntax
|
|
|
EXPLAIN SELECT * FROM
|
|
|
(SELECT /*+ QB_NAME(qb1) BKA(@qb1 t1@qb1, t2@qb1, t3) */ t2.f1, t2.f2, t2.f3 FROM t1,t2,t3) tt;
|
|
|
|
|
|
--echo # Check '@qb_name table_name' syntax. BKA should be used for t2.
|
|
|
EXPLAIN SELECT /*+ BKA(@qb1 t2) */ * FROM (SELECT /*+ QB_NAME(QB1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1) AS s1;
|
|
|
|
|
|
--echo # Check that original table name is not recognized if alias is used.
|
|
|
EXPLAIN SELECT * FROM (SELECT /*+ BKA(t2) */ tb2.f1, tb2.f2, tb2.f3 FROM t1 tb1,t2 tb2
|
|
|
WHERE tb1.f1=tb2.f1 AND tb2.f2 BETWEEN tb1.f1 and tb1.f2 and tb2.f2 + 1 >= tb1.f1 + 1) AS s1;
|
|
|
--echo # Table t2 should use BKA.
|
|
|
EXPLAIN SELECT * FROM (SELECT /*+ BKA(tb2) */ tb2.f1, tb2.f2, tb2.f3 FROM t1 tb1,t2 tb2
|
|
|
WHERE tb1.f1=tb2.f1 AND tb2.f2 BETWEEN tb1.f1 and tb1.f2 and tb2.f2 + 1 >= tb1.f1 + 1) AS s1;
|
|
|
|
|
|
--echo # Check that PS and conventional statements give the same result.
|
|
|
FLUSH STATUS;
|
|
|
SELECT /*+ BKA(@qb1 t2) */ * FROM (SELECT /*+ QB_NAME(QB1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1) AS s1;
|
|
|
SHOW STATUS LIKE 'handler_read%';
|
|
|
|
|
|
PREPARE stmt1 FROM "SELECT /*+ BKA(@qb1 t2) */ * FROM (SELECT /*+ QB_NAME(QB1) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
|
|
|
WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1) AS s1";
|
|
|
|
|
|
FLUSH STATUS;
|
|
|
EXECUTE stmt1;
|
|
|
SHOW STATUS LIKE 'handler_read%';
|
|
|
|
|
|
FLUSH STATUS;
|
|
|
EXECUTE stmt1;
|
|
|
SHOW STATUS LIKE 'handler_read%';
|
|
|
|
|
|
DEALLOCATE PREPARE stmt1;
|
|
|
|
|
|
DROP TABLE t1, t2, t3;
|
|
|
|
|
|
--echo # BNL & NO_BNL hint testing
|
|
|
|
|
|
set optimizer_switch=default;
|
|
|
set optimizer_switch='block_nested_loop=on';
|
|
|
|
|
|
CREATE TABLE t1 (a INT, b INT);
|
|
|
INSERT INTO t1 VALUES (1,1),(2,2);
|
|
|
CREATE TABLE t2 (a INT, b INT);
|
|
|
INSERT INTO t2 VALUES (1,1),(2,2);
|
|
|
CREATE TABLE t3 (a INT, b INT);
|
|
|
INSERT INTO t3 VALUES (1,1),(2,2);
|
|
|
|
|
|
--echo # Check statistics without hint
|
|
|
FLUSH STATUS;
|
|
|
SELECT t1.* FROM t1,t2,t3;
|
|
|
SHOW STATUS LIKE 'handler_read%';
|
|
|
|
|
|
--echo # Check statistics with hint
|
|
|
FLUSH STATUS;
|
|
|
SELECT /*+ NO_BNL() */t1.* FROM t1,t2,t3;
|
|
|
SHOW STATUS LIKE 'handler_read%';
|
|
|
|
|
|
EXPLAIN SELECT t1.* FROM t1,t2,t3;
|
|
|
EXPLAIN SELECT /*+ NO_BNL() */t1.* FROM t1,t2,t3;
|
|
|
EXPLAIN SELECT /*+ NO_BNL(t2, t3) */t1.* FROM t1,t2,t3;
|
|
|
EXPLAIN SELECT /*+ NO_BNL(t1, t3) */t1.* FROM t1,t2,t3;
|
|
|
|
|
|
set optimizer_switch='block_nested_loop=off';
|
|
|
|
|
|
EXPLAIN SELECT t1.* FROM t1,t2,t3;
|
|
|
EXPLAIN SELECT /*+ BNL() */t1.* FROM t1,t2,t3;
|
|
|
EXPLAIN SELECT /*+ BNL(t2, t3) */t1.* FROM t1,t2,t3;
|
|
|
EXPLAIN SELECT /*+ BNL(t1, t3) */t1.* FROM t1,t2,t3;
|
|
|
EXPLAIN SELECT /*+ BNL(t2) BNL(t3) */t1.* FROM t1,t2,t3;
|
|
|
|
|
|
DROP TABLE t1, t2, t3;
|
|
|
|
|
|
|
|
|
--echo # BNL in subquery
|
|
|
set optimizer_switch = DEFAULT;
|
|
|
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a));
|
|
|
CREATE TABLE t2 (a INT, INDEX a (a));
|
|
|
CREATE TABLE t3 (a INT, b INT, INDEX a (a,b));
|
|
|
INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
|
|
|
INSERT INTO t2 VALUES (2), (3), (4), (5);
|
|
|
INSERT INTO t3 VALUES (10,3), (20,4), (30,5);
|
|
|
ANALYZE TABLE t1, t2, t3;
|
|
|
|
|
|
SET optimizer_prune_level = 0;
|
|
|
|
|
|
EXPLAIN SELECT /*+ QB_NAME(q) */ * FROM t1 JOIN t2 ON t1.b = t2.a WHERE
|
|
|
t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.b = t4.b);
|
|
|
|
|
|
EXPLAIN SELECT /*+ QB_NAME(q) NO_BNL() */ * FROM t1 JOIN t2 ON t1.b = t2.a WHERE
|
|
|
t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.b = t4.b);
|
|
|
|
|
|
EXPLAIN SELECT /*+ QB_NAME(q) NO_BNL(t1, t2) */ * FROM t1 JOIN t2 ON t1.b = t2.a WHERE
|
|
|
t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.b = t4.b);
|
|
|
|
|
|
EXPLAIN SELECT /*+ QB_NAME(q) NO_BNL(@subq1) */ * FROM t1 JOIN t2 ON t1.b = t2.a WHERE
|
|
|
t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.b = t4.b);
|
|
|
|
|
|
EXPLAIN SELECT /*+ QB_NAME(q) NO_BNL(t4@subq1) */ * FROM t1 JOIN t2 ON t1.b = t2.a WHERE
|
|
|
t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.b = t4.b);
|
|
|
|
|
|
EXPLAIN SELECT /*+ QB_NAME(q) NO_BNL(t3@subq1,t4@subq1) */ * FROM t1 JOIN t2 ON t1.b = t2.a WHERE
|
|
|
t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.b = t4.b);
|
|
|
|
|
|
EXPLAIN SELECT /*+ QB_NAME(q) NO_BNL(@subq1 t3, t4) */ * FROM t1 JOIN t2 ON t1.b = t2.a WHERE
|
|
|
t2.a IN (SELECT /*+ QB_NAME(subq1) */ t3.b FROM t3 JOIN t1 t4 ON t3.b = t4.b);
|
|
|
|
|
|
EXPLAIN SELECT /*+ QB_NAME(q) */ * FROM t1 JOIN t2 ON t1.b = t2.a WHERE
|
|
|
t2.a IN (SELECT /*+ QB_NAME(subq1) NO_BNL(t3, t4) */ t3.b FROM t3 JOIN t1 t4 ON t3.b = t4.b);
|
|
|
|
|
|
SET optimizer_prune_level = DEFAULT;
|
|
|
|
|
|
DROP TABLE t1, t2, t3;
|
|
|
|
|
|
--echo # MRR & NO_MRR hint testing
|
|
|
set optimizer_switch=default;
|
|
|
|
|
|
CREATE TABLE t1
|
|
|
(
|
|
|
f1 int NOT NULL DEFAULT '0',
|
|
|
f2 int NOT NULL DEFAULT '0',
|
|
|
f3 int NOT NULL DEFAULT '0',
|
|
|
INDEX idx1(f2, f3), INDEX idx2(f3)
|
|
|
);
|
|
|
|
|
|
INSERT INTO t1(f1) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
|
|
|
INSERT INTO t1(f2, f3) VALUES (3,4), (3,4);
|
|
|
ANALYZE TABLE t1;
|
|
|
|
|
|
set optimizer_switch='mrr=on,mrr_cost_based=off';
|
|
|
|
|
|
--echo # Check statistics without hint
|
|
|
FLUSH STATUS;
|
|
|
SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
|
|
SHOW STATUS LIKE 'handler_read%';
|
|
|
|
|
|
--echo # Check statistics with hint
|
|
|
FLUSH STATUS;
|
|
|
SELECT /*+ NO_MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
|
|
SHOW STATUS LIKE 'handler_read%';
|
|
|
|
|
|
EXPLAIN SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
|
|
--echo # Turn off MRR. MRR should not be used.
|
|
|
EXPLAIN SELECT /*+ NO_MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
|
|
--echo # Turn off MRR. MRR should not be used.
|
|
|
EXPLAIN SELECT /*+ NO_MRR(t1 idx2) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
|
|
--echo # Turn off MRR for unused key. MRR should be used.
|
|
|
EXPLAIN SELECT /*+ NO_MRR(t1 idx1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
|
|
|
|
|
set optimizer_switch='mrr=off,mrr_cost_based=off';
|
|
|
|
|
|
EXPLAIN SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
|
|
--echo # Turn on MRR. MRR should be used.
|
|
|
EXPLAIN SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
|
|
--echo # Turn on MRR. MRR should be used.
|
|
|
EXPLAIN SELECT /*+ MRR(t1 IDX2) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
|
|
--echo # Turn on MRR for unused key. MRR should not be used.
|
|
|
EXPLAIN SELECT /*+ MRR(t1 idx1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
|
|
|
|
|
set optimizer_switch='mrr=off,mrr_cost_based=on';
|
|
|
|
|
|
EXPLAIN SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
|
|
--echo # Turn on MRR. MRR should be used.
|
|
|
EXPLAIN SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
|
|
--echo # Turn on MRR. MRR should be used.
|
|
|
EXPLAIN SELECT /*+ MRR(t1 idx2) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
|
|
--echo # Turn on MRR for unused key. MRR should not be used.
|
|
|
EXPLAIN SELECT /*+ MRR(t1 IDX1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
|
|
|
|
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
|
--echo # Bug#21205282 CRASH/ASSERTION IN JOIN_CACHE::SET_MATCH_FLAG_IF_NONE WITH NO_BNL HINT
|
|
|
--echo #
|
|
|
|
|
|
CREATE TABLE t(a INT);
|
|
|
INSERT INTO t VALUES (1);
|
|
|
|
|
|
# Test turning off BNL
|
|
|
SET optimizer_switch='block_nested_loop=on';
|
|
|
|
|
|
EXPLAIN SELECT 1 FROM t t1 LEFT JOIN t t2 ON 1 LEFT JOIN (t t3 LEFT JOIN t t4 ON 1) ON 1;
|
|
|
EXPLAIN SELECT /*+ NO_BNL(t1) */ 1 FROM t t1 LEFT JOIN t t2 ON 1 LEFT JOIN (t t3 LEFT JOIN t t4 ON 1) ON 1;
|
|
|
EXPLAIN SELECT /*+ NO_BNL(t2) */ 1 FROM t t1 LEFT JOIN t t2 ON 1 LEFT JOIN (t t3 LEFT JOIN t t4 ON 1) ON 1;
|
|
|
EXPLAIN SELECT /*+ NO_BNL(t3) */ 1 FROM t t1 LEFT JOIN t t2 ON 1 LEFT JOIN (t t3 LEFT JOIN t t4 ON 1) ON 1;
|
|
|
EXPLAIN SELECT /*+ NO_BNL(t4) */ 1 FROM t t1 LEFT JOIN t t2 ON 1 LEFT JOIN (t t3 LEFT JOIN t t4 ON 1) ON 1;
|
|
|
EXPLAIN SELECT /*+ NO_BNL(t3) */ 1 FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON 1 LEFT JOIN t t4 ON 1) ON 1 WHERE 1;
|
|
|
|
|
|
SELECT /*+ NO_BNL(t4) */ 1 FROM t t1 LEFT JOIN t t2 ON 1 LEFT JOIN (t t3 LEFT JOIN t t4 ON 1) ON 1;
|
|
|
SELECT /*+ NO_BNL(t3) */ 1 FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON 1 LEFT JOIN t t4 ON 1) ON 1 WHERE 1;
|
|
|
|
|
|
# Test turning on BNL
|
|
|
SET optimizer_switch='block_nested_loop=off';
|
|
|
|
|
|
EXPLAIN SELECT 1 FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON 1 INNER JOIN t t4 ON 1) ON 1;
|
|
|
EXPLAIN SELECT /*+ BNL(t1) */ 1 FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON 1 INNER JOIN t t4 ON 1) ON 1;
|
|
|
EXPLAIN SELECT /*+ BNL(t2) */ 1 FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON 1 INNER JOIN t t4 ON 1) ON 1;
|
|
|
EXPLAIN SELECT /*+ BNL(t3) */ 1 FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON 1 INNER JOIN t t4 ON 1) ON 1;
|
|
|
EXPLAIN SELECT /*+ BNL(t4) */ 1 FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON 1 INNER JOIN t t4 ON 1) ON 1;
|
|
|
EXPLAIN SELECT /*+ BNL(t2, t3) */ 1 FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON 1 INNER JOIN t t4 ON 1) ON 1;
|
|
|
EXPLAIN SELECT /*+ BNL(t3, t4) */ 1 FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON 1 INNER JOIN t t4 ON 1) ON 1;
|
|
|
EXPLAIN SELECT /*+ BNL(t2, t3, t4) */ 1 FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON 1 INNER JOIN t t4 ON 1) ON 1;
|
|
|
|
|
|
DROP TABLE t;
|
|
|
|
|
|
# Test turning on BKA
|
|
|
CREATE TABLE t(a INT, b INT, KEY k(a));
|
|
|
INSERT INTO t VALUES (1,1);
|
|
|
|
|
|
EXPLAIN SELECT * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 USING(a) LEFT JOIN t t4 USING(a)) USING(a);
|
|
|
EXPLAIN SELECT /*+ BKA(t1) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 USING(a) LEFT JOIN t t4 USING(a)) USING(a);
|
|
|
EXPLAIN SELECT /*+ BKA(t2) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 USING(a) LEFT JOIN t t4 USING(a)) USING(a);
|
|
|
EXPLAIN SELECT /*+ BKA(t3) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 USING(a) LEFT JOIN t t4 USING(a)) USING(a);
|
|
|
EXPLAIN SELECT /*+ BKA(t4) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 USING(a) LEFT JOIN t t4 USING(a)) USING(a);
|
|
|
EXPLAIN SELECT /*+ BKA(t2, t3) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 USING(a) LEFT JOIN t t4 USING(a)) USING(a);
|
|
|
EXPLAIN SELECT /*+ BKA(t2, t4) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 USING(a) LEFT JOIN t t4 USING(a)) USING(a);
|
|
|
EXPLAIN SELECT /*+ BKA(t2, t3, t4) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 USING(a) LEFT JOIN t t4 USING(a)) USING(a);
|
|
|
|
|
|
# Queries with a mix of BKA and BNL
|
|
|
# Turn both on with optimizer_switch
|
|
|
SET optimizer_switch='block_nested_loop=on,batched_key_access=on,mrr_cost_based=off';
|
|
|
|
|
|
EXPLAIN SELECT * FROM t t1 INNER JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b LEFT JOIN t t4 ON t3.b=t4.b) ON t1.a=t2.a;
|
|
|
EXPLAIN SELECT /*+ NO_BKA(t1) */ * FROM t t1 INNER JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b LEFT JOIN t t4 ON t3.b=t4.b) ON t1.a=t2.a;
|
|
|
EXPLAIN SELECT /*+ NO_BKA(t2) */ * FROM t t1 INNER JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b LEFT JOIN t t4 ON t3.b=t4.b) ON t1.a=t2.a;
|
|
|
EXPLAIN SELECT /*+ NO_BNL(t3) */ * FROM t t1 INNER JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b LEFT JOIN t t4 ON t3.b=t4.b) ON t1.a=t2.a;
|
|
|
EXPLAIN SELECT /*+ NO_BNL(t4) */ * FROM t t1 INNER JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b LEFT JOIN t t4 ON t3.b=t4.b) ON t1.a=t2.a;
|
|
|
EXPLAIN SELECT /*+ NO_BKA(t2) NO_BNL(t3) */ * FROM t t1 INNER JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b LEFT JOIN t t4 ON t3.b=t4.b) ON t1.a=t2.a;
|
|
|
EXPLAIN SELECT /*+ NO_BNL(t3) NO_BKA(t4) */ * FROM t t1 INNER JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b LEFT JOIN t t4 ON t3.b=t4.b) ON t1.a=t2.a;
|
|
|
EXPLAIN SELECT /*+ NO_BKA(t2) NO_BNL(t3) NO_BKA(t4) */ * FROM t t1 INNER JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b LEFT JOIN t t4 ON t3.b=t4.b) ON t1.a=t2.a;
|
|
|
|
|
|
# Turn BKA is off, BNL is still on
|
|
|
SET optimizer_switch='batched_key_access=off';
|
|
|
|
|
|
EXPLAIN SELECT * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b INNER JOIN t t4 ON t3.a=t4.a) ON t1.b=t2.b;
|
|
|
EXPLAIN SELECT /*+ BKA(t1) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b INNER JOIN t t4 ON t3.a=t4.a) ON t1.b=t2.b;
|
|
|
EXPLAIN SELECT /*+ NO_BNL(t2) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b INNER JOIN t t4 ON t3.a=t4.a) ON t1.b=t2.b;
|
|
|
EXPLAIN SELECT /*+ BNL(t3) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b INNER JOIN t t4 ON t3.a=t4.a) ON t1.b=t2.b;
|
|
|
EXPLAIN SELECT /*+ BKA(t4) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b INNER JOIN t t4 ON t3.a=t4.a) ON t1.b=t2.b;
|
|
|
EXPLAIN SELECT /*+ BNL(t2, t3) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b INNER JOIN t t4 ON t3.a=t4.a) ON t1.b=t2.b;
|
|
|
EXPLAIN SELECT /*+ BKA(t3, t4) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b INNER JOIN t t4 ON t3.a=t4.a) ON t1.b=t2.b;
|
|
|
|
|
|
# Let both BKA and BNL be off
|
|
|
SET optimizer_switch='block_nested_loop=off';
|
|
|
|
|
|
EXPLAIN SELECT * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b INNER JOIN t t4 ON t3.a=t4.a) ON t1.b=t2.b;
|
|
|
EXPLAIN SELECT /*+ BKA(t1) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b INNER JOIN t t4 ON t3.a=t4.a) ON t1.b=t2.b;
|
|
|
EXPLAIN SELECT /*+ BNL(t2) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b INNER JOIN t t4 ON t3.a=t4.a) ON t1.b=t2.b;
|
|
|
EXPLAIN SELECT /*+ BNL(t3) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b INNER JOIN t t4 ON t3.a=t4.a) ON t1.b=t2.b;
|
|
|
EXPLAIN SELECT /*+ BKA(t4) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b INNER JOIN t t4 ON t3.a=t4.a) ON t1.b=t2.b;
|
|
|
EXPLAIN SELECT /*+ BNL(t2, t3) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b INNER JOIN t t4 ON t3.a=t4.a) ON t1.b=t2.b;
|
|
|
EXPLAIN SELECT /*+ BKA(t3, t4) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b INNER JOIN t t4 ON t3.a=t4.a) ON t1.b=t2.b;
|
|
|
EXPLAIN SELECT /*+ BNL(t2, t3, t4) */ * FROM t t1 LEFT JOIN (t t2 LEFT JOIN t t3 ON t2.b=t3.b INNER JOIN t t4 ON t3.a=t4.a) ON t1.b=t2.b;
|
|
|
|
|
|
DROP TABLE t;
|
|
|
|
|
|
set optimizer_switch=default;
|
|
|
|
|
|
--echo #
|
|
|
--echo # Duplicate hints
|
|
|
--echo #
|
|
|
|
|
|
CREATE TABLE t1 (i INT PRIMARY KEY);
|
|
|
|
|
|
SELECT /*+ BKA() BKA() */ 1;
|
|
|
SELECT /*+ BKA(t1) BKA(t1) */ * FROM t1;
|
|
|
SELECT /*+ QB_NAME(q1) BKA(t1@q1) BKA(t1@q1) */ * FROM t1;
|
|
|
SELECT /*+ QB_NAME(q1) NO_ICP(@q1 t1 PRIMARY) NO_ICP(@q1 t1 PRIMARY) */ * FROM t1;
|
|
|
|
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
|
--echo # Bug#21192857 ASSERTION FAILED: KEYINFO_ARRAY.SIZE() == 0, FILE OPT_HINTS.CC:280
|
|
|
--echo #
|
|
|
|
|
|
CREATE TABLE t1(a INT, KEY(a));
|
|
|
INSERT INTO t1(a) SELECT /*+ NO_RANGE_OPTIMIZATION(t1 a)*/ 1 FROM t1;
|
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo # WL#8016 Parser for optimizer hints
|
|
|
|
|
|
|
|
|
CREATE TABLE t1 (i INT, j INT);
|
|
|
CREATE INDEX i1 ON t1(i);
|
|
|
CREATE INDEX i2 ON t1(j);
|
|
|
|
|
|
--echo
|
|
|
--echo # empty hint comment is ok:
|
|
|
--echo
|
|
|
SELECT /*+*/ 1;
|
|
|
SELECT /*+ */ 1;
|
|
|
SELECT /*+ * ** / // /* */ 1;
|
|
|
SELECT /*+ @ */ 1;
|
|
|
SELECT /*+ @foo */ 1;
|
|
|
SELECT /*+ foo@bar */ 1;
|
|
|
SELECT /*+ foo @bar */ 1;
|
|
|
SELECT /*+ `@` */ 1;
|
|
|
SELECT /*+ `@foo` */ 1;
|
|
|
SELECT /*+ `foo@bar` */ 1;
|
|
|
SELECT /*+ `foo @bar` */ 1;
|
|
|
SELECT /*+ BKA( @) */ 1;
|
|
|
SELECT /*+ BKA( @) */ 1;
|
|
|
SELECT /*+ BKA(t1 @) */ 1;
|
|
|
|
|
|
|
|
|
--echo
|
|
|
--echo # We don't support "*/" inside quoted identifiers (syntax error):
|
|
|
--echo
|
|
|
|
|
|
--error ER_PARSE_ERROR
|
|
|
SELECT /*+ BKA(`test*/`) */ 1;
|
|
|
|
|
|
#
|
|
|
# disabled test because of mysqltest bug #19785832
|
|
|
# see test_wl8016() at mysql_client_test.c for the workaround
|
|
|
#
|
|
|
#--echo # should just warn:
|
|
|
#SELECT /*+ BKA(`test*/ 1;
|
|
|
|
|
|
--echo
|
|
|
--echo # valid hint sequences:
|
|
|
--echo
|
|
|
SELECT /*+ NO_ICP() */ 1;
|
|
|
SELECT /*+NO_ICP()*/ 1;
|
|
|
SELECT /*+ NO_ICP () */ 1;
|
|
|
SELECT /*+ NO_ICP ( ) */ 1;
|
|
|
|
|
|
SELECT /*+ NO_ICP() */ 1 UNION SELECT 1;
|
|
|
(SELECT /*+ NO_ICP() */ 1) UNION (SELECT 1);
|
|
|
((SELECT /* + NO_ICP() */ 1));
|
|
|
EXPLAIN SELECT /*+ QB_NAME(qb1) */ 1 UNION SELECT /*+ QB_NAME(qb2) */ 1;
|
|
|
EXPLAIN (SELECT /*+ QB_NAME(qb1) */ 1) UNION (SELECT /*+ QB_NAME(qb2) */ 1);
|
|
|
|
|
|
UPDATE /*+ NO_ICP() */ t1 SET i = 10;
|
|
|
INSERT /*+ NO_ICP() */ INTO t1 VALUES ();
|
|
|
REPLACE /*+ NO_ICP() */ INTO t1 VALUES ();
|
|
|
DELETE /*+ NO_ICP() */ FROM t1 WHERE 1;
|
|
|
|
|
|
SELECT /*+ BKA(t1) */ 1 FROM t1;
|
|
|
SELECT /*+ BKA(a b) */ 1 FROM t1 a, t1 b;
|
|
|
|
|
|
SELECT /*+ NO_ICP(i1) */ 1 FROM t1;
|
|
|
SELECT /*+ NO_ICP(i1 i2) */ 1 FROM t1;
|
|
|
SELECT /*+ NO_ICP(@qb ident) */ 1 FROM t1;
|
|
|
|
|
|
--echo #
|
|
|
--echo # test explainable statements for hint support:
|
|
|
--echo # they should warn with a hint syntax error near "test */"
|
|
|
--echo #
|
|
|
|
|
|
EXPLAIN SELECT /*+ test */ 1;
|
|
|
EXPLAIN INSERT /*+ test */ INTO t1 VALUES (10, 10);
|
|
|
EXPLAIN REPLACE /*+ test */ INTO t1 VALUES (10, 10);
|
|
|
EXPLAIN UPDATE /*+ test */ t1 SET i = 10 WHERE j = 10;
|
|
|
EXPLAIN DELETE /*+ test */ FROM t1 WHERE i = 10;
|
|
|
|
|
|
--echo
|
|
|
--echo # non-alphabetic and non-ASCII identifiers:
|
|
|
--echo
|
|
|
|
|
|
CREATE INDEX 3rd_index ON t1(i, j);
|
|
|
SELECT /*+ NO_ICP(3rd_index) */ 1 FROM t1;
|
|
|
|
|
|
CREATE INDEX $index ON t1(j, i);
|
|
|
SELECT /*+ NO_ICP($index) */ 1 FROM t1;
|
|
|
|
|
|
CREATE TABLE ` quoted name тест` (i INT);
|
|
|
SELECT /*+ BKA(` quoted name тест`) */ 1 FROM t1;
|
|
|
SELECT /*+ BKA(` quoted name тест`@`select#1`) */ 1 FROM t1;
|
|
|
DROP TABLE ` quoted name тест`;
|
|
|
|
|
|
SET SQL_MODE = 'ANSI_QUOTES';
|
|
|
|
|
|
CREATE TABLE " quoted name тест" (i INT);
|
|
|
SELECT /*+ BKA(" quoted name тест") */ 1 FROM t1;
|
|
|
SELECT /*+ BKA(" quoted name тест"@"select#1") */ 1 FROM t1;
|
|
|
|
|
|
CREATE TABLE `test1``test2``` (i INT);
|
|
|
|
|
|
SELECT /*+ BKA(`test1``test2```) */ 1;
|
|
|
SELECT /*+ BKA("test1""test2""") */ 1;
|
|
|
|
|
|
SET SQL_MODE = '';
|
|
|
--echo # should warn:
|
|
|
SELECT /*+ BKA(" quoted name тест") */ 1 FROM t1;
|
|
|
|
|
|
DROP TABLE ` quoted name тест`;
|
|
|
DROP TABLE `test1``test2```;
|
|
|
|
|
|
EXPLAIN SELECT /*+ QB_NAME(`*`) */ 1;
|
|
|
EXPLAIN SELECT /*+ QB_NAME(`a*`) */ 1;
|
|
|
EXPLAIN SELECT /*+ QB_NAME(`*b`) */ 1;
|
|
|
EXPLAIN SELECT /*+ QB_NAME(`a
|
|
|
b`) */ 1;
|
|
|
|
|
|
--echo # hint syntax error: empty quoted identifier
|
|
|
EXPLAIN SELECT /*+ QB_NAME(``) */ 1;
|
|
|
|
|
|
SET NAMES utf8;
|
|
|
--character_set latin1
|
|
|
EXPLAIN SELECT /*+ QB_NAME(`<EFBFBD>``<EFBFBD>`) */ 1;
|
|
|
--character_set utf8mb4
|
|
|
|
|
|
CREATE TABLE tableТ (i INT);
|
|
|
SELECT /*+ BKA(tableТ) */ 1 FROM t1;
|
|
|
SELECT /*+ BKA(test@tableТ) */ 1 FROM t1;
|
|
|
DROP TABLE tableТ;
|
|
|
|
|
|
CREATE TABLE таблица (i INT);
|
|
|
|
|
|
SELECT /*+ BKA(`таблица`) */ 1 FROM t1;
|
|
|
SELECT /*+ BKA(таблица) */ 1 FROM t1;
|
|
|
SELECT /*+ BKA(test@таблица) */ 1 FROM t1;
|
|
|
|
|
|
--echo # broken multibyte char, should warn:
|
|
|
SELECT /*+ NO_ICP(`<EFBFBD>`) */ 1 FROM t1;
|
|
|
|
|
|
DROP TABLE таблица;
|
|
|
|
|
|
--echo
|
|
|
--echo # derived tables and other subqueries:
|
|
|
--echo
|
|
|
|
|
|
SELECT * FROM (SELECT /*+ DEBUG_HINT3 */ 1) a;
|
|
|
SELECT (SELECT /*+ DEBUG_HINT3 */ 1);
|
|
|
SELECT 1 FROM DUAL WHERE 1 IN (SELECT /*+ DEBUG_HINT3 */ 1);
|
|
|
|
|
|
--echo
|
|
|
--echo # invalid hint sequences (should warn):
|
|
|
--echo
|
|
|
SELECT /*+ 10 */ 1;
|
|
|
SELECT /*+ NO_ICP() */ 1;
|
|
|
SELECT /*+ NO_ICP(10) */ 1;
|
|
|
SELECT /*+ NO_ICP( */ 1;
|
|
|
SELECT /*+ NO_ICP) */ 1;
|
|
|
SELECT /*+ NO_ICP(t1 */ 1;
|
|
|
SELECT /*+ NO_ICP(t1 ( */ 1;
|
|
|
(SELECT 1) UNION (SELECT /*+ NO_ICP() */ 1);
|
|
|
|
|
|
INSERT INTO t1 VALUES (1, 1), (2, 2);
|
|
|
|
|
|
--echo
|
|
|
--echo # wrong place for hint, so recognize that stuff as a regular commentary:
|
|
|
--echo
|
|
|
|
|
|
SELECT 1 FROM /*+ regular commentary, not a hint! */ t1;
|
|
|
SELECT 1 FROM /*+ #1 */ t1 WHERE /*+ #2 */ 1 /*+ #3 */;
|
|
|
|
|
|
SELECT /*+ NO_ICP() */ 1
|
|
|
FROM /*+ regular commentary, not a hint! */ t1;
|
|
|
|
|
|
SELECT /*+ NO_ICP(t1) bad_hint */ 1 FROM t1;
|
|
|
|
|
|
SELECT /*+
|
|
|
NO_ICP(@qb ident)
|
|
|
*/ 1 FROM t1;
|
|
|
|
|
|
SELECT /*+
|
|
|
? bad syntax
|
|
|
*/ 1;
|
|
|
|
|
|
SELECT
|
|
|
/*+ ? bad syntax */ 1;
|
|
|
|
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
|
--echo # Bug #21095608: OPTIMIZER HINT PARSER DOESN'T ACCEPT NUMBER-PREFIXED
|
|
|
--echo # QUERY BLOCK NAMES AFTER @
|
|
|
--echo #
|
|
|
|
|
|
CREATE TABLE t1 (i INT);
|
|
|
EXPLAIN SELECT /*+ QB_NAME(1a) BKA(t1@1a) */ 1 FROM t1;
|
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
|
--echo # Bug #21148405: OPTIMIZER HINTS: READ OF FREE MEMORY FOR INVALID HINTS
|
|
|
--echo #
|
|
|
|
|
|
DELIMITER |;
|
|
|
CREATE PROCEDURE p1()
|
|
|
BEGIN
|
|
|
DECLARE cur1 CURSOR FOR SELECT /*+ NO_MRR(q w)*/1;
|
|
|
OPEN cur1;
|
|
|
END|
|
|
|
DELIMITER ;|
|
|
|
CALL p1();
|
|
|
CALL p1();
|
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
|
|
|
--echo #
|
|
|
--echo # WL#9307 MERGE/NO_MERGE hint for derived table, view
|
|
|
--echo #
|
|
|
|
|
|
create table t1(a int);
|
|
|
|
|
|
explain select * from (select * from t1) as dt;
|
|
|
explain select /*+ merge(dt) */ * from (select * from t1) as dt;
|
|
|
explain select /*+ no_merge(dt) */ * from (select * from t1) as dt;
|
|
|
--echo # Also testing that case of letters is irrelevant.
|
|
|
explain select /*+ no_mERge(dt) */ * from (select * from t1) as dt;
|
|
|
|
|
|
create view v1 as (select * from t1);
|
|
|
explain select * from v1;
|
|
|
explain select /*+ merge(v1) */ * from v1;
|
|
|
explain select /*+ no_merge(v1) */ * from v1;
|
|
|
drop view v1;
|
|
|
|
|
|
--echo # hint is OVERRIDDEN by algorithm=
|
|
|
create algorithm=merge view v1 as (select * from t1);
|
|
|
explain select * from v1;
|
|
|
explain select /*+ merge(v1) */ * from v1;
|
|
|
explain select /*+ no_merge(v1) */ * from v1;
|
|
|
drop view v1;
|
|
|
|
|
|
create algorithm=temptable view v1 as (select * from t1);
|
|
|
explain select * from v1;
|
|
|
explain select /*+ merge(v1) */ * from v1;
|
|
|
explain select /*+ no_merge(v1) */ * from v1;
|
|
|
drop view v1;
|
|
|
|
|
|
--echo # hint OVERRIDES optimizer_switch
|
|
|
|
|
|
set optimizer_switch="derived_merge=off";
|
|
|
|
|
|
explain select * from (select * from t1) as dt;
|
|
|
explain select /*+ merge(dt) */ * from (select * from t1) as dt;
|
|
|
explain select /*+ no_merge(dt) */ * from (select * from t1) as dt;
|
|
|
|
|
|
create view v1 as (select * from t1);
|
|
|
explain select * from v1;
|
|
|
explain select /*+ merge(v1) */ * from v1;
|
|
|
explain select /*+ no_merge(v1) */ * from v1;
|
|
|
drop view v1;
|
|
|
|
|
|
set optimizer_switch=default;
|
|
|
|
|
|
--echo # Can apply to certain derived tables
|
|
|
|
|
|
create table t2(a int, b int);
|
|
|
create table t3 like t2;
|
|
|
|
|
|
explain select /*+ no_merge(dt) merge(dt2) */ *
|
|
|
from (select * from t1) as dt, (select * from t2) as dt2;
|
|
|
|
|
|
--echo # Or to all:
|
|
|
|
|
|
explain select /*+ no_merge() */ *
|
|
|
from (select * from t1) as dt, (select * from t2) as dt2;
|
|
|
|
|
|
--echo # And be specified in outer blocks, with naming:
|
|
|
|
|
|
explain select /*+ no_merge(dt@qb1) merge(dt2@qb1) */ * from t1 where a =
|
|
|
(select /*+ qb_name(qb1) */ 3
|
|
|
from (select * from t1) as dt, (select * from t2) as dt2);
|
|
|
|
|
|
--echo # with another syntax:
|
|
|
|
|
|
explain select /*+ no_merge(@qb1 dt) merge(@qb1 dt2) */ * from t1 where a =
|
|
|
(select /*+ qb_name(qb1) */ 3
|
|
|
from (select * from t1) as dt, (select * from t2) as dt2);
|
|
|
|
|
|
--echo # A hint can list more than one table
|
|
|
|
|
|
explain select /*+ merge(dt2) no_merge(dt,dt3) */ *
|
|
|
from (select * from t1) as dt, (select * from t2) as dt2,
|
|
|
(select * from t3) as dt3;
|
|
|
|
|
|
--echo # Merge hint OVERRIDES heuristics, for example the one which
|
|
|
--echo # materializes when user variables are set.
|
|
|
|
|
|
explain select * from
|
|
|
(
|
|
|
select * from t1
|
|
|
where (1,a,2) = (
|
|
|
select @n:=@n+1, t2.a, sum(t2.b)
|
|
|
from (select @n:=1) as dt, t2
|
|
|
group by t2.a
|
|
|
)
|
|
|
) as dt2
|
|
|
;
|
|
|
|
|
|
explain select /*+ merge(dt2) */ * from
|
|
|
(
|
|
|
select * from t1
|
|
|
where (1,a,2) = (
|
|
|
select @n:=@n+1, t2.a, sum(t2.b)
|
|
|
from (select @n:=1) as dt, t2
|
|
|
group by t2.a
|
|
|
)
|
|
|
) as dt2
|
|
|
;
|
|
|
|
|
|
--echo # ALGORITHM clause overrides heuristics too
|
|
|
|
|
|
let $query_end=
|
|
|
view v1 as select (select t1.a from t1 where t1.a=t2.a) from t2;
|
|
|
eval create $query_end;
|
|
|
explain select * from v1;
|
|
|
drop view v1;
|
|
|
eval create algorithm=merge $query_end;
|
|
|
explain select * from v1;
|
|
|
drop view v1;
|
|
|
|
|
|
--echo # Hint for index is useless and should be ignored
|
|
|
|
|
|
select /*+ no_mrr(dt idx1) */ * from (select 1 from t1 limit 1) dt;
|
|
|
select /*+ no_mrr(dt idx1) */ * from (select 1 from t1) dt;
|
|
|
|
|
|
--echo # Hint for UPDATE
|
|
|
|
|
|
insert into t1 values(1),(2);
|
|
|
create view v1 as
|
|
|
select * from t1 where a <> 0;
|
|
|
|
|
|
let $query_part=t3, v1 set t3.a=v1.a+10 where t3.a-v1.a=0;
|
|
|
|
|
|
delete from t3;
|
|
|
insert into t3 values(1,1),(2,2);
|
|
|
eval explain update $query_part;
|
|
|
eval update $query_part;
|
|
|
select * from t3;
|
|
|
|
|
|
delete from t3;
|
|
|
insert into t3 values(1,1),(2,2);
|
|
|
eval explain update /*+ no_merge(v1) */ $query_part;
|
|
|
eval update /*+ no_merge(v1) */ $query_part;
|
|
|
select * from t3;
|
|
|
|
|
|
--echo # Update v1
|
|
|
|
|
|
let $query_part=t3, v1 set v1.a=t3.a+10 where t3.a-v1.a=0;
|
|
|
|
|
|
delete from t3;
|
|
|
insert into t3 values(1,1),(2,2);
|
|
|
|
|
|
delete from t1;
|
|
|
insert into t1 values(1),(2);
|
|
|
eval explain update $query_part;
|
|
|
eval update $query_part;
|
|
|
select * from t1;
|
|
|
|
|
|
delete from t1;
|
|
|
insert into t1 values(1),(2);
|
|
|
--error ER_NON_UPDATABLE_TABLE
|
|
|
eval update /*+ no_merge(v1) */ $query_part;
|
|
|
select * from t1;
|
|
|
|
|
|
--echo # A derived table in UPDATE
|
|
|
|
|
|
let $query_part=t3, (select * from t1) dt set t3.a=dt.a+10 where t3.a-dt.a=0;
|
|
|
|
|
|
delete from t1;
|
|
|
insert into t1 values(1),(2);
|
|
|
|
|
|
delete from t3;
|
|
|
insert into t3 values(1,1),(2,2);
|
|
|
eval explain update $query_part;
|
|
|
eval update $query_part;
|
|
|
select * from t3;
|
|
|
|
|
|
delete from t3;
|
|
|
insert into t3 values(1,1),(2,2);
|
|
|
eval explain update /*+ no_merge(dt) */ $query_part;
|
|
|
eval update /*+ no_merge(dt) */ $query_part;
|
|
|
select * from t3;
|
|
|
|
|
|
--echo # A derived table in first-level subquery of UPDATE, the update
|
|
|
--echo # target not being in the derived table. Before the WL, the
|
|
|
--echo # derived table would always be materialized; now it's only
|
|
|
--echo # heuristic and can be overridden.
|
|
|
|
|
|
delete from t1;
|
|
|
insert into t1 values(1),(2);
|
|
|
|
|
|
let $query_part=
|
|
|
t3 set b=NULL
|
|
|
where a in (select /*+ qb_name(sub) */ a
|
|
|
from (select * from t1 where a>1) dt);
|
|
|
|
|
|
delete from t3;
|
|
|
insert into t3 values(1,1),(2,2);
|
|
|
eval explain update $query_part;
|
|
|
eval update $query_part;
|
|
|
select * from t3;
|
|
|
|
|
|
delete from t3;
|
|
|
insert into t3 values(1,1),(2,2);
|
|
|
|
|
|
eval explain update /*+ merge(dt@sub) */ $query_part;
|
|
|
eval update /*+ merge(dt@sub) */ $query_part;
|
|
|
select * from t3;
|
|
|
|
|
|
--echo # A derived table in UPDATE, the update target being in the
|
|
|
--echo # derived table.
|
|
|
|
|
|
let $query_part=
|
|
|
t3 set b=NULL
|
|
|
where a in (select /*+ qb_name(sub) */ a
|
|
|
from (select * from t3 where b>1) dt);
|
|
|
|
|
|
delete from t3;
|
|
|
insert into t3 values(1,1),(2,2);
|
|
|
eval explain update $query_part;
|
|
|
eval update $query_part;
|
|
|
select * from t3;
|
|
|
|
|
|
delete from t3;
|
|
|
insert into t3 values(1,1),(2,2);
|
|
|
--echo # The heuristic which materializes, intends to allow the query;
|
|
|
--echo # if you disable it, the query cannot run:
|
|
|
--error ER_UPDATE_TABLE_USED
|
|
|
eval update /*+ merge(dt@sub) */ $query_part;
|
|
|
select * from t3;
|
|
|
|
|
|
--echo # DELETE.
|
|
|
|
|
|
let $query_part=t3.* from t3, v1 where t3.a-v1.a=0;
|
|
|
|
|
|
delete from t3;
|
|
|
insert into t3 values(1,1),(2,2);
|
|
|
eval explain delete $query_part;
|
|
|
eval delete $query_part;
|
|
|
select * from t3;
|
|
|
|
|
|
delete from t3;
|
|
|
insert into t3 values(1,1),(2,2);
|
|
|
eval explain delete /*+ no_merge(v1) */ $query_part;
|
|
|
eval delete /*+ no_merge(v1) */ $query_part;
|
|
|
select * from t3;
|
|
|
|
|
|
drop view v1;
|
|
|
|
|
|
drop table t1,t2,t3;
|
|
|
|
|
|
--echo #
|
|
|
--echo # Unbalanced commentary test
|
|
|
--echo #
|
|
|
|
|
|
--error ER_PARSE_ERROR
|
|
|
SELECT /*+ 10;
|
|
|
# PLEASE DON'T INSERT NEW STUFF TREE: WE ARE TESTING UNBALANCED COMMENTARY
|
|
|
# END OF FILE
|
|
|
|
