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

learn more… | top users | synonyms (1)

1
vote
0answers
9 views

Does issuing DDL against a view in PostgreSQL take out the same locks as issuing DDL against a table?

I understand that views are implemented using the rule system but I'm unclear if this has any advantages/disadvantages when running transactional DDL against them. Does issuing CREATE OR REPLACE VIEW ...
2
votes
2answers
56 views

Filtering UNION ALL result is much slower than filtering each subquery

(edit: see end for a simpler example) I'm searching in a table named "cases" (135k rows, 29 columns). Some of the rows in this table have a type of parent-child relationship (of different types), ...
1
vote
1answer
112 views

ER Diagram - View Intergration/Centralised approach

Could anyone enlighten me on the difference between a view integration and centralised approach when designing a database, and how the ER diagram can be used to illustrate upon this; can't seem to ...
1
vote
2answers
20 views

How to grant permissions to users based on table information?

Say I have a table: +------------------------+ | Employees | +------+--------+--------+ | Dept | Name | Salary | +------+--------+--------+ | 1 | John | 10000 | | 1 | Mary | ...
0
votes
1answer
527 views

Restrict access to rows by usename

In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so ...
2
votes
1answer
107 views

Is there a way to make an OOo form for PostgreSQL updateable view?

I have a PostgreSQL database, with data from some 8 tables (not counting enums implemented as a table referenced by foreign key) summarized in a view. I made an INSTEAD OF trigger, so the view is in ...
1
vote
1answer
229 views

Teradata view with “With Clause” syntax

I am trying to a view with "With" clause in Teradata but it is not allowing to create a view. Please suggest with any options. Given below is the query which I have tried: create view ...
6
votes
3answers
116 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 ...
0
votes
1answer
20 views

Replicate View different servers

Using MySQL, and related to this answer open in another thread (How to replicate MySQL table in different servers?), It arises me the idea of replicating only a View in different servers (through ...
1
vote
3answers
4k views

Running an “ORDER BY CASE” query on MySQL view breaks it afterwards

Whenever I create a MySQL view and then run a query ordering the results with ORDER BY CASE WHEN (...), the view becomes unusable. The newly created view works properly and all SQL is correct. This ...
7
votes
2answers
1k 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 ...
2
votes
2answers
62 views

Why am I able to query for more than 1024 columns but I can not make a view of the same query?

My boss is requesting a view with greater than 1024 columns where in he will only select a few columns he wants when he queries the view so that the actual returned columns are less than 1024. Why am ...
1
vote
0answers
19 views

Efficient Provision of Multiple Versions of Data

We have a fairly unusual database use case: we have a number of large datasets used for research (the largest being a couple of billion rows). The datasets are used by potentially several hundred ...
0
votes
1answer
32 views

Is the same thing(internally) putting conditions in a query over a view that directly on the view?

I'm having trouble understanding performance with queries on views. Given this two scenarios, the queries internally will be the same? SCENARIO 1 View: CREATE VIEW TEST.SIMPLEVIEW AS SELECT ...
1
vote
1answer
53 views

Create a table based on a table depending on the values in the initial table column

I have a table like: +----+--------+----------------+ | id | name | address | +----+--------+----------------+ | 1 | Ivan | Moscow,Moscow | | 2 | Petr | Samara,Samara | | 3 | Grigor ...
3
votes
2answers
3k views

while restoring mysql database, views changed into tables

I have a full database dump and i restored it. But in my new restored server all the views are appeared as MyISAM tables. In early times dumps were restored perfectly. can anyone help me to trigger ...
1
vote
1answer
33 views

Creating a view with a Left Outer Join and Where Condition

Let's say I have a User Table and a User Record table which lists Data Sources associated with that User. I want to create a (Sql Server) View that lists all the contents of a User, and an optional ...
2
votes
1answer
201 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 ...
2
votes
2answers
20 views

Most restricting permissions for querying view with linked server

I've created view to query another server via linked server(SQL Server 2014). I need to grant permissions to developers to use this view. Granting only select is not enough and they got the ...
0
votes
1answer
86 views

Encapsulate table with view

I need to change the order of columns in my table (Oracle and SQL Server). Reason is that some of our software calls select * and insert without specifying columns. Refactoring will be too expensive ...
3
votes
1answer
4k views

PostgreSQL 9.1 - Query on VIEWS taking a lot of time

Using PostgreSQL 9.1, we are having problems while executing queries on a VIEW of PostgreSQL. Following is the situation: We have a partitioned Table "buz_scdr" over which we have built a VIEW ...
8
votes
2answers
33k 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 ...
1
vote
1answer
41 views

special setup to create views in Oracle in default schema

When creating views in Oracle, do you need special setup for a given user if the view and table in which the view is reading from are in two different schemas? The user creating the view has dba ...
0
votes
1answer
43 views

Create a temp table that consolidates start and end dates plus generates a uniqueid for each consolidated record

Hey Microsoft SQL Gurus I need your help, please. This is my first post, and I have a table called projecthistory, it’s actually a view so I could take advantage of the Rank()function to get a unique ...
0
votes
1answer
21 views

Can there be situations where using (noexpand) on an indexed view is undesirable?

From the past experience using the schema bound and indexed view in a query with (noexpand) hint usually dramatically improves performance if the view is designed well. Apparently using the indexed ...
0
votes
1answer
574 views

Best Practice For Data Views with Linked Server in SQL Server

In SQL Server, I have to design a View that I can map on a DBML file in order to dynamically retrieve information from the database with multiple conditions to filter. The problem is that I have to ...
13
votes
3answers
12k views
0
votes
2answers
34 views

Database view listing distinct days

I have a list of sensors of many machines, that stores on a second base (i.e., 86400 records each day for each machine). One of the many columns is the datetime of the value (that can also be stored ...
3
votes
1answer
31 views

Select Permissions Multiple Schema

I have an interesting one that I have not had the pleasure of solving before. I have been asked by a client to implement some views for reporting purposes, the views will be accessed via PowerBI, ...
1
vote
1answer
32 views

Is it possible to write a select * view that includes new columns added to table?

I would like to write a view that is essentially something simple select a + b, * from table_name and will always truly include all columns in table_name, even when I add or remove them. If I do the ...
1
vote
0answers
53 views

Get the name of the table column referenced by a (auto updatable) view column (PostgreSQL)

Given the folowing view definition create or replace view projects as select id, client_id as clientId from data.projects Is it possible, knowing the view name and the view column to get the name ...
0
votes
0answers
56 views

Can't create a view for other user's table despite having select privilege

I already executed the following commands: grant create view to scott; conn hr/hr; grant select on employees to scott; but: create or replace view company_phone_book as select employee_id emp_id, ...
0
votes
1answer
20 views

Hide column values for some rows in auto-updatable views while retaining the “write” capability for them in PostgreSQL

Suppose i have a view defined like this create or replace view clients as select id, name, case app_user_type() -- hide column content for everyone except the administrator when ...
7
votes
0answers
107 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 ...
0
votes
1answer
33 views

Partitioned view with dedicated “routing” column

I have an application (Microsoft Dynamics NAV to be exact) that splits up data for different companies in tables with the exact same structure. I can not change that behavior, so I built VIEWs that ...
1
vote
1answer
56 views

Postgres CTE query for a single row not optimized

From my database I am trying to get a weighted sum of a user's stats, and I will only be querying the table one or two users at a time, so I wrote it as a view. Since it's a view, I pretend to ...
1
vote
2answers
33 views

Correct way to write this view? Total sales/purchases/profits by day

I'm currently trying to create a view that lists a sum of sales, purchases and profits done by each person per day. This data comes from 3 tables: transaction: idtransaction (pk) user_iduser (fk) ...
6
votes
3answers
230 views

Updating a view on multiple joined tables

Since MSDN does not say much, what happens exactly if I execute the following query? update claims set status='Awaiting Auth.' where status = 'Approved' Can I use the ClaimStatusName column of the ...
1
vote
2answers
347 views

Complex view becomes slow when adding ORDER BY in outer query with small LIMIT [closed]

I have a pretty large query in a view (let's call it a_sql), that is really fast unless I use ORDER BY in an outer SELECT with a small LIMIT: SELECT customs.id AS custom_id, customs.custom_name AS ...
1
vote
1answer
29 views

DB2 - cannot invalidate a view

I work on something and I need to check some views. I use this code: SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME ...
0
votes
1answer
35 views

Cast as INT(11) instead of DECIMAL 11,0 in a view

I have a view with a column defined as: IFNULL(priority.id, 2) priority_id, ... where priority.id has type INT(11), however the resulting view column has type: DECIMAL(11,0) How do I get the ...
1
vote
1answer
66 views

Making data fetching faster on VIEW

I have created a rest service which fetches data from Oracle database and send it back to the application from which the rest service is called . Below is the query to fetch data from VIEW ,this ...
1
vote
1answer
30 views

Find insert/update/delete queries that reference a partitioned view

I have just given a SQL Server with performance problems. The database is very large and use distributed views with a very large primary clustered key. (24 bytes). I would like to change this primary ...
1
vote
1answer
48 views

Performance issue using view vs. direct statement

I have a table with structure similar to this: table values ( id1 int, id2 int, value real, value_at timestamp, constraint primary key (id1,id2,value_at) ) I need to get the "current value" for a ...
2
votes
1answer
1k views

Insert into a view derived from a table with a discriminator field

I am in a situation in which I have a base table with some fields and a discriminator field which could have one out of a specific set of values. From that base table, I have derived many views, one ...
3
votes
1answer
135 views

Bad performance with select on view if check for 'is null'

I have two quite simple tables, and a view to join them on bigint fields: CREATE TABLE foo.ao ( id serial NOT NULL, ao_id bigint NOT NULL, ao_plz text, ao_community text, ao_street text, ...
35
votes
5answers
25k 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 ...
6
votes
1answer
2k views

need suggestions to improve view performance

I am looking to improve the performance of a view in SQL Server 2008. This view exists in a reporting database that is widely used by not-very-technical-folk to basically denormalize all of these ...
6
votes
1answer
1k 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 ...
-2
votes
1answer
77 views

Create a view for different attributes from different tables

I have three tables: product(pid,name,category,maker-cid) purchase(buyer-ssn,seller-ssn,quantity,pid) person(ssn,name,phone number,city) How can I create a view to expose only the Buyer name, ...