mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-07-28 06:04:59 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/opt_hints_derived_condition_pushdown.result
Dave Gosselin 2ee2e2d0f3 MDEV-36106 New-style hints: [NO_]DERIVED_CONDITION_PUSHDOWN, [NO_]MERGE 
Implements and tests the optimizer hints DERIVED_CONDITION_PUSHDOWN
and NO_DERIVED_CONDITION_PUSHDOWN, table-level hints to enable and
disable, respectively, the condition pushdown for derived tables
which is typically controlled by the condition_pushdown_for_derived
optimizer switch.

Implements and tests the optimizer hints MERGE and NO_MERGE, table-level
hints to enable and disable, respectively, the derived_merge optimization
which is typically controlled by the derived_merge optimizer switch.

Sometimes hints need to be fixed before TABLE instances are available, but
after TABLE_LIST instances have been created (as in the cases of MERGE and
NO_MERGE).  This commit introduces a new function called
fix_hints_for_derived_table to allow early hint fixing for derived tables,
using only a TABLE_LIST instance (so long as such hints are not index-level).
2025-06-18 09:36:10 -04:00

2210 lines
65 KiB
Text
Raw Permalink Blame History

create table t1 (a int, b int, c int);
create table t2 (a int, b int, c int, d decimal);
insert into t1 values
(1,21,345), (1,33,7), (8,33,114), (1,21,500), (1,19,107), (5,14,787),
(8,33,123), (9,10,211), (5,16,207), (1,33,988), (5,27,132), (1,21,104),
(6,20,309), (6,20,315), (1,21,101), (8,33,404), (9,10,800), (1,21,123),
(7,11,708), (6,20,214);
create index t1_a on t1 (a);
insert into t2 values
(2,3,207,207.0000), (1,21,909,12.0000), (7,13,312,406.0000),
(8,64,248,107.0000), (6,20,315,279.3333), (1,19,203,107.0000),
(8,80,800,314.0000), (3,12,231,190.0000), (6,23,303,909.0000);
Warnings:
Note 1265 Data truncated for column 'd' at row 5
create view v1 as select a, b, max(c) as max_c, avg(c) as avg_c from t1
group by a,b having max_c < 707;
create table t3 select 2*seq as a, 2*seq+1 as b from seq_0_to_1000;
CREATE TABLE t4 (a INT, b INT);
INSERT INTO t4 VALUES (1,2),(2,3),(3,4);
create table t5 select seq as i, 10*seq as j from seq_1_to_10;
create view v2 as select * from t5;
create table t6 (a int primary key);
insert into t6 select * from seq_1_to_50;
create view v6 as select a from t6 where a mod 2 = 1;
set @save_optimizer_switch=@@optimizer_switch;
set session optimizer_switch='condition_pushdown_for_derived=on';
explain format=json select /*+ NO_DERIVED_CONDITION_PUSHDOWN(v1) */ * from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a));
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "t2",
"access_type": "ALL",
"loops": 1,
"rows": 9,
"cost": "COST_REPLACED",
"filtered": 100
}
},
{
"block-nl-join": {
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 9,
"rows": 20,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "v1.max_c > 300 or v1.max_c < 135"
},
"buffer_type": "flat",
"buffer_size": "238",
"join_type": "BNL",
"attached_condition": "v1.b = t2.b and v1.max_c > 300 and v1.avg_c > t2.d or v1.a = t2.a and v1.max_c < 135 and v1.max_c < t2.c",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"having_condition": "max_c < 707",
"filesort": {
"sort_key": "t1.a, t1.b",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t1",
"access_type": "ALL",
"loops": 1,
"rows": 20,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
}
]
}
}
select /*+ NO_DERIVED_CONDITION_PUSHDOWN(v1) */ * from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a));
a b max_c avg_c a b c d
1 19 107 107.0000 1 21 909 12
1 19 107 107.0000 1 19 203 107
1 21 500 234.6000 1 21 909 12
6 20 315 279.3333 6 20 315 279
select * from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a));
a b max_c avg_c a b c d
1 19 107 107.0000 1 21 909 12
1 19 107 107.0000 1 19 203 107
1 21 500 234.6000 1 21 909 12
6 20 315 279.3333 6 20 315 279
explain format=json with cte as (
select /*+ QB_NAME(qb1) */ max_c, avg_c from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a)))
select /*+ NO_DERIVED_CONDITION_PUSHDOWN(v1@qb1) */ * from cte;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "t2",
"access_type": "ALL",
"loops": 1,
"rows": 9,
"cost": "COST_REPLACED",
"filtered": 100
}
},
{
"block-nl-join": {
"table": {
"table_name": "<derived3>",
"access_type": "ALL",
"loops": 9,
"rows": 20,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "v1.max_c > 300 or v1.max_c < 135"
},
"buffer_type": "flat",
"buffer_size": "238",
"join_type": "BNL",
"attached_condition": "v1.b = t2.b and v1.max_c > 300 and v1.avg_c > t2.d or v1.a = t2.a and v1.max_c < 135 and v1.max_c < t2.c",
"materialized": {
"query_block": {
"select_id": 3,
"cost": "COST_REPLACED",
"having_condition": "max_c < 707",
"filesort": {
"sort_key": "t1.a, t1.b",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t1",
"access_type": "ALL",
"loops": 1,
"rows": 20,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
}
]
}
}
with cte as (
select /*+ QB_NAME(qb1) */ max_c, avg_c from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a)))
select /*+ NO_DERIVED_CONDITION_PUSHDOWN(v1@qb1) */ * from cte;
max_c avg_c
107 107.0000
107 107.0000
500 234.6000
315 279.3333
with cte as (
select /*+ QB_NAME(qb1) */ max_c, avg_c from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a)))
select * from cte;
max_c avg_c
107 107.0000
107 107.0000
500 234.6000
315 279.3333
explain format=json with recursive cte as (
select max_c, avg_c from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a))
union
select * from cte where max_c < 100
) select /*+ NO_DERIVED_CONDITION_PUSHDOWN(cte) */ * from cte;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 180,
"cost": "COST_REPLACED",
"filtered": 100,
"materialized": {
"query_block": {
"recursive_union": {
"table_name": "<union2,3>",
"access_type": "ALL",
"query_specifications": [
{
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "t2",
"access_type": "ALL",
"loops": 1,
"rows": 9,
"cost": "COST_REPLACED",
"filtered": 100
}
},
{
"block-nl-join": {
"table": {
"table_name": "<derived4>",
"access_type": "ALL",
"loops": 9,
"rows": 20,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "v1.max_c > 300 or v1.max_c < 135"
},
"buffer_type": "flat",
"buffer_size": "238",
"join_type": "BNL",
"attached_condition": "v1.b = t2.b and v1.max_c > 300 and v1.avg_c > t2.d or v1.a = t2.a and v1.max_c < 135 and v1.max_c < t2.c",
"materialized": {
"query_block": {
"select_id": 4,
"cost": "COST_REPLACED",
"having_condition": "max_c < 707",
"filesort": {
"sort_key": "t1.a, t1.b",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t1",
"access_type": "ALL",
"loops": 1,
"rows": 20,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
}
]
}
},
{
"query_block": {
"select_id": 3,
"operation": "UNION",
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 180,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "cte.max_c < 100"
}
}
]
}
}
]
}
}
}
}
}
]
}
}
with recursive cte as (
select max_c, avg_c from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a))
union
select * from cte where max_c < 100
) select /*+ NO_DERIVED_CONDITION_PUSHDOWN(cte) */ * from cte;
max_c avg_c
107 107.0000
500 234.6000
315 279.3333
with recursive cte as (
select max_c, avg_c from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a))
union
select * from cte where max_c < 100
) select * from cte;
max_c avg_c
107 107.0000
500 234.6000
315 279.3333
explain format=json select /*+ NO_DERIVED_CONDITION_PUSHDOWN(dt)*/ * from
(select t3.b as a from t3 group by t3.a) dt where (dt.a=3 or dt.a=5);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dt.a = 3 or dt.a = 5",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"filesort": {
"sort_key": "t3.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
}
]
}
}
select /*+ NO_DERIVED_CONDITION_PUSHDOWN(dt)*/ * from
(select t3.b as a from t3 group by t3.a) dt where (dt.a=3 or dt.a=5);
a
3
5
select * from
(select t3.b as a from t3 group by t3.a) dt where (dt.a=3 or dt.a=5);
a
3
5
"This explain and query shows the result when no hints are specified, for comparison to hint cases in the next three explain and select pairs"
explain format=json select * from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dt.a = 3 or dt.a = 5",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"having_condition": "a = 3 or a = 5",
"filesort": {
"sort_key": "t3.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
},
{
"block-nl-join": {
"table": {
"table_name": "<derived3>",
"access_type": "ALL",
"loops": 1001,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0"
},
"buffer_type": "flat",
"buffer_size": "8Kb",
"join_type": "BNL",
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0",
"materialized": {
"query_block": {
"select_id": 3,
"cost": "COST_REPLACED",
"having_condition": "t3.a MOD 10 <> 0 or t3.a MOD 5 <> 0",
"filesort": {
"sort_key": "t3.b",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t3.a MOD 2 = 0"
}
}
]
}
}
}
}
}
}
]
}
}
select count(*) from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
count(*)
1600
explain format=json select /*+ NO_DERIVED_CONDITION_PUSHDOWN(dt)*/ * from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dt.a = 3 or dt.a = 5",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"filesort": {
"sort_key": "t3.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
},
{
"block-nl-join": {
"table": {
"table_name": "<derived3>",
"access_type": "ALL",
"loops": 1001,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0"
},
"buffer_type": "flat",
"buffer_size": "8Kb",
"join_type": "BNL",
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0",
"materialized": {
"query_block": {
"select_id": 3,
"cost": "COST_REPLACED",
"having_condition": "t3.a MOD 10 <> 0 or t3.a MOD 5 <> 0",
"filesort": {
"sort_key": "t3.b",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t3.a MOD 2 = 0"
}
}
]
}
}
}
}
}
}
]
}
}
select /*+ NO_DERIVED_CONDITION_PUSHDOWN(dt)*/ count(*) from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
count(*)
1600
explain format=json select /*+ NO_DERIVED_CONDITION_PUSHDOWN(du)*/ * from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dt.a = 3 or dt.a = 5",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"having_condition": "a = 3 or a = 5",
"filesort": {
"sort_key": "t3.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
},
{
"block-nl-join": {
"table": {
"table_name": "<derived3>",
"access_type": "ALL",
"loops": 1001,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0"
},
"buffer_type": "flat",
"buffer_size": "8Kb",
"join_type": "BNL",
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0",
"materialized": {
"query_block": {
"select_id": 3,
"cost": "COST_REPLACED",
"filesort": {
"sort_key": "t3.b",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t3.a MOD 2 = 0"
}
}
]
}
}
}
}
}
}
]
}
}
select /*+ NO_DERIVED_CONDITION_PUSHDOWN(du)*/ count(*) from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
count(*)
1600
explain format=json select /*+ NO_DERIVED_CONDITION_PUSHDOWN(dt,du)*/ * from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dt.a = 3 or dt.a = 5",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"filesort": {
"sort_key": "t3.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
},
{
"block-nl-join": {
"table": {
"table_name": "<derived3>",
"access_type": "ALL",
"loops": 1001,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0"
},
"buffer_type": "flat",
"buffer_size": "8Kb",
"join_type": "BNL",
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0",
"materialized": {
"query_block": {
"select_id": 3,
"cost": "COST_REPLACED",
"filesort": {
"sort_key": "t3.b",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t3.a MOD 2 = 0"
}
}
]
}
}
}
}
}
}
]
}
}
select /*+ NO_DERIVED_CONDITION_PUSHDOWN(dt,du)*/ count(*) from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
count(*)
1600
explain format=json SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN(dt) */ * FROM (
SELECT t4.b AS a
FROM t4
GROUP BY t4.a
) dt WHERE (dt.a=2);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 3,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dt.a = 2",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"filesort": {
"sort_key": "t4.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t4",
"access_type": "ALL",
"loops": 1,
"rows": 3,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
}
]
}
}
SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN(dt) */ * FROM (
SELECT t4.b AS a
FROM t4
GROUP BY t4.a
) dt WHERE (dt.a=2);
a
2
SELECT * FROM (
SELECT t4.b AS a
FROM t4
GROUP BY t4.a
) dt WHERE (dt.a=2);
a
2
explain format=json SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN(dt) */ * FROM (
SELECT qb.b AS a
FROM (
select 1*t4.b as b, 1*t4.a as a from t4
) qb
GROUP BY qb.a
) dt WHERE (dt.a=2);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 3,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dt.a = 2",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"filesort": {
"sort_key": "1 * t4.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t4",
"access_type": "ALL",
"loops": 1,
"rows": 3,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
}
]
}
}
SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN(dt) */ * FROM (
SELECT qb.b AS a
FROM (
select 1*t4.b as b, 1*t4.a as a from t4
) qb
GROUP BY qb.a
) dt WHERE (dt.a=2);
a
2
SELECT * FROM (
SELECT qb.b AS a
FROM (
select 1*t4.b as b, 1*t4.a as a from t4
) qb
GROUP BY qb.a
) dt WHERE (dt.a=2);
a
2
explain format=json
select /*+ NO_DERIVED_CONDITION_PUSHDOWN(@qb1 dv6) */ * from
(select /*+ QB_NAME(qb4) */ * from
(select /*+ QB_NAME(qb3) */ * from
(select /*+ QB_NAME(qb2) */ * from
(select /*+ QB_NAME(qb1) */ avg_a from
(select avg(a) as avg_a from v6) dv6
) dv6) dv6) dv6) dv6 where avg_a <> 0;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived6>",
"access_type": "ALL",
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dv6.avg_a <> 0",
"materialized": {
"query_block": {
"select_id": 6,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "t6",
"access_type": "index",
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["a"],
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t6.a MOD 2 = 1",
"using_index": true
}
}
]
}
}
}
}
]
}
}
explain format=json
select /*+ NO_DERIVED_CONDITION_PUSHDOWN(@qb2 dv6) */ * from
(select /*+ QB_NAME(qb4) */ * from
(select /*+ QB_NAME(qb3) */ * from
(select /*+ QB_NAME(qb2) */ * from
(select /*+ QB_NAME(qb1) */ avg_a from
(select avg(a) as avg_a from v6) dv6
) dv6) dv6) dv6) dv6 where avg_a <> 0;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived6>",
"access_type": "ALL",
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dv6.avg_a <> 0",
"materialized": {
"query_block": {
"select_id": 6,
"cost": "COST_REPLACED",
"having_condition": "avg_a <> 0",
"nested_loop": [
{
"table": {
"table_name": "t6",
"access_type": "index",
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["a"],
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t6.a MOD 2 = 1",
"using_index": true
}
}
]
}
}
}
}
]
}
}
explain format=json
select /*+ NO_DERIVED_CONDITION_PUSHDOWN(@qb3 dv6) */ * from
(select /*+ QB_NAME(qb4) */ * from
(select /*+ QB_NAME(qb3) */ * from
(select /*+ QB_NAME(qb2) */ * from
(select /*+ QB_NAME(qb1) */ avg_a from
(select avg(a) as avg_a from v6) dv6
) dv6) dv6) dv6) dv6 where avg_a <> 0;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived6>",
"access_type": "ALL",
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dv6.avg_a <> 0",
"materialized": {
"query_block": {
"select_id": 6,
"cost": "COST_REPLACED",
"having_condition": "avg_a <> 0",
"nested_loop": [
{
"table": {
"table_name": "t6",
"access_type": "index",
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["a"],
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t6.a MOD 2 = 1",
"using_index": true
}
}
]
}
}
}
}
]
}
}
explain format=json
select /*+ NO_DERIVED_CONDITION_PUSHDOWN(@qb4 dv6) */ * from
(select /*+ QB_NAME(qb4) */ * from
(select /*+ QB_NAME(qb3) */ * from
(select /*+ QB_NAME(qb2) */ * from
(select /*+ QB_NAME(qb1) */ avg_a from
(select avg(a) as avg_a from v6) dv6
) dv6) dv6) dv6) dv6 where avg_a <> 0;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived6>",
"access_type": "ALL",
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dv6.avg_a <> 0",
"materialized": {
"query_block": {
"select_id": 6,
"cost": "COST_REPLACED",
"having_condition": "avg_a <> 0",
"nested_loop": [
{
"table": {
"table_name": "t6",
"access_type": "index",
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["a"],
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t6.a MOD 2 = 1",
"using_index": true
}
}
]
}
}
}
}
]
}
}
set session optimizer_switch='condition_pushdown_for_derived=off';
explain format=json select /*+ DERIVED_CONDITION_PUSHDOWN(v1) */ * from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a));
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "t2",
"access_type": "ALL",
"loops": 1,
"rows": 9,
"cost": "COST_REPLACED",
"filtered": 100
}
},
{
"block-nl-join": {
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 9,
"rows": 20,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "v1.max_c > 300 or v1.max_c < 135"
},
"buffer_type": "flat",
"buffer_size": "238",
"join_type": "BNL",
"attached_condition": "v1.b = t2.b and v1.max_c > 300 and v1.avg_c > t2.d or v1.a = t2.a and v1.max_c < 135 and v1.max_c < t2.c",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"having_condition": "max_c < 707 and (max_c > 300 or max_c < 135)",
"filesort": {
"sort_key": "t1.a, t1.b",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t1",
"access_type": "ALL",
"loops": 1,
"rows": 20,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
}
]
}
}
select /*+ DERIVED_CONDITION_PUSHDOWN(v1) */ * from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a));
a b max_c avg_c a b c d
1 19 107 107.0000 1 21 909 12
1 19 107 107.0000 1 19 203 107
1 21 500 234.6000 1 21 909 12
6 20 315 279.3333 6 20 315 279
select * from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a));
a b max_c avg_c a b c d
1 19 107 107.0000 1 21 909 12
1 19 107 107.0000 1 19 203 107
1 21 500 234.6000 1 21 909 12
6 20 315 279.3333 6 20 315 279
explain format=json with cte as (
select /*+ QB_NAME(qb1) */ max_c, avg_c from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a)))
select /*+ DERIVED_CONDITION_PUSHDOWN(v1@qb1) */ * from cte;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "t2",
"access_type": "ALL",
"loops": 1,
"rows": 9,
"cost": "COST_REPLACED",
"filtered": 100
}
},
{
"block-nl-join": {
"table": {
"table_name": "<derived3>",
"access_type": "ALL",
"loops": 9,
"rows": 20,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "v1.max_c > 300 or v1.max_c < 135"
},
"buffer_type": "flat",
"buffer_size": "238",
"join_type": "BNL",
"attached_condition": "v1.b = t2.b and v1.max_c > 300 and v1.avg_c > t2.d or v1.a = t2.a and v1.max_c < 135 and v1.max_c < t2.c",
"materialized": {
"query_block": {
"select_id": 3,
"cost": "COST_REPLACED",
"having_condition": "max_c < 707 and (max_c > 300 or max_c < 135)",
"filesort": {
"sort_key": "t1.a, t1.b",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t1",
"access_type": "ALL",
"loops": 1,
"rows": 20,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
}
]
}
}
with cte as (
select /*+ QB_NAME(qb1) */ max_c, avg_c from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a)))
select /*+ DERIVED_CONDITION_PUSHDOWN(v1@qb1) */ * from cte;
max_c avg_c
107 107.0000
107 107.0000
500 234.6000
315 279.3333
with cte as (
select /*+ QB_NAME(qb1) */ max_c, avg_c from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a)))
select * from cte;
max_c avg_c
107 107.0000
107 107.0000
500 234.6000
315 279.3333
explain format=json with recursive cte as (
select /*+ QB_NAME(qb1) */ max_c, avg_c from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a))
union
select /*+ QB_NAME(qb2) */ * from cte where max_c < 100
) select /*+ DERIVED_CONDITION_PUSHDOWN(v1@qb1) DERIVED_CONDITION_PUSHDOWN(cte) */ * from cte;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 180,
"cost": "COST_REPLACED",
"filtered": 100,
"materialized": {
"query_block": {
"recursive_union": {
"table_name": "<union2,3>",
"access_type": "ALL",
"query_specifications": [
{
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "t2",
"access_type": "ALL",
"loops": 1,
"rows": 9,
"cost": "COST_REPLACED",
"filtered": 100
}
},
{
"block-nl-join": {
"table": {
"table_name": "<derived4>",
"access_type": "ALL",
"loops": 9,
"rows": 20,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "v1.max_c > 300 or v1.max_c < 135"
},
"buffer_type": "flat",
"buffer_size": "238",
"join_type": "BNL",
"attached_condition": "v1.b = t2.b and v1.max_c > 300 and v1.avg_c > t2.d or v1.a = t2.a and v1.max_c < 135 and v1.max_c < t2.c",
"materialized": {
"query_block": {
"select_id": 4,
"cost": "COST_REPLACED",
"having_condition": "max_c < 707 and (max_c > 300 or max_c < 135)",
"filesort": {
"sort_key": "t1.a, t1.b",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t1",
"access_type": "ALL",
"loops": 1,
"rows": 20,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
}
]
}
},
{
"query_block": {
"select_id": 3,
"operation": "UNION",
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 180,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "cte.max_c < 100"
}
}
]
}
}
]
}
}
}
}
}
]
}
}
with recursive cte as (
select /*+ QB_NAME(qb1) */ max_c, avg_c from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a))
union
select /*+ QB_NAME(qb2) */ * from cte where max_c < 100
) select /*+ DERIVED_CONDITION_PUSHDOWN(v1@qb1) DERIVED_CONDITION_PUSHDOWN(cte) */ * from cte;
max_c avg_c
107 107.0000
500 234.6000
315 279.3333
with recursive cte as (
select max_c, avg_c from v1,t2 where
((v1.max_c>300) and (v1.avg_c>t2.d) and (v1.b=t2.b)) or
((v1.max_c<135) and (v1.max_c<t2.c) and (v1.a=t2.a))
union
select * from cte where max_c < 100
) select * from cte;
max_c avg_c
107 107.0000
500 234.6000
315 279.3333
explain format=json select /*+ DERIVED_CONDITION_PUSHDOWN(dt)*/ * from
(select t3.b as a from t3 group by t3.a) dt where (dt.a=3 or dt.a=5);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dt.a = 3 or dt.a = 5",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"having_condition": "a = 3 or a = 5",
"filesort": {
"sort_key": "t3.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
}
]
}
}
select /*+ DERIVED_CONDITION_PUSHDOWN(dt)*/ * from
(select t3.b as a from t3 group by t3.a) dt where (dt.a=3 or dt.a=5);
a
3
5
select * from
(select t3.b as a from t3 group by t3.a) dt where (dt.a=3 or dt.a=5);
a
3
5
explain format=json SELECT /*+ DERIVED_CONDITION_PUSHDOWN(dt) */ * FROM (
SELECT t4.b AS a
FROM t4
GROUP BY t4.a
) dt WHERE (dt.a=2);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 3,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dt.a = 2",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"having_condition": "a = 2",
"filesort": {
"sort_key": "t4.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t4",
"access_type": "ALL",
"loops": 1,
"rows": 3,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
}
]
}
}
SELECT /*+ DERIVED_CONDITION_PUSHDOWN(dt) */ * FROM (
SELECT t4.b AS a
FROM t4
GROUP BY t4.a
) dt WHERE (dt.a=2);
a
2
SELECT * FROM (
SELECT t4.b AS a
FROM t4
GROUP BY t4.a
) dt WHERE (dt.a=2);
a
2
explain format=json SELECT /*+ DERIVED_CONDITION_PUSHDOWN(dt) */ * FROM (
SELECT qb.b AS a
FROM (
select 1*t4.b as b, 1*t4.a as a from t4
) qb
GROUP BY qb.a
) dt WHERE (dt.a=2);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 3,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dt.a = 2",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"having_condition": "b = 2",
"filesort": {
"sort_key": "1 * t4.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t4",
"access_type": "ALL",
"loops": 1,
"rows": 3,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
}
]
}
}
SELECT /*+ DERIVED_CONDITION_PUSHDOWN(dt) */ * FROM (
SELECT qb.b AS a
FROM (
select 1*t4.b as b, 1*t4.a as a from t4
) qb
GROUP BY qb.a
) dt WHERE (dt.a=2);
a
2
SELECT * FROM (
SELECT qb.b AS a
FROM (
select 1*t4.b as b, 1*t4.a as a from t4
) qb
GROUP BY qb.a
) dt WHERE (dt.a=2);
a
2
"This explain and query shows the result when no hints are specified, for comparison to hint cases in the next three explain and select pairs"
explain format=json select * from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dt.a = 3 or dt.a = 5",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"filesort": {
"sort_key": "t3.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
},
{
"block-nl-join": {
"table": {
"table_name": "<derived3>",
"access_type": "ALL",
"loops": 1001,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0"
},
"buffer_type": "flat",
"buffer_size": "8Kb",
"join_type": "BNL",
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0",
"materialized": {
"query_block": {
"select_id": 3,
"cost": "COST_REPLACED",
"filesort": {
"sort_key": "t3.b",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t3.a MOD 2 = 0"
}
}
]
}
}
}
}
}
}
]
}
}
select count(*) from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
count(*)
1600
explain format=json select /*+ DERIVED_CONDITION_PUSHDOWN(dt)*/ * from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dt.a = 3 or dt.a = 5",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"having_condition": "a = 3 or a = 5",
"filesort": {
"sort_key": "t3.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
},
{
"block-nl-join": {
"table": {
"table_name": "<derived3>",
"access_type": "ALL",
"loops": 1001,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0"
},
"buffer_type": "flat",
"buffer_size": "8Kb",
"join_type": "BNL",
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0",
"materialized": {
"query_block": {
"select_id": 3,
"cost": "COST_REPLACED",
"filesort": {
"sort_key": "t3.b",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t3.a MOD 2 = 0"
}
}
]
}
}
}
}
}
}
]
}
}
select /*+ DERIVED_CONDITION_PUSHDOWN(dt)*/ count(*) from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
count(*)
1600
explain format=json select /*+ DERIVED_CONDITION_PUSHDOWN(du)*/ * from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dt.a = 3 or dt.a = 5",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"filesort": {
"sort_key": "t3.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
},
{
"block-nl-join": {
"table": {
"table_name": "<derived3>",
"access_type": "ALL",
"loops": 1001,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0"
},
"buffer_type": "flat",
"buffer_size": "8Kb",
"join_type": "BNL",
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0",
"materialized": {
"query_block": {
"select_id": 3,
"cost": "COST_REPLACED",
"having_condition": "t3.a MOD 10 <> 0 or t3.a MOD 5 <> 0",
"filesort": {
"sort_key": "t3.b",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t3.a MOD 2 = 0"
}
}
]
}
}
}
}
}
}
]
}
}
select /*+ DERIVED_CONDITION_PUSHDOWN(du)*/ count(*) from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
count(*)
1600
explain format=json select /*+ DERIVED_CONDITION_PUSHDOWN(dt,du)*/ * from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dt.a = 3 or dt.a = 5",
"materialized": {
"query_block": {
"select_id": 2,
"cost": "COST_REPLACED",
"having_condition": "a = 3 or a = 5",
"filesort": {
"sort_key": "t3.a",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100
}
}
]
}
}
}
}
}
},
{
"block-nl-join": {
"table": {
"table_name": "<derived3>",
"access_type": "ALL",
"loops": 1001,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0"
},
"buffer_type": "flat",
"buffer_size": "8Kb",
"join_type": "BNL",
"attached_condition": "du.a MOD 10 <> 0 or du.a MOD 5 <> 0",
"materialized": {
"query_block": {
"select_id": 3,
"cost": "COST_REPLACED",
"having_condition": "t3.a MOD 10 <> 0 or t3.a MOD 5 <> 0",
"filesort": {
"sort_key": "t3.b",
"temporary_table": {
"nested_loop": [
{
"table": {
"table_name": "t3",
"access_type": "ALL",
"loops": 1,
"rows": 1001,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t3.a MOD 2 = 0"
}
}
]
}
}
}
}
}
}
]
}
}
select /*+ DERIVED_CONDITION_PUSHDOWN(dt,du)*/ count(*) from (
select t3.b as a from t3 group by t3.a
) dt join (
select t3.a as a, t3.b as b from t3 where t3.a % 2 = 0 group by t3.b
) du where (dt.a=3 or dt.a=5) and (du.a % 10 <> 0 or du.a % 5 <> 0);
count(*)
1600
explain format=json
select /*+ DERIVED_CONDITION_PUSHDOWN(@qb1 dv6) */ * from
(select /*+ QB_NAME(qb4) */ * from
(select /*+ QB_NAME(qb3) */ * from
(select /*+ QB_NAME(qb2) */ * from
(select /*+ QB_NAME(qb1) */ avg_a from
(select avg(a) as avg_a from v6) dv6
) dv6) dv6) dv6) dv6 where avg_a <> 0;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived6>",
"access_type": "ALL",
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dv6.avg_a <> 0",
"materialized": {
"query_block": {
"select_id": 6,
"cost": "COST_REPLACED",
"having_condition": "avg_a <> 0",
"nested_loop": [
{
"table": {
"table_name": "t6",
"access_type": "index",
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["a"],
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t6.a MOD 2 = 1",
"using_index": true
}
}
]
}
}
}
}
]
}
}
explain format=json
select /*+ DERIVED_CONDITION_PUSHDOWN(@qb2 dv6) */ * from
(select /*+ QB_NAME(qb4) */ * from
(select /*+ QB_NAME(qb3) */ * from
(select /*+ QB_NAME(qb2) */ * from
(select /*+ QB_NAME(qb1) */ avg_a from
(select avg(a) as avg_a from v6) dv6
) dv6) dv6) dv6) dv6 where avg_a <> 0;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived6>",
"access_type": "ALL",
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dv6.avg_a <> 0",
"materialized": {
"query_block": {
"select_id": 6,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "t6",
"access_type": "index",
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["a"],
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t6.a MOD 2 = 1",
"using_index": true
}
}
]
}
}
}
}
]
}
}
explain format=json
select /*+ DERIVED_CONDITION_PUSHDOWN(@qb3 dv6) */ * from
(select /*+ QB_NAME(qb4) */ * from
(select /*+ QB_NAME(qb3) */ * from
(select /*+ QB_NAME(qb2) */ * from
(select /*+ QB_NAME(qb1) */ avg_a from
(select avg(a) as avg_a from v6) dv6
) dv6) dv6) dv6) dv6 where avg_a <> 0;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived6>",
"access_type": "ALL",
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dv6.avg_a <> 0",
"materialized": {
"query_block": {
"select_id": 6,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "t6",
"access_type": "index",
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["a"],
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t6.a MOD 2 = 1",
"using_index": true
}
}
]
}
}
}
}
]
}
}
explain format=json
select /*+ DERIVED_CONDITION_PUSHDOWN(@qb4 dv6) */ * from
(select /*+ QB_NAME(qb4) */ * from
(select /*+ QB_NAME(qb3) */ * from
(select /*+ QB_NAME(qb2) */ * from
(select /*+ QB_NAME(qb1) */ avg_a from
(select avg(a) as avg_a from v6) dv6
) dv6) dv6) dv6) dv6 where avg_a <> 0;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "<derived6>",
"access_type": "ALL",
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "dv6.avg_a <> 0",
"materialized": {
"query_block": {
"select_id": 6,
"cost": "COST_REPLACED",
"nested_loop": [
{
"table": {
"table_name": "t6",
"access_type": "index",
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["a"],
"loops": 1,
"rows": 50,
"cost": "COST_REPLACED",
"filtered": 100,
"attached_condition": "t6.a MOD 2 = 1",
"using_index": true
}
}
]
}
}
}
}
]
}
}
set optimizer_switch=@save_optimizer_switch;
create table t7(a int);
insert into t7 values (1),(2),(3);
explain extended select /*+ NO_MERGE(t) NO_ICP(t key1) */ * from (select * from t7) t;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00
2 DERIVED t7 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Warning 4222 Unresolved index name `t`@`select#1` `key1` for NO_ICP hint
Note 1003 /* select#1 */ select /*+ NO_MERGE(`t`@`select#1`) */ `t`.`a` AS `a` from (/* select#2 */ select `test`.`t7`.`a` AS `a` from `test`.`t7`) `t`
explain extended select /*+ NO_MERGE(t) NO_ICP(t) */ * from (select * from t7) t;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00
2 DERIVED t7 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 /* select#1 */ select /*+ NO_ICP(`t`@`select#1`) NO_MERGE(`t`@`select#1`) */ `t`.`a` AS `a` from (/* select#2 */ select `test`.`t7`.`a` AS `a` from `test`.`t7`) `t`
drop view v1, v2, v6;
drop table t1, t2, t3, t4, t5, t6, t7;
Reference in a new issue View git blame Copy permalink