Huge inefficiency of using conditional rather than simple or conditional
My request is structured like this:
select
distinct items,
invoice
from
table_name
where
location = 'warehouse'
and invoice in
(select
t.invoice
from
list_name t
where
t.invoice_date > to_date (('2015-04-18'),'yyyy-mm-dd')
)
The subquery is supposed to give me a list of invoices, which are then returned to the main query to find all the items on the invoice. The number of invoices is not static, so I would like to nest a subquery.
The problem with this request may take several hours, not:
select
distinct items,
invoice
from
table_name
where
location = 'warehouse'
and t.invoice = 'invoice1'
or t.invoice = 'invoice2'
which takes about 0.3 seconds. I tried to limit my state to two values here:
select
distinct items,
invoice
from
table_name
where
location = 'warehouse'
and invoice in ('invoice1','invoice2')
This request still takes about 4 minutes. Any ideas why it's taking so long? I've used condition before it was never this slow, but I can't figure out why this particular implementation isn't working.
edit * Here's an outline of the explanation. Which is the same for the second and third blocks of code after fixing the problem mentioned in @hines.
Description object cost
select statement, goal=all_rows 422542
hash unique 422542
view index$_join$_001 422541
hash join
index range scan PKHIR_IX19 31382
index fast full scan PKHIR_IX16 351172
source to share
The criteria you use differ between the second and third blocks of code. If you are trying to replicate an IN code, you will need to copy around the 'or' operator. Currently, the 'or' operator returns any string with t.invoice = 'invoice2', even if the location is not "repository".
select
distinct items,
invoice
from
table_name
where
location = 'warehouse'
and (invoice = 'invoice1'
or invoice = 'invoice2')
source to share
I've had a similar case sometime, and restoring a query to something like this has worked wonders:
select distinct items, invoice
from table_name a
where location = 'warehouse'
and exists (
select 1
from list_name t
where t.invoice_date > to_date (('2015-04-18'),'yyyy-mm-dd')
and t.invoice = a.invoice )
source to share