Re: Here is a weird one, Not using fragmentation scheme to do fragmentation elmination

In article <3756da41@hwilkins.harrahs.com>, "Carlos Bolden" <cbolden@harrahs.com> wrote: > I have a table that looks like this > > create table mytesting > ( > fnmae char(30), > lastname char (30), > i_guest_id int > ) fragment by expression > (mod(i_guest_id , 14 ) = 0 ) in dbspace13, > (mod(i_guest_id , 14 ) = 1 ) in dbspace15, > (mod(i_guest_id , 14 ) = 2 ) in dbspace17, > (mod(i_guest_id , 14 ) = 3 ) in dbspace19, > (mod(i_guest_id , 14 ) = 4 ) in dbspace21, > (mod(i_guest_id , 14 ) = 5 ) in dbspace23, > (mod(i_guest_id , 14 ) = 6 ) in dbspace25, > (mod(i_guest_id , 14 ) = 7 ) in dbspace27, > (mod(i_guest_id , 14 ) = 8 ) in dbspace30, > (mod(i_guest_id , 14 ) = 9 ) in dbspace03, > (mod(i_guest_id , 14 ) = 10 ) in dbspace05, > (mod(i_guest_id , 14 ) = 11 ) in dbspace07, > (mod(i_guest_id , 14 ) = 12 ) in dbspace09, > (mod(i_guest_id , 14 ) = 13 ) in dbspace11 > lock mode row;> > This table has about 100 rows in it. Ran an update statistics high on the > table and ran the following query with explain set > select * from mytesting where mod(i_guest_id , 14 ) = 1. Sqexplain [... snip ...] The server is not prepared to evaluate expressions more complex than column equality or range expressions (See "Distribution Schemes for Fragment Elimination" in the Performance Guide). I don't think it will make an attempt to match the where clause in the select to one of the fragmentation rules, ever. Thus it will only work with selects like select * from mytesting where i_guest_id = 15 or select * from mytesting where i_guest_id in (1,15,29) Apart from this, why would you want to select a set of rows (as opposed to a specific row) from a table based on a hashing expression? Cheers Gabor -- Gabor Heppes IBM Global Services gaborh@au1.ibm.com Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.