Replacing IN condition in Oracle Oracle here. I have the following tables:

[orders]
===
order_id : integer constraint pk_orders primary key using index
order_name : varchar2(40 char)
order_ordered_by : integer constraint fk_shoppers references accounts
order_total : number(10,2) not null
order_status : char not null

[line_items]
===
line_item_id : integer constraint pk_line_items primary key using index
order_id : integer not null constraint fk_line_items_orders references orders on delete cascade
product_id : integer not null constraint fk_line_items_products references products
line_item_quantity : integer not null

[products]
===
product_id : integer constraint pk_products primary key using index
product_name : varchar2(40 char)
product_category : varchar2(10 char)
product_available_on : date


I am trying to write a query that updates orders and sets their statuses to "ORDERED" where:


the orders.order_status is currently "PENDING"; and
the products.product_category is currently "COFFEE"; and
the products.product_available_on is currently less than or equal to the present time (now)


My best attempt thus far does work and get the job done:

UPDATE orders
SET status = 'ORDERED'
WHERE order_id IN (
    SELECT DISTINCT orders.order_id
    FROM orders
    INNER JOIN line_items ON line_items.orderId = orders.order_id
    INNER JOIN products ON line_items.product_id = products.product_id
    WHERE
        orders.status = 'PENDING' AND
        products.product_category = 'COFFEE' AND
        products.product_available_on &lt;= CURRENT_DATE
);


Again, this does work, however its pretty slow so I'm trying to see if there is a way I can rewrite this to be more efficient where I'm not using the IN condition (I've read in several places that IN can cause performance issues in Oracle). Is there any way to accomplish rewriting this query without the IN so I can compare performance?

Please note: changing the tables (tweaking their fields, adding constraints/indexes/anything) is out of the realm of possibility in my particular use case!
I'd recommend getting rid of that DISTINCT if possible, they tend to act as "make this subquery a black box to the DB engine" keywords, which doesn't help the query optimiser make good choices.

Try the following, if it works in Oracle (I'm a MS-SQL coder, sorry)

UPDATE orders
SET status = 'ORDERED'
WHERE orders.status = 'PENDING'
    AND EXISTS (
    SELECT 1
    FROM line_items
    INNER JOIN products ON line_items.product_id = products.product_id
    WHERE
        products.product_category = 'COFFEE' AND
        products.product_available_on &lt;= CURRENT_DATE
        AND line_items.orderId = orders.order_id 
);