| Which is the Perfect Update Statement [message #374364] |
Fri, 08 June 2001 00:13  |
jim
Messages: 74
Registered: July 2000
|
Member |
|
|
Table Invoice
-----------------------------
Name Type
----------------------------
INVOICE_NO VARCHAR2(20)
SR_NO NUMBER(5)
PRODUCT_ID VARCHAR2(100)
DESCRIPTION VARCHAR2(600)
USER_NAME VARCHAR2(50)
DEPT VARCHAR2(50)
TEL_NO NUMBER(13)
QTY NUMBER(6)
If i want to update the description column
Which Query will be most suitable and work fast (i.e. will give an optimum Performance)
1)update invoice set description = value
where invoice_no = value and sr_no = value;
2)update invoice set description = value
where invoice_no=value and sr_no = value and description = value and user_name = value and
dept = value and qty = value;
The Combination of Invoice_no and Sr_no is always unique.
The results of the above two queries are the same, but i want to know which query will work fast and also which is the best.
Thanks in Advance
Jim
|
|
|
|
|
|
| Re: Which is the Perfect Update Statement [message #374376 is a reply to message #374364] |
Fri, 08 June 2001 12:40  |
Mike Watson
Messages: 2
Registered: June 2001
|
Junior Member |
|
|
Don't use indexing when doing updates. It has to re-index after every update. Usually the more conditions you apply, the better the performance with some exceptions. Number comparisons are faster than string or varchar comparisons. Hope this helps.
Mike
|
|
|
|
|
|
| Re: Which is the Perfect Update Statement [message #374421 is a reply to message #374406] |
Mon, 11 June 2001 09:02 |
Mike Watson
Messages: 2
Registered: June 2001
|
Junior Member |
|
|
Indexing is always better, but you don't want an index on some field when you are going to be updating, because after every update Oracle will have to update its indexes. Therefore, every index you add slows down updates, since all the indexes must be updated along with the table.
In light of that, when updating use as many "where" conditions as possible and remember numeric comparisons are faster than character comparisons. Indexing is great when doing queries on large data sets, or complex joins etc.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
