Optimizer does not use index when selecting from "unioned" view and
WHERE-clause has comparison with result of singletone subquery
----------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4976
URL: http://tracker.firebirdsql.org/browse/CORE-4976
Project: Firebird Core
Issue Type: Bug
Reporter: Pavel Zotov
Test-1.
#####
This sample will work FINE (it's shown here only for comparison with 2nd one):
create or alter view v_simple as
select 1 id from rdb$database;
recreate table thead(id int primary key using descending index thead_pk, x int);
recreate table tdata(x int, y int);
create index tdata_x on tdata(x);
create or alter view v_simple as ------------------ THIS IS _NOT_
"UNIONED"-VIEW, IT'S JUST SINGLE-TABLE PROJECTION
select * from tdata;
insert into thead(id, x) values(1, 1);
insert into tdata(x, y) select 1, rand()*10000 from rdb$types;
commit;
set statistics index thead_pk;
set statistics index tdata_x;
set planonly;
-- set echo on;
select v.*
from v_simple v
where
v.x = 1;
------------------------------------------------------------------------------------
[ 1 ]
select v.*
from v_simple v
where
v.x = (select h.x from thead h order by id desc rows 1);
--------------------- [ 2 ]
Output:
#####
// for [ 1 ]:
PLAN (V TDATA INDEX (TDATA_X))
// for [ 2 ]:
PLAN (H ORDER THEAD_PK)
PLAN (V TDATA INDEX (TDATA_X))
So, index TDATA_X is used in both cases, regardless of expression form in
WHERE-clause (i.e. search by literal or by result of single-tone subquery).
Test-2.
#####
This sample uses "UNIONED" view and optimizer will NOT use indexes of
underlying tables if WHERE clause contains comparison with subquery.
create or alter view v_unioned as select 1 id from rdb$database;
recreate table thead_u(id int primary key using descending index thead_u_pk, x
int);
recreate table tdata_1(x int, y int, z int);
recreate table tdata_2(x int, y int, z int);
create index tdata_1_x on tdata_1(x);
create index tdata_2_x on tdata_2(x);
--create index tdata_1_xy on tdata_1(x, y);
--create index tdata_2_xy on tdata_2(x, y);
create or alter view v_unioned as
select * from tdata_1
union all
select * from tdata_2;
insert into thead_u(id, x) values(1, 1);
insert into tdata_1(x, y) select 1, rand()*10000 from rdb$types;
insert into tdata_2(x, y) select 1, rand()*10000 from rdb$types;
commit;
set statistics index thead_u_pk;
set statistics index tdata_1_x;
set statistics index tdata_2_x;
set planonly;
--set echo on;
select v.*
from v_unioned v
where
v.x = 1;
-------------------------------------------------------------------------------
[ 1 ]
select v.*
from v_unioned v
where
v.x = (select h.x from thead h order by id desc rows 1); -----------------
[ 2 ]
Output:
######
// for [ 1 ] - all OK:
PLAN (V TDATA_1 INDEX (TDATA_1_X), V TDATA_2 INDEX (TDATA_2_X))
// for [ 2 ] -- get NATURAL reads:
PLAN (H ORDER THEAD_PK)
PLAN (V TDATA_1 NATURAL, V TDATA_2 NATURAL)
PS.
Tested on WI-V2.5.5.26936, WI-V3.0.0.32114
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
