| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index question
First, Your english is
excellent or <FONT
size=1>"Usted habla inglés muy bien. "
Assuming you are running
version 8i or better ....
Have you analyzed the tables
you are querying against ? You may not need
to force a rule as the CBO will
try to find the quickest way. It looks like
you are using the RBO by
default.
Do you have optimizer_mode =
Choose ?
Do you have timed_statistics =
true ?
Have you run your queries
through an explain plan ? If not this will
show you the execution path and
a relative cost of each statement.
You can manipulate your SQL to
see different costs as you change the
statement.
Also, Your queries could
have been run while competing for resources in
one case and maybe not in
another case. Run both queries in the same
environment.
I have found that small tables
don't need indexes for the most part although this
is not a hard and fast
rule. You must go through the process.
Espero que eso lo ayude y
la buena suerte a usted. Espero verlo en
Puerto Vallarta Alguna Vez
pronto en la playa con un margarita. Hasta Luego.
Miquel.
-----Original
Message-----From: Teresita Castro
[mailto:[EMAIL PROTECTED]Sent: Wednesday, June 25, 2003
7:04 PMTo: Multiple recipients of list ORACLE-LSubject:
Re: Index question
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.RecordsetrecRS.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 theproblem but like Stephane said - you should
probably stay away from themfor now. If adding a hint decreased
performance then you have proved thisfor 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 whento use indexes or not automatically* Hints can force
the optimisor to choose a non-optimal execution plan. Ifyou are
smarter than the optimisor this may be fine but in most cases theoptimisor
will make the right decision when all tables are analyzed* Hints have
very specific formatting and object name rules. If you renamean
index the hint will become invalid and be blissfully ignored - you
won'teven know.* There are some hints which can safely be used but
it takes a goodunderstanding of Oracle first. My advice would be
don't use them. If youhave a specific problem then post lot's of
details here and someone mightsuggest trying a hint.* Until you
really understand the implications of using a specific hint itcan be
dangerous in terms of performance - Stephane is not lying when hesays 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
lettingOracle do what it does
best.Regards, Mark.
Castro"
<[EMAIL PROTECTED]> martmx.com> cc:
by: Subject: Re: Index question [EMAIL PROTECTED] om
09:29
to ORACLE-LOphss...I have a terrible problem, I am using VB with Oracle and since weput that instruccion on the execute instruction our execution timedecrease.The problem is that my boss was the one that found that instruction and Ihave to told her not to uses it, but with her I have to show some evidencethat 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 harmthan
services---------------------------------------------------------------------ToREMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like
subscribing).<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>Privileged/Confidential information may be contained in this message. If you are
of
it.<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>--Please see the official ORACLE-L FAQ: <A href="http://www.orafaq.net">http://www.orafaq.net-- Author: Mark Richard INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 <A
services---------------------------------------------------------------------ToREMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Received on Wed Jun 25 2003 - 22:19:59 CDT
 |
 |