Oracle – Quick Tip – Zero=One Trick To Generate Table Structures Dynamically From SELECT Statements!

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.

Using PL/SQL


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;                     

Using SQL

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

Target Table

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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s