New answers tagged view
EXPLAIN EXTENDED [select query] and then SHOW WARNINGS will show the rewritten form of the view. From here, it is easier to analyze performance characteristics. Sight checking queries is generally not easy to optimize.
According to the MySQL Documentation on Views Views (including updatable views) are available in MySQL Server 5.6. Views are stored queries that when invoked produce a result set. A view acts as a virtual table. The first thing that must be realized about a view is that it produces a result set. The result set emerging from query invoked from the view ...
The answer has to do with the method of performing each of these operations. Since views are inherently unindexed, JOIN operations using fields from views will take longer than JOIN operations using tables since the scan cannot use an index. In this case, the lookup also limits the number of records that have to be returned in processing- it only pulls ...
Later versions of SQL (2005+) seem better at optimizing the use of views. Views are best for consolidating business rules. EG: where I work we have a telecom product database. Each product is assigned to a rateplan, and that rateplan can get swapped out, and rates on the rateplan can get activated/deacitvated as rates are increased or modified. To make it ...
Besides running a SQL Server Profiler trace using the Standard (default) template + a filter on the TextData column you could also try to check if the view is a dependency of any other object from inside the database: -- not always up to date: EXEC sp_depends 'vw_obsolete_view' -- undocumented: EXEC sp_MSdependencies 'vw_obsolete_view' -- just ...
The rebuild of the table and indexes probably caused the statistics about the distribution of the values of certain columns to reset thus causing the optimizer to be fed inaccurate information. You will probably need to change the statistics gathering for some of the columns. See Oracle Documentation on Histograms
Top 50 recent answers are included