You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
The text was updated successfully, but these errors were encountered:
Submitted by: @pavel-zotov
Is related to CORE4049
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
The text was updated successfully, but these errors were encountered: