how to reduce cost-CodePudding

select *
from inv_dist_stg(table_name)
where interface_line_id in (select interface_line_id from inv_lines_stg
                            where status = 'processed'
                              and trun(creation_date) <= (trun(sysdate)-10))

In inv_dist_stg table already has index on column interface_line_id

CodePudding user response：

in would be much slow, use a join instead. The query is like this:

select *
from inv_dist_stg a,inv_lines_stg b
where a.interface_line_id=b.interface_line_id 
and b.status = 'processed' and trun(b.creation_date) <= (trun(sysdate)-10)

create indexes on status and creation_date

and then you are good to go!!!

CodePudding user response：

Your query looks fine. It is good that your table has a index on interface_line_id. Thus you can quickly access that you find in your subquery.

Now you need an index for the subquery. That is an index that should include status and creation_date. As I don't know which is more selective, you may want to try two indexes and see which one is used by the DBMS. I suppose that trun is a typo and means trunc actually. You can now either create an index on trunc(creation_date) or change the query to use creation_date without trunc. At last you want the index to include interface_line_id which is needed for the main query.

I suggest

select *
from inv_dist_stg
where interface_line_id in 
(
  select interface_line_id
  from inv_lines_stg
  where status = 'processed'
  and creation_date < trunc(sysdate) - 9
);

and

create index idx1 on inv_dist_stg (status, creation_date, interface_line_id);
create index idx2 on inv_dist_stg (creation_date, status, interface_line_id);