March 18, 2011 at 1:34 pm
i have the following DW query,
65 mil row fact table
1 mil dimension
selectivity of query is about 1.2 million
fact table is indexed on policyseqid
SELECT BUSINESS,SUM(ANNUALPREMIUM)
FROM DWH.FACTPOLICYTRANS FT
JOIN DWH.DIMPOLICY DP ON fT.POLICYSEQID = DP.POLICYSEQID
where
exists (select * from #TEMP T where FT.POLICYSEQID = T.POLICYSEQID )
GROUP BY BUSINESS
with a bitmap query execution plan about 1 min 23 sec
i added an index on policyseqid and includes annualpremium
clear cache
bitmap goes away, query runs in 13 seconds
i'm not following how the bitmap is 10 times slower
I come from a db2 background and bitmap indexes (EVI) was almost always what you wanted to see being used.
am i missing something?
March 18, 2011 at 1:44 pm
I'd need to see the DDL and the execution plans to really tell you what was happening here, but I don't see any bitwise operators occuring there, so you're just doing an int comparison, if you've got masking fields.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply