Hot answers tagged

59

One of the biggest benefit of using a materialized view is that Oracle takes care of keeping the data in sync. If you have a separate aggregate table, you are responsible for keeping the data synchronized. That generally requires a reasonable amount of code and a decent amount of testing and most organizations manage to make mistakes that leave holes that ...


54

The book is wrong. Selecting from a view is exactly as fast or slow as running the underlying SQL statement – you can easily check that using explain analyze. The Postgres optimizer (and the optimizer for many other modern DBMSes) will be able to push down predicates on the view into the actual view statement – provided this is a simple statement (again, ...


49

You shouldn't rely too much on cost percentages in execution plans. These are always estimated costs, even in post-execution plans with 'actual' numbers for things like row counts. The estimated costs are based on a model that happens to work pretty well for the purpose it is intended for: enabling the optimizer to choose between different candidate ...


45

From the documentation: select table_name from INFORMATION_SCHEMA.views; If you don't want the system views is your result, try this: select table_name from INFORMATION_SCHEMA.views WHERE table_schema = ANY (current_schemas(false))


43

Views in MySQL are handled using one of two different algorithms: MERGE or TEMPTABLE. MERGE is simply a query expansion with appropriate aliases. TEMPTABLE is just what it sounds like, the view puts the results into a temporary table before running the WHERE clause, and there are no indexes on it. The 'third' option is UNDEFINED, which tells MySQL to select ...


36

This gets logged to the default trace so, as long as it is enabled and hasn't rolled over in the meantime it should appear in the "Schema Changes History" report. To access this in Management Studio right click the database then from the context menu choose Reports -> Standard Reports -> Schema Changes History To retrieve the same information via ...


32

Most of the information you will need is going to be in the execution plan ( and the plan XML). Take this query: SELECT COUNT(val) As ColA, COUNT(val2) As ColB, COUNT(val) + COUNT(val2) As ColC FROM dbo.TableA; The execution plan (opened with sentryone plan explorer) shows what steps it went through: With the stream aggregate aggregating ...


24

The CTE goes inside the view. Take a query with a CTE WITH cte AS (...) SELECT ...; Just add CREATE VIEW AS .. GO CREATE VIEW AS WITH cte AS (...) SELECT ...; GO MSDN does describe multiple CTEs (See example j) CREATE VIEW AS WITH cte1 AS (...), cte2 AS (...), cte3 AS (...) SELECT ... GO


21

You can query pg_catalog.pg_views for your desired information: select viewname from pg_catalog.pg_views; Refined query to get schema name also - just in case you have multiple views with the same name in different schemas - and left out those system views: select schemaname, viewname from pg_catalog.pg_views where schemaname NOT IN ('pg_catalog', '...


20

To give you an example of what @a_horse explained: Postgres implements the information schema, which consists of (sometimes complex) views providing information about DB objects in standardized form. This is convenient and reliable - and can be substantially more expensive than accessing the Postgres catalog tables directly. Very simple example, to get all ...


19

Martin already pointed toward the best avenue, the administrative audit trace which is usually on (unless it has been explicitly disabled). If you cannot find the info in the admin trace (was disabled or it had recycled) you can retrieve the info from the log backups. Since is a production DB, I assume you have a regular backup cycle, with periodic full ...


19

This happens often if the optimiser thinks it can do better with the base tables. If I create an indexed view, I tend to always use NOEXPAND to make it use it. I also compare the query statistics to ensure that it adds some benefit. That is, if I identify a useful indexed view I want the optimiser to always use it. Example: SET STATISTICS IO ON; SET ...


18

It boils down to what UPDATE statement does. It's not entirely obvious but your statement is equivalent to this one: UPDATE upd SET Ticket = 'ARP.ExGE' , Method = 'smtp' , AcctOwner = 'r00417819' , DisplayName = '~AppLight HBSFax-Inactive' , Destination = 'r00417819@mail.ad.ge.com' ...


18

I think the main problem here is that you're trying to use a single view to feed many different reports, thus complicating the view definition over and over. Not all the reports will need all the columns, the calculations and the JOINs in that view, so, in many situations, you will be hitting tables and performing calculations for no reason. A more ...


17

As of PostgreSQL 9.4: Different to the documentation of CREATE VIEW, the documentation of CREATE MATERIALIZED VIEW does NOT mention the REPLACE keyword. There seems to be no shortcut aside from dropping all dependent objects and rebuilding each one. When you do so, I can only recommend two small things: Use DROP MATERIALIZED VIEW blabla CASCADE to get a ...


17

The [Ranking] field is showing as "Nullable" due to being a computed column. Yes, it is declared as NOT NULL, but as the MSDN page for Computed Columns states, the database engine can change that determination at query-time: The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most ...


16

First, stop using SELECT * in your views. I talk about this quite a bit here: Bad habits to kick : using SELECT * / omitting the column list Next, run sp_refreshview or sp_refreshsqlmodule against each view that references a table (or another view!) that you have changed, e.g.: EXEC sp_refreshview N'dbo.viewname'; If you want to generate a script that ...


15

Please don't use the UI for this. It's a confusing mess. It sounds to me like what you want is to create a user in a database, for a specific login, who only has permissions to select from one view. So, since you already have the login created: USE your_db; GO CREATE USER username FROM LOGIN username; GO GRANT SELECT ON dbo.MyViewName TO username; GO EDIT ...


14

Views are security tools. You do not want a particular user or application to know where your data table, you provide a view with only the columns it needs. Remember that views always degrade performance, similar queries should be stored procedures and functions, not views. To make a query tuning, always follow best practices, avoid using functions in ...


14

Materialized Views are automatically updated as their base tables are updated.


14

My answer will focus almost exclusively on SQL Server just because I'm going to give a fairly detailed answer and I don't have the same level of expertise in other platforms. First it's important to realize that the query optimizer doesn't directly work against the SQL you write. It gets transformed into an internal format before optimization. What you have ...


13

To guarantee that the Ranking computed column expression does not return NULL in any circumstances, you must wrap it in ISNULL with a suitable default value. For example: Ranking AS ISNULL(Id + RankingBonus, 0) PERSISTED NOT NULL The NOT NULL constraint ensures the persisted value is not null, in the context of the table- and session-level settings in ...


12

You should see absolutely no difference in the plans or the performance between these two choices. When the view is queried, it is expanded out to a query against the base table, which means the same seek or scan will be used. Now, depending on the data type and selectivity of MyColumn, if you wanted to create a filtered index on the base table (when you ...


12

Partitioned views are a (very) old technique for partitioning data that are very rarely used today. Oracle added the ability to partition tables back in Oracle 8, which provides much more functionality than partitioned views, at which point partitioned views became obsolete. The only reason to consider using partitioned views would be if you can't afford a ...


12

PostgreSQL (true up to at least 9.4) doesn't currently support removing a column with CREATE OR REPLACE VIEW. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. There is no ...


12

A view is just a "saved query". The indexes on the base table are still used whenever you access the view. You don't need to use an indexed view, unless the view contains an expensive logic (aggregations or joins) that you don't want to perform each time you query the view. Please note that even when the view is "materialized", the optimizer is free to ...


12

This query: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE messages.timestamp BETWEEN timeA AND timeB ORDER BY recipient, timestamp DESC; says: For all messages between timeA and timeB, find the recipients and for every recipient, find one message (the latest in between timeA and timeB). ...


12

Yes, there are reasons to not use SELECT * in a view. The most important is that SQL Server caches the metadata of a view's output, and it doesn't magically update if underlying objects change. Here's a quick example to demonstrate: USE tempdb; GO -- simple table with two int columns CREATE TABLE dbo.x(a INT, b INT); GO INSERT dbo.x(a,b) VALUES(1,2); GO -- ...


11

The query optimizer treats an inline table valued function exactly like a view: CREATE FUNCTION dbo.InlineUdf(@arg1 int) RETURNS TABLE AS RETURN ( ... your query here ... ); A multi-statement table-valued function is run more like a stored procedure. They typically have to be executed multiple times, rather than be folded into the main query: CREATE ...


11

To answer your question literally, yes, in MySQL, views do exist as occupied space on the disk. But of course they do: if the didn't, where would they exist? If you rebooted your server, how would the views persist? I imagine what you really meant was "do MySQL views occupy physical space in proportion to the number of rows they contain?", in which case the ...


Only top voted, non community-wiki answers of a minimum length are eligible