RE: Index question
Hi Michael!!! This is the result of the query: SQL select value from v$parameter where name like 'optimizer%' or name = 'timed_statistics'; Results: TRUE 9.2.0 CHOOSE 2000 100 0 1 7 rows selected. About your travel plans: Te recomiendo en otra ocacion planear vacaciones a Guanajuato, o a la ciudad de Mexico son lugares en donde hay mucho que ver. Sobre Cabo San Lucas mis hermanas estuvieron hay, y dicen que es un lugar muy tranquilo y bonito. Saludos!!! [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 scriptcalled utlxplan.sql (spelling) from the $ORACLE_HOME/rdbms/admin directory under the schema you wish to execute this query out of then Theoptimizer_mode = Choose , timed_statistics = true parameters can be set in your init*.ora file, but your can see the current setting of these values through ... SQL select value from v$parameter where name like 'optimizer%' or name = 'timed_statistics'; The query will also return several optimizer values which may be needed for assessment later on. If you cannot bounce ( restart) the instance then consider setting these parameters using the command ... SQL Alter session set . Then SQL set autotrace traceonly also consider SQL set timing on for a relative cost on how much time the query takes and then Execute your SQL statement which willoutput anexplain plan for your viewing pleasure. Lots of information on metalink on how to use and interpretthe explain plan. Lets stop here and report back what your output is on the explain plan. 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 say3rd party productsare bad or anything, its just my style thats all. Espero que esto lo ayude fuera. Miquel. -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 thatI work foris 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 informationand export some information to dbf files. Because we was using SQL Server I used Store procedures to return the select resultto VB
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
RE: Index question
Terrista, Did you run the UTLXPLAN.sql file so you can get a explain plan output ? Check Metalink for an explanation of all of this here is one link http://metalink.oracle.com/metalink/plsql/ml2_gui.startup Then . SQL Set Autotrace on Then run those queries and report back the outputs. Everything else is set up fine for now although we will probaly change a few more optimzer parametersso stay tuned for that. buenos días a usted. Miquel. -Original Message-From: Teresita Castro [mailto:[EMAIL PROTECTED]Sent: Friday, June 27, 2003 10:30 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Index question 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 scriptcalled utlxplan.sql (spelling) from the $ORACLE_HOME/rdbms/admin directory under the schema you wish to execute this query out of then Theoptimizer_mode = Choose , timed_statistics = true parameters can be set in your init*.ora file, but your can see the current setting of these values through ... SQL select value from v$parameter where name like 'optimizer%' or name = 'timed_statistics'; The query will also return several optimizer values which may be needed for assessment later on. If you cannot bounce ( restart) the instance then consider setting these parameters using the command ... SQL Alter session set . Then SQL set autotrace traceonly also consider SQL set timing on for a relative cost on how much time the query takes and then Execute your SQL statement which willoutput anexplain plan for your viewing pleasure. Lots of information on metalink on how to use and interpretthe explain plan. Lets stop here and report back what your output is on the explain plan. 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 say3rd party productsare bad or anything, its just my style thats all. Espero que esto lo ayude fuera. Miquel. -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 margar
RE: Index question
-L [EMAIL PROTECTED] martmx.comcc: 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).
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 thatI work foris 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 informationand export some information to dbf files. Because we was using SQL Server I used Store procedures to return the select resultto 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 itemit takes like 10 seconds. After read the article that I mention shechanges 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 Ihave to dosome really complicated queries that have like 3 or5 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. ButI 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
Re: RE: Index question
go to the rdbms/admin directory in your oracle home(i forget the full path so you will have to do a search). look for the script utlxplan.sql run it in a directory where you can create a table. this will create the plan table. when you want to see the result of a query plan type set autotrace on run the query. results will follow. if you just want the results type set autotrace traceonly. to interpret go to otn.oracle.com its in the performance guide, there is also a really good explain plan doc on metalink. From: Teresita Castro [EMAIL PROTECTED] Date: 2003/06/26 Thu PM 02:24:46 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: 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- 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
RE: Index question
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 scriptcalled utlxplan.sql (spelling) from the $ORACLE_HOME/rdbms/admin directory under the schema you wish to execute this query out of then Theoptimizer_mode = Choose , timed_statistics = true parameters can be set in your init*.ora file, but your can see the current setting of these values through ... SQL select value from v$parameter where name like 'optimizer%' or name = 'timed_statistics'; The query will also return several optimizer values which may be needed for assessment later on. If you cannot bounce ( restart) the instance then consider setting these parameters using the command ... SQL Alter session set . Then SQL set autotrace traceonly also consider SQL set timing on for a relative cost on how much time the query takes and then Execute your SQL statement which willoutput anexplain plan for your viewing pleasure. Lots of information on metalink on how to use and interpretthe explain plan. Lets stop here and report back what your output is on the explain plan. 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 say3rd party productsare bad or anything, its just my style thats all. Espero que esto lo ayude fuera. Miquel. -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 thatI work foris 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 informationand export some information to dbf files. Because we was using SQL Server I used Store procedures to return the select resultto 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.Records
RE: Index question
Download the Oracle 9i client for Windows, you'll need an Oracle Technet account but you can get one for free immediately, then choose the scratchpad tool, it generates the explain plan and also you can get a complete 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
Re: Index question
Here is sql that I used, hope this help.
Joan
set lines 132
col column_name format a15
col index_name format a20
break on index_name skip 1
select c.index_name,c.column_name,i.uniqueness
from dba_ind_columns c,dba_indexes i
where c.table_name = upper('Enter_Table_Name')
and c.table_name=i.table_name
and c.index_name=i.index_name
order by c.index_name, c.column_position
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Joan Hsieh
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).
Re: Index question
I found the next link,that shows you how to
specify and index to determinated table.
http://www.dbasupport.com/oracle/ora9i/index_hints.shtml
This is a fragment of the article:
How to specify Hints
The Hints are enclosed in comment, /* comment */, in an SQL statement, and
can only be specified with SELECT, DELETE and UPDATE keyword.SELECT /* comment */ ;
All hints should start with a + sign. This tells the SQL
Parser that the SQL has a hint specified with it. SELECT /*+{hint} */ ;
Syntax:/*+ INDEX ( table [index [index]...] ) */
Where:
table specifies the name or alias of the table associated with the index
to be scanned.
index specifies an index on which an index scan is to be performed.
Examples:select /*+ INDEX(emp_city idx_job_code) */ empname,
job_code from emp where job_code = 'T';
Thank for all your
help!!!
[EMAIL PROTECTED] 06/25/03 01:19PM
Here is sql that I used, hope this help.Joanset
lines 132col column_name format a15col index_name format a20break on
index_name skip 1select c.index_name,c.column_name,i.uniquenessfrom
dba_ind_columns c,dba_indexes iwhere c.table_name =
upper('Enter_Table_Name')and c.table_name=i.table_nameand
c.index_name=i.index_nameorder by c.index_name, c.column_position/--
Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Joan
Hsieh INET: [EMAIL PROTECTED]Fat City Network
Services -- 858-538-5051 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).
Re: Index question
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 -- Author: Stephane Faroult 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).
Re: Index question
Hi!
Hm, what happened to insert hints.. such APPEND,
PARALLEL, NOLOGGING etc..?
I would avoid this "dbasupport" site, it has
written *a lot* of crap before.
Checkthis for example(Tom Kyte's
comments on an dbasupport article :)
http://asktom.oracle.com/pls/ask/f?p=4950:8:345702010955165697::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4951966319022,
Tanel.
- Original Message -
From:
Teresita Castro
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, June 25, 2003 11:16
PM
Subject: Re: Index question
I found the next link,that shows you how
to specify and index to determinated table.
http://www.dbasupport.com/oracle/ora9i/index_hints.shtml
This is a fragment of the article:
How to specify Hints
The Hints are enclosed in comment, /* comment */, in an SQL statement, and
can only be specified with SELECT, DELETE and UPDATE keyword.SELECT /* comment */ ;
All hints should start with a + sign. This tells the SQL
Parser that the SQL has a hint specified with it. SELECT /*+{hint} */ ;
Syntax:/*+ INDEX ( table [index [index]...] ) */
Where:
table specifies the name or alias of the table associated with the index
to be scanned.
index specifies an index on which an index scan is to be performed.
Examples:select /*+ INDEX(emp_city idx_job_code) */ empname,
job_code from emp where job_code = 'T';
Thank for all your
help!!!
Re: Index question
Ophss...Ihave 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 harmthan good.-- Regards,Stephane FaroultOriole Software-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Stephane Faroult INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 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).
Re: Index question
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.comcc: 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 -- Author: Stephane Faroult 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). 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
Re: Index question
Hi!! Let explain more about my situation. The company thatI work foris 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 informationand export some information to dbf files. Because we was using SQL Server I used Store procedures to return the select resultto 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 itemit takes like 10 seconds. After read the article that I mention shechanges 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 Ihave to dosome really complicated queries that have like 3 or5 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. ButI 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 ye
RE: Index question
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 thatI work foris 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 informationand export some information to dbf files. Because we was using SQL Server I used Store procedures to return the select resultto 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 itemit takes like 10 seconds. After read the article that I mention shechanges 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 Ihave to dosome really complicated queries that have like 3 or5 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. ButI 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 in
Re: Index question
Hi, There are a couple of tables to help you out... user_indexes (or all_indexes) will show you the indexes and their associated tables. user_ind_columns (or all_ind_columns) will show the columns contained within the index (multiple rows per index potentially). In your example, an index on the item column is the only thing which will help. Try: create index ind_itemloc_item on itemloc(item); Then issue: analyze table itemloc estimate statistics; Look at the manuals for additional info on the above statements - you might want to add a tablespace clause to the create index statement for example. The analyze command will provide Oracle with information about the index so that it knows when to use or not use the index. Finally, is the itemloc column a number or string column. If it's a number column then don't wrap quotes around the supplied number - the implicit datatype conversion can cause Oracle to ignore the index. Regards, Mark. Teresita Castro [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] martmx.comcc: Sent by: Subject: Index question [EMAIL PROTECTED] om 25/06/2003 09:44 Please respond to ORACLE-L Hi! I am new using Oracle, and what to ask some doubt about indexes How can I see how many fields of a table have indexes? I am trying to do an update but is too slow, so what I am trying to find is the way to uses an index that can help to do this update more quickly. This is the query example: Update ITEMLOC SET AVERAGE_COST=5.5 WHERE ITEM='0010096' I will like to find the way to set and index that have the item filed include to do this update quicker. Right now it takes like 30 seconds per item and we have to update like 9,000 items. 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 -- Author: Mark Richard 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).
Re: Index question
Hi! If you are new to Oracle, it might be easier to use DBA studio (which usually comes with Oracle) or other GUI tool such is Toad (http://www.toadsoft.com/). But if you want to learn how things really work, then open up sqlplus and start experimenting with data dictionary views: select table_name, index_name, column_name, column_position from user_ind_columnswhere table_name = 'your_table_name'order by 1, 3, 4 Note that your table name will likely be upper case even though you created it with lower case name. The column_position column shows you in which order are the columns in composite indexes. Tanel. - Original Message - From: Teresita Castro To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 25, 2003 2:44 AM Subject: Index question Hi! I am new using Oracle, and what to ask some doubt about indexes How can I see how many fields of a table have indexes? I am trying to do an update but is too slow, so what I am trying to find is the way to uses an index that can help to do this update more quickly. This is the query example: UpdateITEMLOC SET AVERAGE_COST=5.5 WHERE ITEM='0010096' I will like to find the way to set and index that have the item filed include to do this update quicker. Right now it takes like 30 seconds per item and we have to update like 9,000 items.
Re: Index question
THANKS!!! I run the next queries and I found the information that I need. select * from ALL_INDEXESwhere table_name='ITEMLOC' select * from all_ind_columnswhere table_name='ITEMLOC' The only problem that I have was that I was trying to find ALL_INDEXES and all_ind_columnson the wrong places they was on the views tab. About the item,yesit is char(32), thanks for the advice is really important to know this kind of things. [EMAIL PROTECTED] 06/24/03 06:24PM Hi,There are a couple of tables to help you out...user_indexes (or all_indexes) will show you the indexes and theirassociated tables.user_ind_columns (or all_ind_columns) will show the columns containedwithin the index (multiple rows per index potentially).In your example, an index on the item column is the only thing which willhelp.Try:create index ind_itemloc_item on itemloc(item);Then issue:analyze table itemloc estimate statistics;Look at the manuals for additional info on the above statements - you mightwant to add a tablespace clause to the "create index" statement forexample. The analyze command will provide Oracle with information aboutthe index so that it knows when to use or not use the index.Finally, is the itemloc column a number or string column. If it's a numbercolumn then don't wrap quotes around the supplied number - the implicitdatatype conversion can cause Oracle to ignore the index.Regards, Mark. "Teresita Castro" [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] martmx.com cc: Sent by: Subject: Index question [EMAIL PROTECTED] om 25/06/2003 09:44 Please respond to ORACLE-L Hi!I am new using Oracle, and what to ask some doubt about indexesHow can I see how many fields of a table have indexes?I am trying to do an update but is too slow, so what I am trying to find isthe way to uses an index that can help to do this update more quickly.This is the query example:Update ITEMLOCSET AVERAGE_COST=5.5WHERE ITEM='0010096'I will like to find the way to set and index that have the item filedinclude to do this update quicker. Right now it takes like 30 seconds peritem and we have to update like 9,000 items. 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-- Author: Mark Richard INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 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).
RE: Index question
Thanks you all for your response Ofer -Original Message- Sent: Thursday, September 19, 2002 3:23 PM To: Multiple recipients of list ORACLE-L Testing against 8.1.7.4, the drop index was successful and an error was reported by my long running query: ERROR: ORA-08103: object no longer exists Is this because the index is locked by query only when it's used? From one side looks reasonable, but from another - no good someone can drop an index user by running query. Is there any index locking at all?? Regards, Alexandre Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ofer Harel Sent: Thursday, September 19, 2002 6:09 AM To: Multiple recipients of list ORACLE-L Subject: Index question Good morning, Suppose there is a long running query (which already parsed) using an index. Now I dropped the index. What should happened to the running query? Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ofer Harel 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).
RE: Index question
Ofer, Testing against 8.1.7.4, the drop index was successful and an error was reported by my long running query: ERROR: ORA-08103: object no longer exists Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ofer Harel Sent: Thursday, September 19, 2002 6:09 AM To: Multiple recipients of list ORACLE-L Subject: Index question Good morning, Suppose there is a long running query (which already parsed) using an index. Now I dropped the index. What should happened to the running query? Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins 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).
Re: Index question
I think it's not possible. Query will lock an index in some way, so you cannot drop it until query releases it because DDL will not be able to accure exclusive lock on the index. I'm not sure when query releases index lock. I would say at the end of a query but may be wrong. Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 19, 2002 1:08 PM Good morning, Suppose there is a long running query (which already parsed) using an index. Now I dropped the index. What should happened to the running query? Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ofer Harel 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev 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).
RE: Index question
The Query will be re-parsed. As soon as u do a DDL, the dependent queries in Shared SQL area are invalidated. Regards Naveen -Original Message- Sent: Thursday, September 19, 2002 4:39 PM To: Multiple recipients of list ORACLE-L Good morning, Suppose there is a long running query (which already parsed) using an index. Now I dropped the index. What should happened to the running query? Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ofer Harel 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata 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).
RE: Index question
Sorry for the mis-interpretation. I thought the question was if the same SQL statement is re-executed after dropping the index. regards naveen -Original Message- Sent: Thursday, September 19, 2002 5:28 PM To: Multiple recipients of list ORACLE-L The Query will be re-parsed. As soon as u do a DDL, the dependent queries in Shared SQL area are invalidated. Regards Naveen -Original Message- Sent: Thursday, September 19, 2002 4:39 PM To: Multiple recipients of list ORACLE-L Good morning, Suppose there is a long running query (which already parsed) using an index. Now I dropped the index. What should happened to the running query? Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ofer Harel 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata 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).
Re: Index question
Testing against 8.1.7.4, the drop index was successful and an error was reported by my long running query: ERROR: ORA-08103: object no longer exists Is this because the index is locked by query only when it's used? From one side looks reasonable, but from another - no good someone can drop an index user by running query. Is there any index locking at all?? Regards, Alexandre Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ofer Harel Sent: Thursday, September 19, 2002 6:09 AM To: Multiple recipients of list ORACLE-L Subject: Index question Good morning, Suppose there is a long running query (which already parsed) using an index. Now I dropped the index. What should happened to the running query? Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev 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).
Re: Index Question
For OLTP, one key factor is whether the index is in memory. If you double the size of a primary key field from varchar2(15) to varchar2(30), there will probably not be any change for the header and branch blocks of the index -- if they were in memory before they'll probably still be in memory. If the leaf blocks of the index used to be in memory most of the time, there will also probably be no change -- unless you actually store different, longer values in the fields of this column. If longer values are stored (and presumably that's the case, since that's why you're bumping up the column size) it will take more leaf blocks to store them, and you might find that memory space limitations cause fewer of the leaf blocks to stay in memory, not to mention that other blocks get pushed out. Ultimately, this may be a moot point. If you need a larger column size for larger values, you don't have much of a choice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Index question ???
Title: Message Also a lock will be established on deletes. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax: (707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 21, 2001 2:22 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Index question ??? Andrea, It is recommended, not required. Anytime the db needs to verify the fk constraint, if there isn't an index there to support it, a full table scan will happen. Performance killer. What do you mean your indices disappeared? Can you elaborate? -Original Message- From: Andrea Oracle [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, August 21, 2001 14:06 To: Multiple recipients of list ORACLE-L Subject: Index question ??? Hi, all I got couple of indices questions: Is it REQUIRED or just recommended to build an index on FK column/s? I used Alter Table ... Move and Alter Index ... Rebulid to move the table into differenct block and then rebuild index. Table Move works fine, but after index rebuilt, some indices disappeared! How come? Thanks! Andrea __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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).
RE: Index question ???
Title: RE: Index question ??? Andrea, It is recommended, not required. Anytime the db needs to verify the fk constraint, if there isn't an index there to support it, a full table scan will happen. Performance killer. What do you mean your indices disappeared? Can you elaborate? -Original Message- From: Andrea Oracle [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, August 21, 2001 14:06 To: Multiple recipients of list ORACLE-L Subject: Index question ??? Hi, all I got couple of indices questions: Is it REQUIRED or just recommended to build an index on FK column/s? I used Alter Table ... Move and Alter Index ... Rebulid to move the table into differenct block and then rebuild index. Table Move works fine, but after index rebuilt, some indices disappeared! How come? Thanks! Andrea __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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).
Re: Index question ???
First off, a disclaimer. It's late, and I'm tired, and my soon to be martini is beckoning. So I'm worn out, and anxious. Remember, this advice is a free service. :) Forget the 4% number. It's meaningless. What you have to be concerned with is 'will using the index cause me to read more blocks than if I just do a full table scan?'. tkprof and explain plan can help you with that. Personally, I would build the index, analyze all tables and indexes, and build histograms for cases where I know the queried data is skewed, such as yours appears to be. The point is, give the cost based optimizer all the information it needs, and let it sort it out. Jared On Tuesday 14 August 2001 13:41, Janet Linsy wrote: Hi all, According to Oracle's doc, for a table 1000 rows, if the query returns 4% of the data, an index can be built. If I have a table, with ID 1,2, 3 return 4% of the row, but ID 4, 5, 6 returns 4% rows. Should I build an index on ID column? Also you know any general guildlines for building, not building indexes? OEM generates suggestions too, should I follow? Thanks you! Janet __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
