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

learn more… | top users | synonyms

4
votes
2answers
77 views

Partitioned Views - Storage of Indexes

I have a single table that contains 4 billions rows that I plan on splitting up in to separate tables for each Quarter of the year. Then I want to create a partitioned view across all tables. At the ...
0
votes
0answers
75 views

Why does mysql show Primary key constraint on a View?

As far as I know views cannot have a primary key. However the result of SELECT FROM KEY_COLUMN_USAGE says differ. PRIMARY | S_MOBILE_ITEM_VIEW | ID | ...
2
votes
2answers
404 views

View column has an 8000 character limit?

In SQL Server 2008 R2 we are using an updateable view to update a varchar(max) column. When we update the column with text and then check the number of characters, it is showing a length of 8000 ...
0
votes
0answers
54 views

Procedure after commit

I have an application which periodically will insert large amounts of data, from that data I build a few materialized views (well emulate by creating a table from a view and replacing it with the ...
0
votes
1answer
1k views

Database user specified as a definer

I have a view in my database. problem is below Error SQL query: SELECT * FROM `lumiin_crm_prod`.`v_contact` LIMIT 1 ; MySQL said: 1449 - The user specified as a definer ('lumicrm'@'%') ...
0
votes
2answers
29 views

A query for a latest version of data

MySQL. Let there is a table with fields startdate and kind. Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is ...
3
votes
1answer
612 views

What is a partitioned view?

What is a partitioned view? I googled but could not get a satisfactory result.
1
vote
3answers
391 views

do MYSQL views occupy physical space?

do MYSQL views occupy physical space? Or in other words, is a MYSQL database limited to a certain number of views?
-1
votes
1answer
125 views

Odd syntax error when creating view with SCHEMABINDING [closed]

I've been working with SQL Views for a while now, and I recently read that something that I want to do requires that one of my views be created with schemabinding. That sounds fine, but I get a ...
-1
votes
1answer
383 views

How to create view in SQLite using INSERT INTO? [closed]

I'm trying to create view that will summarize data in two tables. Each table have several columns, but they both have NAME and AREA columns. And I want these two columns to be united correspondingly ...
1
vote
1answer
103 views

Does the SQL Server Backup Database Command also backup views from a Database?

I have a backup job (SQL Server Agent job) which will iterate through all of our SQL Server databases in SSMS, invoke the BACKUP DATABASE command to create a .bak file on a different server. This ...
1
vote
1answer
133 views

Can't query one view from another?

Okay, I'm moving a database from SQL Server (which I already miss) to MySql 5.6. I'm having a problem creating a list of valid dates. In SQL Server I just used CTEs to build up numbers, then months, ...
2
votes
2answers
78 views

View Disc Usage

Is this statement correct? Behind the scenes SQL Server actually stores the data in the view as a physical table which must be updated if any of the data behind the view changes I did some ...
2
votes
1answer
586 views

Can a view affect ALTER TABLE commands?

I provide a database system to a number of customers. The database is installed locally on Oracle. We periodically upgrade the database structure (either adding new fields, renaming old fields, ...
2
votes
1answer
273 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 ...
4
votes
1answer
551 views

Join To a @Table Variable is running ineficiently

Alright so I have a report sproc that was running incredibly slow. Customers were complaining that the report would not run so I started investigating exactly where in the sproc the problem was and I ...
0
votes
1answer
34 views

Two dichotomy views of a table, and means to express this as a stored procudure?

There is a table events. Some relatively complex logic determines for each event whether it is archived. Then I need two more views: All archived events and all not archived events. How to express ...
1
vote
1answer
234 views

Question about Oracle's interpretation of “host cpu utilization” in v$sysmetric view

I am studying the v$sysmetric view from Oracle and trying to understand what is Oracle's interpretation of "Host CPU Utilization" It says in the metric_unit as % busy/(idle + busy), but what is busy? ...
0
votes
1answer
495 views

View dropped, now shows as table “in use”, can't drop

I have a view on a MySQL/MyISAM setup, then the underlying table had a column dropped, and made that view invalid. I tried dropping the view so I could recreate it, then MySQL said it was a table ...
1
vote
0answers
46 views

CouchDB views and references

Not sure if/how it is intended to be done but i am trying to match up all the active document in my database with the author of it: function(doc) { if (doc.data.active && doc.data.active ...
1
vote
1answer
88 views

Is it a best practice to contrast table and dependent view metadata to target which views to refresh?

We have non-indexed views created on vendor tables. When they change table structure or column definitions we run sp_refreshview as needed. What is the viability of a script to contrast the table ...
0
votes
1answer
156 views

Create a VIEW that spans 3 tables

Sorry if the title is not clear - feel free to improve. Here are 3 tables (not all data is relevant to the question): CREATE TABLE `content` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
0
votes
2answers
422 views

MySQL Reusing calculation in SELECT clause in a VIEW

I have an SQL query that has a simple date difference between NOW() and the creation date (is in the table). I need to reuse this again within the SELECT clause in a VIEW. Is this possible at all? ...
4
votes
1answer
235 views

Adding indices to views

I have a table that contains XML data in a varchar type field. This field is used to store data from a variety of different XML schemas some of which are related via a record stored in another table. ...
5
votes
1answer
190 views

Are view references in a query properly called “derived tables” as such?

While answering a question on stackoverflow, I presented a definition of derived tables: A derived table is a complete query, inside of parentheses, that is used as if it were a real table. But ...
0
votes
1answer
62 views

Where can I read about views for MySQL?

I really don't know nothing about SQL.. I know how to insert and select data from database using PHP but besides that, nothing. I found this view And I am sure that 5 minutes for you to answer this ...
4
votes
2answers
471 views

Oracle View Ignoring External WHERE Clause

Good day, This one has me stumped. I have a rather nasty developer query that I would like to store in a non-materialized Oracle view. The text for the view itself is a bit long to list here, but ...
1
vote
1answer
118 views

What is the locking behavior for Updatable/Insertable Views?

Consider I have a View that is merely a simple join between 2 tables, selecting all columns. The View is Updatable and Insertable. What would be the locking behavior when executing an UPDATE or ...
2
votes
2answers
115 views

Migrating SQL Server 2000 to SQL Server 2008

I'm migrating SQL Server 2000 to SQL Server 2008. I have many views. How do I check each views that if they have an order by clause? Thanks.
4
votes
1answer
807 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 ...
0
votes
1answer
37 views

viewing Original Object under synonyms

I have certain synonyms in my schema which represent views from another schema. Now in oracle sqldeveloper, I am unable to see the actual SQL for those views but I see only an SQL like: CREATE OR ...
0
votes
1answer
327 views

Retrieving a fixed range of rows from Postgresql

I have a query: select qid,ansid,ans from table1 where askerid='something' This query can retrieve many rows. But I want to display only 50 rows at a time and then if the user clicks on more, the ...
1
vote
2answers
1k views

SQL Server 2008R2 - How to prevent a role from creating or altering views within a database?

I have a situation on SQL Server 2008 R2 where I am trying to prevent users of an AD group from being able to create new views, or alter any existing views, within a particular database. This AD group ...
4
votes
1answer
495 views

Determining whether column in view can only contain unique values

I have a SQL Server 2008 database with several views. I'm looking for a way to definitively determine whether a particular view has a column that can only contain unique values (i.e. can be used as a ...
0
votes
2answers
62 views

View for a MANY_MANY_MANY relationship

Let's say I have 3 main tables: category, tag, url category and tag have a MANY_MANY relationship (category_tag). tag and url have a MANY_MANY relationship (url_tag). I would like for a given ...
13
votes
1answer
1k 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 ...
2
votes
2answers
261 views

Does MySQL have information_schema.VIEW_COLUMN_USAGE or an equivalent?

SQL Server has a an information_schema view, 'VIEW_COLUMN_USAGE', which you can use to determine the actual/base tables that the columns in a view reside in. I'm looking for the equivalent in MySQL - ...
2
votes
1answer
2k views

Grant select to dbo view without select on non dbo tables

SQL Server 2008 R2 We have a login & user called "JoeBlogs", this user has it's own default schema of JoeBlogs - it has Public access only to the database. We then granted it SELECT on a dbo ...
1
vote
2answers
3k views

Why an object became invalid in oracle databse.

There are some invalid views and functions in my database. I am not sure what changes caused these objects to became invalid since many people are working on the same database. How can I see which ...
2
votes
2answers
5k views

SQL query to return a column per date in date range?

Let me explain what I've got and what I'm trying to achieve. Let's say I have Table A: BookingsPerPerson Person_Id ArrivalDate DepartureDate 123456 2012-01-01 2012-01-04 213415 ...
2
votes
2answers
118 views

(MySQL) How to change the results of a view according to the user that's viewing it?

I have a table, named servers, with a single column id. This table is accessed by various machines each with a different username for the database. I want to make it so each server does not see all ...
2
votes
2answers
723 views

IN Clause causes Execution plan to change from Nested Loops to Hash Match

I'm tuning a query and have discovered some behaviour I'm not clear about. If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes. There only 7 rows returned in the ...
3
votes
1answer
107 views

Slow MySQL query, not sure why

I'm working with an intranet application for property tax collection, and I've hit a snag. The following view runs much slower than I'd expect: create or replace view propertytaxessub0 as select ...
0
votes
1answer
295 views

Combining a tree structured Group model with its HABTM User model in one VIEW

I have a tree structured model Group which has the columns id, parent_id, name, lft and rght. I also have a model User which has the columns id, name and password. Group and User have a ...
4
votes
2answers
1k views

SQL Server Linked Server to Progress is slow with an openquery view

We have a SQL Server database setup with a Linked Server setup connecting to a Progress OpenEdge database. We created a SQL Server view (for using with SSRS) of some of the OpenEdge tables using code ...
4
votes
3answers
5k 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 ...
5
votes
2answers
710 views

Create index view with self join

I use SQL Server 2008 R2 and know that in Indexed View cant have self join. I have a tree table that have ID and ParentID Column and I need create indexed view on this table with self join between ...
-1
votes
1answer
174 views

Selecting just one table from Oracle view [closed]

I am creating a view to ease my manual selecting because I only want to fetch data from DB where it is meeting certain criteria which are present in several tables, so it is containing a lot of joins. ...
19
votes
4answers
4k 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 ...
4
votes
3answers
460 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 ...