Teresita
1. Do NOT modify Lawson data directly. A lot of the Lawson integrity
checking is built into the Lawson code. You can easily corrupt the data
without realizing it.
2. I would discourage you from directly querying the Lawson data. When
you upgrade your Lawson system, you'll have to rework all your programs.
3. In case you aren't aware of it, there is an email list devoted to
Lawson sites. If you are interested in alternatives to directly querying the
data, others on that list can help you better than I can. Send an email to:
<mailto:[EMAIL PROTECTED]>
[EMAIL PROTECTED]
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Wednesday, June 25, 2003 9:04 PM
To: Multiple recipients of list ORACLE-L
Hi!!
Let explain more about my situation.
The company that I work for is a chain of stores around some city's on
Mexico, they bought Lawson a system that uses Oracle to manage the data
bases, at first they use SQL Server 2000, but I wasn't enough to manager all
the information.
The structure of the table is all ready done and I have to learn it to do
some reports that Lawson don't have, change or delete information and export
some information to dbf files. Because we was using SQL Server I used Store
procedures to return the select result to VB recordset and the I pass the
select result to Crystal Report or to a DBF file.
Well I see that in Oracle the store procedure do not returns the result set
has easy has SQL Server so I use and statement that after execute it returns
me the result in a record set.
sQuery = "SELECT COMPANY,LOCATION, R_NAME FROM ICLOCATION " & _
"WHERE COMPANY=2000 OR COMPANY=2001 OR COMPANY=2002 order by
COMPANY,LOCATION"
Set recRS = New ADODB.Recordset
recRS.Open sQuery, gcnOracle, adOpenForwardOnly, adLockReadOnly, adCmdText
or execute a delete or update statement
sSQL = "update /*+ INDEX(itemloc ITLSET2) */ itemloc set average_cost =" &
costo & " where (company='2000') and item= '" & Arti & "'"
gcnOracle.Execute sSQL
gcnOracle.Execute "Commit", dbSQLPassThrough
Back to my problem:
In this case the update of the average cost has to be done on the table Item
location ( ITEMLOC) that have all the item that each location( store) have.
The locations have a company, when we changes the average cost is per
company ( each company represent a different city)
So my boss execute the update statement
sSQL = "update itemloc set average_cost =" & costo & " where
(company='2000') and item= '" & Arti & "'"
and she told me that per item it takes like 10 seconds.
After read the article that I mention she changes the statement to this :
sSQL = "update /*+ INDEX(itemloc ITLSET2) */ itemloc set average_cost =" &
costo & " where (company='2000') and item= '" & Arti & "'"
ITLSET2 is a index that have company(1), location(2) and Item(3) and it
takes 2 seconds per item, so that is way she is convinced that we have to
uses the /*+ INDEX(itemloc ITLSET2) */ in all of our select, update or
delete statement.
I hope you undestant my English and my problem too, because I have to do
some really complicated queries that have like 3 or 5 tables in them, and
using this method will give me some serious complications, maybe in this
case (change of the average cost) is not too dangerous.
But I have to give her strong statements to change her mind.
Thanks for everything friends!!
>>> [EMAIL PROTECTED] 06/25/03 06:10PM >>>
Teresita,
I don't fully understand whether adding or removing a hint caused the
problem but like Stephane said - you should probably stay away from them
for now. If adding a hint decreased performance then you have proved this
for yourself.
A couple of important points:
* Using an index isn't always faster than scanning the table
* If a database is correctly analyzed then the optimisor can determine when
to use indexes or not automatically
* Hints can force the optimisor to choose a non-optimal execution plan. If
you are smarter than the optimisor this may be fine but in most cases the
optimisor will make the right decision when all tables are analyzed
* Hints have very specific formatting and object name rules. If you rename
an index the hint will become invalid and be blissfully ignored - you won't
even know.
* There are some hints which can safely be used but it takes a good
understanding of Oracle first. My advice would be don't use them. If you
have a specific problem then post lot's of details here and someone might
suggest trying a hint.
* Until you really understand the implications of using a specific hint it
can be dangerous in terms of performance - Stephane is not lying when he
says you can do more harm than good. I have many cases where I can "tune"
a query simply by removing the hints which someone included and letting
Oracle do what it does best.
Regards,
Mark.
"Teresita Castro"
<[EMAIL PROTECTED] To: Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
martmx.com> cc:
Sent by: Subject: Re: Index
question
[EMAIL PROTECTED]
om
26/06/2003 09:29
Please respond to
ORACLE-L
Ophss...I have a terrible problem, I am using VB with Oracle and since we
put that instruccion on the execute instruction our execution time
decrease.
The problem is that my boss was the one that found that instruction and I
have to told her not to uses it, but with her I have to show some evidence
that show why now to uses hits.
Do you have any information that I can show to her.
Thanks!!!
>>> [EMAIL PROTECTED] 06/25/03 02:34PM >>>
Teresita,
Since you advertise yourself as a newbie, listen to an old-timer :
for the next two years, forget about hints. You risk doing more harm
than good.
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
<http://www.orafaq.net>
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
<http://www.fatcity.com>
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
<http://www.orafaq.net>
--
Author: Mark Richard
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
<http://www.fatcity.com>
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
