有幸今天在OB官网实战新特性，特记录如下，如有雷同，纯属无意。
[root@iZbp13o5257vco612aeutuZ ~]# obclient -h127.0.0.1 -P2881 -utest@sys -p -Dtes
tdb -A
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221487621
Server version: OceanBase_CE 4.2.1.0 (r100000102023092807-7b0f43693565654bb1d7343f728bc2013dfff959) (Built Sep 28 2023 07:25:28)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [testdb]> create table t1(c1 int, c2 int, c3 int);
Query OK, 0 rows affected (0.064 sec)
obclient [testdb]> create table t2(c1 int, c2 int, c3 int);
Query OK, 0 rows affected (0.032 sec)
obclient [testdb]> create index idx_c1 on t2(c1);
Query OK, 0 rows affected (0.278 sec)
obclient [testdb]> set ob_query_timeout = 100000000;
Query OK, 0 rows affected (0.000 sec)
obclient [testdb]> create sequence s1 cache 1000000 noorder;
Query OK, 0 rows affected (0.022 sec)
obclient [testdb]> select version();
+------------------------------+
| version() |
+------------------------------+
| 5.7.25-OceanBase_CE-v4.2.1.0 |
+------------------------------+
1 row in set (0.001 sec)
obclient [testdb]> insert into t1 select uniform(1,1000000,RANDOM(7)),s1.nextval,uniform(1,1000000,RANDOM(7)) from table(generator(1000000));
Query OK, 1000000 rows affected (10.240 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
obclient [testdb]>
obclient [testdb]> insert into t2 select uniform(1,1000000,RANDOM(7)),uniform(1,1000000,RANDOM(7)),uniform(1,1000000,RANDOM(7)) from table(generator(1000000));
Query OK, 1000000 rows affected (27.838 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
obclient [testdb]> set optimizer_dynamic_sampling = 0;
Query OK, 0 rows affected (0.000 sec)
obclient [testdb]> select /*+ case1 */* from t1, t2 where t1.c1 = t2.c1 and t1.c2 = 1000000;
+--------+---------+--------+--------+--------+--------+
| c1 | c2 | c3 | c1 | c2 | c3 |
+--------+---------+--------+--------+--------+--------+
| 576793 | 1000000 | 576793 | 576793 | 576793 | 576793 |
+--------+---------+--------+--------+--------+--------+
1 row in set (0.751 sec)
obclient [testdb]> EXPLAIN extended select /*+ case1 */* from t1, t2 where t1.c1 = t2.c1 and t1.c2 = 1000000;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================= |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------- |
| |0 |HASH JOIN | |90155534|2977804 | |
| |1 |├─TABLE FULL SCAN|t1 |9900 |49056 | |
| |2 |└─TABLE FULL SCAN|t2 |929152 |74557 | |
| ================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1(0x7f221640ec20)], [t1.c2(0x7f2216410170)], [t1.c3(0x7f22164106e0)], [t2.c1(0x7f221640ef10)], [t2.c2(0x7f22164109c0)], [t2.c3(0x7f2216410ca0)]), filter(nil), rowset=256 |
| equal_conds([t1.c1(0x7f221640ec20) = t2.c1(0x7f221640ef10)(0x7f221640e4d0)]), other_conds(nil) |
| 1 - output([t1.c1(0x7f221640ec20)], [t1.c2(0x7f2216410170)], [t1.c3(0x7f22164106e0)]), filter([t1.c2(0x7f2216410170) = 1000000(0x7f221640fa20)]), rowset=256 |
| access([t1.c1(0x7f221640ec20)], [t1.c2(0x7f2216410170)], [t1.c3(0x7f22164106e0)]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t1.__pk_increment(0x7f2216411310)]), range(MIN ; MAX)always true |
| 2 - output([t2.c1(0x7f221640ef10)], [t2.c2(0x7f22164109c0)], [t2.c3(0x7f2216410ca0)]), filter(nil), rowset=256 |
| access([t2.c1(0x7f221640ef10)], [t2.c2(0x7f22164109c0)], [t2.c3(0x7f2216410ca0)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t2.__pk_increment(0x7f22164115f0)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| LEADING(@"SEL$1" ("testdb"."t1"@"SEL$1" "testdb"."t2"@"SEL$1")) |
| USE_HASH(@"SEL$1" "testdb"."t2"@"SEL$1") |
| FULL(@"SEL$1" "testdb"."t1"@"SEL$1") |
| FULL(@"SEL$1" "testdb"."t2"@"SEL$1") |
| OPTIMIZER_FEATURES_ENABLE('4.0.0.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| t1: |
| table_rows:1000000 |
| physical_range_rows:1000000 |
| logical_range_rows:1000000 |
| index_back_rows:0 |
| output_rows:9899 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[t1] |
| stats version:0 |
| dynamic sampling level:0 |
| t2: |
| table_rows:1000009 |
| physical_range_rows:1000009 |
| logical_range_rows:1000009 |
| index_back_rows:0 |
| output_rows:1000009 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[idx_c1, t2] |
| stats version:0 |
| dynamic sampling level:0 |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelisim is 1 because of table property |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
67 rows in set (0.004 sec)
obclient [testdb]> set optimizer_dynamic_sampling = 1;
Query OK, 0 rows affected (0.000 sec)
obclient [testdb]> select /*+ case2 */ * from t1, t2 where t1.c1 = t2.c1 and t1.c2 = 1000000;
+--------+---------+--------+--------+--------+--------+
| c1 | c2 | c3 | c1 | c2 | c3 |
+--------+---------+--------+--------+--------+--------+
| 576793 | 1000000 | 576793 | 576793 | 576793 | 576793 |
+--------+---------+--------+--------+--------+--------+
1 row in set (0.309 sec)
obclient [testdb]> EXPLAIN extended select /*+ case2 */* from t1, t2 where t1.c1 = t2.c1 and t1.c2 = 1000000;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ==================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------------------------- |
| |0 |NESTED-LOOP JOIN | |80 |48738 | |
| |1 |├─TABLE FULL SCAN |t1 |2 |48369 | |
| |2 |└─DISTRIBUTED TABLE RANGE SCAN|t2(idx_c1)|60 |273 | |
| ==================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1(0x7f2210c0ec20)], [t1.c2(0x7f2210c10170)], [t1.c3(0x7f2210c106e0)], [t2.c1(0x7f2210c0ef10)], [t2.c2(0x7f2210c109c0)], [t2.c3(0x7f2210c10ca0)]), filter(nil), rowset=256 |
| conds(nil), nl_params_([t1.c1(0x7f2210c0ec20)(:0)]), use_batch=true |
| 1 - output([t1.c1(0x7f2210c0ec20)], [t1.c2(0x7f2210c10170)], [t1.c3(0x7f2210c106e0)]), filter([t1.c2(0x7f2210c10170) = 1000000(0x7f2210c0fa20)]), rowset=256 |
| access([t1.c1(0x7f2210c0ec20)], [t1.c2(0x7f2210c10170)], [t1.c3(0x7f2210c106e0)]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t1.__pk_increment(0x7f2210c11310)]), range(MIN ; MAX)always true |
| 2 - output([t2.c1(0x7f2210c0ef10)], [t2.c2(0x7f2210c109c0)], [t2.c3(0x7f2210c10ca0)]), filter(nil), rowset=256 |
| access([GROUP_ID(0x7f2210cf5940)], [t2.__pk_increment(0x7f2210c115f0)], [t2.c1(0x7f2210c0ef10)], [t2.c2(0x7f2210c109c0)], [t2.c3(0x7f2210c10ca0)]), partitions(p0) |
| is_index_back=true, is_global_index=false, |
| range_key([t2.c1(0x7f2210c0ef10)], [t2.__pk_increment(0x7f2210c115f0)]), range(MIN ; MAX), |
| range_cond([:0 = t2.c1(0x7f2210c0ef10)(0x7f2210cc3890)]) |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| LEADING(@"SEL$1" ("testdb"."t1"@"SEL$1" "testdb"."t2"@"SEL$1")) |
| USE_NL(@"SEL$1" "testdb"."t2"@"SEL$1") |
| FULL(@"SEL$1" "testdb"."t1"@"SEL$1") |
| INDEX(@"SEL$1" "testdb"."t2"@"SEL$1" "idx_c1") |
| USE_DAS(@"SEL$1" "testdb"."t2"@"SEL$1") |
| OPTIMIZER_FEATURES_ENABLE('4.0.0.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| t1: |
| table_rows:1000000 |
| physical_range_rows:1000000 |
| logical_range_rows:1000000 |
| index_back_rows:0 |
| output_rows:1 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[t1] |
| stats version:0 |
| dynamic sampling level:1 |
| t2: |
| table_rows:715996 |
| physical_range_rows:63 |
| logical_range_rows:59 |
| index_back_rows:59 |
| output_rows:59 |
| table_dop:1 |
| dop_method:DAS DOP |
| avaiable_index_name:[idx_c1, t2] |
| unstable_index_name:[t2] |
| stats version:0 |
| dynamic sampling level:1 |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelisim is 1 because of table property |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
70 rows in set (0.004 sec)
通过开启与关闭动态采样进行对比，开启动态采样后，性能更优，查询效率更高。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/543979/viewspace-3025552/ ，如需转载，请注明出处，否则将追究法律责任。
