- developers - lists.mariadb.org

[Maria-developers] Updated (by Guest): index_merge optimizer: dont discard index_merge union strategies when range is available (24)
by worklog-noreply＠askmonty.org 26 May '09

26 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: index_merge optimizer: dont discard index_merge union strategies when range is available CREATION DATE..: Tue, 26 May 2009, 12:10 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Psergey CATEGORY.......: Client-BackLog TASK ID........: 24 (http://askmonty.org/worklog/?tid=24) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Tue, 26 May 2009, 13:27)=-=- High-Level Specification modified. --- /tmp/wklog.24.old.305 2009-05-26 13:27:32.000000000 +0300 +++ /tmp/wklog.24.new.305 2009-05-26 13:27:32.000000000 +0300 @@ -1 +1,70 @@ +(Not a ready HLS but draft) +<contents> +Solution overview +Limitations +TODO + +</contents> + +Solution overview +================= +The idea is to delay discarding potential index_merge plans until the point +where it is really necessary. + +This way, we won't have to do much changes in the range analyzer, but will be +able to keep potential index_merge plan just enough so that it's possible to +take it into consideration together with range access plans. + +Since there are no changes in the optimizer, the ability to consider both +range and index_merge options will be limited to WHERE clauses of this form: + + WHERE := range_cond(key1_1) AND + range_cond(key2_1) AND + other_cond AND + index_merge_OR_cond1(key3_1, key3_2, ...) + index_merge_OR_cond2(key4_1, key4_2, ...) + +where + + index_merge_OR_cond{N} := (range_cond(keyN_1) OR + range_cond(keyN_2) OR ...) + + + range_cond(keyX) := condition that allows to construct range access of keyX + and doesn't allow to construct range/index_merge accesses + for any keys of the table in question. + + +For such WHERE clauses, the range analyzer will produce SEL_TREE of this form: + + SEL_TREE( + range(key1_1), + ... + range(key2_1), + SEL_IMERGE( (1) + SEL_TREE(key3_1}) + SEL_TREE(key3_2}) + ... + ) + ... + ) + +which can be used to make a cost-based choice between range and index_merge. + +Limitations +----------- +This will not be a full solution in a sense that the range analyzer will not +be able to produce sel_tree (1) if the WHERE clause is specified in other form +(e.g. brackets were opened). + +TODO +---- +* is it a problem if there are keys that are referred to both from + index_merge and from range access? + +* How strict is the limitation on the form of the WHERE? + +* TODO: The optimizer didn't compare costs of index_merge and range before (ok + it did but that was done for accesses to different tables). Will there be any + possible gotchas here? DESCRIPTION: Current range optimizer will discard possible index_merge/[sort]union strategies when there is a possible range plan. This action is a part of measures we take to avoid combinatorial explosion of possible range/ index_merge strategies. A bad side effect of this is that for WHERE clauses in form t.key1= 'very-frequent-value' AND (t.key2='rare-value1' OR t.key3='rare-value2') the optimizer will - discard union(key2,key3) in favor of range(key1) - consider costs of using range(key1) and discard that plan also and the overall effect is that possible poor range access will cause possible good index_merge access not to be considered. This WL is to about lifting this limitation at least for some subset of WHERE clauses. HIGH-LEVEL SPECIFICATION: (Not a ready HLS but draft) <contents> Solution overview Limitations TODO </contents> Solution overview ================= The idea is to delay discarding potential index_merge plans until the point where it is really necessary. This way, we won't have to do much changes in the range analyzer, but will be able to keep potential index_merge plan just enough so that it's possible to take it into consideration together with range access plans. Since there are no changes in the optimizer, the ability to consider both range and index_merge options will be limited to WHERE clauses of this form: WHERE := range_cond(key1_1) AND range_cond(key2_1) AND other_cond AND index_merge_OR_cond1(key3_1, key3_2, ...) index_merge_OR_cond2(key4_1, key4_2, ...) where index_merge_OR_cond{N} := (range_cond(keyN_1) OR range_cond(keyN_2) OR ...) range_cond(keyX) := condition that allows to construct range access of keyX and doesn't allow to construct range/index_merge accesses for any keys of the table in question. For such WHERE clauses, the range analyzer will produce SEL_TREE of this form: SEL_TREE( range(key1_1), ... range(key2_1), SEL_IMERGE( (1) SEL_TREE(key3_1}) SEL_TREE(key3_2}) ... ) ... ) which can be used to make a cost-based choice between range and index_merge. Limitations ----------- This will not be a full solution in a sense that the range analyzer will not be able to produce sel_tree (1) if the WHERE clause is specified in other form (e.g. brackets were opened). TODO ---- * is it a problem if there are keys that are referred to both from index_merge and from range access? * How strict is the limitation on the form of the WHERE? * TODO: The optimizer didn't compare costs of index_merge and range before (ok it did but that was done for accesses to different tables). Will there be any possible gotchas here? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): index_merge optimizer: dont discard index_merge union strategies when range is available (24)
by worklog-noreply＠askmonty.org 26 May '09

26 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: index_merge optimizer: dont discard index_merge union strategies when range is available CREATION DATE..: Tue, 26 May 2009, 12:10 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Psergey CATEGORY.......: Client-BackLog TASK ID........: 24 (http://askmonty.org/worklog/?tid=24) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Current range optimizer will discard possible index_merge/[sort]union strategies when there is a possible range plan. This action is a part of measures we take to avoid combinatorial explosion of possible range/ index_merge strategies. A bad side effect of this is that for WHERE clauses in form t.key1= 'very-frequent-value' AND (t.key2='rare-value1' OR t.key3='rare-value2') the optimizer will - discard union(key2,key3) in favor of range(key1) - consider costs of using range(key1) and discard that plan also and the overall effect is that possible poor range access will cause possible good index_merge access not to be considered. This WL is to about lifting this limitation at least for some subset of WHERE clauses. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): index_merge optimizer: dont discard index_merge union strategies when range is available (24)
by worklog-noreply＠askmonty.org 26 May '09

26 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: index_merge optimizer: dont discard index_merge union strategies when range is available CREATION DATE..: Tue, 26 May 2009, 12:10 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Psergey CATEGORY.......: Client-BackLog TASK ID........: 24 (http://askmonty.org/worklog/?tid=24) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Current range optimizer will discard possible index_merge/[sort]union strategies when there is a possible range plan. This action is a part of measures we take to avoid combinatorial explosion of possible range/ index_merge strategies. A bad side effect of this is that for WHERE clauses in form t.key1= 'very-frequent-value' AND (t.key2='rare-value1' OR t.key3='rare-value2') the optimizer will - discard union(key2,key3) in favor of range(key1) - consider costs of using range(key1) and discard that plan also and the overall effect is that possible poor range access will cause possible good index_merge access not to be considered. This WL is to about lifting this limitation at least for some subset of WHERE clauses. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2705: Fix ps_not_windows test for -all-static build which cannot load plugins.
by noreply＠launchpad.net 22 May '09

22 May '09

------------------------------------------------------------ revno: 2705 committer: knielsen(a)knielsen-hq.org branch nick: work-5.1-buildbot timestamp: Fri 2009-05-22 19:53:25 +0200 message: Fix ps_not_windows test for -all-static build which cannot load plugins. added: mysql-test/include/have_dynamic_loading.inc modified: mysql-test/t/ps_not_windows.test === added file 'mysql-test/include/have_dynamic_loading.inc' --- mysql-test/include/have_dynamic_loading.inc 1970-01-01 00:00:00 +0000 +++ mysql-test/include/have_dynamic_loading.inc 2009-05-22 17:53:25 +0000 @@ -0,0 +1,4 @@ +-- require r/have_dynamic_loading.require +disable_query_log; +show variables like 'have_dynamic_loading'; +enable_query_log; === modified file 'mysql-test/t/ps_not_windows.test' --- mysql-test/t/ps_not_windows.test 2007-01-02 23:31:12 +0000 +++ mysql-test/t/ps_not_windows.test 2009-05-22 17:53:25 +0000 @@ -2,6 +2,8 @@ --source include/not_embedded.inc # Non-windows specific ps tests. --source include/not_windows.inc +# Requires ability to install plugins. +--source include/have_dynamic_loading.inc # # Bug #20665: All commands supported in Stored Procedures should work in -- Main stable branch https://code.launchpad.net/~maria-captains/maria/5.1 Your team Maria developers is subscribed to branch lp:maria. To unsubscribe from this branch go to https://code.launchpad.net/~maria-captains/maria/5.1/+edit-subscription.

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2705)
by knielsen＠knielsen-hq.org 22 May '09

22 May '09

#At lp:maria 2705 knielsen(a)knielsen-hq.org 2009-05-22 Fix ps_not_windows test for -all-static build which cannot load plugins. added: mysql-test/include/have_dynamic_loading.inc modified: mysql-test/t/ps_not_windows.test === added file 'mysql-test/include/have_dynamic_loading.inc' --- a/mysql-test/include/have_dynamic_loading.inc 1970-01-01 00:00:00 +0000 +++ b/mysql-test/include/have_dynamic_loading.inc 2009-05-22 17:53:25 +0000 @@ -0,0 +1,4 @@ +-- require r/have_dynamic_loading.require +disable_query_log; +show variables like 'have_dynamic_loading'; +enable_query_log; === modified file 'mysql-test/t/ps_not_windows.test' --- a/mysql-test/t/ps_not_windows.test 2007-01-02 23:31:12 +0000 +++ b/mysql-test/t/ps_not_windows.test 2009-05-22 17:53:25 +0000 @@ -2,6 +2,8 @@ --source include/not_embedded.inc # Non-windows specific ps tests. --source include/not_windows.inc +# Requires ability to install plugins. +--source include/have_dynamic_loading.inc # # Bug #20665: All commands supported in Stored Procedures should work in

1 0

[Maria-developers] Updated (by Guest): Table Elimination: remove the facts table (20)
by worklog-noreply＠askmonty.org 22 May '09

22 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Table Elimination: remove the facts table CREATION DATE..: Thu, 21 May 2009, 01:07 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Psergey CATEGORY.......: Server-BackLog TASK ID........: 20 (http://askmonty.org/worklog/?tid=20) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Fri, 22 May 2009, 17:51)=-=- Version updated. --- /tmp/wklog.20.old.31642 2009-05-22 17:51:21.000000000 +0300 +++ /tmp/wklog.20.new.31642 2009-05-22 17:51:21.000000000 +0300 @@ -1 +1 @@ -Server-9.x +Server-5.2 -=-=(Guest - Fri, 22 May 2009, 17:50)=-=- Category updated. --- /tmp/wklog.20.old.31598 2009-05-22 17:50:49.000000000 +0300 +++ /tmp/wklog.20.new.31598 2009-05-22 17:50:49.000000000 +0300 @@ -1 +1 @@ -Server-RawIdeaBin +Server-BackLog -=-=(Guest - Fri, 22 May 2009, 17:50)=-=- Status updated. --- /tmp/wklog.20.old.31598 2009-05-22 17:50:49.000000000 +0300 +++ /tmp/wklog.20.new.31598 2009-05-22 17:50:49.000000000 +0300 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Guest - Fri, 22 May 2009, 17:29)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.31038 2009-05-22 17:29:58.000000000 +0300 +++ /tmp/wklog.20.new.31038 2009-05-22 17:29:58.000000000 +0300 @@ -68,7 +68,7 @@ where ACBDT_Birthdate < '1950-01-01' -At this point, it's possible to conclude that table anchor can be removed from +At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( @@ -80,6 +80,18 @@ mean actors that have NULL/no names), but we're not interested in those as it is an inner join. +The result is (notice the changed select): + +select + actor_birthday.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor_birthdate + left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + How to remove ------------- * Find inner joins, such that -=-=(Guest - Thu, 21 May 2009, 22:54)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.28489 2009-05-21 22:54:52.000000000 +0300 +++ /tmp/wklog.20.new.28489 2009-05-21 22:54:52.000000000 +0300 @@ -11,7 +11,7 @@ -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( - AC_ID primary key, + AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), @@ -21,7 +21,7 @@ -- Another physical attribute: actor birthdate -- create table actor_birthdate ( - AC_ID primary key, + AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) -=-=(Psergey - Thu, 21 May 2009, 01:07)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.26317 2009-05-21 01:07:55.000000000 +0300 +++ /tmp/wklog.20.new.26317 2009-05-21 01:07:55.000000000 +0300 @@ -1 +1,91 @@ +Consider the following schema: + +-- +-- The physical anchor table +-- +create table actor ( + AC_ID int primary key +); + +-- +-- A physical attribute: actor name (non-historized for simplicity) +-- +create table actor_name ( + AC_ID primary key, + ACNAM_Name varchar(20), + index(ACNAM_Name), + foreign key (AC_ID) references actor(AC_ID), +); + +-- +-- Another physical attribute: actor birthdate +-- +create table actor_birthdate ( + AC_ID primary key, + ACBDT_Birthdate datetime, + index(ACBDT_Birthdate), + foreign key (AC_ID) references actor(AC_ID) +); + +-- +-- The knot view: actor with its attributes +-- +create view actor_attributes as +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate + ... (other attributes follow) +from + actor + left join actor_name on (actor_name.AC_ID = actor.AC_ID) + left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + ... other attributes follow ... +; + +-- The query: names of senior actors +select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' + +Query processing +---------------- +The following steps will occur +* The VIEW will be resolved with algorithm=merge +* Outer-to-inner join conversion will make use of the + ACBDT_Birthdate < '1950-01-01' condition and change one outer join into + inner. +* Table elimination variant#1 will remove all unused attributes. + +And we'll end up with: + +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor + join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + left join actor_name on (actor_name.AC_ID = actor.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + +At this point, it's possible to conclude that table anchor can be removed from +the query plan: anchor model specifies that actor_name has a foreign key: + + create table actor_name ( + ... + foreign key (AC_ID) references actor(AC_ID), + +This means that each record in actor_name has exactly one match in table actor. +There may be records in `actor` that have no matches in `actor_name` (they +mean actors that have NULL/no names), but we're not interested in those as it +is an inner join. + +How to remove +------------- +* Find inner joins, such that + - some table T can be accessed using eq_ref access method. + - some other table T2 has Foreign Key that refers to table T. + - there are no references to table T anywhere in the query, except for + references to T.primary_key, which can be substituted for columns of T2. + DESCRIPTION: In certain cases it is possible to eliminate table when one is running inner joins and there is a foreign key relationship between the tables. HIGH-LEVEL SPECIFICATION: Consider the following schema: -- -- The physical anchor table -- create table actor ( AC_ID int primary key ); -- -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), ); -- -- Another physical attribute: actor birthdate -- create table actor_birthdate ( AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) ); -- -- The knot view: actor with its attributes -- create view actor_attributes as select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate ... (other attributes follow) from actor left join actor_name on (actor_name.AC_ID = actor.AC_ID) left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) ... other attributes follow ... ; -- The query: names of senior actors select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' Query processing ---------------- The following steps will occur * The VIEW will be resolved with algorithm=merge * Outer-to-inner join conversion will make use of the ACBDT_Birthdate < '1950-01-01' condition and change one outer join into inner. * Table elimination variant#1 will remove all unused attributes. And we'll end up with: select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) left join actor_name on (actor_name.AC_ID = actor.AC_ID) where ACBDT_Birthdate < '1950-01-01' At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( ... foreign key (AC_ID) references actor(AC_ID), This means that each record in actor_name has exactly one match in table actor. There may be records in `actor` that have no matches in `actor_name` (they mean actors that have NULL/no names), but we're not interested in those as it is an inner join. The result is (notice the changed select): select actor_birthday.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor_birthdate left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) where ACBDT_Birthdate < '1950-01-01' How to remove ------------- * Find inner joins, such that - some table T can be accessed using eq_ref access method. - some other table T2 has Foreign Key that refers to table T. - there are no references to table T anywhere in the query, except for references to T.primary_key, which can be substituted for columns of T2. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): Table Elimination: remove the facts table (20)
by worklog-noreply＠askmonty.org 22 May '09

22 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Table Elimination: remove the facts table CREATION DATE..: Thu, 21 May 2009, 01:07 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Psergey CATEGORY.......: Server-BackLog TASK ID........: 20 (http://askmonty.org/worklog/?tid=20) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Fri, 22 May 2009, 17:51)=-=- Version updated. --- /tmp/wklog.20.old.31642 2009-05-22 17:51:21.000000000 +0300 +++ /tmp/wklog.20.new.31642 2009-05-22 17:51:21.000000000 +0300 @@ -1 +1 @@ -Server-9.x +Server-5.2 -=-=(Guest - Fri, 22 May 2009, 17:50)=-=- Category updated. --- /tmp/wklog.20.old.31598 2009-05-22 17:50:49.000000000 +0300 +++ /tmp/wklog.20.new.31598 2009-05-22 17:50:49.000000000 +0300 @@ -1 +1 @@ -Server-RawIdeaBin +Server-BackLog -=-=(Guest - Fri, 22 May 2009, 17:50)=-=- Status updated. --- /tmp/wklog.20.old.31598 2009-05-22 17:50:49.000000000 +0300 +++ /tmp/wklog.20.new.31598 2009-05-22 17:50:49.000000000 +0300 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Guest - Fri, 22 May 2009, 17:29)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.31038 2009-05-22 17:29:58.000000000 +0300 +++ /tmp/wklog.20.new.31038 2009-05-22 17:29:58.000000000 +0300 @@ -68,7 +68,7 @@ where ACBDT_Birthdate < '1950-01-01' -At this point, it's possible to conclude that table anchor can be removed from +At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( @@ -80,6 +80,18 @@ mean actors that have NULL/no names), but we're not interested in those as it is an inner join. +The result is (notice the changed select): + +select + actor_birthday.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor_birthdate + left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + How to remove ------------- * Find inner joins, such that -=-=(Guest - Thu, 21 May 2009, 22:54)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.28489 2009-05-21 22:54:52.000000000 +0300 +++ /tmp/wklog.20.new.28489 2009-05-21 22:54:52.000000000 +0300 @@ -11,7 +11,7 @@ -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( - AC_ID primary key, + AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), @@ -21,7 +21,7 @@ -- Another physical attribute: actor birthdate -- create table actor_birthdate ( - AC_ID primary key, + AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) -=-=(Psergey - Thu, 21 May 2009, 01:07)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.26317 2009-05-21 01:07:55.000000000 +0300 +++ /tmp/wklog.20.new.26317 2009-05-21 01:07:55.000000000 +0300 @@ -1 +1,91 @@ +Consider the following schema: + +-- +-- The physical anchor table +-- +create table actor ( + AC_ID int primary key +); + +-- +-- A physical attribute: actor name (non-historized for simplicity) +-- +create table actor_name ( + AC_ID primary key, + ACNAM_Name varchar(20), + index(ACNAM_Name), + foreign key (AC_ID) references actor(AC_ID), +); + +-- +-- Another physical attribute: actor birthdate +-- +create table actor_birthdate ( + AC_ID primary key, + ACBDT_Birthdate datetime, + index(ACBDT_Birthdate), + foreign key (AC_ID) references actor(AC_ID) +); + +-- +-- The knot view: actor with its attributes +-- +create view actor_attributes as +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate + ... (other attributes follow) +from + actor + left join actor_name on (actor_name.AC_ID = actor.AC_ID) + left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + ... other attributes follow ... +; + +-- The query: names of senior actors +select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' + +Query processing +---------------- +The following steps will occur +* The VIEW will be resolved with algorithm=merge +* Outer-to-inner join conversion will make use of the + ACBDT_Birthdate < '1950-01-01' condition and change one outer join into + inner. +* Table elimination variant#1 will remove all unused attributes. + +And we'll end up with: + +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor + join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + left join actor_name on (actor_name.AC_ID = actor.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + +At this point, it's possible to conclude that table anchor can be removed from +the query plan: anchor model specifies that actor_name has a foreign key: + + create table actor_name ( + ... + foreign key (AC_ID) references actor(AC_ID), + +This means that each record in actor_name has exactly one match in table actor. +There may be records in `actor` that have no matches in `actor_name` (they +mean actors that have NULL/no names), but we're not interested in those as it +is an inner join. + +How to remove +------------- +* Find inner joins, such that + - some table T can be accessed using eq_ref access method. + - some other table T2 has Foreign Key that refers to table T. + - there are no references to table T anywhere in the query, except for + references to T.primary_key, which can be substituted for columns of T2. + DESCRIPTION: In certain cases it is possible to eliminate table when one is running inner joins and there is a foreign key relationship between the tables. HIGH-LEVEL SPECIFICATION: Consider the following schema: -- -- The physical anchor table -- create table actor ( AC_ID int primary key ); -- -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), ); -- -- Another physical attribute: actor birthdate -- create table actor_birthdate ( AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) ); -- -- The knot view: actor with its attributes -- create view actor_attributes as select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate ... (other attributes follow) from actor left join actor_name on (actor_name.AC_ID = actor.AC_ID) left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) ... other attributes follow ... ; -- The query: names of senior actors select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' Query processing ---------------- The following steps will occur * The VIEW will be resolved with algorithm=merge * Outer-to-inner join conversion will make use of the ACBDT_Birthdate < '1950-01-01' condition and change one outer join into inner. * Table elimination variant#1 will remove all unused attributes. And we'll end up with: select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) left join actor_name on (actor_name.AC_ID = actor.AC_ID) where ACBDT_Birthdate < '1950-01-01' At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( ... foreign key (AC_ID) references actor(AC_ID), This means that each record in actor_name has exactly one match in table actor. There may be records in `actor` that have no matches in `actor_name` (they mean actors that have NULL/no names), but we're not interested in those as it is an inner join. The result is (notice the changed select): select actor_birthday.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor_birthdate left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) where ACBDT_Birthdate < '1950-01-01' How to remove ------------- * Find inner joins, such that - some table T can be accessed using eq_ref access method. - some other table T2 has Foreign Key that refers to table T. - there are no references to table T anywhere in the query, except for references to T.primary_key, which can be substituted for columns of T2. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): Table Elimination: remove the facts table (20)
by worklog-noreply＠askmonty.org 22 May '09

22 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Table Elimination: remove the facts table CREATION DATE..: Thu, 21 May 2009, 01:07 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Psergey CATEGORY.......: Server-BackLog TASK ID........: 20 (http://askmonty.org/worklog/?tid=20) VERSION........: Server-9.x STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Fri, 22 May 2009, 17:50)=-=- Category updated. --- /tmp/wklog.20.old.31598 2009-05-22 17:50:49.000000000 +0300 +++ /tmp/wklog.20.new.31598 2009-05-22 17:50:49.000000000 +0300 @@ -1 +1 @@ -Server-RawIdeaBin +Server-BackLog -=-=(Guest - Fri, 22 May 2009, 17:50)=-=- Status updated. --- /tmp/wklog.20.old.31598 2009-05-22 17:50:49.000000000 +0300 +++ /tmp/wklog.20.new.31598 2009-05-22 17:50:49.000000000 +0300 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Guest - Fri, 22 May 2009, 17:29)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.31038 2009-05-22 17:29:58.000000000 +0300 +++ /tmp/wklog.20.new.31038 2009-05-22 17:29:58.000000000 +0300 @@ -68,7 +68,7 @@ where ACBDT_Birthdate < '1950-01-01' -At this point, it's possible to conclude that table anchor can be removed from +At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( @@ -80,6 +80,18 @@ mean actors that have NULL/no names), but we're not interested in those as it is an inner join. +The result is (notice the changed select): + +select + actor_birthday.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor_birthdate + left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + How to remove ------------- * Find inner joins, such that -=-=(Guest - Thu, 21 May 2009, 22:54)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.28489 2009-05-21 22:54:52.000000000 +0300 +++ /tmp/wklog.20.new.28489 2009-05-21 22:54:52.000000000 +0300 @@ -11,7 +11,7 @@ -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( - AC_ID primary key, + AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), @@ -21,7 +21,7 @@ -- Another physical attribute: actor birthdate -- create table actor_birthdate ( - AC_ID primary key, + AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) -=-=(Psergey - Thu, 21 May 2009, 01:07)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.26317 2009-05-21 01:07:55.000000000 +0300 +++ /tmp/wklog.20.new.26317 2009-05-21 01:07:55.000000000 +0300 @@ -1 +1,91 @@ +Consider the following schema: + +-- +-- The physical anchor table +-- +create table actor ( + AC_ID int primary key +); + +-- +-- A physical attribute: actor name (non-historized for simplicity) +-- +create table actor_name ( + AC_ID primary key, + ACNAM_Name varchar(20), + index(ACNAM_Name), + foreign key (AC_ID) references actor(AC_ID), +); + +-- +-- Another physical attribute: actor birthdate +-- +create table actor_birthdate ( + AC_ID primary key, + ACBDT_Birthdate datetime, + index(ACBDT_Birthdate), + foreign key (AC_ID) references actor(AC_ID) +); + +-- +-- The knot view: actor with its attributes +-- +create view actor_attributes as +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate + ... (other attributes follow) +from + actor + left join actor_name on (actor_name.AC_ID = actor.AC_ID) + left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + ... other attributes follow ... +; + +-- The query: names of senior actors +select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' + +Query processing +---------------- +The following steps will occur +* The VIEW will be resolved with algorithm=merge +* Outer-to-inner join conversion will make use of the + ACBDT_Birthdate < '1950-01-01' condition and change one outer join into + inner. +* Table elimination variant#1 will remove all unused attributes. + +And we'll end up with: + +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor + join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + left join actor_name on (actor_name.AC_ID = actor.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + +At this point, it's possible to conclude that table anchor can be removed from +the query plan: anchor model specifies that actor_name has a foreign key: + + create table actor_name ( + ... + foreign key (AC_ID) references actor(AC_ID), + +This means that each record in actor_name has exactly one match in table actor. +There may be records in `actor` that have no matches in `actor_name` (they +mean actors that have NULL/no names), but we're not interested in those as it +is an inner join. + +How to remove +------------- +* Find inner joins, such that + - some table T can be accessed using eq_ref access method. + - some other table T2 has Foreign Key that refers to table T. + - there are no references to table T anywhere in the query, except for + references to T.primary_key, which can be substituted for columns of T2. + DESCRIPTION: In certain cases it is possible to eliminate table when one is running inner joins and there is a foreign key relationship between the tables. HIGH-LEVEL SPECIFICATION: Consider the following schema: -- -- The physical anchor table -- create table actor ( AC_ID int primary key ); -- -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), ); -- -- Another physical attribute: actor birthdate -- create table actor_birthdate ( AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) ); -- -- The knot view: actor with its attributes -- create view actor_attributes as select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate ... (other attributes follow) from actor left join actor_name on (actor_name.AC_ID = actor.AC_ID) left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) ... other attributes follow ... ; -- The query: names of senior actors select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' Query processing ---------------- The following steps will occur * The VIEW will be resolved with algorithm=merge * Outer-to-inner join conversion will make use of the ACBDT_Birthdate < '1950-01-01' condition and change one outer join into inner. * Table elimination variant#1 will remove all unused attributes. And we'll end up with: select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) left join actor_name on (actor_name.AC_ID = actor.AC_ID) where ACBDT_Birthdate < '1950-01-01' At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( ... foreign key (AC_ID) references actor(AC_ID), This means that each record in actor_name has exactly one match in table actor. There may be records in `actor` that have no matches in `actor_name` (they mean actors that have NULL/no names), but we're not interested in those as it is an inner join. The result is (notice the changed select): select actor_birthday.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor_birthdate left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) where ACBDT_Birthdate < '1950-01-01' How to remove ------------- * Find inner joins, such that - some table T can be accessed using eq_ref access method. - some other table T2 has Foreign Key that refers to table T. - there are no references to table T anywhere in the query, except for references to T.primary_key, which can be substituted for columns of T2. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): Table Elimination: remove the facts table (20)
by worklog-noreply＠askmonty.org 22 May '09

22 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Table Elimination: remove the facts table CREATION DATE..: Thu, 21 May 2009, 01:07 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Psergey CATEGORY.......: Server-BackLog TASK ID........: 20 (http://askmonty.org/worklog/?tid=20) VERSION........: Server-9.x STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Fri, 22 May 2009, 17:50)=-=- Category updated. --- /tmp/wklog.20.old.31598 2009-05-22 17:50:49.000000000 +0300 +++ /tmp/wklog.20.new.31598 2009-05-22 17:50:49.000000000 +0300 @@ -1 +1 @@ -Server-RawIdeaBin +Server-BackLog -=-=(Guest - Fri, 22 May 2009, 17:50)=-=- Status updated. --- /tmp/wklog.20.old.31598 2009-05-22 17:50:49.000000000 +0300 +++ /tmp/wklog.20.new.31598 2009-05-22 17:50:49.000000000 +0300 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Guest - Fri, 22 May 2009, 17:29)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.31038 2009-05-22 17:29:58.000000000 +0300 +++ /tmp/wklog.20.new.31038 2009-05-22 17:29:58.000000000 +0300 @@ -68,7 +68,7 @@ where ACBDT_Birthdate < '1950-01-01' -At this point, it's possible to conclude that table anchor can be removed from +At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( @@ -80,6 +80,18 @@ mean actors that have NULL/no names), but we're not interested in those as it is an inner join. +The result is (notice the changed select): + +select + actor_birthday.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor_birthdate + left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + How to remove ------------- * Find inner joins, such that -=-=(Guest - Thu, 21 May 2009, 22:54)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.28489 2009-05-21 22:54:52.000000000 +0300 +++ /tmp/wklog.20.new.28489 2009-05-21 22:54:52.000000000 +0300 @@ -11,7 +11,7 @@ -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( - AC_ID primary key, + AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), @@ -21,7 +21,7 @@ -- Another physical attribute: actor birthdate -- create table actor_birthdate ( - AC_ID primary key, + AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) -=-=(Psergey - Thu, 21 May 2009, 01:07)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.26317 2009-05-21 01:07:55.000000000 +0300 +++ /tmp/wklog.20.new.26317 2009-05-21 01:07:55.000000000 +0300 @@ -1 +1,91 @@ +Consider the following schema: + +-- +-- The physical anchor table +-- +create table actor ( + AC_ID int primary key +); + +-- +-- A physical attribute: actor name (non-historized for simplicity) +-- +create table actor_name ( + AC_ID primary key, + ACNAM_Name varchar(20), + index(ACNAM_Name), + foreign key (AC_ID) references actor(AC_ID), +); + +-- +-- Another physical attribute: actor birthdate +-- +create table actor_birthdate ( + AC_ID primary key, + ACBDT_Birthdate datetime, + index(ACBDT_Birthdate), + foreign key (AC_ID) references actor(AC_ID) +); + +-- +-- The knot view: actor with its attributes +-- +create view actor_attributes as +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate + ... (other attributes follow) +from + actor + left join actor_name on (actor_name.AC_ID = actor.AC_ID) + left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + ... other attributes follow ... +; + +-- The query: names of senior actors +select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' + +Query processing +---------------- +The following steps will occur +* The VIEW will be resolved with algorithm=merge +* Outer-to-inner join conversion will make use of the + ACBDT_Birthdate < '1950-01-01' condition and change one outer join into + inner. +* Table elimination variant#1 will remove all unused attributes. + +And we'll end up with: + +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor + join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + left join actor_name on (actor_name.AC_ID = actor.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + +At this point, it's possible to conclude that table anchor can be removed from +the query plan: anchor model specifies that actor_name has a foreign key: + + create table actor_name ( + ... + foreign key (AC_ID) references actor(AC_ID), + +This means that each record in actor_name has exactly one match in table actor. +There may be records in `actor` that have no matches in `actor_name` (they +mean actors that have NULL/no names), but we're not interested in those as it +is an inner join. + +How to remove +------------- +* Find inner joins, such that + - some table T can be accessed using eq_ref access method. + - some other table T2 has Foreign Key that refers to table T. + - there are no references to table T anywhere in the query, except for + references to T.primary_key, which can be substituted for columns of T2. + DESCRIPTION: In certain cases it is possible to eliminate table when one is running inner joins and there is a foreign key relationship between the tables. HIGH-LEVEL SPECIFICATION: Consider the following schema: -- -- The physical anchor table -- create table actor ( AC_ID int primary key ); -- -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), ); -- -- Another physical attribute: actor birthdate -- create table actor_birthdate ( AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) ); -- -- The knot view: actor with its attributes -- create view actor_attributes as select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate ... (other attributes follow) from actor left join actor_name on (actor_name.AC_ID = actor.AC_ID) left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) ... other attributes follow ... ; -- The query: names of senior actors select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' Query processing ---------------- The following steps will occur * The VIEW will be resolved with algorithm=merge * Outer-to-inner join conversion will make use of the ACBDT_Birthdate < '1950-01-01' condition and change one outer join into inner. * Table elimination variant#1 will remove all unused attributes. And we'll end up with: select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) left join actor_name on (actor_name.AC_ID = actor.AC_ID) where ACBDT_Birthdate < '1950-01-01' At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( ... foreign key (AC_ID) references actor(AC_ID), This means that each record in actor_name has exactly one match in table actor. There may be records in `actor` that have no matches in `actor_name` (they mean actors that have NULL/no names), but we're not interested in those as it is an inner join. The result is (notice the changed select): select actor_birthday.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor_birthdate left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) where ACBDT_Birthdate < '1950-01-01' How to remove ------------- * Find inner joins, such that - some table T can be accessed using eq_ref access method. - some other table T2 has Foreign Key that refers to table T. - there are no references to table T anywhere in the query, except for references to T.primary_key, which can be substituted for columns of T2. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): Table Elimination: remove the facts table (20)
by worklog-noreply＠askmonty.org 22 May '09

22 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Table Elimination: remove the facts table CREATION DATE..: Thu, 21 May 2009, 01:07 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Psergey CATEGORY.......: Server-RawIdeaBin TASK ID........: 20 (http://askmonty.org/worklog/?tid=20) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Fri, 22 May 2009, 17:29)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.31038 2009-05-22 17:29:58.000000000 +0300 +++ /tmp/wklog.20.new.31038 2009-05-22 17:29:58.000000000 +0300 @@ -68,7 +68,7 @@ where ACBDT_Birthdate < '1950-01-01' -At this point, it's possible to conclude that table anchor can be removed from +At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( @@ -80,6 +80,18 @@ mean actors that have NULL/no names), but we're not interested in those as it is an inner join. +The result is (notice the changed select): + +select + actor_birthday.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor_birthdate + left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + How to remove ------------- * Find inner joins, such that -=-=(Guest - Thu, 21 May 2009, 22:54)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.28489 2009-05-21 22:54:52.000000000 +0300 +++ /tmp/wklog.20.new.28489 2009-05-21 22:54:52.000000000 +0300 @@ -11,7 +11,7 @@ -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( - AC_ID primary key, + AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), @@ -21,7 +21,7 @@ -- Another physical attribute: actor birthdate -- create table actor_birthdate ( - AC_ID primary key, + AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) -=-=(Psergey - Thu, 21 May 2009, 01:07)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.26317 2009-05-21 01:07:55.000000000 +0300 +++ /tmp/wklog.20.new.26317 2009-05-21 01:07:55.000000000 +0300 @@ -1 +1,91 @@ +Consider the following schema: + +-- +-- The physical anchor table +-- +create table actor ( + AC_ID int primary key +); + +-- +-- A physical attribute: actor name (non-historized for simplicity) +-- +create table actor_name ( + AC_ID primary key, + ACNAM_Name varchar(20), + index(ACNAM_Name), + foreign key (AC_ID) references actor(AC_ID), +); + +-- +-- Another physical attribute: actor birthdate +-- +create table actor_birthdate ( + AC_ID primary key, + ACBDT_Birthdate datetime, + index(ACBDT_Birthdate), + foreign key (AC_ID) references actor(AC_ID) +); + +-- +-- The knot view: actor with its attributes +-- +create view actor_attributes as +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate + ... (other attributes follow) +from + actor + left join actor_name on (actor_name.AC_ID = actor.AC_ID) + left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + ... other attributes follow ... +; + +-- The query: names of senior actors +select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' + +Query processing +---------------- +The following steps will occur +* The VIEW will be resolved with algorithm=merge +* Outer-to-inner join conversion will make use of the + ACBDT_Birthdate < '1950-01-01' condition and change one outer join into + inner. +* Table elimination variant#1 will remove all unused attributes. + +And we'll end up with: + +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor + join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + left join actor_name on (actor_name.AC_ID = actor.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + +At this point, it's possible to conclude that table anchor can be removed from +the query plan: anchor model specifies that actor_name has a foreign key: + + create table actor_name ( + ... + foreign key (AC_ID) references actor(AC_ID), + +This means that each record in actor_name has exactly one match in table actor. +There may be records in `actor` that have no matches in `actor_name` (they +mean actors that have NULL/no names), but we're not interested in those as it +is an inner join. + +How to remove +------------- +* Find inner joins, such that + - some table T can be accessed using eq_ref access method. + - some other table T2 has Foreign Key that refers to table T. + - there are no references to table T anywhere in the query, except for + references to T.primary_key, which can be substituted for columns of T2. + DESCRIPTION: In certain cases it is possible to eliminate table when one is running inner joins and there is a foreign key relationship between the tables. HIGH-LEVEL SPECIFICATION: Consider the following schema: -- -- The physical anchor table -- create table actor ( AC_ID int primary key ); -- -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), ); -- -- Another physical attribute: actor birthdate -- create table actor_birthdate ( AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) ); -- -- The knot view: actor with its attributes -- create view actor_attributes as select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate ... (other attributes follow) from actor left join actor_name on (actor_name.AC_ID = actor.AC_ID) left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) ... other attributes follow ... ; -- The query: names of senior actors select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' Query processing ---------------- The following steps will occur * The VIEW will be resolved with algorithm=merge * Outer-to-inner join conversion will make use of the ACBDT_Birthdate < '1950-01-01' condition and change one outer join into inner. * Table elimination variant#1 will remove all unused attributes. And we'll end up with: select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) left join actor_name on (actor_name.AC_ID = actor.AC_ID) where ACBDT_Birthdate < '1950-01-01' At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( ... foreign key (AC_ID) references actor(AC_ID), This means that each record in actor_name has exactly one match in table actor. There may be records in `actor` that have no matches in `actor_name` (they mean actors that have NULL/no names), but we're not interested in those as it is an inner join. The result is (notice the changed select): select actor_birthday.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor_birthdate left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) where ACBDT_Birthdate < '1950-01-01' How to remove ------------- * Find inner joins, such that - some table T can be accessed using eq_ref access method. - some other table T2 has Foreign Key that refers to table T. - there are no references to table T anywhere in the query, except for references to T.primary_key, which can be substituted for columns of T2. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0