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, ...
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 ...
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))
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 ...
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 ...
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
The execution plan (opened with sentryone plan explorer) shows what steps it went through:
With the stream aggregate aggregating ...
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', '...
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.
SET STATISTICS IO ON;
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 ...
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 ...
There is no setting to change this behavior nor has it changed in newer SQL Server versions. An excerpt from the current documentation remarks section for SQL Server 2019 and Azure SQL Database:
ALTER VIEW can be applied to indexed views; however, ALTER VIEW
unconditionally drops all indexes on the view
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 = 'email@example.com'
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 ...
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 ...
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:
CREATE USER username FROM LOGIN username;
GRANT SELECT ON dbo.MyViewName TO username;
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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:
-- simple table with two int columns
CREATE TABLE dbo.x(a INT, b INT);
INSERT dbo.x(a,b) VALUES(1,2);
The query optimizer treats an inline table valued function exactly like a view:
CREATE FUNCTION dbo.InlineUdf(@arg1 int)
... 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:
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 ...
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;
For all messages between timeA and timeB, find the recipients and for every recipient, find one message (the latest in between timeA and timeB).
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 ...
This is just Standard SQL join syntax with the optional parentheses removed:
RIGHT JOIN TableA
ON TableA.ID = TableB.ID
ON TableB.TypeID = TableC.TypeID
If you don't like the syntax generated by the SSMS view designer (which is buggy and rarely updated anyway), simply write the views by hand using ...
Here is what I do in such cases, usually some of this helps:
Look at the whole query and try to remove unneeded tables from it.
Rethink outer JOINs (that is, LEFT/RIGHT JOIN) and if possible, eliminate them from view definition, replacing by inner JOINS.
Try to increase planner constants so the server can put more effort into planning phase. You can do ...