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