Learning Goal: I’m working on a mysql discussion question and need an explanation and answer to help me learn.
Q#1 4 pts Query plan comparisons
This question does not require any code development Instead, it displays the query plan generated by MySQL for two very similar queries on 3 COMPANY database tables.
Consider the query Q: “For only the projects located at Stafford,
display each project number, the department number that controls that project, and the last name, address, and date of birth of the manager of the controlling department”
First, generate the MySQL query plan for Q. (Study the GREEN section “Notes on qo” at end of document)
Second, modify Q by omitting the project location condition.
Generate the MySQL query plan for this modified query.
Note – Query plan display column “key” identifies actual indexes used by the corresponding table name or table alias in that display row. You should see a difference in the key values appearing in the query plans.
Q#2 12 pts Computing transitive closure
MySQL does not support the SQL WITH clause, one use is: calculating recursive transitive closure.
Recall that given a COMPANY database EMPLOYEE row PK value, such as ‘333445555’, the transitive closure “chain” of pairs (ssn, superssn) is (‘333445555’,’ 888665555’) (‘888665555’,null).
Each PK value has an associated transitive closure chain, the last element of which is always (‘888665555’,null) because Borg is the only employee having no supervisor.
Develop the following using MySQL:
Create an empty table (refer to as anstab) having row schema (ssn, superssn) whose columns have the same types as in the mitchell_test_1.EMPLOYEE ssn and superssn columns.
anstab must not have a PK because some rows inserted in anstab have a likelihood of duplication.
- Given an initialized user-defined MySQL variable (aka bind variable) @f_ssn (its value is any existing EMPLOYEE ssn value)
- Store the transitive closure chain (@f_ssn, supssn1)(supssn1, supssn2) … (supssnj, null)
as j rows in anstab
- Store a row in anstab that looks like: – – – – -, – – – – – (it indicates visially that a transitive closure chain is complete)
In this chain, supssn1 represents the superssn value for the EMPLOYEE row with ssn = @f_ssn, supssn2 represents the superssn column for the EPLOYEE row with ssn = supssn1, and so on.
There are many ways to populate table anstab as above. We need to simulate the standard SQL WITH using: stored procedure/MySQL processing. The following outlines a suggested solution.
Create a stored procedure (refer to here as P1).
P1 has IN mode parameter ssn and INOUT parameter supssn. Each P1 execution stores a row of a transitive closure chain. P1 uses the MySQL CONCAT function to build and then execute two PREPARE statements:
The first PREPARE statement stores into supssn the superssn column value of the EMPLOYEE row having PK value ssn < = this ssn refers to the IN parameter
and
The second PREPARE statement stores into anstab the row
(value of IN parameter ssn, value of INOUT parameter supssn).
Because supssn is an INOUT parameter, supssn passes back to the caller of P1 the first element of the next transitive closure pair.
Create another stored procedure (refer to as P2).
Each P2 execution calls P1 several times such that all rows of a complete transitive closure are stored in anstab.
P2 has IN mode parameter (refer to as x) whose value is the left component of the first member pair of a transitive closure to be computed. x initiates/starts the storage into anstab of one complete transitive closure.
P2 executes a loop whose loop body continually calls P1 until the previous P1 call returns supssn value null – – returning null means that a complete transitive closure chain has been stored in anstab, and then, the loop terminates (thus finishing a transitive closure).
The MySQL arguments/parameters interface between P1’s returned INOUT parameter value and P2 is that in P2, a user-defined variable named @supssn should be initialized to, say ‘000000000’
(or to any other impossible EMPLOYEE table ssn value).
This argument/parameter interaction simplifies the PREPARE statements logic in P1’s code.
- Assuming the above notations, each P2 call to P1 has the form: CALL P1( x, @supssn );
The last P2 execution action inserts a “border” row into anstab consisting of (‘_ _ _’, ‘_ _ _’).
The border row separates consecutive complete transitive closure, where each transitive closure is produced by a call to P2.
= = = = = = = = = = = = = = = = = = = = =
{ You have the option in the P1 & P2 parameter specifications to add another IN mode parameter for the name of the table. This means that instead of hard-coding table name anstab (as done above to simplify the specifications), P1 and P2 become more adaptable procedures. IF you choose to do this, pass the table name enclosed in single quotes, as demoed in module P4 }
= = = = = = = = = = = = = = = = = = = = =
Sample results
At MySQL command level, the two successive calls call P2(‘999887777’); and call P2( ‘333445555’); with an initially existing and empty anstab table produce:
+———–+———–+
| ssn | supssn |
+———–+———–+
| 333445555 | 888665555 | < == Recall that MySQL displays string type column values without quotes
| 888665555 | NULL | < == Last pair of a transitive closure
| _ _ _ | _ _ _ | < == Transitive closure chain separator
| 999887777 | 987654321 |
| 987654321 | 888665555 |
| 888665555 | NULL | < == Last pair of a transitive closure
| _ _ _ | _ _ _ | < == Transitive closure chain separator
+———–+———–+
7 rows in set (0.00 sec)
Hint#1 – Although the SQL standard specifies (using an indicator variable) a way for 3GL host languages to recognize a null value returned from SQL, each 3GL has its own way of detecting a null value returned from SQL. MySQL does not implement such SQL standard indicator variables.
Use the MySQL built-in function ISNULL(x) that returns 1 if x’s value is null, and returns 0 if x’s value is not null. Thus, you can use ISNULL in P2’s code logic to determine when a transitive closure calculation is finished.
Hint#2 – MySQL error messages are terse and many are inaccurate/unhelpful for stored procedure development and debugging. It is a good idea, particularly during P1 development, to trace/display passed argument values, bind variable values, and so on.
Display your source code for P1 and P2 (or all (alternative to P1 & P2) source code you develop)
Execute your anstab table creation
Call P2 (or your functionally equivalent stored procedure) with argument ‘987987987’
Call P2 (or your functionally equivalent stored procedure) with argument ‘333445555’
Call P2 (or your functionally equivalent stored procedure) with argument ‘888665555’
Display all rows of your final anstab table.
Final Note – DO NOT remove the rows from anstab between P2 calls. The content of anstab must follow the format of the above illustrated “Sample results” and will contain 3 complete transitive closure chains, one chain per P2 call.
Q#3 4 pts Joining projected tables
Earlier we covered horizontal partitioning in which a table T is divided into a number of “partitioned” tables, each partition containing a disjoint subset of entire rows of T.
Only sketched earlier is vertical partitioning in which a table is divided into a number of tables Tk where each Tk has a subset of T’s columns. However, as we will see in this question, the columns of the Tk cannot be disjoint column sets for most vertical partitioning applications.
In large-scale databases, there is often a need for
a) decomposing a table T into tables Tj, 1 <= j <= m and then, at some point in time
b) re-constructing T from the tables Tj.
(Such de/re-constructions occur in (truly) distributed databases where a table can be vertically partitioned across several independent database servers)
Consider the requirement (refer to as “R”) in which the join on all Tj must be exactly all rows of T.
There is a significant problem when implementing R –
Almost every way of creating the tables Tj is such that the join of the Tj WILL NOT equal T …
Given this join issue, fortunately, there is known technique that ensures that the natural join of the Tj satisfies R. That is, the join result returns exactly the rows of T, no more rows, and no fewer rows.::
Given FD: X Y in relation T(U), T can safely be split into two relations with attributes X in common, so that (Π X,Y (T)) naturalJoin x=x (Π X,Z (T)) = T.
In this equation, Z = U–{X,Y} (i.e., Z is all attributes of T other than X and Y)
This means that when the Tj are natural-joined, no data rows are lost, nor are any spurious (means: extra, incorrect) rows introduced. This process is called “non-lossless-join” decomposition of T into smaller relations Tj.
This problem illustrates the R solution technique using the COMPANY database table DEPENDENT.
To simplify your solution development, instead of tables Tj, use views, as defined below.
PART a)
Create a view (refer to as hv1) consisting of the distinct columns essn,dependent_name,sex,bdate of DEPENDENT. Note that SQL DISTINCT implements the RA Π operation.
X is essn, dependent_name and sex, bdate is Y.
Clearly, X Y (i.e. X functionally determines Y)
Create a view (refer to as hv2) consisting of distinct columns essn,dependent_name,relationship of DEPENDENT.
Execute and display the results of query “Q1”: the natural join of views hv1 and hv2 on x.
PART b)
Repeat PART a) changing only the columns for the views. To keep descriptions separate, the two views are named de1 and de2.
The columns of de1 are essn,sex,bdate,relationship, and
the columns of de2 are essn,dependent_name
Here X is essn, and Y is sex,bdate,relationship. Notice that X Y does not hold.
Execute and display the results of query “Q2”: the natural join of views de1 and de2 on x
Suggestions/Hints – Take advantage of the OR REPLACE clause of CREATE VIEW;
it simplifies the scripting and cleanup/reset between successive code executions (eliminates errors such as ‘view xyz already exists …’)
By the way, the default privileges in effect when a user executes a view created by another user is:
so-called SQL invoker privileges. This means that the view is executed with the privileges of the user that is executing the view, not the user that created the view (if different)
Notes on qo
Results of the MySQL query plans are disappointing concerning their completeness/usefulness:
The EXPLAIN EXTENDED format indicates little more than 1) index use per table 2) no index use when
a table is scanned (indicated by a “WHERE” entry in the query plan table column named Extra).
We need only compare the MySQL plan for, say the First query of Q#1 to any advanced RDBMS
(Microsoft SQL server, IBM DB2, Postgres, Oracle) for the exact same query to see the difference in sophistication and usefulness.
Below is the Oracle default query plan for the exact same table schemas, indexes, etc. as Q#1, First query
SQL> select p.pnumber, p.dnum, lname, address, bdate
2 from p, d, e
3 where p.dnum=d.dnumber and
4 d.mgrssn=e.ssn and
5 p.plocation = ‘Stafford’;
———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 2 | 196 | 7 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2 | 196 | 7 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 2 | 66 | 5 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | PROJECT | 2 | 42 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 12 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_C0011634 | 1 | | 0 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_C0011630 | 1 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 65 | 1 (0)| 00:00:01 |
———————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
4 – filter(“P”.”PLOCATION”=’Stafford’)
6 – access(“P”.”DNUM”=”D”.”DNUMBER”)
7 – access(“D”.”MGRSSN”=”E”.”SSN”)
Oracle qo operations are performed from inner-most rows (here, Id = 5 and 6) to outer-most.
There is much more info compared to a MySQL query plan.
The most notable are the Rows through Time (right-most plan columns) that indicate the relative costs of each qo operation. MySQL does no such thing, as MySQL acts mostly as a Rule-based rather than Cost-based optimizer.
Rule-based optimizers are obsolete.
Relative cost terms are the way that a user can identify the most expensive steps of a query plan, for which adjustments in the query, or changes in existing AMs might improve the query plan.
The Predicate Information trailing section is also helpful as it describes HOW and/or WHY a given index/AM is used in a query WHERE clause..
