I have a problem query that I could use some help. This code ran
in 20 minutes last year and there is not much change in the
input data this year....perhaps 10% growth. Currently this is
running 8+ hours and it is unacceptable. This must run in
Production next weekend.
The tables we are running against are in Integration newly loaded
in key order, or just recently reorg'd and have current stats.
Explain follows the query and doesn't reflect long run times.
Internal monitoring shows it using DSNDB07 DSN4K02 a great deal
of the time it is running.
INTO :H , :H
FROM H6_ASSOC A1
, H6_ASSOC A2
, H6_ASSOC A3
, H6_NODE N1
, H6_NODE N2
WHERE A1.PREV_NODE_ID = :H
AND A1.ASSOC_TYPE = 3324
AND A2.PREV_NODE_ID = A1 . NEXT_NODE_ID
AND A2.ASSOC_TYPE = 3324
AND A3.PREV_NODE_ID = :H
AND A3.NEXT_NODE_ID = A2.NEXT_NODE_ID
AND A3.ASSOC_TYPE = 3323
AND N1.NODE_TYPE_ID = 3433
AND N1.NODE_ID = A1.NEXT_NODE_ID
AND N2.NODE_TYPE_ID = 3432
AND N2.NODE_ID = A2.A2.NEXT_NODE_ID ;