| 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
I Installed some CD's on my computer that
said:
Oracle9i Database
Realease(9.2.0.1.0)
for Microsoft Windows
98/NT/2000/XP
CD 1 of 3
Well I installed only the Client. But if is better I can
uninstall it and install the CD that said
Oracle9i Client
Realease(9.2.0.1.0)
for Microsoft Windows
98/NT/2000/XP
Then I remember that I went to an oracle page, I don't
remember what was the link, I responded some question and have and active
account.
I don't now where is the scratchpad tool, could you pleases
tell me.
I have a lot of CD's here some of them have never been open,
the company pay and external person to install and configure the Oracle
server.
If the scratchpad tool is on some of this CD's pleases
tell me:
Oracle Pure Name & Address (APAC)
Oracle Pure Name & Address (EMEA)
Oracle Pure Name & Address (Latin America)
Oracle Pure Name & Address (North
America-Geocode)
Oracle Pure Name & Address (North America)
Oracle Management Pack for SAP R/3
Oracle9i Lite
Oracle Visual Workbench for Oracle Procedural Gateway for IBM
MQSeries
Oracle Enterprise Integration Gateways
Oracle Fail Safe and Oracle Real Application Clusters
Guard
Oracle Real Application
Cluesters
>>> [EMAIL PROTECTED] 06/26/03 03:24PM
>>>Download the Oracle 9i client for Windows, you'll needan
Oracle Technet account but you can get one for freeimmediately, then choose
the scratchpad tool, itgenerates the explain plan and also you can get
acomplete report.. and other tools for oracle
dba..Gabriel--- Teresita Castro
<[EMAIL PROTECTED]>wrote:> Where I can see the
explain Plan?... I have TOAD but> is a try version and I don't have this
option> activated, can I see it in another program?>
> And where I have to define this option:> optimizer_mode =
Choose > timed_statistics = true > >
Michael:> Sobre tus vacaciones como estaras cerca de> Guadalaja,
te recomiendo visitar estar ciudad, y > tomar el tur del tren Tequita
express. Yo no he ido> a puerto Vallarte pero la gente de por alla es
muy> amigable y servicial, buena suerte !!> > >>>
[EMAIL PROTECTED] 06/25/03 09:59PM> >>>>
> First, Your english is excellent or "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-----> Sent: Wednesday, June 25, 2003 7: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.> > > > > > >
Castro" > >
Xavier__________________________________Do you Yahoo!?SBC Yahoo!DSL - Now only $29.95 per month!<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 Fri Jun 27 2003 - 12:21:47 CDT
 |
 |