131 lines
2.9 KiB
MySQL
131 lines
2.9 KiB
MySQL
DROP TABLE badly_correlated1;
|
|
DROP TABLE badly_correlated2;
|
|
|
|
CREATE TABLE badly_correlated1 (id, a, b, c, d, e, f, g, h, val) AS (
|
|
SELECT rownum id, v.* FROM (
|
|
SELECT
|
|
mod(rownum, 100000) a
|
|
, mod(rownum, 100000) b
|
|
, mod(rownum, 100000) c
|
|
, mod(rownum, 100000) d
|
|
, mod(rownum, 100000) e
|
|
, mod(rownum, 100000) f
|
|
, mod(rownum, 100000) g
|
|
, mod(rownum, 100000) h
|
|
, lpad('x',100,'x')
|
|
FROM
|
|
dual CONNECT BY LEVEL <= 100000
|
|
UNION ALL
|
|
SELECT
|
|
90 a
|
|
, 91 b
|
|
, 92 c
|
|
, 93 d
|
|
, 94 e
|
|
, 95 f
|
|
, 96 g
|
|
, 97 h
|
|
, lpad('y',100,'y')
|
|
FROM
|
|
dual CONNECT BY LEVEL <= 100000
|
|
) v
|
|
)
|
|
/
|
|
|
|
CREATE TABLE badly_correlated2 AS SELECT * FROM badly_correlated1;
|
|
|
|
ALTER TABLE badly_correlated1 MODIFY id PRIMARY KEY;
|
|
ALTER TABLE badly_correlated2 MODIFY id PRIMARY KEY;
|
|
|
|
CREATE INDEX idx1_badly_correlated1 ON badly_correlated1 (a,b,c,d,e,f,g);
|
|
CREATE INDEX idx1_badly_correlated2 ON badly_correlated2 (a,b,c,d,e,f,g);
|
|
|
|
EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'BADLY_CORRELATED1', method_opt=>'FOR TABLE', cascade=>true);
|
|
EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'BADLY_CORRELATED2', method_opt=>'FOR TABLE', cascade=>true);
|
|
|
|
|
|
select /*+ opt_param('_optimizer_use_feedback', 'false') */
|
|
*
|
|
from
|
|
badly_correlated1 t1
|
|
, badly_correlated2 t2
|
|
where
|
|
t1.id = t2.id
|
|
and t1.a = 90
|
|
and t1.b = 91
|
|
and t1.c = 92
|
|
and t1.d = 93
|
|
and t1.e = 94
|
|
and t1.f = 95
|
|
and t1.g = 96
|
|
and t1.h = 97
|
|
and t2.val like 'xy%'
|
|
/
|
|
|
|
@x
|
|
|
|
EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'BADLY_CORRELATED1', method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE 254', cascade=>true);
|
|
EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'BADLY_CORRELATED2', method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE 254', cascade=>true);
|
|
|
|
select /*+ opt_param('_optimizer_use_feedback', 'false') */
|
|
*
|
|
from
|
|
badly_correlated1 t1
|
|
, badly_correlated2 t2
|
|
where
|
|
t1.id = t2.id
|
|
and t1.a = 90
|
|
and t1.b = 91
|
|
and t1.c = 92
|
|
and t1.d = 93
|
|
and t1.e = 94
|
|
and t1.f = 95
|
|
and t1.g = 96
|
|
and t1.h = 97
|
|
and t2.val like 'xy%'
|
|
/
|
|
|
|
@x
|
|
|
|
-- create extended stats
|
|
select
|
|
dbms_stats.create_extended_stats(
|
|
ownname => user
|
|
, tabname=>'BADLY_CORRELATED1'
|
|
, extension=>'(a,b,c,d,e,f,g,h)'
|
|
)
|
|
from dual
|
|
/
|
|
|
|
select
|
|
dbms_stats.create_extended_stats(
|
|
ownname => user
|
|
, tabname=>'BADLY_CORRELATED2'
|
|
, extension=>'(a,b,c,d,e,f,g,h)'
|
|
)
|
|
from dual
|
|
/
|
|
|
|
EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'BADLY_CORRELATED1', method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE 254', cascade=>true);
|
|
EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'BADLY_CORRELATED2', method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE 254', cascade=>true);
|
|
|
|
select /*+ opt_param('_optimizer_use_feedback', 'false') */
|
|
*
|
|
from
|
|
badly_correlated1 t1
|
|
, badly_correlated2 t2
|
|
where
|
|
t1.id = t2.id
|
|
and t1.a = 90
|
|
and t1.b = 91
|
|
and t1.c = 92
|
|
and t1.d = 93
|
|
and t1.e = 94
|
|
and t1.f = 95
|
|
and t1.g = 96
|
|
and t1.h = 97
|
|
and t2.val like 'xy%'
|
|
/
|
|
|
|
@x
|