Tag Info

Hot answers tagged

32

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


32

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


25

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


23

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


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


14

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

You can use the PIVOT function to perform this query. My answer will include both a Static and dynamic version because sometimes it is easier to understand it using a static version. A Static Pivot is when you hard-code all of the values that you want to transform into columns. -- first into into a #temp table the list of dates that you want to turn to ...


13

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


12

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


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

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


10

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


9

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


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


8

CONNECT BY is the correct way to handle data that is naturally recursive. I don't know what your table looks like but maybe something like: SELECT * FROM some_table st START WITH st.location = 'BLDG-01' CONNECT BY PRIOR st.location = st.parent; This should get nodes under "BLDG-01". The START WITH clause is your base case. Another explanation (aside ...


8

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


8

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


8

(+) Reasons to create a physical table to store report data: The report data is reusable. I point Crystal Reports or SharePoint to the table and then don't worry about how often or when those tools or my end users access the data. (Well, to an extent, since repeatedly reading a large report table will trash my buffer cache.) I can also maintain a sliding ...


8

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


8

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


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


7

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.


7

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


7

Your expression can be as complicated as you'd like so you can certainly specify that the refresh should happen every day at 2 AM, i.e. NEXT trunc(sysdate+1) + interval '2' hour will specify that the refresh happens every day at 2 AM. The expression is evaluated at the conclusion of each refresh so you just need to ensure that the expression evaluates to ...


7

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


7

A view is macro that expands. So if your view is a JOIN of 2 tables, the execution plan will show the 2 tables. The view is transparent. This doesn't apply if the view is indexed/materialised. However then you wouldn't be asking this question. So, what does the execution plan say? The DTA? Missing indexes dmv query? Most expensive dmv query?


7

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


6

In addition to what others have said (WHERE clause, INDEXes that might help) I suggest you might want to consider indexed views - assuming it's even possible to create indexes on the view (details). Then you may be able to also apply the NOEXPAND hint in your queries (details).


6

No, you do not need to enumerate the columns used in a materialized view when creating the materialized view log. In fact you cannot create a materialized view log using the primary key method and include all the columns because you would be including the primary key column itself, which is not allowed. The concept of a materialized view log is to store ...


6

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