Materialized views - questions

[sweetleaf]

How are mat views different than logical views. My challenge is i don't want to grant power users direct access to tables for their end-user reporting needs. But IT needs to give them some way to analyze their data in an ad hoc way...

-Has anyone gone through something similar?
-Are Oracle mat views a sensible place to begin?
-Anyone know whether SQL Server has an equivalent?

Any help would be greatly appreciated.

Thanks

 

[SantaMufasa]

SweatLeaf,

VIEWS are simply named queries that we store in the Oracle data dictionary. They take up no space except the number of characters required to define the SELECT statement that defines the VIEW.

MATERIALIZED VIEWS are similiar to VIEWS in that SELECT statements define the MVs, but that is where the similarities end. The features of the MV that differ from standard views are that:

1) the structure of the MV is much like a "CREATE TABLE...AS SELECT..." in that the MV actually consumes data space in the owner's schema.

2) we typically specify a "refresh" schedule that updates the contents of the MV by re-running the original SELECT and either repopulating the entire MV or updating the rows that have changed from the original content of the MV.

Materialized Views sound like they would satisfactorily meet your power users needs for ad hoc analysis.

I do not know if SQL Server has a similar feature.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sweetleaf]

Thanks so much! to play devils advocate - why use an MV over an actual table? in an end user (ad hoc) reporting scenario, what benefits can be gained that you know of thru using an MV? Thank you!

 

[SantaMufasa]

The MV refreshes its content on the schedule that you specify. If you created an actual table, then its contents would become further and further from reality (which your power user will quickly dislike) as your base table receives INSERTs, UPDATEs, and DELETEs.

So, the bottom-line benefit of MV is its ability to refresh the data on a "set-it-and-forget-it" basis.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

MV's also offer performance benefits.
If you had a huge table (say billions of rows) and you created MV's to satisfy your most often run queries, thereby only querying a few million rows, the performance gain would be huge.

Regards

T

Grinding away at things Oracular

 

[blom0344]

I do not know if SQL Server has a similar feature.

Yes and no. Indexed views within SQL server are quite a different solution, but there similarities in what is attained (performance gain,automatic reflection of changes,automatic reselecting if optimizer detects better path)

Ties Blom