From: Roy Lyseng Date: April 6 2011 1:27pm Subject: bzr commit into mysql-trunk branch (roy.lyseng:3359) Bug#11822517 List-Archive: http://lists.mysql.com/commits/134815 X-Bug: 11822517 Message-Id: <20110406132724.CA3481F3@tyr67.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============4883578250019901034==" --===============4883578250019901034== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/rl136806/mysql/repo/mysql-work5/ based on revid:roy.lyseng@stripped 3359 Roy Lyseng 2011-04-06 Bug#11822517: Refactor call interface of choose_plan() Part 2 - Change meaning of greedy_search() argument 'remaining_tables'. The purpose of this patch is to make the interface of greedy_search() and related functions simpler. It is also a preparatory step for a simpler test for dependencies when doing semi join materialization together with outer join (WL#5561). The meaning of the argument remaining_tables to greedy_search() is changed: Before, it meant all tables not added to the current join plan, regardless of whether the tables were to be optimized or not. Now, it means all tables remaining to be added. The difference is important when making a plan for a materialized semi join nest. Before, remaining_tables included also the tables belonging to the query, but not part of the semi join nest being optimized. This makes for simpler implementation of best_extension_by_limited_search() and easier maintenance. However, best_access_path() requires that information about all tables not yet added to the plan be available. We implement this by adding a member variable excluded_tables in the Optimize_join_tables class, which contains the set of all tables in the query block that are not being optimized in the current operation. sql/sql_select.cc New member variable excluded_tables is added to Optimize_join_tables. In choose_table_order(), join_tables is calculated as the set of tables to be optimized. In best_extension_by_limited_search(), use of allowed_tables is eliminated, and logic has been slightly simplified. modified: sql/sql_select.cc === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-04-06 11:56:55 +0000 +++ b/sql/sql_select.cc 2011-04-06 13:27:04 +0000 @@ -284,7 +284,10 @@ public: thd->variables.optimizer_search_depth)), prune_level(thd->variables.optimizer_prune_level), thd(thd), join(join), - cur_embedding_map(0) + cur_embedding_map(0), + excluded_tables(sjm_nest ? + join->all_table_map & ~sjm_nest->sj_inner_tables : + 0) { join->emb_sjm_nest= sjm_nest; } @@ -312,6 +315,16 @@ private: */ nested_join_map cur_embedding_map; /** + When calculating a plan for a materialized semi join nest, + best_access_plan() needs to know not only the remaining tables within the + semi join nest, but also all tables outside of this nest, because there may + be key references between the semi join nest and the outside tables + that should not be considered when materializing the semi join nest. + @c excluded_tables tracks these tables. + */ + const table_map excluded_tables; + + /** @todo: Add remaining Join optimization state members here, ie emb_sjm_nest, positions, cur_sj_inner_tables. This is not yet possible because (some of them) are accessed through @@ -7648,7 +7661,7 @@ bool Optimize_table_order::choose_table_ tables from this semi-join as first */ jtab_sort_func= join_tab_cmp_embedded_first; - join_tables= join->all_table_map & ~join->const_table_map; + join_tables= join->emb_sjm_nest->sj_inner_tables; /* Inner tables of semi join nest are not allowed to be identified as const tables. @@ -8334,9 +8347,6 @@ bool Optimize_table_order::best_extensio DBUG_EXECUTE("opt", print_plan(join, idx, record_count, read_time, read_time, "part_plan");); - table_map allowed_tables= ~(table_map)0; - if (join->emb_sjm_nest) - allowed_tables= join->emb_sjm_nest->sj_inner_tables; const bool has_sj= !(join->select_lex->sj_nests.is_empty() || join->emb_sjm_nest); @@ -8358,7 +8368,6 @@ bool Optimize_table_order::best_extensio swap_variables(JOIN_TAB*, join->best_ref[idx], *pos); if ((remaining_tables & real_table_bit) && - (allowed_tables & real_table_bit) && !(remaining_tables & s->dependent) && (!idx || !check_interleaving_with_nj(s))) { @@ -8367,7 +8376,7 @@ bool Optimize_table_order::best_extensio /* Find the best access method from 's' to the current partial plan */ POSITION loose_scan_pos; - best_access_path(join, s, remaining_tables, + best_access_path(join, s, excluded_tables | remaining_tables, idx, false, record_count, position, &loose_scan_pos); @@ -8439,8 +8448,7 @@ bool Optimize_table_order::best_extensio } } - if ((current_search_depth > 1) && - (remaining_tables & ~real_table_bit) & allowed_tables ) + if ((current_search_depth > 1) && (remaining_tables & ~real_table_bit)) { /* Explore more best extensions of plan */ if (best_extension_by_limited_search(remaining_tables & ~real_table_bit, --===============4883578250019901034== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/roy.lyseng@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: roy.lyseng@stripped # target_branch: file:///home/rl136806/mysql/repo/mysql-work5/ # testament_sha1: caf3960912c055b6fe32c6003365ee544603f32f # timestamp: 2011-04-06 15:27:24 +0200 # base_revision_id: roy.lyseng@stripped\ # i0e8qdd8zx9l7gni # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWeNw7IQAAp7/gFAQQABZ9/// 2mXUgL////VgB8ts1l9HIeO07vd7e28er2Gq9bmwGiCZAmk9TGap+k1MGkyek0aGgAMjQCUEIxNB MQmppPaUDR6CeoNGgHpDQBkTTFNCajFP1TwiAPUAHqDQMgAAkSJoTTRqTaelNNo00Jp6TRo0ZGgA DIOYExNBhMmTJkYTBNNMjEwBDAJIiNTEabSnqaeRJ6I2gTJkxANAABREHzFIZUVpmWm6r03bDpqj FPCGo37dyBkshoLbiZ3tFF7JWdBQ90f5pSInzVYTym+HobeXGJB6W23/KL9yAgyCB/tp06eXDhdv Tv7dSIgAjqIs13bZW8lnOaa4SrNCI6hRpMnz4PWPk1TjGePNigG8RBSL8kYbEhxsWk01vrSENUd0 4sSudoPNxh6NR2vxzYktYMdJ+d5jYU8rUsY1pa+SqpP3xcdc3zyL+1Eu+uEsK3NTQ8hZJMf4d+Mk uVIWzzZHRzmFUQopQRlWkWhmvFyKdVy10R/dFg2MF6pdhWlcFjI4Icmos8DjShx1a7F3WzRpcRpe 9pE2LWAkJamZoQGwNUpR5Ritc7ltzVBBgrvnFQUhieoAzunWrtNJwJlw86bDIX1oCenohhZDKlyr h4VZcLTaiFw+gR8cSh9J7A7RIQ4459SbZ5Nl7aUlwaSrdytAHRDBEN/eK4R228HbJYMHoYGaPq2Q ibrikcQ2PZfA2HLoqF4REOpCjXiCkwfKCSSBwGrKZ84YxyQjOyuZC4qq+F561JiGx00tEQmHtj6r o9+OuExM999k9x6MOVNQ3mJx+qGnfXxLRRbclMsNDDSYhzsSy2bpe1ERUzyA0hsDLXVsEE1SpLCB ECaFb0FUo9iIStc8bXToeAdCEhiKNjpoKh8YRMg1BIZh2B+pyAm+bYZ8g6VkFvNXmroTMuyGWJ5v lLxKz1ufhCcVcEhCpziD7vx322SXxSMJWViIwl/Z25eO0b1PoOliGmO2miKEicOYydfgJC6AmvEY 5R3JNWGti8JBwfPMpk2+HKVS+jQukPYk9+H+BlSVsarOuamfNY3sSi6K2aDRLQrse+dpfHRhdtwU tPVZy+lqO72JFd7GP6RV53OUD/kEMbnCj0OzBV5zl6ZknPkGLqGZsFG8NltWhjs2Azmgrty5a5Mi IG3ESjMYMODyl62PXecPhtMJoAs/i5jtmCeQqEYaqA/uKVbBReZkNmR4sHG7ERqQOK7FAuqnpzgJ 2XOgpkf284StOCLFyzhAaDWYWl1dHXI1Y4kynWHQVLiY4TpHpYxHH+lMYG3WCB25HUC+gR9tsFcS e+60rWzuCmnQtjDZjweg4b9xDJS80niyshIbiDgSMqngMgHjmGbjwsC5o06RDbR1MNPPmqUljG0I pUmg7AyIiRjQGCZK++1mYLaNIgljZRYCSWcVmYMrxWowqisUjAsXEQChIgxDwalU1IUjRU53d22g vNaqGDpv597kkPaUFoMmZw0mDicuEgv2wMnR0nB3zbCBD8VQniHd2eepXpD0tid+M1A+sRMGS5tP XsQGuAc1JEux4eGEVQEIdI295wCwmoQIU4lTRHCXU2rbEI8MvPGoYRsfsh0MVluUQ7l6KgZqGaiC 4NFNMUoqJog/hYv6x0wA4UgWnIVI4pdAnMEMoqfzZ88wVrFRBEAiKo9+idr0EWDMsnEIXE0BNPpQ 9zI494ZrFnMMdY2DbeIziD9xXUB4NsbEbnD1MkDGTZQsmceKWpKnQDMcCQminJGietXKJLQ5X5O3 nXObTVaqE0xaioG2MwGl06V75Bg+fCNVxDM/Q2/J0aUsBis69nBmvW42h8da8TTT3gZt0HAOZAsb ZkMbGcooIkgt8XmzdHCtuFjU1kghCncZxd64QtCD+sGLASz2BnXCbhAXWIRyQqohcCCFIJhb2xFy RpZkszUy1J5UtPk7EjiSO5WJWhet4gGOQXQK8kat0hJrFi2Xea2sqSyRZIpV3zcETbFNwwcSUlCT C6cObbi6QQEp1Zy+SGMqCpiGkMVCnl5AkTSmyiMXzUzV11u07CImwbUOaZMyyHV+qAyBsMa9AKiG oA2ZAqxHLRKEVuLZtrdvVzk+5IkGMjmYayYPdN9J3a8in68O/3YJGRk2OSrjxuyY3igjpOTIUySk NJK/PxwjZZ/TOEd3rDOldMrohp4OEi5mMkljKpKThuD/RsVmpp4Ke9YmpMVUtDrrNy4kCob2Q1+7 SFaB+5+greO4ZS7K2xsedg73OUJjYmPFw5ucbZHPVAFM3OYZQU2eyx3cmIkHY2HuBTgdTlMLC6cA 0mgSvwG7mpJ1TI4HZsTRlyMzDXyIAJQg3cpGpq782jN0Z6qF81cw8CZqiRKikUIolE2AVUhh1DrD FETBsGDGNDalO2KUatJSKrt6ZQLVo1WzJmthBpGIsvGi628qFFE+XMkPcFtDEWzXDrPoxQ5HKaUe c+LaCfXuGujeyunLlGWaZGgdT6RgmNJY0uwcXwHMBbbRVhUkZ6reAjqdmIpDRKluLPzJI6tYrG7x MkZ/SK06w4yxf8XckU4UJDjcOyEA --===============4883578250019901034==--