hcq/oceanbase
1
0
Fork 0
Code Issues Pull Requests Actions 2 Packages Projects Releases Wiki Activity
Files
generic-master-2025
oceanbase/unittest/sql/optimizer/opt_est/test_row_count.result
oceanbase-admin cea7de1475 init push
2021-05-31 22:56:52 +08:00

459 lines
16 KiB
Plaintext
Raw Permalink Blame History

*************** Case 1 ***************
min = 1001, max = 10000, ndv = 1000, null_num = 500
t1 row count = 10000, t2 row count = 2000
t1 not null frac is (10000-500)/10000 = 0.95
t2 not null frac is (2000-500)/2000 = 0.75
SQL: select /*+ LEADING(t1, t2) USE_HASH(t1, t2) */ t1.c1 from t1, t2 where t1.c1 = t2.c1;
=====================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
-------------------------------------
|0 |HASH JOIN | |14250 |24667|
|1 | TABLE SCAN|t1 |10000 |3818 |
|2 | TABLE SCAN|t2 |2000 |785 |
=====================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1]), filter(nil),
equal_conds([t1.c1 = t2.c1]), other_conds(nil)
1 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0),
is_index_back=false,
range_key([t1.c1]), range(MIN ; MAX)always true
2 - output([t2.c1]), filter(nil),
access([t2.c1]), partitions(p0),
is_index_back=false,
range_key([t2.c1]), range(MIN ; MAX)always true
*************** Case 1(end) **************
*************** Case 2 ***************
min = 1001, max = 10000, ndv = 1000, null_num = 500
t1 row count = 10000, t2 row count = 2000
t1 not null frac is (10000-500)/10000 = 0.95
t2 not null frac is (2000-500)/2000 = 0.75
SQL: select /*+ LEADING(t1, t2) USE_HASH(t1, t2) */ t1.c1 from t1, t2 where t1.c1 = t2.c1 and t1.c1 = 1;
====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
------------------------------------
|0 |HASH JOIN | |2 |1496|
|1 | TABLE GET |t1 |1 |52 |
|2 | TABLE SCAN|t2 |2000 |785 |
====================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1]), filter(nil),
equal_conds([t1.c1 = t2.c1]), other_conds(nil)
1 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0),
is_index_back=false,
range_key([t1.c1]), range[1 ; 1],
range_cond([t1.c1 = 1])
2 - output([t2.c1]), filter(nil),
access([t2.c1]), partitions(p0),
is_index_back=false,
range_key([t2.c1]), range(MIN ; MAX)always true
*************** Case 2(end) **************
*************** Case 3 ***************
min = 1001, max = 10000, ndv = 1000, null_num = 500
t1 row count = 10000, t2 row count = 2000
t1 not null frac is (10000-500)/10000 = 0.95
t2 not null frac is (2000-500)/2000 = 0.75
SQL: select /*+ LEADING(t1, t2) USE_HASH(t1, t2) */ t1.c1 from t1, t2 where t1.c1 = t2.c1 and t1.c1 > 1;
====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
------------------------------------
|0 |HASH JOIN | |144 |1688|
|1 | TABLE SCAN|t1 |100 |52 |
|2 | TABLE SCAN|t2 |2000 |785 |
====================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1]), filter(nil),
equal_conds([t1.c1 = t2.c1]), other_conds(nil)
1 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0),
is_index_back=false,
range_key([t1.c1]), range(1 ; MAX),
range_cond([t1.c1 > 1])
2 - output([t2.c1]), filter(nil),
access([t2.c1]), partitions(p0),
is_index_back=false,
range_key([t2.c1]), range(MIN ; MAX)always true
*************** Case 3(end) **************
*************** Case 4 ***************
min = 1001, max = 10000, ndv = 1000, null_num = 500
t1 row count = 10000, t2 row count = 2000
t1 not null frac is (10000-500)/10000 = 0.95
t2 not null frac is (2000-500)/2000 = 0.75
SQL: select /*+ LEADING(t1, t2) USE_HASH(t1, t2) */ t1.c1 from t1, t2 where t1.c1 = t2.c1 and t2.c1 = 1;
=====================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
-------------------------------------
|0 |HASH JOIN | |10 |14832|
|1 | TABLE SCAN|t1 |10000 |3818 |
|2 | TABLE GET |t2 |1 |52 |
=====================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1]), filter(nil),
equal_conds([t1.c1 = t2.c1]), other_conds(nil)
1 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0),
is_index_back=false,
range_key([t1.c1]), range(MIN ; MAX)always true
2 - output([t2.c1]), filter(nil),
access([t2.c1]), partitions(p0),
is_index_back=false,
range_key([t2.c1]), range[1 ; 1],
range_cond([t2.c1 = 1])
*************** Case 4(end) **************
*************** Case 5 ***************
min = 1001, max = 10000, ndv = 1000, null_num = 500
t1 row count = 10000, t2 row count = 2000
t1 not null frac is (10000-500)/10000 = 0.95
t2 not null frac is (2000-500)/2000 = 0.75
SQL: select /*+ LEADING(t1, t2) USE_HASH(t1, t2) */ t1.c1 from t1, t2 where t1.c1 = t2.c1 and t2.c1 > 1;
=====================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
-------------------------------------
|0 |HASH JOIN | |927 |15408|
|1 | TABLE SCAN|t1 |10000 |3818 |
|2 | TABLE SCAN|t2 |100 |52 |
=====================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1]), filter(nil),
equal_conds([t1.c1 = t2.c1]), other_conds(nil)
1 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0),
is_index_back=false,
range_key([t1.c1]), range(MIN ; MAX)always true
2 - output([t2.c1]), filter(nil),
access([t2.c1]), partitions(p0),
is_index_back=false,
range_key([t2.c1]), range(1 ; MAX),
range_cond([t2.c1 > 1])
*************** Case 5(end) **************
*************** Case 6 ***************
min = 1001, max = 10000, ndv = 1000, null_num = 500
t1 row count = 10000, t2 row count = 2000
t1 not null frac is (10000-500)/10000 = 0.95
t2 not null frac is (2000-500)/2000 = 0.75
SQL: select /*+ LEADING(t1, t2) USE_HASH(t1, t2) */ t1.c1 from t1, t2 where t1.c1 = t2.c1 and t1.c1 = 1 and t2.c1 = 1;
===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |HASH JOIN | |1 |106 |
|1 | TABLE GET|t1 |1 |52 |
|2 | TABLE GET|t2 |1 |52 |
===================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1]), filter(nil),
equal_conds([t1.c1 = t2.c1]), other_conds(nil)
1 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0),
is_index_back=false,
range_key([t1.c1]), range[1 ; 1],
range_cond([t1.c1 = 1])
2 - output([t2.c1]), filter(nil),
access([t2.c1]), partitions(p0),
is_index_back=false,
range_key([t2.c1]), range[1 ; 1],
range_cond([t2.c1 = 1])
*************** Case 6(end) **************
*************** Case 7 ***************
min = 1001, max = 10000, ndv = 1000, null_num = 500
t1 row count = 10000, t2 row count = 2000
t1 not null frac is (10000-500)/10000 = 0.95
t2 not null frac is (2000-500)/2000 = 0.75
SQL: select /*+ LEADING(t1, t2) USE_HASH(t1, t2) */ t1.c1 from t1, t2 where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c1 = 1;
====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
------------------------------------
|0 |HASH JOIN | |1 |215 |
|1 | TABLE SCAN|t1 |100 |52 |
|2 | TABLE GET |t2 |1 |52 |
====================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1]), filter(nil),
equal_conds([t1.c1 = t2.c1]), other_conds(nil)
1 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0),
is_index_back=false,
range_key([t1.c1]), range(1 ; MAX),
range_cond([t1.c1 > 1])
2 - output([t2.c1]), filter(nil),
access([t2.c1]), partitions(p0),
is_index_back=false,
range_key([t2.c1]), range[1 ; 1],
range_cond([t2.c1 = 1])
*************** Case 7(end) **************
*************** Case 8 ***************
min = 1001, max = 10000, ndv = 1000, null_num = 500
t1 row count = 10000, t2 row count = 2000
t1 not null frac is (10000-500)/10000 = 0.95
t2 not null frac is (2000-500)/2000 = 0.75
SQL: select /*+ LEADING(t1, t2) USE_HASH(t1, t2) */ t1.c1 from t1, t2 where t1.c1 = t2.c1 and t1.c1 = 1 and t2.c1 > 1;
====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
------------------------------------
|0 |HASH JOIN | |1 |139 |
|1 | TABLE GET |t1 |1 |52 |
|2 | TABLE SCAN|t2 |100 |52 |
====================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1]), filter(nil),
equal_conds([t1.c1 = t2.c1]), other_conds(nil)
1 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0),
is_index_back=false,
range_key([t1.c1]), range[1 ; 1],
range_cond([t1.c1 = 1])
2 - output([t2.c1]), filter(nil),
access([t2.c1]), partitions(p0),
is_index_back=false,
range_key([t2.c1]), range(1 ; MAX),
range_cond([t2.c1 > 1])
*************** Case 8(end) **************
*************** Case 9 ***************
min = 1001, max = 10000, ndv = 1000, null_num = 500
t1 row count = 10000, t2 row count = 2000
t1 not null frac is (10000-500)/10000 = 0.95
t2 not null frac is (2000-500)/2000 = 0.75
SQL: select /*+ LEADING(t1, t2) USE_HASH(t1, t2) */ t1.c1 from t1, t2 where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c1 > 1;
====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
------------------------------------
|0 |HASH JOIN | |96 |303 |
|1 | TABLE SCAN|t1 |100 |52 |
|2 | TABLE SCAN|t2 |100 |52 |
====================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1]), filter(nil),
equal_conds([t1.c1 = t2.c1]), other_conds(nil)
1 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0),
is_index_back=false,
range_key([t1.c1]), range(1 ; MAX),
range_cond([t1.c1 > 1])
2 - output([t2.c1]), filter(nil),
access([t2.c1]), partitions(p0),
is_index_back=false,
range_key([t2.c1]), range(1 ; MAX),
range_cond([t2.c1 > 1])
*************** Case 9(end) **************
*************** Case 10 ***************
min = 1001, max = 10000, ndv = 1000, null_num = 500
t1 row count = 10000, t2 row count = 2000
t1 not null frac is (10000-500)/10000 = 0.95
t2 not null frac is (2000-500)/2000 = 0.75
SQL: select /*+ LEADING(a, b, c) USE_HASH(a, b, c) */ c.c1 from t1 a, t1 b, t1 c where b.c1 = c.c1;
=========================================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
---------------------------------------------------------
|0 |HASH JOIN | |949995250|762142938|
|1 | NESTED-LOOP JOIN CARTESIAN| |100000000|70773404 |
|2 | TABLE SCAN |a |10000 |3818 |
|3 | MATERIAL | |10000 |5655 |
|4 | TABLE SCAN |b |10000 |3818 |
|5 | TABLE SCAN |c |10000 |3818 |
=========================================================
Outputs & filters:
-------------------------------------
0 - output([c.c1]), filter(nil),
equal_conds([b.c1 = c.c1]), other_conds(nil)
1 - output([b.c1]), filter(nil),
conds(nil), nl_params_(nil)
2 - output([a.c1]), filter(nil),
access([a.c1]), partitions(p0),
is_index_back=false,
range_key([a.c1]), range(MIN ; MAX)always true
3 - output([b.c1]), filter(nil)
4 - output([b.c1]), filter(nil),
access([b.c1]), partitions(p0),
is_index_back=false,
range_key([b.c1]), range(MIN ; MAX)always true
5 - output([c.c1]), filter(nil),
access([c.c1]), partitions(p0),
is_index_back=false,
range_key([c.c1]), range(MIN ; MAX)always true
*************** Case 10(end) **************
*************** Case 11 ***************
min = 1001, max = 10000, ndv = 1000, null_num = 500
t1 row count = 10000, t2 row count = 2000
t1 not null frac is (10000-500)/10000 = 0.95
t2 not null frac is (2000-500)/2000 = 0.75
SQL: select /*+ LEADING(a, b, c) USE_HASH(a, b, c) */ c.c1 from t1 a, t1 b, t1 c where b.c1 = c.c1 and c.c1 = 1;
=========================================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
---------------------------------------------------------
|0 |HASH JOIN | |100000 |198770318|
|1 | NESTED-LOOP JOIN CARTESIAN| |100000000|70773404 |
|2 | TABLE SCAN |a |10000 |3818 |
|3 | MATERIAL | |10000 |5655 |
|4 | TABLE SCAN |b |10000 |3818 |
|5 | TABLE GET |c |1 |52 |
=========================================================
Outputs & filters:
-------------------------------------
0 - output([c.c1]), filter(nil),
equal_conds([b.c1 = c.c1]), other_conds(nil)
1 - output([b.c1]), filter(nil),
conds(nil), nl_params_(nil)
2 - output([a.c1]), filter(nil),
access([a.c1]), partitions(p0),
is_index_back=false,
range_key([a.c1]), range(MIN ; MAX)always true
3 - output([b.c1]), filter(nil)
4 - output([b.c1]), filter(nil),
access([b.c1]), partitions(p0),
is_index_back=false,
range_key([b.c1]), range(MIN ; MAX)always true
5 - output([c.c1]), filter(nil),
access([c.c1]), partitions(p0),
is_index_back=false,
range_key([c.c1]), range[1 ; 1],
range_cond([c.c1 = 1])
*************** Case 11(end) **************
*************** Case 12 ***************
min = 1001, max = 10000, ndv = 1000, null_num = 500
t1 row count = 10000, t2 row count = 2000
t1 not null frac is (10000-500)/10000 = 0.95
t2 not null frac is (2000-500)/2000 = 0.75
SQL: select /*+ LEADING(a, b, c) USE_HASH(a, b, c) */ c.c1 from t1 a, t1 b, t1 c where b.c1 = c.c1 and c.c1 > 1;
=========================================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
---------------------------------------------------------
|0 |HASH JOIN | |9561745 |204381939|
|1 | NESTED-LOOP JOIN CARTESIAN| |100000000|70773404 |
|2 | TABLE SCAN |a |10000 |3818 |
|3 | MATERIAL | |10000 |5655 |
|4 | TABLE SCAN |b |10000 |3818 |
|5 | TABLE SCAN |c |100 |52 |
=========================================================
Outputs & filters:
-------------------------------------
0 - output([c.c1]), filter(nil),
equal_conds([b.c1 = c.c1]), other_conds(nil)
1 - output([b.c1]), filter(nil),
conds(nil), nl_params_(nil)
2 - output([a.c1]), filter(nil),
access([a.c1]), partitions(p0),
is_index_back=false,
range_key([a.c1]), range(MIN ; MAX)always true
3 - output([b.c1]), filter(nil)
4 - output([b.c1]), filter(nil),
access([b.c1]), partitions(p0),
is_index_back=false,
range_key([b.c1]), range(MIN ; MAX)always true
5 - output([c.c1]), filter(nil),
access([c.c1]), partitions(p0),
is_index_back=false,
range_key([c.c1]), range(1 ; MAX),
range_cond([c.c1 > 1])
*************** Case 12(end) **************
*************** Case 13 ***************
min = 1001, max = 10000, ndv = 1000, null_num = 500
t1 row count = 10000, t2 row count = 2000
t1 not null frac is (10000-500)/10000 = 0.95
t2 not null frac is (2000-500)/2000 = 0.75
SQL: select /*+ LEADING(a, b, c) USE_HASH(a, b, c) */ c.c1 from t1 a, t1 b, t1 c where b.c1 = c.c1 and a.c1 = 1 and b.c1 = 1 and c.c1 = 1;
====================================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
----------------------------------------------------
|0 |HASH JOIN | |1 |159 |
|1 | NESTED-LOOP JOIN CARTESIAN| |1 |105 |
|2 | TABLE GET |a |1 |52 |
|3 | TABLE GET |b |1 |52 |
|4 | TABLE GET |c |1 |52 |
====================================================
Outputs & filters:
-------------------------------------
0 - output([c.c1]), filter(nil),
equal_conds([b.c1 = c.c1]), other_conds(nil)
1 - output([b.c1]), filter(nil),
conds(nil), nl_params_(nil)
2 - output([a.c1]), filter(nil),
access([a.c1]), partitions(p0),
is_index_back=false,
range_key([a.c1]), range[1 ; 1],
range_cond([a.c1 = 1])
3 - output([b.c1]), filter(nil),
access([b.c1]), partitions(p0),
is_index_back=false,
range_key([b.c1]), range[1 ; 1],
range_cond([b.c1 = 1])
4 - output([c.c1]), filter(nil),
access([c.c1]), partitions(p0),
is_index_back=false,
range_key([c.c1]), range[1 ; 1],
range_cond([c.c1 = 1])
*************** Case 13(end) **************
Reference in New Issue View Git Blame Copy Permalink