----------------------------------------------------------------------- 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)