RE: Index question

From: Teresita Castro <Teresita.Castro_at_s-martmx.com>
Date: Fri, 27 Jun 2003 10:19:08 -0700
Message-ID: <F001.005BB39F.20030627093028@fatcity.com>

About the time test, this are some of the results:

SQL> set time on10:22:59 SQL> select * from ictrans where item='0010096'10:23:30 2 10:23:39 SQL> 10:23:53 SQL> select * from ictrans where location='TJU01'10:24:20 2 10:24:21 SQL> update itemloc set average_cost=6.3 where company='2000' and item='0010041'10:27:45 2 10:27:49 SQL>
>>> [EMAIL PROTECTED] 06/26/03 03:09PM
>>>
Teresita,

Usted tiene un nombre hermoso. Yo nunca he estado a Guadalajara, pero oigo es muy agradable. La mayor parte de mi tiempo en México ha sido de Tiajuana completamente al sur a Zijuantinajo. El País hermoso y muy entibiar a gente Italia mucho más apreciando. Mi próximo viaje a México estará a Cabo San Lucas probablemente en agosto. Espero llegar a Guadalajara algún día.
As for your current problem
with the index......

First run a script called
utlxplan.sql (spelling) from the $ORACLE_HOME/rdbms/admin directory under the schema you wish to execute this query out of then
<FONT

size=4>

The optimizer_mode = Choose , timed_statistics = true parameters can be set in your
<SPAN

class=291544102-26062003>init*.ora file, but your can see the current setting of these values through ...
<SPAN

class=291544102-26062003>
<SPAN

class=291544102-26062003>SQL> select value from v$parameter where name like 'optimizer%'
<SPAN

class=291544102-26062003> or
name = 'timed_statistics';
<SPAN

class=291544102-26062003>
<SPAN

class=291544102-26062003>The query will also return several optimizer values which may be needed for assessment

<SPAN

class=291544102-26062003>later
on.
<SPAN

class=291544102-26062003>
<SPAN

class=291544102-26062003>If you cannot bounce ( restart) the instance then consider setting these parameters using the command ...
<SPAN

class=291544102-26062003>
<SPAN

class=291544102-26062003>SQL> Alter
session set .....
<SPAN

class=291544102-26062003>
<SPAN

class=291544102-26062003>Then
....
<SPAN

class=291544102-26062003>
<SPAN

class=291544102-26062003>SQL> set
autotrace traceonly
<SPAN

class=291544102-26062003>also
consider
<SPAN

class=291544102-26062003>SQL> set timing on
<SPAN

class=291544102-26062003>for a relative cost on how much time the query takes
<SPAN

class=291544102-26062003>
<SPAN

class=291544102-26062003>and then
Execute
your SQL statement which will output an explain plan for your viewing pleasure.
<SPAN

class=291544102-26062003>
<SPAN

class=291544102-26062003>Lots of information on metalink on how to use and interpret the explain plan.
<SPAN

class=291544102-26062003>
<SPAN

class=291544102-26062003>Lets stop here and report back what your output is on the explain plan.
<SPAN

class=291544102-26062003>
<SPAN

class=291544102-26062003>CAUTION: I
never use 3rd party products. I like to go straight to the data dictionary to find out whats going on with the database. Not to say 3rd party products are bad or anything, its just my style thats all.
<SPAN

class=291544102-26062003>
<SPAN

class=291544102-26062003>
<SPAN

class=291544102-26062003>Espero que esto lo ayude fuera.
<SPAN

class=291544102-26062003>
<SPAN

class=291544102-26062003>Miquel.
<SPAN

class=291544102-26062003>

-----Original Message-----From: Teresita Castro [mailto:[EMAIL PROTECTED]Sent: Thursday, June 26, 2003 11:25 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Index question
 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-----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.

"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 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 good.--Regards,Stephane FaroultOriole Software--Please see the official ORACLE-L FAQ: <A href="http://www.orafaq.net">http://www.orafaq.net--Author: Stephane Faroult INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 <A
href="http://www.fatcity.com">http://www.fatcity.comSan Diego, California -- Mailing list and web hosting

    services---------------------------------------------------------------------To

REMOVE 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 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: <A href="http://www.orafaq.net">http://www.orafaq.net-- Author: Mark Richard INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 <A href="http://www.fatcity.com">http://www.fatcity.comSan Diego, California -- Mailing list and web hosting

    services---------------------------------------------------------------------To