Hello out there! I've been banging my head against this problem all day and decided to ask for help.
In my query, I need to filter out the "newest" rows of a table and join that result to other tables. I use the analytic row_number() to achieve this. This query is saved as a view to be used throughout my application.
Database version (before I forget):
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
The issue is that the runtime predicates (end-user parameters), which are searched against the same columns present in the row_number() windowing clause, do not fully get applied to the innermost query, resulting in poor performance. Here's a test case so you can reproduce:
drop table test_inner_table;
drop table test_ids;
create table test_inner_table (
dt date,
some_id number,
more_data number);
create table test_ids (
id number,
login varchar2(10));
begin
-- Seed data
for i in 1..20000 loop
insert into test_ids (id, login) values(i, 'TEST' || mod(i, 100));
end loop;
-- Generate data, cartesian product.
insert
into test_inner_table
(dt,
some_id,
more_data)
select dt,
id,
to_number(to_char(dt, 'yyyymmdd'))
from (select trunc(sysdate)-rownum dt
from dual
connect by rownum < 20),
test_ids;
-- ~4million records in this table after this query finishes.
insert
into test_inner_table
select dt,
some_id,
more_data+rn
from test_inner_table,
(select rownum rn
from dual
connect by rownum <= 10);
commit;
end;
/
create index test_inner_table_idx on test_inner_table(dt, some_id);
create index test_ids_login_idx on test_ids(login);
select /*+ qb_name(outer)*/
n.*
from (select /*+ qb_name (inner)*/
test_inner_table.*,
row_number() over (partition by dt, some_id order by more_data desc) rn
from test_inner_table
-- where some_id in (select id from test_ids where login = 'TEST2')
-- and dt between to_date(:DATE_FROM) and to_date(:DATE_THROUGH)
) nhere 1 = n. some_id in (select id from test_ids where login = 'TEST2')
and dt between to_date(:DATE_FROM) and to_date(:DAE_THROUGH)
Things work out well if I put my criteria against the innermost query. Hopefully, that is not my only option, since I would like to bundle this up in a view (the real query is much more gnarly of course).
select /*+ qb_name(outer)*/
n.*
from (select /*+ qb_name (inner)*/
test_inner_table.*,
row_number() over (partition by dt, some_id order by more_data desc) rn
from test_inner_table
where some_id in (select id from test_ids where login = 'TEST2')
and dt between to_date(:DATE_FROM) and to_date(:DATE_THROUGH)
) n
where 1 = n.rn;
Actually, the plan shows only the dt column of the index being used, but in testing my real query it uses both dt and some_id (in other words, don't worry about that part). Screenshot of plan: http://i.imgur.com/s9OUPdl.png
When I move the criteria outside of the main query, no matter what, I can get the dt to hit the index on the inner query but can't get the "test_ids" subquery in there.
select /*+ qb_name(outer)*/
n.*
from (select /*+ qb_name (inner)*/
test_inner_table.*,
row_number() over (partition by dt, some_id order by more_data desc) rn
from test_inner_table
) n
where 1 = n.rn
and some_id in (select id from test_ids where login = 'TEST2')
and dt between to_date(:DATE_FROM) and to_date(:DATE_THROUGH);
Here's the plan for that one. Notice the index on test_inner_table.dt gets used, but the additional column (composite index) on test_inner_table.some_id does not.
http://i.imgur.com/n5G8Cfj.png
I appreciate any help you can give!