FTS takes forever...
Hi!
I have this query that have taken over 7 hours! Its stuck on "db file sequential read" on table ORDLOC, its doing a FTS...
The statistics are up to date and no new plan.
The table involved is 3.1 million rows and analysed the 9th of September.
The SQL:
SELECT 'X'
FROM DUAL
WHERE EXISTS
(SELECT DISTINCT 'X'
FROM ORDLOC OL,
STORE S,
ORDHEAD OH,
BLA_WH_MARKET LWM
WHERE S.DEFAULT_WH = LWM.WH
AND S.STORE = :B1
AND (OL.SKU IN (SELECT :B2 FROM DUAL
UNION ALL
SELECT PACK_NO
FROM PACKSKU PS
WHERE PS.SKU = :B2))
AND OH.ORDER_NO = OL.ORDER_NO
I have this query that have taken over 7 hours! Its stuck on "db file sequential read" on table ORDLOC, its doing a FTS...
The statistics are up to date and no new plan.
The table involved is 3.1 million rows and analysed the 9th of September.
The SQL:
SELECT 'X'
FROM DUAL
WHERE EXISTS
(SELECT DISTINCT 'X'
FROM ORDLOC OL,
STORE S,
ORDHEAD OH,
BLA_WH_MARKET LWM
WHERE S.DEFAULT_WH = LWM.WH
AND S.STORE = :B1
AND (OL.SKU IN (SELECT :B2 FROM DUAL
UNION ALL
SELECT PACK_NO
FROM PACKSKU PS
WHERE PS.SKU = :B2))
AND OH.ORDER_NO = OL.ORDER_NO
0