Hot answers tagged view
26
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 ...
22
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 ...
20
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. ...
17
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 ...
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
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 ...
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 ...
10
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 ...
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
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 ...
8
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 ...
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
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, ...
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
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
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
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?
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
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
...
6
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 ...
6
Views are typically not implemented for performance. And while you currently cannot implement explicit indexed views (which are just views that SQL Server maintains for you), you can certainly maintain facts manually yourself.
For example, you mention that you currently calculate "whether someone is dead or not" using three CTEs and a CASE expression ...
6
No, it shouldn't prevent the table from being altered. Though, if you drop the underlying table a view depends on, or alter/remove the columns from the table the view uses, the view can become invalid. You can check what views are invalid in your system with the following query:
sql> select name from dba_objects where object_type = 'VIEW' and status = ...
5
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
-- ...
5
Never assume or "belief" the cause for something. Test it.
In your case run an EXPLAIN PLAN on the statements in question and check if the plan with views is more expensive than the corresponding SELECT using the base tables.
You can also use SQL*Plus' autotrace feature to check out logical IO done by both queries.
If that still doesn't give enough ...
Only top voted, non community-wiki answers of a minimum length are eligible
