Oracle SQL - Subquery Works fine, However Create Table with that subquery appears to hang

Question

I have the following query structure

CREATE TABLE <Table Name> AS 
(
   SELECT .... FROM ...
) 

When i run the SELECT statement on its own, this compiles and returns the results within seconds. however when I run that with the CREATE Table Statement it takes hours to the point where I believe it has hung and will never compile.

What is the reason for this? and what could a work around be?

• Oracle Database 12c <12.1.0.2.0>

Answer 1

So the answer to this one.

CREATE TABLE <Table Name> AS 
(
   SELECT foo 
   FROM baa
   LEFT JOIN 
   ( SELECT foo FROM baa WHERE DATES BETWEEN SYSDATE AND SYSDATE - 100 )
   WHERE DATES_1 BETWEEN SYSDATE - 10 AND SYSDATE - 100
)  

The problem was that the BETWEEN statements did not match the same time period and the sub query was looking at more data than the main query (I guess this was causing a full scan over the tables?)

The below query has the matching between statement time period and this returned the results in less than 3 minutes.

CREATE TABLE <Table Name> AS 
(
   SELECT foo FROM baa
   LEFT JOIN ( SELECT foo FROM baa WHERE DATES BETWEEN SYSDATE - 10 AND SYSDATE - 100 )
   WHERE DATES_1 BETWEEN SYSDATE - 10 AND SYSDATE - 100
)