1

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 <= 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!

1

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 <= CURRENT_DATE
        AND line_items.orderId = orders.order_id 
);
  • The difference between your query and the version that Oreo posted is that your query has a non-correlated subquery. They in part of the query and the main part of the queries are run separately and the results are joined together. Oreo's query is a correlated sub query where the subquery is filtering the main query as the data from the main query is being gathered. – Gandolf989 Jul 18 at 17:56
0

Is this faster? This might allow the optimizer to filter by STATUS before touching the other tables. It also reduces the three-table join down to two.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.