1375:
1385:
1395:
284:
Microsoft SQL Server differs from other RDBMS by the way of implementing materialized view via a concept known as "Indexed Views". The main difference is that such views do not require a refresh because they are in fact always synchronized to the original data of the tables that compound the view. To
491:
materialization A somewhat unsophisticated technique for implementing operations on views according to which (a) the relational expression that defines the view is evaluated at the time the operation is invoked, (b) the view is thereby materialized, and (c) the operation in question is then executed
610:
materialized view
Deprecated term for a snapshot. The problem is that (as the definition indicates) snapshots have come to be known, at least in some circles, not as snapshots at all but as materialized views. But snapshots aren't views; views are virtual and snapshots aren't, and 'materialized
103:
as a concrete ("materialized") table (rather than a view as such) that may be updated from the original base tables from time to time. This enables much more efficient access, at the cost of extra storage and of some data being potentially out-of-date. Materialized views find use especially in
115:
can be built on any column. In contrast, in a normal view, it's typically only possible to exploit indexes on columns that come directly from (or have a mapping to) indexed columns in the base tables; often this functionality is not offered at all.
285:
achieve this, it is necessary that the lines of origin and destination are "deterministic" in their mapping, which limits the types of possible queries to do this. This mechanism has been realised since the 2000 version of SQL Server.
99:. Whenever a query or an update addresses an ordinary view's virtual table, the DBMS converts these into queries or updates against the underlying base tables. A materialized view takes a different approach: the query result is
908:
786:
903:
215:, version 9.3 and newer natively support materialized views. In version 9.3, a materialized view is not auto-refreshed, and is populated only at time of creation (unless
448:
using data modification events captured by DynamoDB Streams. Google announced in 8 April 2020 the availability of materialized views for BigQuery as a beta release.
440:
doesn't support materialized views natively, but workarounds can be implemented by using triggers or stored procedures or by using the open-source application
985:
783:
968:
615:
is often taken to mean a materialized view specifically, and thus we're in danger of no longer having a good term for a view in the original sense.
62:. As with other forms of precomputation, database users typically use materialized views for performance reasons, i.e. as a form of optimization.
980:
441:
872:
626:
596:
569:
542:
515:
477:
35:. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or
1419:
1378:
1051:
940:
588:
The
Relational Database Dictionary: A Comprehensive Glossary of Relational Terms and Concepts, with Illustrative Examples
469:
The
Relational Database Dictionary: A Comprehensive Glossary of Relational Terms and Concepts, with Illustrative Examples
66:
898:
1398:
1104:
534:
Business
Intelligence: First European Summer School, EBISS 2011, Paris, France, July 3-8, 2011, Tutorial Lectures
1355:
1002:
814:
611:
view' is a contradiction in terms (at least as far as the model is concerned). Worse yet, the unqualified term
888:
1294:
913:
758:
690:
1289:
1320:
1039:
1243:
1233:
1009:
77:
802:
1424:
1330:
1063:
851:
664:
1279:
933:
1429:
1360:
1315:
992:
58:
of the value of a function in functional languages, and it is sometimes described as a form of
559:
1335:
1089:
716:
586:
532:
505:
467:
96:
1388:
1325:
1207:
1177:
1046:
997:
504:
Karen Morton; Kerry
Osborne; Robyn Sands; Riyaj Shamsudeen; Jared Still (28 October 2013).
8:
1345:
1238:
1223:
1150:
975:
410:
223:. In version 9.4, the refresh may be concurrent with selects on the materialized view if
1434:
1340:
1284:
1253:
1202:
1094:
1034:
926:
40:
1160:
1014:
592:
565:
538:
511:
473:
51:
32:
503:
1350:
1197:
1187:
1155:
112:
105:
89:
81:
1258:
1228:
1182:
963:
893:
790:
639:
445:
413:, Materialize, and RisingWave all support materialized views on streams of data.
130:
85:
70:
1310:
1248:
1192:
1165:
1058:
1019:
100:
59:
918:
838:
108:
scenarios, where frequent queries of the actual base tables can be expensive.
1413:
1129:
1114:
65:
Materialized views that store data based on remote tables were also known as
826:
839:
Flexviews for MySQL – incrementally refreshable materialized views w/ MySQL
425:
406:
402:
1119:
1099:
55:
1263:
1172:
1134:
1109:
433:
212:
36:
737:
20:
1124:
1079:
949:
436:
supports materialized views that automatically refresh on merges.
93:
28:
894:
SQL Snippets: SQL Features
Tutorials – Materialized Views – Oracle
46:
The process of setting up a materialized view is sometimes called
530:
429:
1029:
422:
1024:
899:
Oracle9i
Replication Management API Reference Release 2 (9.2)
437:
288:
Example syntax to create a materialized view in SQL Server:
230:
Example syntax to create a materialized view in
PostgreSQL:
1084:
561:
IBM Data
Warehousing: with IBM Business Intelligence Tools
803:
Improving
Performance with SQL Server 2005 Indexed Views
136:
Example syntax to create a materialized view in Oracle:
531:
Marie-Aude Aufaure; Esteban Zimányi (16 January 2012).
133:: the Query rewrite feature was added from version 8i.
841:. Flexviews.sourceforge.net. Retrieved on 2012-02-09.
537:. Springer Science & Business Media. p. 43.
889:
Materialized View Concepts and Architecture – Oracle
829:. Shinguz.ch (2006-11-06). Retrieved on 2012-02-09.
557:
219:is used). It may be refreshed later manually using
524:
129:Materialized views were implemented first by the
1411:
873:Google BigQuery Materialized Views documentation
497:
948:
432:, they are called "materialized query tables".
397:
934:
817:. Clickhouse.yandex. Retrieved on 2019-09-05.
629:. Ecst.csuchico.edu. Retrieved on 2012-02-09.
551:
444:. Materialized views can be implemented in
941:
927:
815:ClickHouse Documentation MaterializedView
805:. Microsoft.com. Retrieved on 2012-02-09.
793:. Ianywhere.com. Retrieved on 2012-02-09.
584:
465:
421:Materialized views are also supported in
827:Implementing materialized views in MySQL
784:Materialized Views – Sybase SQL Anywhere
558:Michael L. Gonzales (25 February 2003).
458:
1412:
759:"Is RisingWave the Next Apache Flink?"
640:"Materialized Views - PostgreSQL wiki"
591:. "O'Reilly Media, Inc.". p. 59.
564:. John Wiley & Sons. p. 214.
472:. "O'Reilly Media, Inc.". p. 59.
31:object that contains the results of a
922:
492:against the relation so materialized.
39:result, or may be a summary using an
914:Creating Materialized Views In MySQL
1394:
875:Google.com Retrieved on 2020-05-20.
738:"CMU DB Talk: Building Materialize"
54:the results of a query, similar to
13:
119:
16:In databases, cached query results
14:
1446:
904:Materialized Views in Oracle 11.2
882:
1393:
1383:
1374:
1373:
909:Materialized query tables in Db2
1384:
866:
844:
832:
820:
808:
796:
777:
751:
730:
709:
683:
657:
632:
620:
578:
1:
627:Oracle8i Tuning Release 8.1.5
451:
279:
206:
92:representing the result of a
585:C.J. Date (28 August 2006).
466:C.J. Date (28 August 2006).
409:(since v2.0), Apache Flink,
398:Stream processing frameworks
7:
1420:Database management systems
950:Database management systems
691:"REFRESH MATERIALIZED VIEW"
10:
1451:
1356:Object–relational database
854:. Google.com. 8 April 2020
665:"CREATE MATERIALIZED VIEW"
78:database management system
1369:
1331:Federated database system
1303:
1272:
1216:
1143:
1072:
1064:Blockchain-based database
956:
416:
221:REFRESH MATERIALIZED VIEW
124:
763:www.singularity-data.com
695:PostgreSQL Documentation
669:PostgreSQL Documentation
290:
232:
138:
111:In a materialized view,
1361:Transaction processing
1316:Database normalization
1259:Query rewriting system
510:. Apress. p. 48.
1336:Referential integrity
1326:Distributed database
717:"Materialized Views"
50:. This is a form of
1346:Relational calculus
1224:Concurrency control
644:wiki.postgresql.org
1341:Relational algebra
1285:Query optimization
1090:Armstrong's axioms
789:2009-12-14 at the
697:. 13 February 2020
671:. 10 November 2022
41:aggregate function
1407:
1406:
1015:Wide-column store
1010:Document-oriented
598:978-1-4493-9115-7
571:978-0-471-45736-7
544:978-3-642-27357-5
517:978-1-4302-6220-6
479:978-1-4493-9115-7
405:(since v0.10.2),
25:materialized view
1442:
1425:Relational model
1397:
1396:
1387:
1386:
1377:
1376:
1351:Relational model
1321:Database storage
1198:Stored procedure
943:
936:
929:
920:
919:
876:
870:
864:
863:
861:
859:
848:
842:
836:
830:
824:
818:
812:
806:
800:
794:
781:
775:
774:
772:
770:
755:
749:
748:
746:
744:
734:
728:
727:
725:
723:
713:
707:
706:
704:
702:
687:
681:
680:
678:
676:
661:
655:
654:
652:
650:
636:
630:
624:
618:
617:
607:
605:
582:
576:
575:
555:
549:
548:
528:
522:
521:
501:
495:
494:
488:
486:
462:
393:
390:
387:
384:
381:
378:
375:
372:
369:
366:
363:
360:
357:
354:
351:
348:
345:
342:
339:
336:
333:
330:
327:
324:
321:
318:
315:
312:
309:
306:
303:
300:
297:
294:
275:
272:
269:
266:
263:
260:
257:
254:
251:
248:
245:
242:
239:
236:
226:
222:
218:
202:
199:
196:
193:
190:
187:
184:
181:
178:
175:
172:
169:
166:
163:
160:
157:
154:
151:
148:
145:
142:
106:data warehousing
82:relational model
1450:
1449:
1445:
1444:
1443:
1441:
1440:
1439:
1410:
1409:
1408:
1403:
1365:
1311:Database models
1299:
1268:
1254:Query optimizer
1229:Data dictionary
1212:
1183:Transaction log
1139:
1095:Codd's 12 rules
1068:
998:Column-oriented
964:Object-oriented
952:
947:
885:
880:
879:
871:
867:
857:
855:
852:"Release notes"
850:
849:
845:
837:
833:
825:
821:
813:
809:
801:
797:
791:Wayback Machine
782:
778:
768:
766:
765:. 28 April 2022
757:
756:
752:
742:
740:
736:
735:
731:
721:
719:
715:
714:
710:
700:
698:
689:
688:
684:
674:
672:
663:
662:
658:
648:
646:
638:
637:
633:
625:
621:
603:
601:
599:
583:
579:
572:
556:
552:
545:
529:
525:
518:
502:
498:
484:
482:
480:
463:
459:
454:
446:Amazon DynamoDB
419:
400:
395:
394:
391:
388:
385:
382:
379:
376:
373:
370:
367:
364:
361:
358:
355:
352:
349:
346:
343:
340:
337:
334:
331:
328:
325:
322:
319:
316:
313:
310:
307:
304:
301:
298:
295:
292:
282:
277:
276:
273:
270:
267:
264:
261:
258:
255:
252:
249:
246:
243:
240:
237:
234:
224:
220:
216:
209:
204:
203:
200:
197:
194:
191:
188:
185:
182:
179:
176:
173:
170:
167:
164:
161:
158:
155:
152:
149:
146:
143:
140:
131:Oracle Database
127:
122:
120:Implementations
48:materialization
17:
12:
11:
5:
1448:
1438:
1437:
1432:
1427:
1422:
1405:
1404:
1402:
1401:
1391:
1381:
1370:
1367:
1366:
1364:
1363:
1358:
1353:
1348:
1343:
1338:
1333:
1328:
1323:
1318:
1313:
1307:
1305:
1304:Related topics
1301:
1300:
1298:
1297:
1292:
1287:
1282:
1280:Administration
1276:
1274:
1270:
1269:
1267:
1266:
1261:
1256:
1251:
1249:Query language
1246:
1241:
1236:
1231:
1226:
1220:
1218:
1214:
1213:
1211:
1210:
1205:
1200:
1195:
1190:
1185:
1180:
1175:
1170:
1169:
1168:
1163:
1158:
1147:
1145:
1141:
1140:
1138:
1137:
1132:
1127:
1122:
1117:
1112:
1107:
1102:
1097:
1092:
1087:
1082:
1076:
1074:
1070:
1069:
1067:
1066:
1061:
1056:
1055:
1054:
1044:
1043:
1042:
1032:
1027:
1022:
1017:
1012:
1007:
1006:
1005:
995:
990:
989:
988:
983:
973:
972:
971:
960:
958:
954:
953:
946:
945:
938:
931:
923:
917:
916:
911:
906:
901:
896:
891:
884:
883:External links
881:
878:
877:
865:
843:
831:
819:
807:
795:
776:
750:
729:
708:
682:
656:
631:
619:
597:
577:
570:
550:
543:
523:
516:
507:Pro Oracle SQL
496:
478:
456:
455:
453:
450:
418:
415:
399:
396:
291:
281:
278:
233:
208:
205:
139:
126:
123:
121:
118:
80:following the
73:terminology).
60:precomputation
15:
9:
6:
4:
3:
2:
1447:
1436:
1433:
1431:
1430:Data modeling
1428:
1426:
1423:
1421:
1418:
1417:
1415:
1400:
1392:
1390:
1382:
1380:
1372:
1371:
1368:
1362:
1359:
1357:
1354:
1352:
1349:
1347:
1344:
1342:
1339:
1337:
1334:
1332:
1329:
1327:
1324:
1322:
1319:
1317:
1314:
1312:
1309:
1308:
1306:
1302:
1296:
1293:
1291:
1288:
1286:
1283:
1281:
1278:
1277:
1275:
1271:
1265:
1262:
1260:
1257:
1255:
1252:
1250:
1247:
1245:
1242:
1240:
1237:
1235:
1232:
1230:
1227:
1225:
1222:
1221:
1219:
1215:
1209:
1206:
1204:
1201:
1199:
1196:
1194:
1191:
1189:
1186:
1184:
1181:
1179:
1176:
1174:
1171:
1167:
1164:
1162:
1159:
1157:
1154:
1153:
1152:
1149:
1148:
1146:
1142:
1136:
1133:
1131:
1130:Surrogate key
1128:
1126:
1123:
1121:
1118:
1116:
1115:Candidate key
1113:
1111:
1108:
1106:
1103:
1101:
1098:
1096:
1093:
1091:
1088:
1086:
1083:
1081:
1078:
1077:
1075:
1071:
1065:
1062:
1060:
1057:
1053:
1050:
1049:
1048:
1045:
1041:
1038:
1037:
1036:
1033:
1031:
1028:
1026:
1023:
1021:
1018:
1016:
1013:
1011:
1008:
1004:
1001:
1000:
999:
996:
994:
991:
987:
984:
982:
979:
978:
977:
974:
970:
967:
966:
965:
962:
961:
959:
955:
951:
944:
939:
937:
932:
930:
925:
924:
921:
915:
912:
910:
907:
905:
902:
900:
897:
895:
892:
890:
887:
886:
874:
869:
853:
847:
840:
835:
828:
823:
816:
811:
804:
799:
792:
788:
785:
780:
764:
760:
754:
739:
733:
718:
712:
696:
692:
686:
670:
666:
660:
645:
641:
635:
628:
623:
616:
614:
600:
594:
590:
589:
581:
573:
567:
563:
562:
554:
546:
540:
536:
535:
527:
519:
513:
509:
508:
500:
493:
481:
475:
471:
470:
461:
457:
449:
447:
443:
439:
435:
431:
427:
424:
414:
412:
408:
404:
305:SCHEMABINDING
289:
286:
231:
228:
214:
137:
134:
132:
117:
114:
109:
107:
102:
98:
95:
91:
88:is a virtual
87:
83:
79:
74:
72:
68:
63:
61:
57:
53:
49:
44:
42:
38:
34:
30:
26:
22:
868:
856:. Retrieved
846:
834:
822:
810:
798:
779:
767:. Retrieved
762:
753:
741:. Retrieved
732:
720:. Retrieved
711:
699:. Retrieved
694:
685:
673:. Retrieved
668:
659:
647:. Retrieved
643:
634:
622:
612:
609:
602:. Retrieved
587:
580:
560:
553:
533:
526:
506:
499:
490:
483:. Retrieved
468:
460:
426:SQL Anywhere
420:
407:Apache Spark
403:Apache Kafka
401:
287:
283:
238:MATERIALIZED
229:
225:CONCURRENTLY
217:WITH NO DATA
210:
144:MATERIALIZED
135:
128:
110:
75:
69:(deprecated
64:
47:
45:
24:
18:
1399:WikiProject
1290:Replication
1178:Transaction
1120:Foreign key
1100:CAP theorem
1047:Multi-model
722:28 December
701:29 November
675:29 November
649:29 November
411:Kinetica DB
56:memoization
1414:Categories
1264:Query plan
1217:Components
1135:Unique key
1052:comparison
986:comparison
976:Relational
969:comparison
604:26 October
485:26 October
452:References
434:ClickHouse
383:MV_MY_VIEW
344:table_name
299:MV_MY_VIEW
280:SQL Server
268:table_name
244:MV_MY_VIEW
213:PostgreSQL
207:PostgreSQL
195:table_name
150:MV_MY_VIEW
1435:Databases
1273:Functions
1208:Partition
1035:In-memory
993:Key–value
464:Compare:
442:Flexviews
371:CLUSTERED
227:is used.
67:snapshots
21:computing
1379:Category
1295:Sharding
1151:Relation
1125:Superkey
1080:Database
1073:Concepts
787:Archived
743:30 March
94:database
29:database
1389:Outline
1188:Trigger
1144:Objects
858:21 July
769:30 June
430:IBM Db2
171:SYSDATE
165:SYSDATE
153:REFRESH
113:indexes
76:In any
52:caching
1203:Cursor
1161:column
1030:NewSQL
595:
568:
541:
514:
476:
423:Sybase
417:Others
368:UNIQUE
365:CREATE
311:SELECT
293:CREATE
256:SELECT
235:CREATE
183:SELECT
141:CREATE
125:Oracle
101:cached
71:Oracle
1193:Index
1156:table
1059:Cloud
1025:NoSQL
1020:Graph
957:Types
438:MySQL
428:. In
374:INDEX
350:GROUP
335:TOTAL
159:START
97:query
90:table
33:query
27:is a
1244:ODBC
1234:JDBC
1173:View
1110:Null
1105:CRUD
1085:ACID
1040:list
1003:list
981:list
860:2021
771:2022
745:2022
724:2022
703:2022
677:2022
651:2022
613:view
606:2016
593:ISBN
566:ISBN
539:ISBN
512:ISBN
487:2016
474:ISBN
389:COL1
356:COL1
347:>
341:<
338:FROM
326:COL2
314:COL1
302:WITH
296:VIEW
271:>
265:<
262:FROM
241:VIEW
198:>
192:<
189:FROM
168:NEXT
162:WITH
156:FAST
147:VIEW
86:view
84:, a
37:join
23:, a
1239:XQJ
1166:row
320:SUM
211:In
19:In
1416::
761:.
693:.
667:.
642:.
608:.
489:.
392:);
380:ON
377:XV
362:GO
353:BY
332:AS
308:AS
253:AS
180:AS
43:.
942:e
935:t
928:v
862:.
773:.
747:.
726:.
705:.
679:.
653:.
574:.
547:.
520:.
386:(
359:;
329:)
323:(
317:,
274:;
259:*
250:]
247:)
201:;
186:*
177:1
174:+
Text is available under the Creative Commons Attribution-ShareAlike License. Additional terms may apply.