This is one of those ancient tricks that everybody discovers for themselves before knowing that it was common-knowledge already.
Here is a simple scenario:
- How to efficiently create a table structure (with no data) based on a SQL SELECT statement which could potentially return millions of rows?
Our Million Row Table – EMP_TEST
Let us pretend that the below table we are creating (with a few rows) really has millions of rows!
CREATE TABLE Emp_Test AS SELECT * FROM ( WITH sample_data AS ( SELECT TO_CHAR(NULL) AS Manager, 'Employee 1' AS Employee FROM DUAL UNION ALL SELECT 'Employee 1' AS Manager, 'Employee 1.1' AS Employee FROM DUAL UNION ALL SELECT 'Employee 1' AS Manager, 'Employee 1.2' AS Employee FROM DUAL UNION ALL SELECT 'Employee 1' AS Manager, 'Employee 1.3' AS Employee FROM DUAL ) SELECT * FROM sample_data ) DESC Emp_Test; CREATE TABLE EMP_TEST ( MANAGER VARCHAR2(10 BYTE), EMPLOYEE VARCHAR2(12 BYTE) );
Complex SELECT against EMP_TEST
Let us also pretend that this is a complicated select statement that would return millions of rows and we need to serialize the structure of this query through PL/SQL code
-- ****** This query returns millions of rows ******* -- SELECT a.*, CASE WHEN Manager IS NULL THEN 'Y' ELSE 'N' END AS Is_Super_Boss, SYSDATE AS Report_Date FROM Emp_Test a
Creating a new table structure based on query that returns a large resultset
Here is how to do it with the “0=1” trick. Basically, we add a “0=1” condition around the SQL to make it return no rows and then use the results to create the empty structure.
DECLARE v_SQL VARCHAR2(255); v_SQL_Struct VARCHAR2(255); BEGIN v_SQL := 'SELECT ' || ' a.*, ' || ' CASE WHEN Manager IS NULL THEN ''Y'' ELSE ''N'' END AS Is_Super_Boss, ' || ' SYSDATE AS Report_Date ' || 'FROM Emp_Test a '; -- ****** This query return milllions of rows. Wrap it with “0=1” WHERE clause ******* -- v_SQL_Struct := 'CREATE TABLE QUERY_STRUCT_TEST AS ' || 'SELECT * FROM ' || ' (' || v_SQL || ' ) WHERE 0 = 1'; EXECUTE IMMEDIATE v_SQL_Struct; END;
CREATE TABLE QUERY_STRUCT_TEST AS -- ****** This query returns millions of rows ******* -- SELECT a.*, CASE WHEN Manager IS NULL THEN 'Y' ELSE 'N' END AS Is_Super_Boss, SYSDATE AS Report_Date FROM Emp_Test a WHERE 0=1
We got the structure for the complicated query:
DESC QUERY_STRUCT_TEST CREATE TABLE QUERY_STRUCT_TEST ( MANAGER VARCHAR2(10 BYTE), EMPLOYEE VARCHAR2(12 BYTE), IS_SUPER_BOSS CHAR(1 BYTE), REPORT_DATE DATE )
Controlling the column datatypes with CAST
One could also influence the data types of the columns created by the SQL by CAST’ing columns in the SELECT to the appropriate data type.
E.g.,: SELECT CAST(‘Y’ AS VARCHAR2(1)) AS Column1 FROM DUAL;