Re: Sybase and materialized views
- From: "sybdba" <sybdba@xxxxxxxxx>
- Date: 14 Dec 2005 09:12:36 -0800
Since this work seems to be dependent on committed work on tables, the
equivalent could be done in ASE by using ASE Replicator (or full blown
Rep Server).
You can write all the procedural logic you want and update any tables
you want, using this event based mechanism, so what is the big deal?
regards,
sybdba
Galen Boyer wrote:
> 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: Galen Boyer
- 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
- Re: Sybase and materialized views
- From: Galen Boyer
- Sybase and materialized views
- Prev by Date: Puzzeled by slow select/join on Sybase ASE 12.5(Linux)
- Next by Date: Powerdesigner viewer?
- Previous by thread: Re: Sybase and materialized views
- Next by thread: Re: Sybase and materialized views
- Index(es):
Relevant Pages
|
