Re: Sybase and materialized views
- From: Galen Boyer <galen_boyer@xxxxxxxxx>
- Date: 13 Dec 2005 22:06:03 -0600
On Mon, 12 Dec 2005, kayser_c@xxxxxxx wrote:
>
> "DA Morgan" <damorgan@xxxxxxxxx> wrote in message
> news:1134252669.433523@xxxxxxxxxxxxxxxxxxxxxx
>> supergel wrote:
>>> Hi I'm new to Sybase from Oracle. Does Sybase have anything
>>> equivalent to materialzed views? If not want is the best practice
>>> for creating views with huge amounts of data? Other than indexing
>>> the tables. Thanks for help.
>>
>> For those not familiar with Oracle materialized views ... they are
>> tables (not views) that refresh themselves based on a SQL statement
>> based on criteria supplied during their creation. Multiple
>> materialized views can be made to refresh as part of a single
>> transaction so as to guarantee their data consistency.
>>
>> I too am looking for the answer.
>> --
>> Daniel A. Morgan
>> http://www.psoug.org
>> damorgan@xxxxxxxxxxxxxxxx
>> (replace x with u to respond)
>
> (1) It doesn't seem to exist in Sybase, at least with that name. (ASE
> 15.0 does have materialized [deterministic | computed] columns, but
> this seems different from your description.)
>
> (2) I imagine that the equivalent functionality could be done with
> triggers on the base table(s) that would update the
> aggregate/dimension/whatever "materialized view".
Its a reasonable way to look at it, for understanding them.
> Apparently Oracle materialized views are more efficient than this
> approach?
Oracle uses its own logging mechanism on the tables being queried to
know how to update the table.
>
> (3) I would think that this capability is not best suited for OLTP
> (too frequent activity) environments.
It can be tuned quite a bit by a DBA that knows what she is doing.
> And static tables can be created/updated in batch DSS environments.
>
> (4) Why does Oracle designate a table as a materialized "view"?
There are two uses for these very nifty features. One is really a
replacment for homegrown batch processes which build reporting
tables/marts/dws. Instead of putting the insert/update/refresh logic in
place with code, UNIX scripts, cron, ... jobs, just code the SQL that
gets the data needed, slap "create materialized view" on top of it and
then set the parameters of the materialized view to let Oracle "batch"
it up. You can then, either query the view directly, or use them to
insert to the final tables.
But, the much more interesting usage (even though the previous one is
quite nice), IMHO, is that these things can become very sophisticated
indexes for an already working application. It involves setting the
instance parameter, query_rewrite to true (I think thats the parm and
setting). Without changing anything about an application, one can speed
up retrieval by adding an index, correct? Okay, suppose your query that
is taking a long time is something like:
SELECT code, count(*)
FROM some_table
GROUP BY code;
This is sitting somewhere in your application and cannot be touched, and
is taking too long. Well,
CREATE materialized view code_grouper_byer
AS
SELECT code, count(*)
FROM some_table
GROUP BY code;
Then, with query_rewrite enabled, the next time the code in the app
runs, Oracle will know by its meta data that it already has the answer
stored by the materialized view and "rewrite" the query to access the
materialized view. As data changes happen to the underlying
"some_table", Oracle will keep code_grouper_byer up-do-date as well.
The complexity of the view's code can be pretty hairy, and Oracle
usually can handle it. I think the limitation it has is when you use
analytics within the view.
--
Galen Boyer
.
- Follow-Ups:
- Re: Sybase and materialized views
- From: sybdba
- Re: Sybase and materialized views
- References:
- Sybase and materialized views
- From: supergel
- Re: Sybase and materialized views
- From: DA Morgan
- Re: Sybase and materialized views
- From: Carl Kayser
- Sybase and materialized views
- Prev by Date: Re: bcp into syslogins
- Next by Date: Puzzeled by slow select/join on Sybase ASE 12.5(Linux)
- Previous by thread: Re: Sybase and materialized views
- Next by thread: Re: Sybase and materialized views
- Index(es):
Relevant Pages
|
