-1

I have a SQL query which takes much time to execute (like 30 minutes!) due to the huge data it takes.

However, I need to do a couple of things more on this result a self join on this query.

I can't create a temporary table on this database.

What I want is a way to do this self join without executing this query twice.

Is there a way to write a main query (maybe with the 'WITH' syntax) to allow that?

Edit: Here is a sample of what would work:

With
MyQueryAlias1 AS
(30 minutes sql query here)

MyQueryAlias2 AS
(30 minutes sql query here)

SELECT
MyQueryAlias1.field1

FROM
MyQueryAlias1
JOIN MyQueryAlias2
ON MyQueryAlias1.field2 = MyQueryAlias2.field3

This is just an example but it shows that the "30 minutes sql query here" will be executed twice, which is my problem.

Papa doc
  • 179
  • 2
  • 11
  • Please post a minimal example of what you are trying to achieve (including the DDL and DML statements to generate appropriate tables and sample data) and tell us what the expected results would be. There are various possibilities for eliminating self joins (sometimes using analytic functions or hierarchical queries - but it may equally be that it is not possible to eliminate the join) but we can't help unless we know what the problem is. – MT0 Mar 07 '16 at 09:42
  • Cant help unless we get a example to work upon – Avrajit Roy Mar 07 '16 at 09:42

1 Answers1

3

You could just simplify your query to:

WITH MyQueryAlias1 AS (30 minutes sql query here)
SELECT q1.field1
FROM   MyQueryAlias1 q1
       JOIN MyQueryAlias1 q2
       ON q1.field2 = q2.field3

Which will get rid of the second sub-query factoring clause.

This might also work:

WITH MyQueryAlias1 AS (30 minutes sql query here)
SELECT field1
FROM   MyQueryAlias1
WHERE  LEVEL = 1
AND    ( field2 = field3 OR CONNECT_BY_ISLEAF = 0 )
CONNECT BY NOCYCLE
       PRIOR field2 = field3
AND    LEVEL = 2;
MT0
  • 143,790
  • 11
  • 59
  • 117