maria-developers team mailing list archive

[worklog-noreply@xxxxxxxxxxxx]

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