performance - how to use 50 thousand Ids at where or join clause in oracle pl/sql for a select query?

Question

I have a list of 50 thousand receipt Ids (hard coded values). i want to apply these 50 thousand Ids in where condition or join operation. I have used below 'with' clause to create a temp table to collect those 50 thousand Ids. Then I used this temp table in join query for filtering.

with temp_receiptIds(receiptId)
as
(
select 'M0000001' from dual
union
select 'M0000002' from dual
union
select 'M0000003' from dual
union
select 'M0000004' from dual
..
..
...
union
select 'M0049999' from dual
union
select 'M0050000' from dual
)

select sal.receiptId, prd.product_name, prd.product_price, sal.sales_date, sal.seller_name 
from product prd 
join sales sal on prd.product_id=sal.product_id 
join temp_receiptIds tmp on tmp.receiptId=sal.receiptId

Whenever I run the above select join query to extract data as requested by business people, it takes about 8 minutes to fetch result in the production server. Is my above approach correct? Are there any simpler approach than this by considering best performance in the production server. Please note, every second , the production database is used by customer. since production db is very busy, can I run this query in production db directly, will it cause slow performance in the customer using website which calls this production db in every second. Correct answers would be greatly appreciated! Thanks

question from:https://stackoverflow.com/questions/65898161/how-to-use-50-thousand-ids-at-where-or-join-clause-in-oracle-pl-sql-for-a-select

Answer 1

Why wouldn't you store those receiptIDs into a table?

create table receiptids as
with temp_receiptIds(receiptId)
as
(
select 'M0000001' from dual
union all                        --> "union ALL" instead of "union"
...
) 
select * from temp_receiptids;

Index it:

create index i1recid on receiptids (receiptIdD);

See how that query now behaves.

---

If you - for some reason - can't do that, see whether UNION ALL within the CTE does any good. For 50.000 rows, it could make a difference.