| can i create a materialized with column aliases. [message #144612] |
Thu, 27 October 2005 07:12  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
