View performance degraded in CTAS
Hi all,
Routinely tuning my databases I noticed that there is a MV refreshing slowly. I extracted SQL it was based on, and tuned it using sqltrpt.sql. Finally I got BEGIN_OUTLINE_DATA / END_OUTLINE_DATA hint and SQL performs well. Final result set is small, 2000 rows, and table is not wide, maybe 20 columns. I created view based on this SQL, and select or select count(*) from this view both perform well. But when I try Create table ... as select * from this view , then plan becomes inefficient again and this statement takes again considerable amount of time. So please help