Slow Oracle query

Question

I am building a web application for a customer and I have a problem with a particular SQL query.

The query is:

select order_header.order_no,
       order_header.purchase_order_no,
       order_header.entry_date,
       order_header.delivery_date,
       order_totals.total_quantity
from   order_header,
       order_totals
where  order_header.order_no = order_totals.order_no

I have done some troubleshooting and this:

where  order_header.order_no = order_totals.order_no

is the problem. The SQL query with this line takes 35 seconds (causes DataTables to even time out at times) and without it it is instant.

So, I know the problem but I'm not a DBA so don't know the solution.

It's not my database, so the solution I need to send to the DBA to sort so I can continue with my job. Something like

"Hey, would you mind doing A on B table so that C speeds up?"

I just don't know what actually needs to be done!

Answer 1

Ask your DB to

add an index on the order_no column in both order_header and order_total

That should help with your current problem. Also, look up JOIN and change your query to use the JOIN syntax.

 select order_header.order_no,
           order_header.purchase_order_no,
           order_header.entry_date,
           order_header.delivery_date,
           order_totals.total_quantity
    from   order_header 
    join   order_totals ON order_header.order_no = order_totals.order_no

Answer 2

First add an index on both order_header.order_no and order_totals.order_no and check that both the columns are of the same type. For other optimizations we should talk about the data. Don't forget to update the statistics