Questions tagged [view]

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

Filter by
Sorted by
Tagged with
66
votes
4answers
43k views

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 ...
62
votes
3answers
84k views

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 ...
59
votes
7answers
75k views

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 ...
49
votes
6answers
108k views

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 ...
44
votes
5answers
35k views

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 ...
32
votes
3answers
30k views

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 ...
30
votes
4answers
3k views

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

Take the following example: SELECT <CalculationA> As ColA, <CalculationB> As ColB, <CalculationA> + <CalculationB> As ColC FROM TableA Would CalculationA and ...
28
votes
5answers
61k views

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 ...
27
votes
3answers
23k views

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 ...
26
votes
3answers
46k views

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 ...
25
votes
1answer
10k views

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 ...
23
votes
4answers
27k views

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

How do I switch off SCHEMABINDING for a view without recreating it?
21
votes
4answers
20k views

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 ...
20
votes
1answer
976 views

SQL Server - Handling localization of strings in nested non-deterministic view stacks

While profiling a database I came across a view that is referencing some non-deterministic functions that get accessed 1000-2500 times per minute for each connection in this application's pool. A ...
17
votes
2answers
8k views

Why is a NOT NULL computed column considered nullable in a view?

I have a table: CREATE TABLE [dbo].[Realty]( [Id] [int] IDENTITY(1,1) NOT NULL, [RankingBonus] [int] NOT NULL, [Ranking] AS ([Id]+[RankingBonus]) PERSISTED NOT NULL .... ) And a ...
16
votes
1answer
390 views

In what order does PostgreSQL check object permissions?

Given a database role, user1, a function something() defined as a stored procedure and a view created like: CREATE VIEW view1 AS select * from something() And, given this permissions: REVOKE ALL ON ...
14
votes
2answers
40k views

Transact SQL using WITH in CREATE VIEW

I want to create VIEW using WITH clauses, but really can't find any references on correct syntax. I want smth like this WITH TempTbl AS (SELECT ...) CREATE VIEW SomeView SELECT * FROM TempTbl And ...
14
votes
5answers
8k views

How do I query this 20 million record view faster?

For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking ...
14
votes
2answers
80k views

How to add a user with access to a single view?

I'm working with MSSQL Server Management Studio 2008 and I need to expose a view to a third party for their data reconciliation. I have created the appropriate view but I'm having trouble creating a ...
13
votes
1answer
8k views

How do you represent VIEWS in an ERD?

Are VIEWS (CREATE VIEW) represented in an ERD? If so, how do you represent them and specify what tables they came from?
12
votes
2answers
638 views

ALTER VIEW drops Index from View

as the header line suggests: Using an ALTER VIEW statement on a VIEW that has an index on it will drop this (all?) indexes from the VIEW without warning. I would prefer the ALTER VIEW statement to ...
12
votes
5answers
8k views

Why does this UPDATE fail with a unique key constraint violation?

I'm an "accidental" DBA, relatively inexperienced and baffled by this problem. Running MS SQL Server 2012. The problem is with this UPDATE statement: UPDATE dbo.tAccts SET Ticket ...
12
votes
1answer
21k views

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 ...
12
votes
3answers
8k views

What are the alternatives for an ORDER BY clause in a View?

This question just had to be in this site :) ORDER BY is forbidden to use in a view, as I understood because of the possibility for multiple order by's when using this view. I know that there are ...
12
votes
1answer
13k views

Access view based on table in another database without account in that other database

I created view in database1 based on tables in database2. I gave SELECT permission to a user who has access only to database1. The user can't get this view to work because he doesn't have an account ...
11
votes
1answer
4k views

noexpand hint for non-enterpise edition and performance

I have to use Indexed views to reach performance. As I can see from this comparison table standard edition does not support indexed views. But BOL says: Indexed views can be created in any ...
11
votes
2answers
29k views

Will mysqldump command also backup all the views that are present in DB or there is a special command for it?

I am trying to backup my entire database along with the views. I am going to use mysqldump command. I was wondering if that will copy all the views or I will have to use separate command for it. ...
11
votes
1answer
215 views

Why can you only drop a SQL Server view in the current database?

Just noticed this and was having a hard time coming up with a technical reason for it. Tables can be dropped using three part names, but views are restricted to two part names. What is the ...
11
votes
2answers
4k views

Tracking current user through views and triggers in PostgreSQL

I have a PostgreSQL (9.4) database that limits access to records depending upon the current user, and tracks changes made by the user. This is achieved through views and triggers, and for the most ...
10
votes
1answer
4k views

Indexed view not being used - SQL Server [duplicate]

I've a query with multiple joins which takes long time to return data for one date. I've created an indexed view on the same. I set all the required options appropriately, while creating view and ...
10
votes
1answer
10k views

Does view take advantage of table indexes

I have a table with a clustered index and 2 non clustered index. Now, in my view I am selecting everything from my table create view dbo.MyView as Select * from MyTable Now, when I am using this ...
10
votes
3answers
7k views

Oracle: How do I query a Hierarchical table?

Background This is for the construction of some views we'll be using for reporting. I have an table of locations, the key fields being "location" and "parent". The structure that these two fields ...
10
votes
2answers
7k views

Does a view need its own foreign key constraints?

Disclaimer: I'm a programmer, not a DBA, so bear with me... I have a view that I use to just map 2 entities together. I have to do a join between a few different tables to get that: CREATE OR ...
10
votes
1answer
29k views

Using a linked server with OPENQUERY in a database project

I have a SQL Server 2008 running a database I want to throw in TFS. Therefore I used a Visual Studio 2013 database project where I imported the DB. After fixing a bunch of errors I'm stuck with only ...
10
votes
2answers
2k views

Window functions cause awful execution plan when called from a view with external parametrized 'where' clause

I had this issue long time ago, I found a workaround which suited me and forgot about it. But now there's that question on SO so I'm willing to bring this problem up. There's a view that joins few ...
9
votes
3answers
4k views

Performance of Inline-TVF vs. Views

I have a database where i am using inline TVFs (table value functions) instead of views. For example, I might have two tables called [car model] and [car manufacturer] that I'm joining together ...
9
votes
2answers
7k views

What's the difference between a v$ view and its dba_ equivalent?

What's the general difference between a v$ view and its dba_ equivalent? Take, for example, v$tablespace and dba_tablespaces.
9
votes
2answers
5k views

How can I help SQL Server recognize my indexed view column is NOT NULL-able?

I have the following indexed view defined in SQL Server 2008 (you can download a working schema from gist for testing purposes): CREATE VIEW dbo.balances WITH SCHEMABINDING AS SELECT user_id ...
9
votes
1answer
1k views

Materialized views in oracle

How are materialized views implemented in oracle? I've created a materialized view in oracle. What I now see is a quite normal table with a job. Are materialized views translated into tables & ...
9
votes
3answers
367 views

How to improve estimate of 1 row in a View constrained by DateAdd() against an index

Using Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64). Given a table and index: create table [User].[Session] ( SessionId int identity(1, 1) not null primary key CreatedUtc ...
9
votes
2answers
10k views

How do I get the execution plan for a view?

I have a schema with a number of views. I need to check the execution plans to make sure the appropriate indexes are in place and being used. How do I do this? I'd rather not have to copy and paste ...
9
votes
2answers
27k views

Grant permissions on views, deny select on tables

I have a MySQL user and I want it to view ONLY the views I want and not any other table in the database. I've granted this user permissions only on certain views as following: GRANT SHOW VIEW ON `...
9
votes
1answer
11k views

How to check/know the highest run queries

I would like to gather statistics on the highest run queries in my database. For that I need to know how I can track queries so I can create views or materialized views to cache the results of the ...
8
votes
1answer
4k views

why are people advised not to update through views

During College it was banged into our heads to not update tables through views, and again when I got into the workplace we where told do not update the database through views. Where there/Are there ...
8
votes
2answers
494 views

Will Postgres perform the computation of calculated columns that aren't selected in a view?

I'm trying to understand the performance impact of selecting data from a view, where one of the columns in a view is function of other data in the original table. Does the computation get performed ...
8
votes
2answers
16k views

How to make a union view execute more efficiently?

I have a large table (tens to hundreds of millions of records) that we have split for performance reasons into active and archive tables, using a direct field mapping, and running an archive process ...
8
votes
1answer
6k views

Oracle materialized view refresh schedule

Since I see that it is possible to specify an expression like sysdate + 1, here are my questions: Is it possible to specify the exact time of the refresh for materialized views in oracle? Is it ...
8
votes
1answer
14k views

Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared with MySQL 5.6.30

Problem/Issue : Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared I have a Stored Procedure which contains a Prepared Statement and a view DROP PROCEDURE IF EXISTS `sampleProc`...
8
votes
1answer
3k views

SELECT COUNT(*) on a view is orders of magnitude slower than SELECT * on the same view

The View CREATE VIEW [dbo].[vProductList] WITH SCHEMABINDING AS SELECT p.[Id] ,p.[Name] ,price.[Value] as CalculatedPrice ,orders.[Value] as OrdersWithThisProduct FROM products ...
8
votes
2answers
18k views

How to get mysql view definitions?

Show create view viewName does not display correctly formatted text. How can I get the correctly-formatted SQL? For example, I created a view like create view v1 as select * from mysql.uer, but it ...

1
2 3 4 5
12