Hot answers tagged

42

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 ...


38

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 ...


28

Regardless of platform, the following remarks apply. (-) Nested views: are harder to understand and debug e.g. What table column does this view column refer to? Lemme dig through 4 levels of view definitions... make it harder for the query optimizer to come up with the most efficient query plan See this, this, this, and this for anecdotal evidence. ...


27

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 ...


20

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 ...


17

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


16

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 ...


16

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' ...


15

Sometimes nested views are used to prevent repeating aggregates. Let's say you have a view that counts messages and groups them by userid, you might have a view over that that counts the number of users that have > 100 messages, that kind of thing. This is most effective when the base view is an indexed view - you don't necessarily want to create yet another ...


14

A materialized view in Oracle is a combination of a structure to hold the data (a table), a job that refreshes the data (a job), and a process that figures out how to refresh the data based on the specified query. This process would generally involve the creation and maintenance of materialized view logs on the base table to track changes so that the ...


14

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 ...


13

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


13

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))


12

You can use ALTER VIEW in conjunction with the information schema. You mentioned dumping it out to a text file, so perhaps something like this: SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW ",table_name," AS ", view_definition,";") FROM information_schema.views WHERE table_schema='databasename' Mix this with the mysql command line (assuming *nix, ...


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 ...


11

Note: This answer addresses a couple of basic problems, but it's not the final solution. The question was still inconsistent after several requests for clarification, so I stopped processing. General difficulty The Problem is: predicates on some columns, ORDER BY on a different column. In your fast query, without ORDER BY, the first (arbitrary) 10 rows ...


10

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 ...


10

You'll need to use a union instead. If your data is indeed distinct, use a union all for better performance -- the step to eliminate duplicates is eliminated. create view table_full as select * from [A].[dbo].[AUX] union all select * from [B].[dbo].[AUX];


10

One good case for using MVs is that some times you want to aggregate data and get this summary information from large tables frequently and quickly. Without materialized views, you have to either deonormalize some of your tables and maintain the aggregates via code or repeatedly scan large sets of rows. Either way is not always acceptable specially with ...


10

For a view to be updatable without using INSTEAD OF triggers "SQL Server must be able to unambiguously trace modifications from the view definition to one base table.". There is no performance disadvantage to Updating these Views as SQL Server will just generate a query plan for the base table affected. One possible disadvantage might be that it adds a ...


9

The difference is that Enterprise edition without the hint may decide not to use the indexed view but the base tables instead. My personal experience is that SQL Server is somewhat braindead in this. I almost always have to use the hint: the query is quicker with far less IO even though the plan "looks" worse with a scan on the view not index seeks on the ...


9

Only the outermost ORDER BY will guarantee order Any intermediate or internal ORDER BY is ignored.This includes ORDER BY in a view There is no implied order in any table There is no implied order from any index (clustered or not) on that table Links "Sorting Rows with ORDER BY" (MSDN) ORDER BY guarantees a sorted result only for the outermost ...


9

The easiest way to think of it is: DBA_ / USER_ / ALL_ views are built on the data dictionary - they're not available if the database is not mounted and opened. V$ views tend to run against the instance, and therefore may be available if the database is not mounted, or is not mounted and opened, depending on the nature of the view. Using your example: ...


9

user_id, currency_id, and transaction_amount are all defined as NOT NULL columns in dbo.transactions It looks to me that SQL Server has a blanket assumption that an aggregate can produce a null even if the field(s) it operates on are not null. This is obviously true in certain cases: create table foo(bar integer not null); select sum(bar) from foo -- ...


9

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 ...


8

A simple example: CREATE TABLE dbo.x(a INT, b NCHAR(4)); GO CREATE VIEW dbo.vx AS SELECT a, b FROM dbo.x; GO ALTER TABLE dbo.x ALTER COLUMN a TINYINT; ALTER TABLE dbo.x ALTER COLUMN b NVARCHAR(4); GO SELECT a,b INTO #blat FROM dbo.vx; GO EXEC tempdb.dbo.sp_columns N'#blat'; GO DROP VIEW dbo.vx; DROP TABLE dbo.x, #blat; Partial output: COLUMN_NAME ...


8

This is just Standard SQL join syntax with the optional parentheses removed: SELECT * FROM tableC LEFT JOIN ( TableB 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 ...


8

A View is a logical table that is based on one or more physical tables. If there are foreign key relationships in the underlying tables, then they will be manifested in the view. Views are entirely dependent on the tables they are derived from, so trying to add foreign keys to them is not possible.


8

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: ...


8

Use WITH SCHEMABINDING in the view CREATE VIEW ExampleDBaseII WITH SCHEMABINDING AS SELECT T.ID, Cast(T.Name AS Varchar) as Name, Cast(T.City AS Varchar) as City, FROM Team T GO This will disallow any changes to the underling tables that could affect the view It also requires the use of qualifiers (schema, alias) and disallows the use of SELECT *. Which ...



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