Re: I don't get this
ok, Art, that's starting to make sense now. I ran the command, here are the
outputs for state column and company column.
Distribution for root.m4566prm_rec.pol_state
Constructed on 01/29/1999
High Mode, 0.500000 Resolution
--- DISTRIBUTION ---
( AL )
--- OVERFLOW ---
1: ( 407467, AL )
2: ( 376391, AR )
3: ( 264906, AZ )
4: ( 860538, CA )
5: ( 183370, CO )
6: ( 276582, CT )
7: ( 645646, FL )
8: ( 255939, GA )
9: ( 55900, IA )
10: ( 89562, ID )
11: ( 132936, IL )
12: ( 405041, IN )
13: ( 280021, KY )
14: ( 254933, LA )
15: ( 177574, MD )
16: ( 141522, MN )
17: ( 214648, MO )
18: ( 637396, MS )
19: ( 25160, MT )
20: ( 136150, NE )
21: ( 128780, NM )
22: ( 120238, NY )
23: ( 168444, OH )
24: ( 183911, OK )
25: ( 226429, OR )
26: ( 567505, PA )
27: ( 137393, SD )
28: ( 343755, TN )
29: ( 439835, TX )
30: ( 259879, UT )
31: ( 255299, VA )
32: ( 258137, WA )
33: ( 296794, WI )
34: ( 147682, WV )
Distribution for root.m4566prm_rec.company
Constructed on 01/29/1999
High Mode, 0.500000 Resolution
--- DISTRIBUTION ---
( 02 )
--- OVERFLOW ---
1: ( 87522, 02 )
2: ( 879637, 04 )
3: ( 8599032, 05 )
4: ( 1959850, 06 )
5: ( 804612, 09 )
6: ( 410064, 11 )
Does this make sense to you?
thanks again.
yan
Art S. Kagel wrote:
> Yan Zhu wrote:
>
> > ok, the more test I do, the more confused I am about this.
>
> > simple table with one composite key:
>
> > create index index0 on m4566prm_rec (pol_state,company,acct_date);>
> > an update satistics is done like this:
> > update statistics high for table m4566prm_rec
> > (pol_state,company,acct_date);>
> > query and explain as following:
>
> > QUERY:
> > ------
> > select *
> > from M4566PRM_REC p
> > where (p.pol_state = 'NY')>
> > Estimated Cost: 3340
> > Estimated # of Rows Returned: 57014
>
> > 1) root.p: SEQUENTIAL SCAN
>
> > Filters: root.p.pol_state = 'NY'
>
> > Why was this a sequential scan? I read books and manuals both claims
> > that you could use the first parameter of a composte index as query and
> > still will use the index?
>
> > i am using INFORMIX-OnLine Version 7.23.UC2 on solaris 2.6 ultra 450.
> > thanks a lot.
>
> Same answer as your last two questions, Yan. The data distribution
> tells the engine that a sequential scan is the FASTEST way to get you
> the data you want. Post the output of:
>
> dbschema -d yourdatabase -hd m4566prm_rec>
> This will print out the data distributions for the table and you/we
> will be able to see why the optimizer feels that a sequential scan is
> a good thing in this case. If you do not see the reason yourself then
> feel free to post the output for the column pl_state (there is usually
> too much output to post it all) and we'll take a look.
>
> Art S. Kagel