|
| I'd say it's B because there is no where clause. Just about any time there
is a where clause you're going to be performing a table scan. In my
experience the only time I've seen statements with no where clause that did
not do table scans was when you were doing table joins and you specified in
the join the same thing that you would have put in your where clause.
(ex. select * from table1 T1 join table2 T2 on T1.objectid = T2.objectid
and T1.date > '20010202' and T2.date > '20010202' This would not
perform a table scan if you had indexed the date column in both tables even
though you have no where clause in the select statement - I see a lot of
people get confused when they see something like this it's not covered well
in the sql books).
Indexing won't matter because theres no where clause. Subquerys can have
where clauses which do not cause table scans. Aggregates don't have to be
computed by using a table scan - if you had indexed a column and used that
indexed column in your where clause, you could perform aggregate operations
that did not perform table scans.
-Tom
"masa" <lijinie@hotmail.com> wrote in message
news:9qtga1$6r0$1@mail.cn99.com...
> as i am a software developer rather than
> a DBA,i am not quite expertise on parsing
> the result of show_plan text,would
> anybody shed some light on the following
> result,and tell me which is the correct
> answer and why?
>
> thanks in advance!
>
> You are the database administrator for your company. You receive reports
> that your sales application has very
> poor response times.
> The database includes a table that is defined as follows:
> CREATE TABLE dbo.Orders (
> OrderID int IDENTITY (1,1) NOTNULL,
> SaiesPersonID int NOTNULL,
> RegionID int NOTNULL,
> OrderDate datetime NOTNULL,
> OrderAmount int NOTNULL,
> CustomerID int NULL
> )
> The OrderID column is the primary key of the table. There are also indexes
> on the RegionID and OrderAmount
> columns.
> You decide to run a showplan on all queries in the application. The
> following query, which accesses this table, is
> used to list total average sales by region:
> SELECT t1.RegionID, AVG(t1.SalesTotal) AS RegionAverage
> FROM (SELECT RegionID, SalesPersonID, SUM(OrderAmount) AS SalesTotal
> FROM Orders
> GROUP BY RegionID, SalesPersonID) AS t1
> GROUP BY t1.RegionID
> You set the SHOWPLAN_TEXT option to ON and execute the query. The showplan
> output is as follows:
> |--Compute Scalar(DEFINE [Expr1003]=If ( [Expr1006]=0) then NULL else
> ( [Expr1007]/[Expr1006])))
> [--Stream Aggregate(GROUP BY[Orders].(RegionID])
> DEFINE[ExprlOO6]=COUNT([Expr
lOO2]), [Expr1007]=SUM([Expr1002])))
> |--Compute Scalar(DEFINE[Expr1002]=If ([Expr1004]=0) then NULL else
> [Expr1005]))
> |--Stream Aggregate(GROUP BY[Orders].[RegionID],
> [orders].[SalesPersonID]) DEFINE[Expr1004]=Count(*),
> [Expr1005]=SUM([Orders].[OrderAmount])))
> |--Sort(ORDER BY[Orders].[RegionID] ASC,
> [Orders].[SalesPersonID] ASC))
> |--Table Scan(OBJECT[ServerA].[dbo].[Orders])
> You suspect that this query is part of the problem because the showplan
> indicates that the query is performing a
> table scan operation. What is the most likely reason that this query is
> performing a table scan?
> A. There is no composite index on OrderID, RegionlD, and OrderAmount.
> B. There is no WHERE clause in the query.?C. The query contains a
subquery.
> D. The query is performing aggregate operations.
>
>
|
|