A virtual table that represents the data in one or more tables in an alternative way.
14
votes
4answers
1k 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 ...
12
votes
1answer
709 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 ...
11
votes
2answers
2k 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 ...
10
votes
3answers
2k 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 ...
10
votes
3answers
1k 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
...
9
votes
5answers
1k 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 ...
8
votes
3answers
795 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 ...
8
votes
1answer
387 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?
7
votes
3answers
721 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 ...
7
votes
1answer
1k 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
...
6
votes
1answer
627 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 & ...
6
votes
2answers
7k 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 ...
6
votes
1answer
640 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.
6
votes
1answer
2k 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 ...
5
votes
2answers
6k 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 ...
5
votes
2answers
742 views
how does SQL Server know to lock view objects?
Not sure whether this is a more or less appropriate place to ask this question, originally posed at Stack Overflow.
In SQL Server 2008 I have a view V over tables A and B that looks roughly like
...
5
votes
1answer
1k 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 ...
5
votes
1answer
580 views
Do I need to enumerate the columns used in a materialized view when creating the MV log?
I'm currently creating materialized view logs on a whole bunch of tables in order to support materialized views with FAST REFRESH. The materialized views we're creating contain approx 30 columns from ...
5
votes
1answer
1k 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 ...
5
votes
2answers
4k 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?
5
votes
1answer
189 views
Does sql server calculate views every time when someone query the views?
I wonder whether sql server calculate views every time when someone query the views. It is because everytime I query the views, it take such a long time.
And when I tried to shorten the time by ...
5
votes
1answer
142 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 ...
5
votes
2answers
465 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 ...
4
votes
3answers
1k views
Optimizing query using view on EAV structure
An application is writing into a database that follows an EAV structure, similar to this:
CREATE TABLE item (
id INTEGER PRIMARY KEY,
description TEXT
);
CREATE TABLE item_attr (
item ...
4
votes
2answers
284 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 ...
4
votes
2answers
728 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 ...
4
votes
1answer
311 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 ...
4
votes
2answers
587 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 ...
4
votes
2answers
56 views
Frequency distributions segmented by a field
Some background:
I have a sample population data file. Each record in the data file has a frequency weight (FIELD NAME: wgt) indicating how many times the records needs to be replicated to get the ...
4
votes
1answer
2k 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.
...
4
votes
1answer
155 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 ...
4
votes
2answers
53 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 ...
4
votes
1answer
307 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 ...
4
votes
2answers
205 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 ...
4
votes
2answers
1k views
sql 2008, select from a view as a different user, like owner
In sql server 2008, the system views sys.database_role_members and sys.database_principals, only returns the users and group that the current account is allowed to view.
I need to check id a certain ...
4
votes
2answers
528 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 ...
3
votes
3answers
871 views
How to create a view that refresh automatically
I was triying to create a View by the simplest way like this:
Use SoccerDB;
GO
CREATE VIEW ExampleDBaseII
AS
SELECT ID, Cast(Name AS Varchar) as Name,Cast(City AS Varchar) as City,
FROM Team
GO
...
3
votes
1answer
105 views
What is a partitioned view?
What is a partitioned view? I googled but could not get a satisfactory result.
3
votes
3answers
238 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 ...
3
votes
2answers
301 views
Join condition on complex views
If i join two/more complex views (views that are made up by joining two/more tables); would it result in multiple iteration of reads on the base tables?
Would it not be more efficient just to have ...
3
votes
2answers
2k views
How can I help SQL Server recognize my 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
...
3
votes
2answers
5k 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 ...
3
votes
1answer
126 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.
...
3
votes
2answers
890 views
sys.database_files / sys.filegroups VS sys.sysfiles / sys.sysfilegroups
These catalog views seem to show the exact same data. What is the point of having multiple system views that seem to show the same thing?
Here is how I proved it is the same data:
select
...
3
votes
1answer
2k views
Oracle: How to create a not null column in a view
Given a table like:
CREATE TABLE "MyTable"
(
"MyColumn" NUMBER NOT NULL
);
I want to create a view like:
CREATE VIEW "MyView" AS
SELECT
CAST("MyColumn" AS BINARY_DOUBLE) AS "MyColumn"
FROM ...
3
votes
1answer
80 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
...
3
votes
2answers
109 views
SQL SERVER - Linked Server and query performance
I have a performance problem related to SQL Server/linked servers/views.
I hope you can help me understand what is the best way to do what I want =).
I have a database K with 3 linked servers ...
2
votes
2answers
920 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 ...
2
votes
2answers
321 views
What reason is there to create a physical table for a report output?
I've noticed that for Crystal Reports made by our organization and by some of our ERA software providers have a tendency to use physical tables for their reports' data sets, rather than using a view ...
2
votes
2answers
80 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 ...
