EXPLAIN REFERENCED was created as a convenience to gather information about a SQL query.
EXPLAIN REFERENCED option sql_statement |
Where sql_statement must be explainable DML and the option provided is one of the following:
| DATABASES | List of databases referenced by objects in the query and their oids | ||
| SCHEMAS | same as DATABASES | ||
| TABLES | List of tables referenced by the query, the table oid, and database name | ||
| CREATE SQL | DDL statements needed to create objects (tables and views) referenced by the query. | ||
| VIEWS | List of views referenced by objects in the query and their oids | ||
Example
The following query queries tables and views. EXPLAIN REFERENCED provides the complete list of DDL needed by the query.
SQL> explain referenced create sql select firstname, lastname, x from vfoo, bar where vfoo.id = bar.id;
+---------------------------------------------------------------------------------------------------------------------------------+
| Create SQL |
+---------------------------------------------------------------------------------------------------------------------------------+
| CREATE DATABASE `clieu` /*!40100 CHARACTER SET utf8 */ |
| CREATE TABLE `clieu`.`foo` (
`x` int(11) not null,
`y` int(11),
`z` int(11),
`id` int(11),
PRIMARY KEY (`x`) /*$ DISTRIBUTE=1 */,
KEY `z_idx` (`z`) /*$ DISTRIBUTE=1 */
) CHARACTER SET utf8 /*$ SLICES=3 */
|
| CREATE TABLE `clieu`.`bar` (
`id` int(11),
`firstname` varchar(25) CHARACTER SET utf8 not null,
`lastname` varchar(25) CHARACTER SET utf8 not null,
`username` varchar(16) CHARACTER SET utf8 not null,
`email` varchar(35) CHARACTER SET utf8,
`joined` date not null,
PRIMARY KEY (`joined`) /*$ DISTRIBUTE=1 */
) CHARACTER SET utf8 /*$ SLICES=4 */
|
| CREATE VIEW `clieu`.`vfoo` (`x`, `y`, `z`, `id`) AS select * from foo |
+---------------------------------------------------------------------------------------------------------------------------------+
|
Overview
Content Tools