Re: Another SQL question

On 26-Aug-2014 06:58 -0500, Jim Oberholtzer wrote:

On 25-Aug-2014 17:45 -0500, CRPence wrote:

<<SNIP>>
select YEAR(OrderDate) as YR
, MONTH(OrderDate) as MO
, SUM(SubTotal) as MOSUBTOT
, SUM(DscTotal) as MODSCTOT /* additional summary */
from Sales.SalesOrderHeader as SOH
group by YEAR(OrderDate) /* note: grouping unchanged */
, MONTH(OrderDate)
order by 1, 2
<<SNIP>>

<<SNIP>> I believe all of the SQL presented would force a table scan
<<SNIP>>

Each being an aggregate query against all data [no WHERE clause], all of the data will [necessarily] be read, thus almost surely via the arrival access path for the implementation. Irrespective the naming likely being /full table scan/ generically, that particular full-table read is performed without any actual /scan/ being performed to reduce the number of records selected [again, no WHERE clause], instead having aggregation effect a reduction to the number of rows returned. I suspect the only drastic improvement over that implementation, would come from having an EVI with aggregate support to have all of the [essentially pre-calculated] SUMs included [per having specified multiple INCLUDE values for the specific SUM()]. Perhaps the following [untested] CREATE INDEX?:

create encoded vector index Sales.IxSOHdr
ON Sales.SalesOrderHeader
( YEAR(OrderDate) as OrderDateYear for OrdYr ASC
, MONTH(OrderDate) as OrderDateMonth for OrdMo ASC
)
INCLUDE( SUM(SubTotal)
, SUM(DscTotal)
)

Derived key access paths, whether created by explicit CREATE INDEX requests or as Maintained Temporary Indexes (MTI), should enable the grouping queries [grouped by the date expressions] to operate more efficiently if either there were some selection added on those date derivations or the amount of data was large and optimization was either *FIRSTIO or _FOR few ROWS_.