0

I have two tables:

OrdHead: Order_no, CustOrd
OrdItem: Item, Order_no, CustOrd

Both the tables are joined using Order_No which is Alphanumeric.. I need to to update CustOrd (NUMBER) column of OrdItem with the same CustOrd number in OrdHead table using Order_no.

What is the best way to do that ? Do I add any index to the tables?

Currently I do it with the following query, but its taking alot of time just with 100,000 records.

UPDATE ORDITEM A SET CUSTORD =
(SELECT CUSTORD FROM ORDHEAD b WHERE b.ORDER_NO = a.ORDER_NO);
Siyual
  • 16,415
  • 8
  • 44
  • 58
Imran Hemani
  • 599
  • 3
  • 12
  • 27

1 Answers1

0

If your query works, then you can fix the performance with an index:

create index idx_ordhead_order_no_custord on orderhead(order_no, custord);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • As usual you send to fast, but why you use composite index instead just `ORDER_NO` – Juan Carlos Oropeza Nov 03 '15 at 16:22
  • custord column will be part of index, it will improve performance – are Nov 03 '15 at 16:26
  • 2
    @JuanCarlosOropeza if both columns are being queried, but only one of them is in the index, then Oracle - if it uses the index - will have to also do a lookup in the table. However, if both columns are in the index, Oracle doesn't need to bother with going to the table. Indexes are usually smaller and therefore quicker to query than the corresponding table. – Boneist Nov 03 '15 at 16:32
  • @Boneist That works in other database too like MSSQL and Postgres? Or is just an oracle thing? – Juan Carlos Oropeza Nov 03 '15 at 16:34
  • I don't know; I've only ever worked with Oracle. I would imagine it would be similar in other database platforms, though; why bother doing extra work when you have all the information you need in the index? – Boneist Nov 03 '15 at 16:35
  • 1
    same in MSSSQL and Postgre, but in MSSQL I'd put custord column into included columns of index... – are Nov 03 '15 at 16:39