THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang

Oracle Index Skip Scan

There is a feature, called index skip scan that has been in Oracle since version 9i. When I across this, it seemed like a very clever trick, but not a critical capability. More recently, I have been advocating DW on SSD in approrpiate situations, and I am thinking this is now a valuable feature in keeping the number of nonclustered indexes to a minimum.

Briefly, suppose we have an index with key columns: Col1, Col2, in that order. Obviously, a query with a search argument (SARG) on Col1 can use this index, assuming the data distribution is favorable. However, a query with the SARG on Col2 but not Col1 cannot use the index in a seek operation.

Now suppose that the cardinality of Col1, (the number of distinct values of Col1), is relatively low. The database engine could seek each distinct first value of Col1 and the specified SARG on Col2. Microsoft SQL Server currently does not have the Oracle Index Skip-Scan feature, but the capability can be achieved with a work-around.

In this example, the LINEITEM table has a cluster key on columns L_SHIPDATE, L_ORDERKEY, but does not have an index leading with L_ORDERKEY. Our query is to find a specific Order Key in the LineItem table. If there is a table with the distinct date values, DimDate, we could force a loop join from the DimDate table to LineItem (even though only columns from LineItem are required) to get the execution plan below.

SkipScan

The question is now: how effective is this technique? The most efficient execution plan is of course, to have an index leading with the Order Key column. But the situation calls for keeping the number of nonclustered indexes to an absolute minimum. So how does the above execution plan compare with a table scan?

A table scan, in this type of query, such that only few rows meet an easy to evaluare SARG, might run at about 1GB/s per core. Note this is far higher than the 200MB/sec cited in the Microsoft Fast Track Data Warehouse documents. This is because the FTDW baseline is a table scan that aggregates several columns of every row. And not only that, a Hash Match is also required to group the results. Basically, a needle in haystack table scan runs much faster than the more complex aggregate and group scan. At most, a 1GB table scan might acceptable for a non-parallel execution plan and even a 50GB table scan could be tolerable on a powerful 32-core system with an unrestricted parallel execution plan.

A loop join can run somewhere in range of 100,000-200,000 seeks/sec to the inner source. Realistically, a Data Warehouse with 10 years data has distinct 3652 days (depending on the leap year situation). A loop join with 3650 rows from the outer source should run some where around 36ms. Even if the DW had 20 years data, this is still acceptable, on the assumption that the non-lead column Order Key search is in the minority, with the plus being one less index on the big table is required. If the query could be bounded to with a single year or quarter-year, then we are approaching the efficiency of having the extra nonclustered index.

Published Monday, June 13, 2011 12:55 AM by jchang
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

robnoi said:

Regarding the discussion about the "skip scan", there is now a Microsoft Connect item: https://connect.microsoft.com/SQLServer/feedback/details/695044/implement-index-skip-scan

Please vote for it.

October 15, 2011 10:32 AM
 

jchang said:

Well the connect request could have included a link to my blog. I am sure Conor loves my suggestions and will promptly put all my requests to the top of his (recycle) bin

October 18, 2011 11:39 PM
 

tobi said:

I just added a link to this post from the connect item (I opened the connect item originally). Please vote on it.

June 7, 2012 4:46 PM
 

Daniel Adeniji said:

Thanks.  Your insight into Storage Performance is groundbreaking & breathtaking.

I like your frustration about certain things.  And, how you take the minimal, engineering approach to problem isolation and solving.

And, turn around and ask each of us, have you thought about this problem, as well.  And, how did you solve it -- By adding another index?

March 23, 2013 12:16 PM
 

New Yeezys V2 said:

yeezy v 2の販売,환상 판매,La vendita Yeezy V2,Yeezy V2 de vente,En el mercado yeezy V2.

http://www.newyeezyv2.org/ Yeezys For Sale

http://www.newyeezyv2.org/ Yeezys Boost 350 V2

http://www.newyeezyv2.org/ Yeezys Boost 350

http://www.newyeezyv2.org/ Yeezys Adidas

http://www.newyeezyv2.org/ Yeezys

http://www.newyeezyv2.org/ Yeezy Zebra

http://www.newyeezyv2.org/ Yeezy Yellow

http://www.newyeezyv2.org/ Yeezy V2 Boost

http://www.newyeezyv2.org/ Yeezy V2

http://www.newyeezyv2.org/ Yeezy Pirate Black

http://www.newyeezyv2.org/ Yeezy Outlet

http://www.newyeezyv2.org/ Yeezy For Sale

http://www.newyeezyv2.org/ Yeezy Cream

http://www.newyeezyv2.org/ Yeezy Boost V2

http://www.newyeezyv2.org/ Yeezy Boost 750

http://www.newyeezyv2.org/ Yeezy Boost 700

http://www.newyeezyv2.org/ Yeezy Boost 350 V2 Zebra

http://www.newyeezyv2.org/ Yeezy Boost 350 V2 Outlet

http://www.newyeezyv2.org/ Yeezy Boost 350 V2 Kaufen

http://www.newyeezyv2.org/ Yeezy Boost 350 V2 Footlocker

http://www.newyeezyv2.org/ Yeezy Boost 350 V2 Blue Tint

http://www.newyeezyv2.org/ Yeezy Boost 350 V2 Beluga

http://www.newyeezyv2.org/ Yeezy Boost 350 V2

http://www.newyeezyv2.org/ Yeezy Blue Tint

http://www.newyeezyv2.org/ Yeezy Blue

http://www.newyeezyv2.org/ Yeezy Beluga 2.0

http://www.newyeezyv2.org/ Yeezy Beluga

http://www.newyeezyv2.org/ Yeezy 750

http://www.newyeezyv2.org/ Yeezy 700

http://www.newyeezyv2.org/ Yeezy 500

http://www.newyeezyv2.org/ Yeezy 350 V2

http://www.newyeezyv2.org/ Yeezy 2018

http://www.newyeezyv2.org/ Yeezy

http://www.newyeezyv2.org/ White Yeezys

http://www.newyeezyv2.org/ White Yeezy

http://www.newyeezyv2.org/ Where To Buy Yeezys

http://www.newyeezyv2.org/ Supreme Yeezy

http://www.newyeezyv2.org/ Real Yeezys

http://www.newyeezyv2.org/ New Yeezys

http://www.newyeezyv2.org/ New Yeezy V2

http://www.newyeezyv2.org/ New Yeezy

http://www.newyeezyv2.org/ Kanye West Yeezy

http://www.newyeezyv2.org/ Kanye West Sneakers

http://www.newyeezyv2.org/ Kanye West Shoes

http://www.newyeezyv2.org/ Kanye West

http://www.newyeezyv2.org/ Kanye

http://www.newyeezyv2.org/ Cheap Yeezy

http://www.newyeezyv2.org/ Blue Tint Yeezy

http://www.newyeezyv2.org/ Black Yeezys

http://www.newyeezyv2.org/ Black Yeezy

http://www.newyeezyv2.org/ Beluga Yeezy

http://www.newyeezyv2.org/ Adidas Yeezys

http://www.newyeezyv2.org/ Adidas Yeezy V2

http://www.newyeezyv2.org/ Adidas Yeezy Shoes

http://www.newyeezyv2.org/ Adidas Yeezy Boost 350 V2 Zebra

http://www.newyeezyv2.org/ Adidas Yeezy Boost 350 V2

http://www.newyeezyv2.org/ Adidas Yeezy Boost 350

http://www.newyeezyv2.org/ Adidas Yeezy

http://www.newyeezyv2.org/ Adidas V2

http://www.yeezyv2sales.org/ Adidas Yeezys

http://www.yeezyv2sales.org/ Adidas Yeezy V2

http://www.yeezyv2sales.org/ Adidas Yeezy 350

http://www.yeezyv2sales.org/ Adidas Yeezy

http://www.yeezyv2sales.org/ Adidas V2

http://www.yeezyv2sales.org/ Adidas UltraBoost

http://www.yeezyv2sales.org/ Adidas Ultra Boost Men

http://www.yeezyv2sales.org/ Adidas Ultra Boost

http://www.yeezyv2.top/ Yeezy V2

http://www.yeezyv2.top/ Where To Buy Yeezys

http://www.yeezyv2.top/ Ultra Boost

http://www.yeezyv2.top/ Supreme Yeezy

http://www.yeezyv2.top/ Nmd Adidas

http://www.yeezysv2.co.uk/ Yeezys Boost 350 V2

http://www.yeezysv2.co.uk/ Yeezys

http://www.yeezysv2.co.uk/ Yeezy'S

http://www.yeezysv2.co.uk/ Yeezy Outlet

http://www.yeezysv2.co.uk/ Beluga Yeezy

http://www.yeezysv2.co.uk/ Adidas Yeezys

http://www.yeezysv2.co.uk/ Adidas Yeezy V2

http://www.yeezyshoes.top/ Yeezy Shoes

http://www.yeezyshoes.top/ Adidas Yeezys

http://www.yeezyshoes.top/ Adidas Yeezy V2

http://www.yeezyshoes.top/ Adidas Yeezy Shoes

http://www.yeezyshoes.top/ Adidas Yeezy Boost 350 V2 Zebra

http://www.yeezyshoes.top/ Adidas Yeezy Boost 350 V2

http://www.yeezyshoes.top/ Adidas Yeezy Boost 350

http://www.yeezyshoes.top/ Adidas Yeezy Boost

http://www.yeezyboost350v2outlet.com/ Yeezy Boost V2

http://www.yeezyboost350v2outlet.com/ Yeezy Boost 750

http://www.yeezyboost350v2outlet.com/ Yeezy Boost 700

http://www.yeezyboost350v2outlet.com/ Yeezy Boost 350 V2 Zebra

http://www.yeezyboost350v2outlet.com/ Yeezy Boost 350 V2 Outlet

http://www.yeezyboost350v2outlet.com/ Yeezy Boost 350 V2 Kaufen

http://www.yeezyboost350v2outlet.com/ Yeezy Boost 350 V2 Footlocker

http://www.yeezyboost350v2outlet.com/ Yeezy Boost 350 V2 Blue Tint

http://www.yeezyboost350v2outlet.com/ Yeezy Boost 350 V2 Beluga

http://www.yeezyboost350v2outlet.com/ Yeezy Boost 350 V2

http://www.yeezyboost350v2outlet.com/ Yeezy Boost 350 For Sale

http://www.yeezyboost350v2outlet.com/ Yeezy Boost 350 Foot Locker

http://www.yeezyboost350v2outlet.com/ Yeezy Boost 350

http://www.yeezyboost350v2outlet.com/ Yeezy Boost

http://www.yeezy500.org/ Yellow Yeezy

http://www.yeezy500.org/ Yeezysupply

http://www.yeezy500.org/ Yeezys For Sale

http://www.yeezy500.org/ Yeezys

http://www.yeezy500.org/ Yeezy'S

http://www.yeezy500.org/ Yeezys

http://www.yeezy500.org/ Yeezy Zebra

http://www.yeezy500.org/ Yeezy Yellow

http://www.yeezy500.org/ Yeezy 750 Boost

http://www.yeezy500.org/ Yeezy 750

http://www.yeezy500.org/ Yeezy 700

http://www.yeezy500.org/ Yeezy 500

http://www.yeezy500.org/ Yeezy 350 V2

http://www.newyeezyv2.com/ New Yeezys

http://www.newyeezyv2.com/ New Yeezy V2

http://www.newyeezyv2.com/ New Yeezy

http://www.newyeezyv2.com/ Adidas Yeezys

http://www.newyeezyv2.com/ Adidas Yeezy V2

http://www.newyeezyv2.com/ Adidas Yeezy Shoes

http://www.newyeezyv2.com/ Adidas Yeezy Boost 350 V2 Zebra

http://www.newyeezyv2.com/ Adidas Yeezy Boost 350 V2

http://www.newyeezyv2.com/ Adidas Yeezy Boost 350

http://www.newyeezyv2.com/ Adidas Yeezy Boost

http://www.newyeezyv2.com/ Adidas Yeezy Blue Tint

http://www.newyeezyv2.com/ Adidas Yeezy 350

http://www.newyeezyv2.com/ Adidas Yeezy

http://www.newyeezyv2.com/ Adidas V2

http://www.ukmonclercoat.co.uk/ Uk Moncler Coat

http://www.ukmonclercoat.co.uk/ Uk Moncler

http://www.monclerukonline.co.uk/ Moncler Uk Online

http://www.monclerukonline.co.uk/ Moncler Uk

http://www.moncler-ukoutlet.co.uk/ Moncler Uk Outlet

http://www.moncler-ukoutlet.co.uk/ Moncler Uk

http://www.monclerjacketoutlets.co.uk/ Moncler Jacket Outlets

http://www.monclerjacketoutlets.co.uk/ Moncler Jacket

http://www.monclerukjackets.co.uk/ Moncler Uk Jackets

http://www.monclerukjackets.co.uk/ Moncler Jackets

http://www.canadagooseuk.org/ Goose Down Jackets

http://www.canadagooseuk.org/ Canada Goose Uk

http://www.usnfljerseys.us/ Nfl Jerseys

http://www.usnfljerseys.us/ Nfl Jersey Cheap

http://www.usnfljerseys.us/ Nfl Jersey

http://www.usnfljerseys.org/ Nfl Jersey Cheap

http://www.usnfljerseys.org/ Nfl Jersey

http://www.usnfljerseys.org/ Nfl Cheap Jerseys

http://www.uscheapnfljersey.com/ Cheap Nfl Jerseys

http://www.uscheapnfljersey.com/ Cheap Nfl Jersey

http://www.nfljerseyscheapsales.com/ Nhl Jerseys 2018

http://www.nfljerseyscheapsales.com/ Nfl Jerseys For Sale

http://www.nfljerseyscheapsales.com/ Nfl Jerseys Cheap

http://www.cheapnba.top/ Nba Jerseys Cheap

http://www.cheapnba.top/ Nba Jerseys 2018

http://www.cheapnba.top/ Nba Jerseys

http://www.uscheapjerseys.us.com/ Cheap Jerseys

http://www.uscheapjerseys.us.com/ Cheap Jersey 2018 Us

http://www.uscheapjerseys.us.com/ Cheap China Jerseys

http://www.air-max2018.com/ Air Max 2018

http://www.air-max2018.com/ 2018 Nike Air Max

http://www.air-max2018.com/ 2018 Air Max

http://www.nikeairvapormax.top/ Nike Vapormax

http://www.nikeairvapormax.top/ Nike Air Vapor Max

http://www.vapor-max.com/ Vapormax

http://www.vapor-max.com/ Vapor Max Nike

http://www.vapor-max.com/ Vapor Max

http://www.vapormax2018.org Vapormax 2018

http://www.vapormax2018.org Vapormax

http://www.vapormax2018.org Vapor Max Nike

http://www.vapormax2018.org Vapor Max

http://russia2018jersers.com/ 2018

http://russia2018jersers.com/ Worldcup 2018

http://russia2018jersers.com/ World Cup Soccer 2018

http://russia2018jersers.com/ World Cup Russia 2018

http://www.footballjerseycheap.com/ World Cup Russia

http://www.footballjerseycheap.com/ World Cup Football 2018

http://www.footballjerseycheap.com/ World Cup Football

http://www.footballjerseycheap.com/ World Cup 2018

http://www.warriors-gs.com/ Warriors Jersey

http://www.warriors-gs.com/ Nba Warriors

http://www.warriors-gs.com/ Golden State Warriors

http://www.michael-kors-bags.us/ Michael Kors Factory Sale

http://www.michael-kors-bags.us/ Michael Kors bags

http://www.michael-kors-bags.us/ Michael Kors

http://www.jordan-air.us/ Jordan Air

http://www.jordan-air.us/ Cheap Jordan Shoes

http://www.jordan-air.us/ Air Jordan Shoes

http://www.jordan-air.us/ Air Jordan

http://www.asics.us.com/ Asics Shoes

http://www.asics.us.com/ Asics

http://www.clearancefitflop.com/ Cheap Fitflop

http://www.clearancefitflop.com/ Fitflop Clearance

http://www.clearancefitflop.com/ Fitflop Sale Clearance

http://www.clearancefitflop.com/ Discount Fitflop

http://www.clearancefitflop.com/ Fitflop Outlet

http://www.clearancefitflop.com/ Fitflop Clearance Outlet

http://www.clearancefitflop.com/ Clearance Fitflop

http://www.pandorajewellerywholesale.co.uk/ Cheap Pandora Charms

http://www.pandorajewellerywholesale.co.uk/ Pandora Charms Sale Clearance

http://www.pandorajewellerywholesale.co.uk/ Pandora Charms

http://www.pandorajewellerywholesale.co.uk/ Wholesale Pandora Jewellery

http://www.pandorajewellerywholesale.co.uk/ Pandora Wholesale

http://www.pandorajewellerywholesale.co.uk/ Pandora Jewellery Wholesale

http://www.pandorajewellerywholesale.co.uk/ Pandora Jewelry

http://www.pandorajewellerywholesale.co.uk/ Pandora Outlet

http://www.pandorajewellerywholesale.co.uk/ Pandora Charms Outlet

http://www.pandorajewellerywholesale.co.uk/ Pandora Rings

http://www.pandorajewellerywholesale.co.uk/ Pandora Bracelets

http://www.canadagoosejacketuk.co.uk/ Canada Goose

http://www.canadagoosejacketuk.co.uk/ Canada Goose UK

http://www.canadagoosejacketuk.co.uk/ Down Jackets For Women

http://www.canadagoosejacketuk.co.uk/ Canada Goose UK Jacket

http://www.canadagoosejacketuk.co.uk/ Canada Goose Jacket UK

http://www.canadagoosejacketuk.co.uk/ Canada Goose Jacket

July 2, 2018 3:02 PM

Leave a Comment

(required) 
(required) 
Submit

About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog

Syndication

Privacy Statement