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:
orders.order_statusis currently "PENDING"; and
products.product_categoryis currently "COFFEE"; and
products.product_available_onis 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 <= 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!