Deprecated: add_custom_background is deprecated since version 3.4.0! Use add_theme_support( 'custom-background', $args ) instead. in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5084

Deprecated: add_custom_image_header is deprecated since version 3.4.0! Use add_theme_support( 'custom-header', $args ) instead. in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5084

Notice: wp_enqueue_script was called incorrectly. Scripts and styles should not be registered or enqueued until the wp_enqueue_scripts, admin_enqueue_scripts, or login_enqueue_scripts hooks. This notice was triggered by the jquery handle. Please see Debugging in WordPress for more information. (This message was added in version 3.3.0.) in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5536

Notice: wp_enqueue_script was called incorrectly. Scripts and styles should not be registered or enqueued until the wp_enqueue_scripts, admin_enqueue_scripts, or login_enqueue_scripts hooks. This notice was triggered by the smoothscroll handle. Please see Debugging in WordPress for more information. (This message was added in version 3.3.0.) in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5536

Deprecated: The called constructor method for WP_Widget in Yoko_SocialLinks_Widget is deprecated since version 4.3.0! Use __construct() instead. in /home/pqmz7qzy9yt5/public_html/wp-includes/functions.php on line 5177
Intro to Databases: Views, Materialized Views | Megan Taylor

Megan Taylor

front-end dev, volunteacher, news & data junkie, bibliophile, Flyers fan, sci-fi geek and kitteh servant

Intro to Databases: Views, Materialized Views

Why use materialized views?
Same as virtual views, plus improve query performance

Virtual views
View V = ViewQuery(R1, R…, Rn)
Schema of V is schema of query result
Query Q involving V, conceptually:
V:= ViewQuery(R1, R…, Rn)
Evaluate Q
In reality, Q rewritten to use R1, R…, Rn instead of V
Note: Ri could itself be a view

Materialized views
View V = ViewQuery(R1, R…, Rn)
Create table V with schema of query result
Execute ViewQuery and put results in V
Queries refer to V as if its a table, because it is
BUT…
V could be ginormous
Modification to R1, R2, R…, Rn -> recompute or modify V

Create Materialized View CA-CS As
Select C.cName, S.sName
From College C, Student S, Apply A
Where C.cName = A.cName and S.sID = A.sID and C.state = ‘CA’ and A.major = ‘CS’

Modifications to base data invalidate View

Modifications
Good news: just update the stored table
Bad News: base tables must stay in sync
– same issues as virtual views

Picking which materialized views to create
Efficiency benefits of a materialized view depend on:
– size of data
– complexity of View- number of queries using view
– number of modifications affecting view
– also “incremental maintenance” versus full recomputation

December 3, 2011 | Comments Off on Intro to Databases: Views, Materialized Views | Categories: Posts | Permalink

Comments are closed.


Notice: Undefined index: host in /home/pqmz7qzy9yt5/public_html/wp-content/plugins/jetpack/modules/stats.php on line 209