ApsaraDB RDS:Statement outline

AliSQL uses a built-in system table named outline to store hints. The system automatically creates the outline table on startup. The following statement is used to create the outline table:

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'

To efficiently manage statement outlines, AliSQL provides a set of predefined procedures within the DBMS_OUTLN toolkit.

• add_optimizer_outline

  Adds an optimizer hint. Sample command:

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

  Note

  You can configure one of the Digest or query parameters in the statement. If you enter a query statement for the query parameter, DBMS_OUTLN calculates the values of the Digest and Digest_text parameters.

  Examples:

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

• add_index_outline

  Add an index hint. Sample command:

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

  Note

  You can configure one of the Digest or query parameters in the statement. If you enter a query statement for the Query parameter, DBMS_OUTLN calculates the values of the Digest and Digest_text parameters.

  Examples:

  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

  Checks whether an SQL statement matches existing outlines. This is useful for manual validation. Sample command:

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

  Examples:

  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 statement outlines that are hit in memory. Sample command:

  dbms_outln.show_outline();

  Examples:

  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 parameters.

  Parameter	Description
  HIT	The number of times that the statement outline finds the destination query block or table.
  OVERFLOW	The number of times that the statement outline does not find the destination query block or table.

• del_outline

  Deletes a statement outline from the memory or a table. Sample command:

  dbms_outln.del_outline(<Id>);

  Examples:

  mysql> call dbms_outln.del_outline(32);

  Note

  If the outline that you want to delete does not exist, the system reports an error. You can execute the SHOW WARNINGS; statement to view the error message.

  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 modify the statement outline in the outline table, you must execute the following statement for the new statement outline to take effect. Sample command:

  dbms_outln.flush_outline(); 

  Examples:

  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 check whether a statement outline takes effect by using one of the following methods:

• Execute the preview_outline statement.

  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)

• Execute the EXPLAIN statement.

  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)