Good day,
We have a weird issue, wherein a trivial CTAS statement on a 5-row view would yield in close to 5 min, whereas the SELECT statement per se yields instantly
SELECT * FROM V ; -- instantly yields 5 rows
CREATE TABLE T AS SELECT * FROM V ; -- takes ca. 5 mins to complete
We have seen a similar performance issue on a ~20k rows view, but nailed that down to an OUTER JOIN. In this situation, firstly there are no OUTER JOINs, and secondly the view yields only a handful of rows...
The view DDL looks like :
CREATE OR REPLACE FORCE VIEW V
AS
SELECT
SUBSTR(A2.SVAL, 0,3) BOU,
A1.KEY, A1.DTE, A1.STATUS
FROM ( SELECT KEY, DTE, '' STATUS FROM VOSES WHERE NSY=(SELECT MAX(NSY) FROM OSES)
UNION SELECT 'IMAGE' KEY, MAX(DAT) DTE, '' STATUS FROM VBVA
UNION SELECT 'AUTO' KEY, SYSDATE DTE, 'X' STATUS FROM DUAL
UNION SELECT 'VEXE' KEY, SYSDATE DTE, NVL(SVAL,'?') FROM PRMUSR WHERE KEY='VER_EXE'
UNION SELECT 'VDB' KEY, SYSDATE DTE, NVL(SVAL,'?') FROM PRMUSR WHERE KEY='VER_DB'
) A1, PRMUSR A2
WHERE A2.KEY = 'DBID'
ORDER BY A1.KEY
;
(The only "large" structure in this is VBVA view which is an inner join of a couple of large tables, indexed, ca. 100k records -- DAT is indexed too -- the other tables are simple structures with <100 rows.)
Any thoughts on this issue would be greatly appreciated. (Explain plan + empty create then insert /*+ append */ will follow in a couple of days.)
Oracle version:
Oracle Database 10g Release 10.2.0.1.0 - Production
(is this another bug patched in 10.2.0.3?)