Frank Karlstrøm (JIRA) wrote:
I consider my database to be rather small, and my query to be relatively
simple, but I think I am affected by this issue. Explanation: I have two
tables, aTable and bTable with about 15 cols on a Table and 5 cols on
bTable. bTable refers to aTable.
When you say "bTable refers to aTable", what is the nature of the referal? Do
you mean that aTable.id is a primary key, and that bTable.aTable_id is a foreign
key that refers to aTable.id?
The following query is executed against these two tables:
select a.col1, a.col2, a.col3, (select sum(b.col1)
from bTable as b where bTable.aTable_id=a.id)
where a.id=####
It's hard to say for certain what's going on here, since this isn't actually
a valid query. Aside from syntax errors and column reference errors, it's not
clear to me where "a" is declared w.r.t to the subquery. Can you perhaps post
the actual query you're running, or else a valid simplified version that shows
the issue you describe?
both table have appropiate indexes on a.id, b.id and b.aTable_id.
Can you be more specific about the indexes here? Does aTable have a single index
on "id" and bTable have two separate indexes, one on "id" and one on "aTable_id"?
For the sake of argument, let's suppose the following:
1) Query is supposed to be:
select
a.col1,
a.col2,
a.col3,
(select sum(b.col1) from bTable as b where b.aTable_id=a.id) x
from
aTable a
where a.id=####
2) We have a primary key on aTable.id and a foreign key (aTable_id) in bTable
that refers to aTable.id.
3) We have an index on aTable.id (which is a duplicate index, because of the
primary key), an index on bTable.aTable_id (which is also a duplicate), and an
index on bTable.id.
With all of those assumptions, I did the following:
create table aTable (col1 int, col2 int, col3 int, id int primary key not null);
create table bTable (col1 int, col2 int, col3 int, id int, aTable_id int
references aTable(id));
insert into aTable values (1, 1, 1, 1);
insert into aTable values (2, 2, 2, 2);
insert into aTable values (4, 4, 4, 4);
insert into btable values (1, 1, 1, 1, 1);
insert into btable values (2, 2, 2, 2, 2);
select
a.col1,
a.col2,
a.col3,
(select sum(b.col1) from bTable as b where b.aTable_id=a.id) x
from
aTable a
where a.id=2
;
The log query plan for this query shows an index scan on both ATABLE and
BTABLE--i.e. the table scan that you mentioned isn't occuring. So apparently
one or more of the above assumptions is incorrect...?
Other suggestions are welcome. :)
The more info you can provide, the better. If you can give a repro script,
including the DDL and the query, that's the best way for people to see what's
going on. Otherwise, I myself am not quite clear on what is actually happening
with the scenario you describe, so it's hard to offer any suggestions...
Army
