Thanks, and sorry for simple questions, for all this is pretty new to me.
> >So joining tables via primary and foreign keys are slower than joining
> >multiple columns?
> <sigh> "I think it's the "corp" field in table B in the non-relational
> query that makes the difference, not the join strategy."
> The two queries are completely different. Non-relational starts with
> table B, relational starts with table A. I think the performance
> difference can be attributed to the fact that the corp column in table
> B severely restricts the number of rows returned from table B, making
> for a more speedy query. As an experiment, try adding the "corp"
> column to B in the relational query and try
> select distinct a.sin, a.corp, a.plan, a.seq, b.effect_dt,
> from e03_plan1 a, e06_dept1 b
> where a.id = b.id
> and b.corp matches 'G01682'
> and a.seq = 1;
> I still want to know why you have these in your WHERE clause:
> "and a.sin matches '*'
> and a.plan matches '*'"
> >> >I thought I might as well attach both queries and set explain output
> >> >those that might interested
> >> Well, as far as I can see, the optimiser is behaving perfectly
> >> rationally: it has obviously decided that in the non-relational
> >> version, the extra selectivity on table B makes it more sensible to
> >> drive the query from that than from table A. Then joining the result
> >> set is quicker. I think it's the "corp" field in table B in the
> >> non-relational query that makes the difference, not the join strategy.
> >> For some reason, A.corp is not as selective as B.corp.
> >> ># of rows in table a = 6010, b= 523774
> >> >example Query 1 -relational
> >> >select distinct a.sin, a.corp, a.plan, a.seq, b.effect_dt,
> >> >from e03_plan1 a, e06_dept1 b
> >> >where a.id = b.id
> >> >and a.sin matches '*'
> >> >and a.corp matches 'G01682'
> >> >and a.plan matches '*'
> >> >and a.seq = 1;
> >> >Estimated Cost: 979
> >> >Estimated # of Rows Returned: 1
> >> >1) informix.a: SEQUENTIAL SCAN
> >> > Filters: (informix.a.sin MATCHES '*' AND (informix.a.corp MATCHES
> >> >'G01682' AND (informix.a.plan MATCHES '*' AND informix.a.seq =
> >> > 1 ) ) )
> >> >2) informix.b: INDEX PATH
> >> > (1) Index Keys: id
> >> > Lower Index Filter: informix.b.id = informix.a.id
> >> >Time = real 0m9.15s
> >> > user 0m2.05s
> >> > sys 0m0.69s
> >> >Query2 -non-relational
> >> ># of rows in table b= 6010, a= 523774
> >> >select b.sin, b.corp, b.plan, b.seq, a.effect_dt, a.deposit_dt
> >> >from e06_dept a, e03_plan b
> >> >where b.sin = a.sina
> >> >nd b.corp = a.corp
> >> >and b.plan = a.plana
> >> >nd b.seq = a.seq
> >> >and b.sin matches '*'
> >> >and b.corp matches 'G01682'
> >> >and b.plan matches '*'
> >> >and b.seq = 1
> >> >Estimated Cost: 561
> >> >Estimated # of Rows Returned: 1
> >> >1) informix.b: INDEX PATH Filters: (informix.b.sin MATCHES '*' AND
> >> >(informix.b.corp MATCHES 'G01682' AND (informix.b.plan MATCHES '*' AND
> >> >informix.b.seq = 1 ) ) ) (1) Index Keys: sin corp plan seq
> >> >informix.a: INDEX PATH (1) Index Keys: sin corp plan seq deposit_dt
> >> >to_acct ref_no Lower Index Filter: (informix.a.seq =
> >> >AND (informix.a.plan = informix.b.plan AND (informix.a.corp =
> >> >informix.b.corp AND informix.a.sin = informix.b.sin ) ) )
> >> >Time = real 0m33.61s
> >> > user 0m0.28s
> >> > sys 0m0.14s
> >> >Thanks Again
> >> >Jay
> >> >> Hi All,
> >> >> I'm trying to gather statistics on queries performed against a none
> >> >> relational database and one of a relational model. When I execute a
> >> >> it seem the non-relational model performs faster, and I cannot
> >> >the
> >> >> set explain output. What is meant by Estimated cost? And why does
> >> >> faster query (non-relational) have a higher cost? What is estimated
> >> >> rows? The number of rows that are returned by both queries is 9062.
> >> >> the relation model estimates 87 and the non-relational estimates 1.
> >> >would
> >> >> appreciate any clarification on this to help me optimize the query
> >> >> better.
> >> >> Thanks
> >> >> Jay