Create Clustered Index on VIEW
-
Hi, We have two tables and we views like: 1) create view ... select bno from A : we can create a clustered index on this view 2) create view ... select bno from B : we can create a clustered index on this view 3) create view ... select A.bno from A LEFT JOIN B ON B.bno=A.bno : we cannot create a clustered index for this view, it gives error like: "... one or more disallowed construct" How can we solve this problem. Thanks for your help (:
-
Hi, We have two tables and we views like: 1) create view ... select bno from A : we can create a clustered index on this view 2) create view ... select bno from B : we can create a clustered index on this view 3) create view ... select A.bno from A LEFT JOIN B ON B.bno=A.bno : we cannot create a clustered index for this view, it gives error like: "... one or more disallowed construct" How can we solve this problem. Thanks for your help (:
Simple. You cannot have a clustered index on a multi-table view - you can on the underlying tables, but not the view. This is because a view does not actually exist in the database, it is effectively just a select statement. A clustered index physically orders the data and as a multi-table view does not have a physical presence it cannot be ordered. The clustered index you have created on the single tyable views have been applied to the underlying table. Hope this helps
Bob Ashfield Consultants Ltd
-
Simple. You cannot have a clustered index on a multi-table view - you can on the underlying tables, but not the view. This is because a view does not actually exist in the database, it is effectively just a select statement. A clustered index physically orders the data and as a multi-table view does not have a physical presence it cannot be ordered. The clustered index you have created on the single tyable views have been applied to the underlying table. Hope this helps
Bob Ashfield Consultants Ltd
So, how can we optimize that query to make it faster if we cannot declare an index? What's the way to improve our performance? Thanks.
-
So, how can we optimize that query to make it faster if we cannot declare an index? What's the way to improve our performance? Thanks.
Thats a whole can of worms. Firstly, outer joins are bad. Can it be avoided? Secondly, you can create a clustered index on each of the underlying tables, also a (non-clustered) index covering the columns in the view may help, but its really not possible to give a definative answer with only the limited oinformation yuo have supplied. Thirdly, how large are the tables? The whole table may be cached if they are small, so indexes are relatively unimportant in that case. What is the frequency of insert/update/delete from the underlying tables? More indexes means slower for these activities. I could go on, but you get the idea, there is no simple answer.
Bob Ashfield Consultants Ltd
-
Simple. You cannot have a clustered index on a multi-table view - you can on the underlying tables, but not the view. This is because a view does not actually exist in the database, it is effectively just a select statement. A clustered index physically orders the data and as a multi-table view does not have a physical presence it cannot be ordered. The clustered index you have created on the single tyable views have been applied to the underlying table. Hope this helps
Bob Ashfield Consultants Ltd
We have found a sample that contains multi-table view and it has a clustered index, we tasted its create script and found the difference. Multi-table views can have Clustered Index but it mustn't contain LEFT OUTER JOIN, in our view the select query defined like LEFT JOIN but we've just learned that LEFT JOIN means LEFT OUTER JOIN so we couldn't be able to create clustered index, the problem was JOIN statement. That was all (:
-
We have found a sample that contains multi-table view and it has a clustered index, we tasted its create script and found the difference. Multi-table views can have Clustered Index but it mustn't contain LEFT OUTER JOIN, in our view the select query defined like LEFT JOIN but we've just learned that LEFT JOIN means LEFT OUTER JOIN so we couldn't be able to create clustered index, the problem was JOIN statement. That was all (:
-
Thats a whole can of worms. Firstly, outer joins are bad. Can it be avoided? Secondly, you can create a clustered index on each of the underlying tables, also a (non-clustered) index covering the columns in the view may help, but its really not possible to give a definative answer with only the limited oinformation yuo have supplied. Thirdly, how large are the tables? The whole table may be cached if they are small, so indexes are relatively unimportant in that case. What is the frequency of insert/update/delete from the underlying tables? More indexes means slower for these activities. I could go on, but you get the idea, there is no simple answer.
Bob Ashfield Consultants Ltd
I used to look for a definitive Yes/No answer to managing indexes, I have since come to the conclusion the DB tuning is an Art for NOT a science. There are so many factors that weigh differently on the results that each case is very individual. So I have a thumb rule - grab the fields used in the join and where clauses and create a index covering the most common 2/3 queries.
Never underestimate the power of human stupidity RAH