s******e
发帖数: 493 | 1 Just find our materialized views are built on top of normal views (from
clauses are all normal views). Our normal views are chained together (again
from clauses are normal views for many of them. Only several root views are
built against tables). This is in oracle 9.2. What a "clever" design!!!!
Comments. So to speak. |
L*********r
发帖数: 92 | 2 Could you give a list of disadvantages of this design?
It is normal to use view as the abstract representation of physical table.
again
are
【在 s******e 的大作中提到】
: Just find our materialized views are built on top of normal views (from
: clauses are all normal views). Our normal views are chained together (again
: from clauses are normal views for many of them. Only several root views are
: built against tables). This is in oracle 9.2. What a "clever" design!!!!
: Comments. So to speak.
|
s******e
发帖数: 493 | 3
Stacking too many views masks performance issue.
Firstly, you might return many unneccesary columns via a complex stacked
views, but actually you only want to return several columns, which can be
done easily using a simpler query against tables.
Secondly, stack more views on top of more views may break down predicate
pushing.
thirdly, stack too many views may cause optimizer not to generate the
optimized excution plan. (subqueries may not be merged efficently)
The gain of using normal view to c
【在 L*********r 的大作中提到】
: Could you give a list of disadvantages of this design?
: It is normal to use view as the abstract representation of physical table.
:
: again
: are
|
i********a
发帖数: 1 | 4 materialized view is kind of physical table. Yes, it may take long time to
create it in your case. Materialized view is a snap shot of your data. It
contains physical data. all query will go through these physical data after
it's created. it gives you a good performance for the query. |
L*********r
发帖数: 92 | 5 If all those views share the same context and mergeable, all those
disadvantages will not happen in most cases.
How will you implement this?
It also is possible to have all those disadvantages when using normal view
with complex sql.
【在 s******e 的大作中提到】
:
: Stacking too many views masks performance issue.
: Firstly, you might return many unneccesary columns via a complex stacked
: views, but actually you only want to return several columns, which can be
: done easily using a simpler query against tables.
: Secondly, stack more views on top of more views may break down predicate
: pushing.
: thirdly, stack too many views may cause optimizer not to generate the
: optimized excution plan. (subqueries may not be merged efficently)
: The gain of using normal view to c
|
g*****g
发帖数: 34805 | 6 I don't like views, it's not standarized and will have performance
issue across different databases. We use hibernate and support both
MySql and Oracle in our products, we simply avoid views, it's kind of
Store procedure IMHO.
【在 L*********r 的大作中提到】
: If all those views share the same context and mergeable, all those
: disadvantages will not happen in most cases.
: How will you implement this?
: It also is possible to have all those disadvantages when using normal view
: with complex sql.
|
L*********r
发帖数: 92 | 7 View and SP has different use case. To use view or SP depends on what is the
problem. It likes comparing orange to apple to compare them.
The purpose of view is not performance. It is possible to degrade the
performance if the view is not used correctly.
【在 g*****g 的大作中提到】
: I don't like views, it's not standarized and will have performance
: issue across different databases. We use hibernate and support both
: MySql and Oracle in our products, we simply avoid views, it's kind of
: Store procedure IMHO.
|
s******e
发帖数: 493 | 8
Are we still talking about "the pros and cons about stacking views on top of
each other" or you want to talk sth more general about views?
Didn't get it.
At least you do not break down predicate pushing due to stacking too many
views.
Besides complex sql doesn't necessarilly confuse db optimizer. But if you
stack views, which is just like issuing the query "select * from (selecct *
from (select * from...))... especailly the table appears more than once and
tangles with others with complext sqls
【在 L*********r 的大作中提到】
: If all those views share the same context and mergeable, all those
: disadvantages will not happen in most cases.
: How will you implement this?
: It also is possible to have all those disadvantages when using normal view
: with complex sql.
|
L*********r
发帖数: 92 | 9 You know the term 'mergeable view', right?
I do not suggest stacking view. But if it does not hurt performance too much
, it is a well design, specially, in your case, the result is a materialized
view.
I just interesting in what is your implementation about your problem.
share your ideas?
of
*
and
【在 s******e 的大作中提到】
:
: Are we still talking about "the pros and cons about stacking views on top of
: each other" or you want to talk sth more general about views?
: Didn't get it.
: At least you do not break down predicate pushing due to stacking too many
: views.
: Besides complex sql doesn't necessarilly confuse db optimizer. But if you
: stack views, which is just like issuing the query "select * from (selecct *
: from (select * from...))... especailly the table appears more than once and
: tangles with others with complext sqls
|
s******e
发帖数: 493 | 10 I agree that performance gain is not goal of normal views. But in many cases
, performance is the purpose of m-view in legacy environment if a little
data stalement is not concerned(also m-view can work as wharehouse for
reporting etc). This is true in our case.
We suffer a lot for performance when we querry our normal views. This is
mainly due to the fact that they are chained together.
Since our m-views are built on top of the normal views, it is reasonable to
believe that the performance degr |
L*********r
发帖数: 92 | 11 If there is a performance problem, this stack view design is bad in your
case.
Your performance issue often happens when working with large data table or
fact table. This kind of performance tuning always is interesting.
Hope you get it done and share your experience later.
cases
to
【在 s******e 的大作中提到】
: I agree that performance gain is not goal of normal views. But in many cases
: , performance is the purpose of m-view in legacy environment if a little
: data stalement is not concerned(also m-view can work as wharehouse for
: reporting etc). This is true in our case.
: We suffer a lot for performance when we querry our normal views. This is
: mainly due to the fact that they are chained together.
: Since our m-views are built on top of the normal views, it is reasonable to
: believe that the performance degr
|
