April 6, 2008 at 10:53 am
Hi All,
I have a table and I created one view on it and I created Clustered Index on this view.
When i am making a Select Statement to view the data,
this view is doing table scan instead of index scan,
In this point I am really confused, then what is the used of a Indexed View??
Please Clear me on this concepts...
Code Present Below
IF OBJECT_ID('v') IS NOT NULL DROP VIEW v
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
go
CREATE TABLE t
(id int NOT NULL,
a int NOT NULL,
b float(53) NOT NULL)
GO
INSERT t VALUES(1, 1,1.0e1)
INSERT t VALUES(2, 1,1.0e2)
INSERT t VALUES(3, 2, 1.0e0)
INSERT t VALUES(4, 2, 5.0e-17)
INSERT t VALUES(5, 2, 5.0e-17)
INSERT t VALUES(6, 2, 5.0e-17)
GO
CREATE VIEW v WITH SCHEMABINDING AS
SELECT a, SUM(b) AS sum_b, COUNT_BIG(*) AS c
FROM dbo.t
GROUP BY a
GO
CREATE UNIQUE CLUSTERED INDEX idx ON v(a)
GO
Select * from v
Please help me ...
Cheers!
Sandy.
--
April 6, 2008 at 12:06 pm
April 6, 2008 at 3:13 pm
Sandy: not sure why your view isn't using the clustered index but the group by is not the issue. using your sample data script, here's my sqlcmd results:
2> CREATE VIEW v WITH SCHEMABINDING AS
3> SELECT a, SUM(b) AS sum_b, COUNT_BIG(*) AS c
4> FROM dbo.t
5> GROUP BY a
6> GO
1> CREATE UNIQUE CLUSTERED INDEX idx ON v(a)
2> GO
1> set showplan_text on
2> go
1> Select * from v where a = 2
2> go
StmtText
------------------------------
select * from v where a = 2
(1 rows affected)
StmtText
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------
|--Stream Aggregate(DEFINE: ([Expr1004]=SUM([staging].[dbo].[v].[sum_b]), [Expr
1005]=SUM([staging].[dbo].[v].[c]), [staging].[dbo].[t].[a]=ANY([staging].[dbo].
[v].[a])))
|--Clustered Index Seek(OBJECT: ([staging].[dbo].[v].[idx]), SEEK: ([stagin
g].[dbo].[v].[a]=(2)) ORDERED FORWARD)
(2 rows affected)
April 6, 2008 at 10:48 pm
You will never get an INDEX SEEK using SELECT *... only index scans.
--Jeff Moden
April 7, 2008 at 2:58 am
hi antonio.collins,
I have used your query too, but still same result, I mean table scan here...
|--Stream Aggregate(DEFINE:([master].[dbo].[t].[a]=ANY([master].[dbo].[t].[a])))
|--Table Scan(OBJECT:([master].[dbo].[t]), WHERE:([master].[dbo].[t].[a]=(2)))
Can u please help me on this?
hey Jeff Moden,
Can you please clear me, I am not clear about your statement and
can u give me small idea about Indexed view??
You will never get an INDEX SEEK using SELECT *... only index scans.
Can you clear me about Indexed view concepts??
Cheers!
Sandy.
--
April 7, 2008 at 6:36 am
You will never get an INDEX SEEK using SELECT *... only index scans.
My most sincere apologies... the comment above does NOT apply to INDEXED VIEWs.
What matters is what you have in the WHERE clause of whatever query is using the INDEXED VIEW. For example, the following WILL cause an INDEX SCAN because it needs to return ALL rows from the view and it's more efficient to do a scan...
SELECT *
FROM v
... but if you have something in the WHERE clause that is capable of using the clustered or other index, you'll get a nice fast INDEX SEEK. For example, the following will use an INDEX SEEK...
SELECT *
FROM v
WHERE A = 2
This is important when a join comes into play on the indexed column. But, depending on the query, you could still get an INDEX SCAN just like on any other derived table (which is sometimes called an "Inline View").
The real key to the speed of an INDEXED VIEW is that the aggregates are precalculated and materialized as if you had populated a temp table with the same query. That's why they don't recommend using INDEXED VIEWs on tables that are made to suffer a great many changes... everytime you change something, the INDEXED VIEW must reaggregate the answers for the affected rows.
--Jeff Moden
April 7, 2008 at 10:37 pm
Thanks Jeff,
Cheers!
Sandy.
--
December 15, 2009 at 1:47 pm
It's a very late response, but I would put my 2 cents anyway.
I totally agree to what Jeff has explained, but I guess the problem what Sandy is referring to is a little different. If the WITH (NOEXPAND) hint is used in the query then an INDEX scan will happen instead of TABLE scan.
Select * from V with (NOEXPAND)
In Sandy's case, the query processor just takes the Indexed View as a regular view and expands the view when the query is run, instead of using the Clustered Index on the view.
Deepak
December 15, 2009 at 2:22 pm
Not sure if this is relavent, but an INDEX scan on a clustered index is a table scan.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply