Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sanjnep
Posting Yak Master
191 Posts |
 Posted - 2006-09-29 : 15:19:09
|
I have following query to delete the data from fact history table based on fact table. logid, level and post_date uniquely identify the rows on both fact and history table. I want to create indexes on the joined columns.I tried with clustered index (logid, level and post_date) it gives clustered index scan. I also tried with non clustered indexes on each column (logid, level and post_date) but still getting table scan. Do you have any suggestion on which columns should I create proper indexes to avoid table or index scan? There are about 6 million rows on each table.DELETE xbar_fact_historyFROM xbar_fact_history AS a INNER JOIN xbar_fact AS b ON a.logid = b.logid AND a.level = b.level AND a.post_date = b.post_date AND a.check_CheckSum <> BINARY_CHECKSUM(b.out_mins,b.nor_hrs,b.pdi_call) ______________________________________________________________Thanks Sanjeev |
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-09-29 : 16:31:56
|
| What happens when you try a clusted index on (logid, level, post_date, check_CheckSum)? |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
sanjnep
Posting Yak Master
191 Posts |
Posted - 2006-09-29 : 17:06:47
|
| What happens when you try a clusted index on (logid, level, post_date, check_CheckSum)?-- I just tried it but it also gives the same index scan Thanks |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-29 : 17:18:55
|
You could try creating a view that includes the binary_checksum value calculation and index the view. Then run the delete by joining the table to the view.Ignore this, it's unlikely to help in this case. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-29 : 17:50:48
|
Why do you need this code:AND a.check_CheckSum <>BINARY_CHECKSUM(b.out_mins,b.nor_hrs,b.pdi_call) Why not use this instead?and (a.out_mins <> b.out_mins ora.nor_hrs <> b.nor_hrs ora.pdi_call <> a.pdi_call) The BINARY_CHECKSUM can have problems anyway, because it is not guaranteed that different values will not produce the same checksum.The ordering of the table may be an issue. If the order is such that it has to delete at least one row from each page, then a table scan will be faster than any other method, because it has to access every page anyway.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Index help to avoid table scan