Tag Info

Hot answers tagged

29

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


29

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


23

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


22

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


15

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


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


11

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


11

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


11

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


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

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


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

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

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

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


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


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

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

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


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

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


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

This is one (of many) downsides of EAV designs. You can't really improve the JOIN: because of the necessary complexity, a cost based optimiser won't get to the perfect plan. It finds "good enough" Suggestions: don't use a view: use aggregate type queries (eg COUNT(*) = 2 if I match both height and weight) use a trigger to maintain a real (or sparse) ...


6

Create a text file with all the view definitions: mysql -uusername -ppassword -A --skip-column-names -e"SELECT CONCAT('SHOW CREATE VIEW ',table_schema,'.',table_name,'\\G') FROM information_schema.tables WHERE engine IS NULL" | mysql -uusername -ppassword -A --skip-column-names > AllMyViews.sql You edit AllMyViews.sql from there. Then, Drop the Views ...


6

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.


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


6

There is no specific option for dumping views. You can try the following: mysqldump -h... -u... -p... --all-databases --routines --triggers --no-data > /root/MySQLDBSchema.sql grep "CREATE ALGORITHM" /root/MySQLDBSchema.sql You should be able to see the views. This indicates that when you dump databases, the view comes with it. Another stunt you can ...


6

The comments on the question show that the issue is that the test database the OP was using to develop the query had radically different data characteristics than the production database. It had much fewer rows and the field being used for filtering wasn't selective enough. When the number of distinct values in a column is too small the index may not be ...


6

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



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