CODEWITHSUNDEEP
oracle-databasevieworacle10g
dman
dman

Reputation: 73

Creating a non-materialized view takes a lot of Time and CPU in oracle

I'm working on migrating application from Access to Oracle and have faced with a strange issue . So we have a regular oracle schema - nothing fancy. on the top of that schema I created a number of views - approximately 15. These views use each other and dependency tree can be deep - I'd say up to 6-8 levels.

So now I faced with an issue when I cannot create another view - CPU at oracle servers goes to 50% when I execute 'create or replace view' statement and it takes forever. Views are right now in such a state that selection data from these views may take time, but the issue appears in 'create' statement. I'm not using 'select * ...' in the views and the problematic view depends just on two others.

I'm using Oracle 10g Enterprise v 10.2 In SQL Server I'm familiar with Profiler and would do a trace, view schema locks, but I don't know Oracle that much.

Will appreciate any hints. Thank you.

Upvotes: 0

Views: 520

Answers (1)

APC
APC

Reputation: 146239

Views referencing views referencing views strikes me as highly unnecessary. I know we're all supposed to be in favour of "don't repeat yourself" but DRY is a guideline, not a cast-iron rule. It's certainly not intended to be applied so compactly in a database context that nothing compiles.

So try separating out all the views, so that each one references only tables in the FROM clause. That should solve your problem and allow you to make progress with your code.

You can always review the situation later. The neat thing about a view is that it is just an interface. If you subsequnetly want to refactor some views, to replace tables with views than you will be able to, with the minimum of inconvenience (unless you re-introduce the compilation hang).

Upvotes: 1

Related Questions