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.

Both tables are optimized and the database sysmod'ed

Heres the table structures...

Name: clinic_look_up
Owner: ingres
Created: 24.09.2004 01:24:48
Location: ii_database
Type: user table
Version: II2.6
Page size: 8192
Cache priority: 0
Alter table version: 0
Alter table totwidth: 96
Row width: 96
Number of rows: 5891
Storage structure: btree with unique keys
Compression: none
Duplicate Rows: not allowed
Number of pages: 134
Overflow data pages: 0
Journaling: enabled
Base table for view: yes
Optimizer statistics: yes; see avg count below, more info in the iistats
catalog

Column Information:
Key Avg
Count
Column Name Type Length Nulls Defaults Seq Per
Value
master_clinic_code char 4 no no 1
unique
consultant_code char 4 yes null
22.8
specialty_code char 4 yes null
hospital_code char 8 yes null
282.9
clinic_description char 30 yes null
clinic_length date yes null
tot_news integer 4 yes null
tot_fups integer 4 yes null
personnel_code char 4 yes null
17.6
overbooks integer 4 yes null
clinic_type char 8 yes null

Secondary indexes:
Index Name Structure Keyed On
index1_clinic_look_up btree consultant_code
index2_clinic_look_up btree hospital_code
index3_clinic_look_up btree personnel_code
index4_clinic_lookup btree master_clinic_code


************************************************** ******************
Name: clinic_diary
Owner: ingres
Created: 24.09.2004 01:23:33
Location: ii_database
Type: user table
Version: II2.6
Page size: 8192
Cache priority: 0
Alter table version: 0
Alter table totwidth: 107
Row width: 107
Number of rows: 434603
Storage structure: btree
Compression: none
Duplicate Rows: allowed
Number of pages: 11419
Overflow data pages: 0
Journaling: enabled
Base table for view: no

Optimizer statistics: yes; see avg count below, more info in the iistats
catalog
Column Information:
Key Avg
Count
Column Name Type Length Nulls Defaults Seq Per
Value
clinic_date date no no 1
5.9
clinic_code c 4 no no 2
82.5
clinic_status c 1 yes null
86994.6
news_booked integer 2 yes null
fups_booked integer 2 yes null
allocated_by c 4 yes null
signed_off_by c 4 yes null
1286.9
clinic_location c 8 yes null
cmm_arrival c 1 yes null
cmm_consultation c 1 yes null
108743.2
cmm_discharge c 1 yes null
comments c 50 yes null
cfis_code char 3 yes null
rbslot integer 2 yes null
6396.7

Secondary indexes:
Index Name Structure Keyed On
clinic_diary_1 btree clinic_code, clinic_status
clinic_diary_2 btree clinic_date
clinic_diary_3 btree clinic_code, clinic_date
index4_clinic_diary btree clinic_code


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

Any thoughts

Gareth
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.

2.16677.info-ingres (AT) cariboulake (DOT) com...
[snip]

Type mismatch. The semantics of c(4) != char(4) != varchar(4). Make all
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

Hi Gareth,



It would be interesting to see the QEP’s plus the output from trace points
qe90 and op188.



The secondary indexes may be playing a part here and in any case you have
some basically unnecessary indexes it would seem at first glance.



index4_clinic_lookup is a duplicate of the table key.

clinic_diary_2 is the same as the first part of the table key. As the table
is BTREE any query that ONLY restricts on clinic_date WILL be able to use
the table key.

You can probably get away with just one index on clinic_code. Chances are
that any query on clinic_code is going to find all the keys on one leaf page
and all the rows on 1 or 2 data pages (on average). Unless you have some
values of clinic code that have high row counts in which case the other
indexes may reduce the number of pages in the index that need to be
traversed.



Just my $0.02



Peter Gale
PJG Computer Services Ltd
peter.gale (AT) pjgcsl (DOT) com

_____

From: info-ingres-admin (AT) cariboulake (DOT) com
[mailto:info-ingres-admin (AT) cariboulake (DOT) com] On Behalf Of Gareth Williams
Sent: 18 March 2005 12:21
To: info-ingres (AT) cariboulake (DOT) com
Subject: [Info-ingres] query help...



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.

Both tables are optimized and the database sysmod'ed

Heres the table structures...

Name: clinic_look_up
Owner: ingres
Created: 24.09.2004 01:24:48
Location: ii_database
Type: user table
Version: II2.6
Page size: 8192
Cache priority: 0
Alter table version: 0
Alter table totwidth: 96
Row width: 96
Number of rows: 5891
Storage structure: btree with unique keys
Compression: none
Duplicate Rows: not allowed
Number of pages: 134
Overflow data pages: 0
Journaling: enabled
Base table for view: yes
Optimizer statistics: yes; see avg count below, more info in the iistats
catalog

Column Information:
Key Avg
Count
Column Name Type Length Nulls Defaults Seq Per
Value
master_clinic_code char 4 no no 1
unique
consultant_code char 4 yes null
22.8
specialty_code char 4 yes null
hospital_code char 8 yes null
282.9
clinic_description char 30 yes null
clinic_length date yes null
tot_news integer 4 yes null
tot_fups integer 4 yes null
personnel_code char 4 yes null
17.6
overbooks integer 4 yes null
clinic_type char 8 yes null

Secondary indexes:
Index Name Structure Keyed On
index1_clinic_look_up btree consultant_code
index2_clinic_look_up btree hospital_code
index3_clinic_look_up btree personnel_code
index4_clinic_lookup btree master_clinic_code



************************************************** ******************
Name: clinic_diary
Owner: ingres
Created: 24.09.2004 01:23:33
Location: ii_database
Type: user table
Version: II2.6
Page size: 8192
Cache priority: 0
Alter table version: 0
Alter table totwidth: 107
Row width: 107
Number of rows: 434603
Storage structure: btree
Compression: none
Duplicate Rows: allowed
Number of pages: 11419
Overflow data pages: 0
Journaling: enabled
Base table for view: no

Optimizer statistics: yes; see avg count below, more info in the iistats
catalog
Column Information:
Key Avg
Count
Column Name Type Length Nulls Defaults Seq Per
Value
clinic_date date no no 1
5.9
clinic_code c 4 no no 2
82.5
clinic_status c 1 yes null
86994.6
news_booked integer 2 yes null
fups_booked integer 2 yes null
allocated_by c 4 yes null
signed_off_by c 4 yes null
1286.9
clinic_location c 8 yes null
cmm_arrival c 1 yes null
cmm_consultation c 1 yes null
108743.2
cmm_discharge c 1 yes null
comments c 50 yes null
cfis_code char 3 yes null
rbslot integer 2 yes null
6396.7

Secondary indexes:
Index Name Structure Keyed On
clinic_diary_1 btree clinic_code, clinic_status
clinic_diary_2 btree clinic_date
clinic_diary_3 btree clinic_code, clinic_date
index4_clinic_diary btree clinic_code



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

Any thoughts

Gareth





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 ohoni na
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 information on
Freedom of Information, please refer to the Bro Morgannwg NHS
Trust website at www.bromor-tr.wales.nhs.uk.

Of course. Silly me. Both queries will be table scanning but the first query
only has to scan a very small table.
I still don't like your indexes though.

I think what Roy is alluding to is joins of the form

where b.master_clinic_code = char(a.clinic_code)

Using a function in this way guarantees that the optimizer will not use any
secondary indexes or table keys. You will have to actually change the data
type in the table to make the joins work.



Peter Gale
PJG Computer Services Ltd
peter.gale (AT) pjgcsl (DOT) com

-----Original Message-----
From: info-ingres-admin (AT) cariboulake (DOT) com
[mailto:info-ingres-admin (AT) cariboulake (DOT) com] On Behalf Of Roy Hann
Sent: 18 March 2005 12:57
To: info-ingres (AT) cariboulake (DOT) com
Subject: Re: [Info-ingres] query help...

2.16677.info-ingres (AT) cariboulake (DOT) com...
[snip]

Type mismatch. The semantics of c(4) != char(4) != varchar(4). Make all
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