Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Wed Dec 03, 2008 1:59 pm Post subject: Simple query with FETCH FIRST x ROWS ONLY
I have a query I'm running Visual Explain on. The access path is all over the place. When I change the value in the Fetch clause it will use the index and other times it will do a tablespace scan. The table contans 11 million rows and there is an index on ELIG_PERIOD. Can anyone help me under stand this?
Code:
SELECT * FROM FDBEL.ELIG_MTHDTL
where elig_period = '2008-11-01'
fetch first 8000 rows only
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: Chicago
Posted: Wed Dec 03, 2008 2:41 pm Post subject:
The Optimizer will drive you crazy. It's all based on filter factors, cardinality, cluster ratios, etc, and existing stats. First things first. Are the stats current? The Optimizer will do all kinds of strange things when the stats are not current. Also, I'm guessing that the index with ELIG_PERIOD is not the clustering index. Is there a high cardinality on this index? With 11 million rows, you probably have 4 levels in the index. If your FETCH FIRST x ROWS clause has a small number, and the cardinality of the index is high, and the rows are relatively small, I can see where DB2 might choose to run a TS scan thinking that the I/O will be less than traversing an index. Think about this. You have a minimum of 4 physical I/O's to get to the bottom level of the index. Once you're there, you potentially could have one more physical I/O for each row retrieved because there's no guarantee that the rows you're trying to retrieve are on the same data page even though they're next to each other in the index. So let's assume you want to fetch 25 rows. DB2 might calculate that he'd need 29 physical I/O's to get those 25 rows. Now, trying to get into the Optimizer's head, I'm thinking that DB2 will conclude that he'd find the 25 rows using direct data page reads starting at the beginning of a table long before issuing 29 physical I/O's. Factor in a little prefetch and you could potentially look at thousands more rows in a TS scan when compared to an index lookup using the same number of physical I/O's. Not all the time, but more often than not, the Optimizer makes good decisions.
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Wed Dec 03, 2008 4:59 pm Post subject:
jsharon1248, Thanks for your explanation. The table had stats run 2 days ago, it's in good shape. I'm still testing with differnt results. The index has 63 unque(sp) values, this may be why DB2 goes to the TS.
Thanks _________________ Thanks,
NASCAR9
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
