易鲸捷钱库新特性之SQL级别HINT功能初见

易鲸捷数据库之金融数据库在即将到来的新版本1.6.0中将支持SQL级别的HINT功能。熟悉使用Oracle的各位看官可能都知道Oracle的HINT功能，大致语法如下，
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]… */
Oracle支持多种HINT，下面列举一些常用的HINT，

1、/*+ ALL_ROWS */ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.

SELECT /*+ ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

2、/*+ FIRST_ROWS(n) */ 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.

SELECT /*+FIRST_ROWS(20) */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

3、/*+ RULE*/ 表明对语句块选择基于规则的优化方法.

SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

4、/*+ FULL(TABLE)*/ 表明对表选择全局扫描的方法.

SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

5、/*+ INDEX(TABLE INDEX_NAME) */ 表明对表选择索引的扫描方法.

SELECT /*+INDEX(BSEMPMS SEX_INDEX) */ * FROM BSEMPMS WHERE SEX='M';

6、/*+ INDEX_JOIN(TABLE INDEX_NAME1 INDEX_NAME2) */ 当谓词中引用的列都有索引的时候，可以通过指定采用索引关联的方式，来访问数据

select /*+ index_join(t t_ind t_bm) */ id from t where id=100 and object_name='EMPLOYEES'

钱库在新版本中也支持了类似的HINT功能，虽然，在此之前，EsgynDB已经支持了少数几个HINT，如
<<+cardinality 1e10>>，<<+index index_name>>等。
在新的钱库版本中，支持了类似Oracle相同的语法（但不完全相同），它的基本语法是这样的，

/*+ token('value') [token('value')]*/
例子：
select /*+ hash_joins('off') merge_joins('off')*/ count(*) from t1 join t2 on t1.c1=t2.c1;
以上例子等于下面的操作：
control query defaults hash_joins,merge_joins hold;
cqd hash_joins 'off';
cqd merge_joins 'off';
select count(*) from t1 join t2 on t1.c1=t2.c1;
control query defaults hash_joins,merge_joins restore;

目前支持这种写法的Statement有SELECT,INSERT,UPDATE,UPSERT,DELETE,LOAD,MERGE。只能紧跟在这些关键字之后。
如果有subquery的情况下，只有第一个Hint中设置的CQD起效，例如：
select /*+ MDAM_SCAN_METHOD(‘OFF’)*/a from t1 where t1.a in（select /*+ HBASE_HASH2_PARTITIONING(‘OFF’) */a from t2 where t2.a>10）;
以上语句中，只有第一个hint中的CQD会起效。
目前一个hint中最大可以设置20个CQD，并且该功能受到mode_compatible_1的控制，关闭mode_compatible_1会报语法错误。

下面提供一个例子，使用HINT来控制SQL语句的不同的执行行为，

create table test1(a int, b varchar(50), primary key (a)) salt using 4 partitions on (a);
create table test2(a int, b varchar(50), primary key (a)) salt using 4 partitions on (a);
upsert into test1 select element, uuid() from udf(series(1,100000));
upsert into test2 select element, uuid() from udf(series(1,100000));
update statistics for table test1 on every column sample;
update statistics for table test2 on every column sample;
select * from test1, test2 where test1.a=test2.a;

>>explain options 'f' select * from test1, test2 where test1.a=test2.a;


LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

4    .    5    root                                                  1.00E+005
3    .    4    esp_exchange                    1:2(hash2)            1.00E+005
2    1    3    hybrid_hash_join      u                               1.00E+005
.    .    2    trafodion_scan                  TEST1                 1.00E+005
.    .    1    trafodion_scan                  TEST2                 1.00E+005

//关闭hash joins
>>explain options 'f' select /*+ hash_joins('OFF')*/* from test1, test2 where test1.a=test2.a;


LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

4    .    5    root                                                  1.00E+005
3    .    4    esp_exchange                    1:2(hash2)            1.00E+005
1    2    3    nested_join                                           1.00E+005
.    .    2    trafodion_vsbb_scan             TEST1                 1.00E+000
.    .    1    trafodion_scan                  TEST2                 1.00E+005

//设置并发度为8
>>explain options 'f' select /*+ parallel_num_esps('8')*/* from test1, test2 where test1.a=test2.a;


LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

6    .    7    root                                                  1.00E+005
5    .    6    esp_exchange                    1:8(hash2)            1.00E+005
4    2    5    hybrid_hash_join      u                               1.00E+005
3    .    4    esp_exchange                    8(hash2):4(hash2)     1.00E+005
.    .    3    trafodion_scan                  TEST1                 1.00E+005
1    .    2    esp_exchange                    8(hash2):4(hash2)     1.00E+005
.    .    1    trafodion_scan                  TEST2                 1.00E+005

//关闭esp并发
>>explain options 'f' select /*+ attempt_esp_parallelism('OFF')*/* from test1, test2 where test1.a=test2.a;


LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

3    .    4    root                                                  1.00E+005
2    1    3    hybrid_hash_join      u                               1.00E+005
.    .    2    trafodion_scan                  TEST1                 1.00E+005
.    .    1    trafodion_scan                  TEST2                 1.00E+005