RE: [Info-ingres] query help... SUMMARY

#1

Thanks for all those who replied.

As Roy suggested it's the different column types (bug 109134, problem INGSRV
2001)

I'm going to be setting all the types to ASNI char to fix the issue (there's
no reason why they were C other than who read the manual when creating the
table).

The reason for all the random style of indexing was desperation - they'llbe
promptly dropped.

Thanks again.

Gareth

-----Original Message-----
From: Roy Hann [mailto:specially (AT) processed (DOT) almost.meat]
Sent: 18 March 2005 12:57
To: info-ingres (AT) cariboulake (DOT) com
Subject: Re: [Info-ingres] query help...

Quote:

"Gareth Williams" <Gareth.Williams (AT) bromor-tr (DOT) wales.nhs.uk> wrote in
message news:mailman.111114828

2.16677.info-ingres (AT) cariboulake (DOT) com...

Quote:

II 2.6/0305 (su9.us5/00)
10626

ok... can someone explain this to me...

select *
from clinic_diary a,
clinic_look_up b
where b.master_clinic_code = a.clinic_code
and a.clinic_code = '575'

returns instantly, where as...

select *
from clinic_diary a,
clinic_look_up b
where b.master_clinic_code = a.clinic_code
and b.master_clinic_code = '575'

takes around 15 seconds.

The iotrace and QEP tells me that the second query is doing a full table
scan of table a.

[snip]

Quote:

So the question is - Why is the second query not chosing indexed fields?

Type mismatch. The semantics of c(4) != char(4) != varchar(4). Makeall
your columns of the same type (ideally char(4) in this case), and it should
be fine.

I'm kinda swamped at the moment, so maybe someone else will wade in on the
subject of function attribute joins...

Roy

_______________________________________________
Info-ingres mailing list
Info-ingres (AT) cariboulake (DOT) com
http://mailman.cariboulake.com/mailm...py/info-ingres
Cymraeg:- Mae'r neges hon yn gyfrinachol.Os nad chi yw'r derbynnydd y bwriedid y neges ar ei gyfer, byddwch mor garedig â rhoi gwybod
i'r anfonydd yn ddi-oed. Dylid ystyried un rhywd datganiadau neu sylwadau a wneir uchod yn rhai personol,ac nid o angen rhaid yn rhai o
eiddo Ymddiriedolaeth GIG Bro Morgannwg, nac unrhyw ran gyfansoddol ohonina chorff cysylltiedig.

Cofiwch fod yn ymwybodol ei bod yn
bosibl y bydd disgwyl i Ymddiriedolaeth GIG Bro Morgannwg roi cyhoeddusrwydd i gynnwys unrhyw ebost neu ohebiaeth a dderbynnir, yn
unol ag amodau'r Ddeddf Rhyddid Gwybodaeth 2000. I gael mwy o wybodaeth am Ryddid Gwybodaeth, cofiwch gyfeirio at wefan
Ymddiriedolaeth GIG Bro Morgannwg ar www.bromor-tr.wales.nhs.uk

English:- This message is confidential. If you are not the intended recipient of the message then please notify the sender immediately.
Any of the statements or comments made above should be regarded as personal and not necessarily those of Bro Morgannwg NHS Trust, any
constituent part or connected body.

Please be aware that, under the terms of the Freedom of Information Act 2000, Bro Morgannwg NHS Trust may be required to make public the
content of any emails or correspondence received. For further informationon Freedom of Information, please refer to the Bro Morgannwg NHS
Trust website at www.bromor-tr.wales.nhs.uk.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

RE: [Info-ingres] query help... SUMMARY

comp.databases.ingres comp.databases.ingres

RE: [Info-ingres] query help... SUMMARY - 03-18-2005 , 10:28 AM