Database Tuning (MOSC)

MOSC Banner

View performance degraded in CTAS

in Database Tuning (MOSC) 6 commentsAnswered ✓

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center