Home » RDBMS Server » Performance Tuning » can i create a materialized with column aliases.
can i create a materialized with column aliases. [message #144612] Thu, 27 October 2005 07:12 Go to next message
siddhardha
Messages: 3
Registered: October 2005
Junior Member
Hi all,

I have a view like this.

CREATE OR REPLACE FORCE VIEW
MSC_ITEM_ATTRIBUTES_V (a1,b1,c1,d1,e1)
AS
SELECT a,b,c,d,e
FROM table1,table2 ....
where some joins
union
select g,h,i,j,k
FROM tablen,tablen+1,...
where some joins

the above view performance is very slow.

I thought i will create a materialized view out of it.
But don't know whether i can create using column aliases as above or is there any better way of changing the same view.

1.created required indexes on base tables
2.using hint as /*+ FIRST_ROWS */ in the oracle forms also.

I am using this view even in PL/SQL code for some logic.

Pls let me know if i can improve this by any way.

Thanks.
Siddhardha.
Re: can i create a materialized with column aliases. [message #144915 is a reply to message #144612] Fri, 28 October 2005 17:56 Go to previous message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
This is very difficult to solve, for the simple reason that
<quote>
SELECT a,b,c,d,e
FROM table1,table2 ....
where some joins
union
select g,h,i,j,k
FROM tablen,tablen+1,...
where some joins
</quote>
is not very helpful. I mean, where's the explain plan, the
proper queries etc?

If not forthcoming, a thing you could try (but of course, it would
depend on your data) is changing the UNION to a UNION ALL, or are you relying on the distinct set?

Regards
Previous Topic: Should Timeout period be increased
Next Topic: issue in temporary tablespace
Goto Forum:
  


Current Time: Fri Mar 29 07:08:38 CDT 2024