mirror of
https://github.com/MariaDB/server.git
synced 2025-11-30 17:39:38 +01:00
049ee29e7e
Query blocks have implicit names, such as `select#1`, formulated
by appending their select number to the string `select#`. This patch
allows hints to scope their applicability by implicit query block
name. For example,
SELECT /*+ JOIN_ORDER(@`select#2` t1, t2) */ ...
@`select#2` is an implicit query block name. Users can control hint
applicability per query block without first naming the blocks with
QB_NAME().
Hints may now be specified within VIEWs during their creation and
they are applied locally within that VIEW's scope. For example,
CREATE VIEW v1 AS
SELECT /*+ IGNORE_INDEX(t1 idx1) */ FROM t1 ... GROUP BY ... HAVING ...
In many cases and for some parts of the VIEW, the query plan
doesn't really depend on how the VIEW is used, so it makes sense
to control a part of the query plan from the VIEW definition.
Implicit names are not yet supported in VIEWs. Attempting to create
a VIEW with an implicit name reference will cause the server to create
the VIEW, but it will emit a warning and exclude that hint from the query.
525 lines
16 KiB
Text
525 lines
16 KiB
Text
--echo #
|
|
--echo # MDEV-36092 New-style hint: [NO_]SPLIT_MATERIALIZED
|
|
--echo #
|
|
|
|
--source include/have_innodb.inc
|
|
--source include/have_sequence.inc
|
|
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
|
|
--echo #
|
|
--echo # case 1
|
|
--echo #
|
|
create table t1 (
|
|
n1 int(10) not null,
|
|
n2 int(10) not null,
|
|
c1 char(1) not null,
|
|
key c1 (c1),
|
|
key n1_c1_n2 (n1,c1,n2)
|
|
) engine=innodb charset=latin1;
|
|
insert into t1 values (0, 2, 'a'), (1, 3, 'a');
|
|
insert into t1 select seq+1,seq+2,'c' from seq_1_to_1000;
|
|
|
|
analyze table t1;
|
|
|
|
--echo # default showing lateral derived optimization in use
|
|
explain
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
|
|
--echo # cases where hint disables lateral derived optimization
|
|
explain
|
|
select /*+ no_split_materialized(t) */ t1.n1 from t1,
|
|
(select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
|
|
select /*+ no_split_materialized(t) */ t1.n1 from t1,
|
|
(select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
|
|
--echo # query having two subqueries, both of which are subject to LATERAL DERIVED optimization
|
|
--echo # explain and result without hints
|
|
explain
|
|
with cte as (
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select * from cte;
|
|
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select * from cte;
|
|
|
|
--echo # explain and result with first half of query disabling the optimization
|
|
explain
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ NO_SPLIT_MATERIALIZED(t@qb1) */ * from cte;
|
|
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ NO_SPLIT_MATERIALIZED(t@qb1) */ * from cte;
|
|
|
|
--echo # explain and result with second half of query disabling the optimization
|
|
explain
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ NO_SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ NO_SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
|
|
--echo # explain and result with both halves of query disabling the optimization
|
|
explain
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ NO_SPLIT_MATERIALIZED(t@qb1) NO_SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ NO_SPLIT_MATERIALIZED(t@qb1) NO_SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
|
|
--echo # test opposite way, where hint enables the optimization instead of disabling it
|
|
set optimizer_switch='split_materialized=off';
|
|
|
|
--echo # default showing lateral derived optimization not used
|
|
explain
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
|
|
--echo # cases where hint enables lateral derived optimization
|
|
explain
|
|
select /*+ split_materialized(t) */ t1.n1 from t1,
|
|
(select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
|
|
select /*+ split_materialized(t) */ t1.n1 from t1,
|
|
(select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
|
|
--echo # query having two subqueries, both of which are subject to LATERAL DERIVED optimization
|
|
--echo # explain and result without hints
|
|
explain
|
|
with cte as (
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select * from cte;
|
|
|
|
with cte as (
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select * from cte;
|
|
|
|
--echo # explain and result with first half of query enabling the optimization
|
|
explain
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ SPLIT_MATERIALIZED(t@qb1) */ * from cte;
|
|
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ SPLIT_MATERIALIZED(t@qb1) */ * from cte;
|
|
|
|
--echo # explain and result with second half of query enabling the optimization
|
|
explain
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
|
|
--echo # explain and result with both halves of query enabling the optimization
|
|
explain
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ SPLIT_MATERIALIZED(t@qb1) SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ SPLIT_MATERIALIZED(t@qb1) SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
drop table t1;
|
|
--echo #
|
|
--echo # end case 1
|
|
--echo #
|
|
|
|
--echo #
|
|
--echo # case 2
|
|
--echo #
|
|
# 5 values
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
|
|
create table t1(a int, b int);
|
|
insert into t1 select seq,seq from seq_1_to_5;
|
|
|
|
# 5 value groups of size 2 each
|
|
create table t2(a int, b int, key(a));
|
|
insert into t2
|
|
select a.seq,b.seq from seq_1_to_25 a, seq_1_to_2 b;
|
|
|
|
# 5 value groups of size 3 each
|
|
create table t3(a int, b int, key(a));
|
|
insert into t3
|
|
select a.seq,b.seq from seq_1_to_5 a, seq_1_to_3 b;
|
|
|
|
analyze table t1,t2,t3 persistent for all;
|
|
|
|
create table t10 (
|
|
grp_id int,
|
|
col1 int,
|
|
key(grp_id)
|
|
);
|
|
|
|
# 100 groups of 100 values each
|
|
insert into t10
|
|
select
|
|
a.seq,
|
|
b.seq
|
|
from
|
|
seq_1_to_100 a,
|
|
seq_1_to_100 b;
|
|
|
|
# and x10 multiplier
|
|
|
|
create table t11 (
|
|
col1 int,
|
|
col2 int
|
|
);
|
|
insert into t11
|
|
select a.seq, a.seq from seq_1_to_10 a;
|
|
|
|
analyze table t10,t11 persistent for all;
|
|
|
|
create table t21 (pk int primary key);
|
|
insert into t21 values (1),(2),(3);
|
|
|
|
create table t22 (pk int primary key);
|
|
insert into t22 values (1),(2),(3);
|
|
|
|
--echo # default showing lateral derived optimization in use
|
|
explain
|
|
select * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
|
|
select * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
|
|
--echo # cases where hint disables lateral derived optimization
|
|
explain
|
|
select /*+ no_split_materialized(t) */ * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
|
|
select /*+ no_split_materialized(t) */ * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
|
|
--echo # test opposite way, where hint enables the optimization instead of disabling it
|
|
set optimizer_switch='split_materialized=off';
|
|
|
|
--echo # default showing lateral derived optimization not used
|
|
explain
|
|
select * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
|
|
select * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
|
|
--echo # cases where hint enables lateral derived optimization
|
|
explain
|
|
select /*+ split_materialized(t) */ * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
|
|
select /*+ split_materialized(t) */ * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
drop table t1, t2, t3, t10, t11, t21, t22;
|
|
--echo #
|
|
--echo # end case 2
|
|
--echo #
|
|
|
|
--echo #
|
|
--echo # case 3
|
|
--echo #
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
|
|
create table t1 (a char(1)) engine=myisam;
|
|
insert into t1 values ('1'),('2');
|
|
create table t2 (b int, key(b)) engine=myisam;
|
|
alter table t2 disable keys;
|
|
insert into t2 values (1),(2),(3);
|
|
alter table t2 enable keys;
|
|
create table t3 (c int) engine=myisam;
|
|
insert into t3 (c) select seq from seq_1_to_101;
|
|
|
|
--echo # default showing lateral derived optimization in use
|
|
explain
|
|
select * from t1 where t1.a in (
|
|
select b from (
|
|
select t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
|
|
select * from t1 where t1.a in (
|
|
select b from (
|
|
select t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
|
|
--echo # cases where hint disables lateral derived optimization
|
|
explain
|
|
select /*+ no_split_materialized(@qb1 sq) */ * from t1 where t1.a in (
|
|
select /*+ qb_name(qb1) */ b from (
|
|
select /*+ qb_name(qb2) */ t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
|
|
select /*+ no_split_materialized(@qb1 sq) */ * from t1 where t1.a in (
|
|
select /*+ qb_name(qb1) */ b from (
|
|
select /*+ qb_name(qb2) */ t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
|
|
--echo # test opposite way, where hint enables the optimization instead of disabling it
|
|
set optimizer_switch='split_materialized=off';
|
|
|
|
--echo # default showing lateral derived optimization not used
|
|
explain
|
|
select * from t1 where t1.a in (
|
|
select b from (
|
|
select t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
|
|
select * from t1 where t1.a in (
|
|
select b from (
|
|
select t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
|
|
--echo # cases where hint enables lateral derived optimization
|
|
explain select /*+ split_materialized(@qb1 sq) */ * from t1 where t1.a in (
|
|
select /*+ qb_name(qb1) */ b from (
|
|
select /*+ qb_name(qb2) */ t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
|
|
select /*+ split_materialized(@qb1 sq) */ * from t1 where t1.a in (
|
|
select /*+ qb_name(qb1) */ b from (
|
|
select /*+ qb_name(qb2) */ t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
drop table t1, t2, t3;
|
|
--echo #
|
|
--echo # end case 3
|
|
--echo #
|
|
|
|
--echo #
|
|
--echo # case 4
|
|
--echo #
|
|
create table one_k(a int);
|
|
insert into one_k select seq from seq_1_to_1000;
|
|
|
|
CREATE TABLE t1000 (
|
|
grp int(11) DEFAULT NULL,
|
|
val int(11) DEFAULT NULL,
|
|
KEY grp (grp)
|
|
);
|
|
|
|
insert into t1000 select A.seq, B.seq from seq_1_to_100 A, seq_1_to_10 B;
|
|
analyze table t1000;
|
|
|
|
--echo # force lateral derived when optimizer otherwise wouldn't use it
|
|
explain
|
|
select *
|
|
from
|
|
one_k T1, (select grp, count(*) from t1000 group by grp) TBL where TBL.grp=T1.a;
|
|
|
|
explain
|
|
select /*+ SPLIT_MATERIALIZED(TBL) */ *
|
|
from
|
|
one_k T1, (select grp, count(*) from t1000 group by grp) TBL where TBL.grp=T1.a;
|
|
|
|
drop table one_k, t1000;
|
|
--echo #
|
|
--echo # end case 4
|
|
--echo #
|
|
|
|
--echo #
|
|
--echo # End 12.1 tests
|
|
--echo #
|