Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [view]

A virtual table that represents the data in one or more tables in an alternative way.

676 questions
100
votes
4 answers

Are views harmful for performance in PostgreSQL?

The following is an excerpt from a book about db design (Beginning Database Design ISBN: 0-7645-7490-6): The danger with using views is filtering a query against a view, expecting to read a very small portion of a very large table. Any filtering…
ARX
  • 1,509
  • 3
  • 14
  • 15
77
votes
7 answers

How to list all views in SQL in PostgreSQL?

How do I list all views for a database using an SQL command in PostgreSQL? I would like something similar to output of the psql \dv command, but preferably just a list of view names. e.g., SELECT ...; my_view_1 my_view_2 my_view_3 I'm running…
Rob Bednark
  • 2,253
  • 6
  • 22
  • 22
65
votes
6 answers

Why not use a table instead of a materialized view?

I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some parameter. If view is saved as a physical table,…
jrara
  • 5,393
  • 20
  • 58
  • 65
65
votes
3 answers

When to use views in MySQL?

When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table? One reason that I would prefer to use views is that the database schema has been developed by our administrator from within…
David LeBauer
  • 3,162
  • 8
  • 32
  • 34
48
votes
5 answers

Is nested view a good database design?

I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect statement. Students SELECT studentID, first_name,…
Richard Sayakanit
  • 947
  • 2
  • 8
  • 12
47
votes
3 answers

Replace a materialized view in Postgres

I have a materialized view in Postgres 9.3 that I'd like to update with new columns. However, other materialized views also depend upon this view, and the error message indicates that dropping a view isn't possible when other objects depend on it. …
John
  • 777
  • 1
  • 7
  • 12
34
votes
7 answers

Modify DEFINER on Many Views

I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result. Got error: 1449: The user specified as a definer ('cittool'@'%') does not…
Zoredache
  • 491
  • 1
  • 4
  • 10
34
votes
3 answers

Are Views optimized when I add a WHERE clause to them?

Does it make a difference if you filter a View inside or outside the View? For example, is there any difference between these two queries? SELECT Id FROM MyTable WHERE SomeColumn = 1 Or SELECT Id FROM MyView WHERE SomeColumn = 1 And MyView is…
Rachel
  • 8,547
  • 20
  • 51
  • 74
33
votes
4 answers

Best way to create a materialized view in MySQL

I am using MySQL 5.6. I am not being able to a create materialized view like I can in Oracle. I have seen one or two solutions like Flexview. Can anybody tell me best way to create a materialized view in MySQL (auto refresh like in Oracle ) with the…
Bhupendra Pandey
  • 579
  • 3
  • 8
  • 16
32
votes
4 answers

Does SQL Server Only Perform Calculations In A SELECT List Once?

Take the following example: SELECT As ColA, As ColB, + As ColC FROM TableA Would CalculationA and CalculationB, each be calculated twice? Or would the optimizer be clever…
Gravitate
  • 431
  • 4
  • 10
31
votes
5 answers

How do I switch off SCHEMABINDING for a view without recreating it?

How do I switch off SCHEMABINDING for a view without recreating it?
garik
  • 6,782
  • 10
  • 44
  • 56
26
votes
1 answer

Execution plan shows expensive CONVERT_IMPLICIT operation. Can I fix this with indexing or do I need to change the table?

I have a really important, really slow view which includes some really ugly conditions like this in its where clause. I am also aware that the joins are gross and slow joins on varchar(13) instead of integer identity fields, but would like to…
Warren P
  • 1,087
  • 2
  • 14
  • 24
23
votes
6 answers

Is there a generic term for tables and views?

I am looking for a generic term, e.g. for a database abstraction, that includes all tabular data structures like database tables, views, tabular query results aso. As far as I understand, 'entity' is not the proper term since this would correspond…
Pinke Helga
  • 341
  • 2
  • 5
21
votes
4 answers

Determining how a schema change occurred?

Something bad happened yesterday. A view that was created sometime back ago was modified by someone which eventually broke the reports. Unfortunately. somebody (knowingly or unknowingly) did this modification in PRODUCTION database. My Question: Is…
xorpower
  • 577
  • 1
  • 8
  • 18
20
votes
1 answer

PostgreSQL : drop column from view

I have a VIEW where I'm attempting to create an evolution script for, so I can add a column to it. That part works fine; column added just fine. However, the reverse does not work; remove that last added column fails with a ERROR: cannot drop…
Yanick Rochon
  • 1,651
  • 4
  • 20
  • 28
1
2 3
…
44 45