Statement outline

AliSQL includes a built-in system table named outline to store hints. This table is created automatically at startup. The following CREATE TABLE statement is provided for reference:

​CREATE TABLE `mysql`.`outline` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Schema_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Digest` varchar(64) COLLATE utf8_bin NOT NULL,
  `Digest_text` longtext COLLATE utf8_bin,
  `Type` enum('IGNORE INDEX','USE INDEX','FORCE INDEX','OPTIMIZER') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Scope` enum('','FOR JOIN','FOR ORDER BY','FOR GROUP BY') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '',
  `State` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Y',
  `Position` bigint(20) NOT NULL,
  `Hint` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`Id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Statement outline'​

AliSQL provides the following six stored procedures in DBMS_OUTLN to manage statement outlines:

• add_optimizer_outline

  Adds an optimizer hint. Use the following command:

  dbms_outln.add_optimizer_outline('<Schema_name>','<Digest>','<query_block>','<hint>','<query>');

  Note

  You can specify either Digest or Query (the original SQL statement). If you provide Query, DBMS_OUTLN calculates Digest and Digest_text.

  Example:

  mysql> call dbms_outln.add_optimizer_outline("outline_db", '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
                                        "select * from t1 where id = 1");

• add_index_outline

  Adds an index hint. Use the following command:

  dbms_outln.add_index_outline('<Schema_name>','<Digest>',<Position>,'<Type>','<Hint>','<Scope>','<Query>');

  Note

  You can specify either Digest or Query (the original SQL statement). If you provide Query, DBMS_OUTLN calculates Digest and Digest_text.

  Example:

  mysql> call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', '',
                                  "select * from t1 where t1.col1 =1 and t1.col2 ='xpchild'");

• preview_outline

  Previews the outlines that match a query. This is useful for manual validation. Use the following command:

  dbms_outln.preview_outline('<Schema_name>','<Query>');

  Note

  The value of the max_digest_length parameter can affect the accuracy of statement outline matching. If a statement outline is not matched correctly, increase the value of this parameter.

  Example:

  ​mysql> call dbms_outln.preview_outline('outline_db', "select * from t1 where t1.col1 =1 and t1.col2 ='xpchild'");
  +------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
  | SCHEMA     | DIGEST                                                           | BLOCK_TYPE | BLOCK_NAME | BLOCK | HINT                |
  +------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
  | outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | TABLE      | t1         |     1 | USE INDEX (`ind_1`) |
  +------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
  1 row in set (0.00 sec)​

• show_outline

  Displays the statement outlines currently loaded in memory, along with their hit statistics. Use the following command:

  dbms_outln.show_outline();

  Example:

  ​mysql> call dbms_outln.show_outline();
  +------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
  | ID   | SCHEMA     | DIGEST                                                           | TYPE      | SCOPE | POS  | HINT                                                  | HIT  | OVERFLOW | DIGEST_TEXT                                                                         |
  +------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
  |   33 | outline_db | 36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 | OPTIMIZER |       |    1 | /*+ SET_VAR(foreign_key_checks=OFF) */                |    1 |        0 | SELECT * FROM `t1` WHERE `id` = ?                                                   |
  |   32 | outline_db | 36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 | OPTIMIZER |       |    1 | /*+ MAX_EXECUTION_TIME(1000) */                       |    2 |        0 | SELECT * FROM `t1` WHERE `id` = ?                                                   |
  |   34 | outline_db | d4dcef634a4a664518e5fb8a21c6ce9b79fccb44b773e86431eb67840975b649 | OPTIMIZER |       |    1 | /*+ BNL(t1,t2) */                                     |    1 |        0 | SELECT `t1` . `id` , `t2` . `id` FROM `t1` , `t2`                                   |
  |   35 | outline_db | 5a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER |       |    2 |  /*+ QB_NAME(subq1) */                                |    2 |        0 | SELECT * FROM `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `t2` )               |
  |   36 | outline_db | 5a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER |       |    1 | /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */  |    2 |        0 | SELECT * FROM `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `t2` )               |
  |   30 | outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | USE INDEX |       |    1 | ind_1                                                 |    3 |        0 | SELECT * FROM `t1` WHERE `t1` . `col1` = ? AND `t1` . `col2` = ?                    |
  |   31 | outline_db | 33c71541754093f78a1f2108795cfb45f8b15ec5d6bff76884f4461fb7f33419 | USE INDEX |       |    2 | ind_2                                                 |    1 |        0 | SELECT * FROM `t1` , `t2` WHERE `t1` . `col1` = `t2` . `col1` AND `t2` . `col2` = ? |
  +------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
  7 rows in set (0.00 sec)​

  The following table describes the HIT and OVERFLOW columns.

  Parameter	Description
  HIT	The number of times this statement outline was matched.
  OVERFLOW	The number of times this statement outline failed to find the corresponding query block or table.

• del_outline

  Deletes a statement outline from memory and the outline table. Use the following command:

  dbms_outln.del_outline(<Id>);

  Example:

  ​mysql> call dbms_outln.del_outline(32);

  Note

  If the outline you are trying to delete does not exist, the system returns a warning. You can run show warnings; to view the details.

  ​mysql> call dbms_outln.del_outline(1000);
  Query OK, 0 rows affected, 2 warnings (0.00 sec)
  
  mysql> show warnings;
  +---------+------+----------------------------------------------+
  | Level   | Code | Message                                      |
  +---------+------+----------------------------------------------+
  | Warning | 7521 | Statement outline 1000 is not found in table |
  | Warning | 7521 | Statement outline 1000 is not found in cache |
  +---------+------+----------------------------------------------+
  2 rows in set (0.00 sec)​

• flush_outline

  If you directly modify the outline table, you must run this command to apply the changes. Use the following command:

  dbms_outln.flush_outline(); 

  Example:

  mysql> update mysql.outline set Position = 1 where Id = 18;
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  ​mysql> call dbms_outln.flush_outline(); 
  Query OK, 0 rows affected (0.01 sec)​

You can use one of the following two methods to verify that a statement outline is active:

• Use preview_outline to check for a match.

  mysql> call dbms_outln.preview_outline('outline_db', "select * from t1 where t1.col1 =1 and t1.col2 ='xpchild'");
  +------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
  | SCHEMA     | DIGEST                                                           | BLOCK_TYPE | BLOCK_NAME | BLOCK | HINT                |
  +------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
  | outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | TABLE      | t1         |     1 | USE INDEX (`ind_1`) |
  +------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
  1 row in set (0.01 sec)

• Use EXPLAIN to view the execution plan.

  mysql> explain select * from t1 where t1.col1 =1 and t1.col2 ='xpchild';
  +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
  | id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
  +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
  |  1 | SIMPLE      | t1    | NULL       | ref  | ind_1         | ind_1 | 5       | const |    1 |   100.00 | Using where |
  +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
  1 row in set, 1 warning (0.00 sec)
  
  mysql> show warnings;
  +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Level | Code | Message                                                                                                                                                                                                                                                 |
  +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Note  | 1003 | /* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` USE INDEX (`ind_1`) where ((`outline_db`.`t1`.`col1` = 1) and (`outline_db`.`t1`.`col2` = 'xpchild')) |
  +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)