Re: function based indexes.
Chris, This is exactly what extensible indexing was created for. The idea is simple: You implement an effective search (typically involving some helper tables/indexes), and you express your "search" using a function in SQL, that will call out to your implementation. The actual implementation is, however, somewhat complex as you need to implement everything create/drop/alter index, insert/update/delete, and the actual search does. It's documented in the Data Cartridge Developers Guide. /Bjrn. Chris Stephens wrote: RE: Oh Where Oh Where Is My Redo Coming From I don't think this is possible but I would like to confirm here. I would like to create a function based index to speed up the OH SO SLOW query that follows: SELECT dmzu.ZIP_CODE FROM dm_zip_unq dmzu WHERE fnc_dist(some number,some number,dmzu.LATITUDE,dmzu.LONGITUDE) power(100,2) ) Here is the function definition: CREATE OR REPLACE FUNCTION fnc_Dist ( inp_lat DM_ZIP_UNQ.LATITUDE%TYPE, inp_lng DM_ZIP_UNQ.LONGITUDE%TYPE, dl_lat DM_ZIP_UNQ.LATITUDE%TYPE, dl_lng DM_ZIP_UNQ.LONGITUDE%TYPE) RETURN NUMBER IS BEGIN RETURN (POWER((69.1*(dl_lng - inp_lng ) * COS(inp_lat / 57.3)), 2) + POWER((69.1*(dl_lat - inp_lat)),2)); END fnc_Dist; / with those first 2 parameters, I won't be able to create the function based index correct? Any ideas on how to alter the design of the query? The idea is to provide the functionality to locate a dealer of a certain product when given a certain longitude and latitude. I pretty much know the answer to my question but am looking for any and all design suggestions. Thanks. BEER ME! chris -Original Message- From: Nick Wagner [mailto:[EMAIL PROTECTED]] Sent: Friday, February 21, 2003 12:50 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oh Where Oh Where Is My Redo Coming From Since SharePlex for Oracle and LiveReorg are dependant on redo log volumes, transaction sizes, and things like that we have developed a free utility that parses through the redo logs for some pre-defined amount of time, and let's you know how many operations are on each table, average operations per second, and peak operations per second. It should give you a really good idea where the activity is coming from. Send me a private email if you want me to get you set up on this, and I can send you the files, and the instructions on how to use it. Nick ([EMAIL PROTECTED]) p.s. 28GB is good, but we've seen much more... around 100GB is massive, and we've seen maybe 2-3 databases up to 120GB per day. -Original Message- From: Post, Ethan [mailto:[EMAIL PROTECTED]] Sent: Friday, February 21, 2003 7:44 AM To: Multiple recipients of list ORACLE-L Subject: Oh Where Oh Where Is My Redo Coming From Just had a thought here, have not tried it yet. I have a database that I am working with that is generating 28 GB of redo each day. I would really like to know what objects are generating all this redo without going through the hassle of mining a bunch of log files. It occurred to me that if table monitoring is active and my stats are up to date I should be able to multiply the total number of updates, inserts and commits by the average row size and get a rough % of what objects are generating the most redo. I am sure there are a number of other factors I need to consider, any ideas what they are? * Should I weight inserts, updates and deletes? * ?? The goal is to identify the objects, then identify the jobs that work on those objects and see if I can reduce redo. I suspect a lot of this redo is being generated because of some poor design issues. Thanks! - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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). -- Bjrn Engsig, Miracle A/S Member of Oak Table Network [EMAIL PROTECTED] - http://MiracleAS.dk
function based indexes.
Title: RE: Oh Where Oh Where Is My Redo Coming From I don't think this is possible but I would like to confirm here. I would like to create a function based index to speed up the OH SO SLOW query that follows: SELECT dmzu.ZIP_CODE FROM dm_zip_unq dmzu WHERE fnc_dist(some number,some number,dmzu.LATITUDE,dmzu.LONGITUDE) power(100,2) ) Here is the function definition: CREATE OR REPLACE FUNCTION fnc_Dist ( inp_lat DM_ZIP_UNQ.LATITUDE%TYPE, inp_lng DM_ZIP_UNQ.LONGITUDE%TYPE, dl_lat DM_ZIP_UNQ.LATITUDE%TYPE, dl_lng DM_ZIP_UNQ.LONGITUDE%TYPE) RETURN NUMBER IS BEGIN RETURN (POWER((69.1*(dl_lng - inp_lng ) * COS(inp_lat / 57.3)), 2) + POWER((69.1*(dl_lat - inp_lat)),2)); END fnc_Dist; / with those first 2 parameters, I won't be able to create the function based index correct? Any ideas on how to alter the design of the query? The idea is to provide the functionality to locate a dealer of a certain product when given a certain longitude and latitude. I pretty much know the answer to my question but am looking for any and all design suggestions. Thanks. BEER ME! chris -Original Message- From: Nick Wagner [mailto:[EMAIL PROTECTED] Sent: Friday, February 21, 2003 12:50 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oh Where Oh Where Is My Redo Coming From Since SharePlex for Oracle and LiveReorg are dependant on redo log volumes, transaction sizes, and things like that we have developed a free utility that parses through the redo logs for some pre-defined amount of time, and let's you know how many operations are on each table, average operations per second, and peak operations per second. It should give you a really good idea where the activity is coming from. Send me a private email if you want me to get you set up on this, and I can send you the files, and the instructions on how to use it. Nick ([EMAIL PROTECTED]) p.s. 28GB is good, but we've seen much more... around 100GB is massive, and we've seen maybe 2-3 databases up to 120GB per day. -Original Message- From: Post, Ethan [mailto:[EMAIL PROTECTED]] Sent: Friday, February 21, 2003 7:44 AM To: Multiple recipients of list ORACLE-L Subject: Oh Where Oh Where Is My Redo Coming From Just had a thought here, have not tried it yet. I have a database that I am working with that is generating 28 GB of redo each day. I would really like to know what objects are generating all this redo without going through the hassle of mining a bunch of log files. It occurred to me that if table monitoring is active and my stats are up to date I should be able to multiply the total number of updates, inserts and commits by the average row size and get a rough % of what objects are generating the most redo. I am sure there are a number of other factors I need to consider, any ideas what they are? * Should I weight inserts, updates and deletes? * ?? The goal is to identify the objects, then identify the jobs that work on those objects and see if I can reduce redo. I suspect a lot of this redo is being generated because of some poor design issues. Thanks! - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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: function based indexes.
Title: RE: Oh Where Oh Where Is My Redo Coming From Chris, I could be wrong, but I was under impression that parameter to the F in FBI needed to be either hard coded values or db table columns. I don't think they could be variables. But then again I have been wrong ... Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message-From: Chris Stephens [mailto:[EMAIL PROTECTED]Sent: Friday, February 21, 2003 2:55 PMTo: Multiple recipients of list ORACLE-LSubject: function based indexes. I don't think this is possible but I would like to confirm here. I would like to create a function based index to speed up the OH SO SLOW query that follows: SELECT dmzu.ZIP_CODE FROM dm_zip_unq dmzu WHERE fnc_dist(some number,some number,dmzu.LATITUDE,dmzu.LONGITUDE) power(100,2) ) Here is the function definition: CREATE OR REPLACE FUNCTION fnc_Dist ( inp_lat DM_ZIP_UNQ.LATITUDE%TYPE, inp_lng DM_ZIP_UNQ.LONGITUDE%TYPE, dl_lat DM_ZIP_UNQ.LATITUDE%TYPE, dl_lng DM_ZIP_UNQ.LONGITUDE%TYPE) RETURN NUMBER IS BEGIN RETURN (POWER((69.1*(dl_lng - inp_lng ) * COS(inp_lat / 57.3)), 2) + POWER((69.1*(dl_lat - inp_lat)),2)); END fnc_Dist; / with those first 2 parameters, I won't be able to create the function based index correct? Any ideas on how to alter the design of the query? The idea is to provide the functionality to locate a dealer of a certain product when given a certain longitude and latitude. I pretty much know the answer to my question but am looking for any and all design suggestions. Thanks. BEER ME! chris This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Function based indexes
Hi, I've seen oracle application 11i(11.5.3) setting one parameter _OR_EXPAND_NVL_PREDICATE for queries using nvl(:bind_var, col_name) syntax. Have a look at this parameter on metalink. It is published hidden parameter. It may help you. Regards, Sandeep. -Original Message- Sent: Friday, July 12, 2002 9:18 AM To: Multiple recipients of list ORACLE-L Subject:Re: Function based indexes I don't think that will work. If you need this to work in a SQL statement in SQL*Plus, what you can do is something like this: create or replace package types as type cursorType is ref cursor; end; / create or replace function sp_ListEmp ( col_value_in my_table.my_column.%type default 'My Default Value') return types.cursortype as l_cursortypes.cursorType; begin open l_cursor for select 'x' from my_table where my_column = col_value_in; return l_cursor; end; / REM SQL*Plus commands to use a cursor variable variable c refcursor variable my_bind='testdata' exec :c := sp_ListEmp(:my_bind) print c HTH Jared On Thursday 11 July 2002 15:24, Imma C. Rocco wrote: Hi, I have read that on Oracle 8.1.7 it is possible to create a function based index like the one: Create index ind1 on table (substr(column_name)) tabelspace etc And if Oracle optimizer is in CBO mode and query_rewrite_enabled = true query_rewrite_integrity = trusted compatible = 8.1.0 or greater A statement which has a where clause that involve a substr(colum_name) should use the index. (*)I would like to know if it is also possible to create a function based index on a NVL function that involve a bind variable, because a have a statement like the one that follow: select 'x' from table where col_name = nvl(:p_aa, col_name) This statement perform a full table scan on table ps: if (*) is possible could you please give an sintax example? Thanks Imma _ Supercharge your e-mail with a 25MB Inbox, POP3 Access, No Ads and NoTaglines -- LYCOS MAIL PLUS. http://www.mail.lycos.com/brandPage.shtml?pageId=plus -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sandeep Kurliye 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).
Function based indexes
Hi, I have read that on Oracle 8.1.7 it is possible to create a function based index like the one: Create index ind1 on table (substr(column_name)) tabelspace etc And if Oracle optimizer is in CBO mode and query_rewrite_enabled = true query_rewrite_integrity = trusted compatible = 8.1.0 or greater A statement which has a where clause that involve a substr(colum_name) should use the index. (*)I would like to know if it is also possible to create a function based index on a NVL function that involve a bind variable, because a have a statement like the one that follow: select 'x' from table where col_name = nvl(:p_aa, col_name) This statement perform a full table scan on table ps: if (*) is possible could you please give an sintax example? Thanks Imma _ Supercharge your e-mail with a 25MB Inbox, POP3 Access, No Ads and NoTaglines -- LYCOS MAIL PLUS. http://www.mail.lycos.com/brandPage.shtml?pageId=plus -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Imma C. Rocco 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: Function based indexes
I don't think that will work. If you need this to work in a SQL statement in SQL*Plus, what you can do is something like this: create or replace package types as type cursorType is ref cursor; end; / create or replace function sp_ListEmp ( col_value_in my_table.my_column.%type default 'My Default Value') return types.cursortype as l_cursortypes.cursorType; begin open l_cursor for select 'x' from my_table where my_column = col_value_in; return l_cursor; end; / REM SQL*Plus commands to use a cursor variable variable c refcursor variable my_bind='testdata' exec :c := sp_ListEmp(:my_bind) print c HTH Jared On Thursday 11 July 2002 15:24, Imma C. Rocco wrote: Hi, I have read that on Oracle 8.1.7 it is possible to create a function based index like the one: Create index ind1 on table (substr(column_name)) tabelspace etc And if Oracle optimizer is in CBO mode and query_rewrite_enabled = true query_rewrite_integrity = trusted compatible = 8.1.0 or greater A statement which has a where clause that involve a substr(colum_name) should use the index. (*)I would like to know if it is also possible to create a function based index on a NVL function that involve a bind variable, because a have a statement like the one that follow: select 'x' from table where col_name = nvl(:p_aa, col_name) This statement perform a full table scan on table ps: if (*) is possible could you please give an sintax example? Thanks Imma _ Supercharge your e-mail with a 25MB Inbox, POP3 Access, No Ads and NoTaglines -- LYCOS MAIL PLUS. http://www.mail.lycos.com/brandPage.shtml?pageId=plus -- 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).
Oracle 8.1.7 function based indexes
Hi, I have read that on Oracle 8.1.7 it is possible to create a function based index like the one: Create index ind1 on table (substr(column_name)) tabelspace etc And if Oracle optimizer is in CBO mode and query_rewrite_enabled = true query_rewrite_integrity = trusted compatible = 8.1.0 or greater A statement which has a where clause that involve a substr(colum_name) should use the index. (*)I would like to know if it is also possible to create a function based index on a NVL function that involve a bind variable, because a have a statement like the one that follow: select 'x' from table where col_name = nvl(:p_aa, col_name) This statement perform a full table scan on table ps: if (*) is possible could you please give an sintax example? Thanks Imma _ Supercharge your e-mail with a 25MB Inbox, POP3 Access, No Ads and NoTaglines -- LYCOS MAIL PLUS. http://www.mail.lycos.com/brandPage.shtml?pageId=plus -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Imma C. Rocco 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: Oracle 8.1.7 function based indexes
Imma, First thought is no - oracle implements function-based indexes by using a hidden psuedocolumn to hold the resultant data of the function. Since you would be using a bind variable, it would not be able to create this column. Since you are passing a variable, Oracle cannot use the table stats (high/low values, histograms) to determine access path easily, so is choosing a FTS. If you are sure that you want to use an index, you might consider forcing an index lookup via a hint. A word of caution - when using the index hint, consider not putting the name of the index in the hint, since if you do, and the index name ever changes, the hint will be ignored and the FTS behavior will return. hth, Jack --- Imma C. Rocco [EMAIL PROTECTED] wrote: Hi, I have read that on Oracle 8.1.7 it is possible to create a function based index like the one: Create index ind1 on table (substr(column_name)) tabelspace etc And if Oracle optimizer is in CBO mode and query_rewrite_enabled = true query_rewrite_integrity = trusted compatible = 8.1.0 or greater A statement which has a where clause that involve a substr(colum_name) should use the index. (*)I would like to know if it is also possible to create a function based index on a NVL function that involve a bind variable, because a have a statement like the one that follow: select 'x' from table where col_name = nvl(:p_aa, col_name) This statement perform a full table scan on table ps: if (*) is possible could you please give an sintax example? Thanks Imma _ Supercharge your e-mail with a 25MB Inbox, POP3 Access, No Ads and NoTaglines -- LYCOS MAIL PLUS. http://www.mail.lycos.com/brandPage.shtml?pageId=plus -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Imma C. Rocco 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). __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey 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: Oracle 8.1.7 function based indexes
A friend of mine named Jane just called me and pointed out that you could create a function-based index on the column using this syntax, but you can't use a bind variable to define it. If you create an index with this form nvl(column_name, '_null_') and then issue a query with the form where nvl(column_name, '_null_') = :bind then the function-based index is a candidate to be used. However, since you are using bind variables, oracle is having to use a default data estimate to decide on execution plan, so you may still have to hint the index to get it to be used. As always, test and then test some more. hth, Jack --- Jack Silvey [EMAIL PROTECTED] wrote: Imma, First thought is no - oracle implements function-based indexes by using a hidden psuedocolumn to hold the resultant data of the function. Since you would be using a bind variable, it would not be able to create this column. Since you are passing a variable, Oracle cannot use the table stats (high/low values, histograms) to determine access path easily, so is choosing a FTS. If you are sure that you want to use an index, you might consider forcing an index lookup via a hint. A word of caution - when using the index hint, consider not putting the name of the index in the hint, since if you do, and the index name ever changes, the hint will be ignored and the FTS behavior will return. hth, Jack --- Imma C. Rocco [EMAIL PROTECTED] wrote: Hi, I have read that on Oracle 8.1.7 it is possible to create a function based index like the one: Create index ind1 on table (substr(column_name)) tabelspace etc And if Oracle optimizer is in CBO mode and query_rewrite_enabled = true query_rewrite_integrity = trusted compatible = 8.1.0 or greater A statement which has a where clause that involve a substr(colum_name) should use the index. (*)I would like to know if it is also possible to create a function based index on a NVL function that involve a bind variable, because a have a statement like the one that follow: select 'x' from table where col_name = nvl(:p_aa, col_name) This statement perform a full table scan on table ps: if (*) is possible could you please give an sintax example? Thanks Imma _ Supercharge your e-mail with a 25MB Inbox, POP3 Access, No Ads and NoTaglines -- LYCOS MAIL PLUS. http://www.mail.lycos.com/brandPage.shtml?pageId=plus -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Imma C. Rocco 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). __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey 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). __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey 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).
function based indexes
Hi, We are using function based index and optimizer is using indexes properly. parameter query_rewrite_integrity is set as ENFORCED docs says that we need to change the valus to TRUSTED. I just want to know the comments of DBA's using function based indexes whether this parameter should be changed to TRUSTED and what is use of TRUSTED. Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: function based indexes
Harvinder - I had to make these settings in order to use function-based indexes maybe a year ago in Oracle 8.1.6, and thus far have experienced no ill effects. For specifics of what they do, I would read the Oracle documentation and do a search on http://www.google.com. Try it on your test database first, of course. If you have multiple production databases, you might test it on your least critical production database first. Other than that, if you wish to use a function-based index, then you must set these parameters. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, May 13, 2002 4:29 PM To: Multiple recipients of list ORACLE-L Hi, We are using function based index and optimizer is using indexes properly. parameter query_rewrite_integrity is set as ENFORCED docs says that we need to change the valus to TRUSTED. I just want to know the comments of DBA's using function based indexes whether this parameter should be changed to TRUSTED and what is use of TRUSTED. Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: optimizer not using function based indexes
And I respectfully agree that I was wrong :)It happens, I think I already
posted a note to the list apologizing and correcting my error.
This is what happens when I post from memory without double checking
obviously I had done a flush of my shared_pool!
Rachel
|+---
|| |
|| |
|| MGogala@oxhp.|
|| com |
|| |
|| 05/09/2002 |
|| 05:43 PM |
|| Please |
|| respond to |
|| ORACLE-L |
|| |
|+---
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: RE: optimizer not using function|
| based indexes|
|
I respectfully disagree. In order to use a function based index
you do need the upper. If optimizer doesn't pick it up straigt
away, he should give a hand with a hint (/*+ INDEX(alias,index) */)
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 4:49 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: optimizer not using function based indexes
you don't need the upper(ename) in your where clause... that
forces oracle to
NOT use an index
|+-
|| |
|| |
|| Harvinder.Singh@met|
|| ratech.com |
|| |
|| 05/09/2002 04:18 PM|
|| Please respond to |
|| ORACLE-L |
|| |
|+-
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: optimizer not using function|
| based indexes|
|
Hi,
We are trying to use function based indexes in our
application but it looks like
optimizer is not using them.
So we created a sample table emp with 3 columns
empno,ename,sal and populate
this table with 10 rows and created function based index
on upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can
be faster than
index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gogala, Mladen
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access
optimizer not using function based indexes
Hi,
We are trying to use function based indexes in our application but it looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and populate
this table with 10 rows and created function based index on upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster than index in
this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
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: optimizer not using function based indexes
you don't need the upper(ename) in your where clause... that forces oracle to
NOT use an index
|+-
|| |
|| |
|| Harvinder.Singh@met|
|| ratech.com |
|| |
|| 05/09/2002 04:18 PM|
|| Please respond to |
|| ORACLE-L |
|| |
|+-
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: optimizer not using function|
| based indexes|
|
Hi,
We are trying to use function based indexes in our application but it looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and populate
this table with 10 rows and created function based index on upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster than
index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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: optimizer not using function based indexes
that's why we are using function based indexes...
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 4:49 PM
To: Multiple recipients of list ORACLE-L
you don't need the upper(ename) in your where clause... that forces oracle to
NOT use an index
|+-
|| |
|| |
|| Harvinder.Singh@met|
|| ratech.com |
|| |
|| 05/09/2002 04:18 PM|
|| Please respond to |
|| ORACLE-L |
|| |
|+-
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: optimizer not using function|
| based indexes|
|
Hi,
We are trying to use function based indexes in our application but it looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and populate
this table with 10 rows and created function based index on upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster than
index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
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: optimizer not using function based indexes
Another requirement is that your compatibility init.ora parameter must
be set to 8.1.7 or higher, I believe, but check the doco.
If that doesn't fix it, then trying using a hint to force the use of
that index. If it doesn't work with the hint, then you know you have an
environment problem that is preventing the use of function-based
indexes. If it does work with the hint, then you know that function
based is working but you have to figure out why the optimizer is not
choosing it.
HTH,
Beth
-Original Message-
Sent: Thursday, May 09, 2002 4:19 PM
To: Multiple recipients of list ORACLE-L
Hi,
We are trying to use function based indexes in our application but it
looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and
populate
this table with 10 rows and created function based index on
upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster
than index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seefelt, Beth
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: optimizer not using function based indexes
I thought that's what told the optimizer to use the FBI? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 3:49 PM To: Multiple recipients of list ORACLE-L Subject: Re: optimizer not using function based indexes you don't need the upper(ename) in your where clause... that forces oracle to NOT use an index -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: optimizer not using function based indexes
I respectfully disagree. In order to use a function based index
you do need the upper. If optimizer doesn't pick it up straigt
away, he should give a hand with a hint (/*+ INDEX(alias,index) */)
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 4:49 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: optimizer not using function based indexes
you don't need the upper(ename) in your where clause... that
forces oracle to
NOT use an index
|+-
|| |
|| |
|| Harvinder.Singh@met|
|| ratech.com |
|| |
|| 05/09/2002 04:18 PM|
|| Please respond to |
|| ORACLE-L |
|| |
|+-
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: optimizer not using function|
| based indexes|
|
Hi,
We are trying to use function based indexes in our
application but it looks like
optimizer is not using them.
So we created a sample table emp with 3 columns
empno,ename,sal and populate
this table with 10 rows and created function based index
on upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can
be faster than
index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gogala, Mladen
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: optimizer not using function based indexes
I think the point Rachel made is the query should instead be:
where ename=upper('abc98');
Harvinder Singh wrote:
that's why we are using function based indexes...
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 4:49 PM
To: Multiple recipients of list ORACLE-L
you don't need the upper(ename) in your where clause... that forces oracle to
NOT use an index
|+-
|| |
|| |
|| Harvinder.Singh@met|
|| ratech.com |
|| |
|| 05/09/2002 04:18 PM|
|| Please respond to |
|| ORACLE-L |
|| |
|+-
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: optimizer not using function|
| based indexes|
|
Hi,
We are trying to use function based indexes in our application but it looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and populate
this table with 10 rows and created function based index on upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster than
index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Suzy Vordos
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: optimizer not using function based indexes
and stats have to have been collected on the index and base table as well
just checked the 9i docs... I was wrong you do need the upper on the column, my
bad.. but you need the stats, the compatibility set (as Beth says)
|+--
|| |
|| |
|| Beth.Seefelt@tet|
|| leyusa.com |
|| |
|| 05/09/2002 05:24|
|| PM |
|| Please respond |
|| to ORACLE-L |
|| |
|+--
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: RE: optimizer not using function|
| based indexes|
|
Another requirement is that your compatibility init.ora parameter must
be set to 8.1.7 or higher, I believe, but check the doco.
If that doesn't fix it, then trying using a hint to force the use of
that index. If it doesn't work with the hint, then you know you have an
environment problem that is preventing the use of function-based
indexes. If it does work with the hint, then you know that function
based is working but you have to figure out why the optimizer is not
choosing it.
HTH,
Beth
-Original Message-
Sent: Thursday, May 09, 2002 4:19 PM
To: Multiple recipients of list ORACLE-L
Hi,
We are trying to use function based indexes in our application but it
looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and
populate
this table with 10 rows and created function based index on
upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster
than index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seefelt, Beth
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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: optimizer not using function based indexes
I take it back... the query was correct as originally written. Just
tried similar queries on my database and the FBI was used for:
upper(ename)=upper('abc98')
upper(ename)='ABC98'
It's been a LONG week...
[EMAIL PROTECTED] wrote:
and stats have to have been collected on the index and base table as well
just checked the 9i docs... I was wrong you do need the upper on the column, my
bad.. but you need the stats, the compatibility set (as Beth says)
|+--
|| |
|| |
|| Beth.Seefelt@tet|
|| leyusa.com |
|| |
|| 05/09/2002 05:24|
|| PM |
|| Please respond |
|| to ORACLE-L |
|| |
|+--
|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: RE: optimizer not using function|
| based indexes|
|
Another requirement is that your compatibility init.ora parameter must
be set to 8.1.7 or higher, I believe, but check the doco.
If that doesn't fix it, then trying using a hint to force the use of
that index. If it doesn't work with the hint, then you know you have an
environment problem that is preventing the use of function-based
indexes. If it does work with the hint, then you know that function
based is working but you have to figure out why the optimizer is not
choosing it.
HTH,
Beth
-Original Message-
Sent: Thursday, May 09, 2002 4:19 PM
To: Multiple recipients of list ORACLE-L
Hi,
We are trying to use function based indexes in our application but it
looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and
populate
this table with 10 rows and created function based index on
upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster
than index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seefelt, Beth
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Suzy Vordos
INET: [EMAIL PROTECTED]
Fat
RE: Function based indexes
Sergey, I did a quick test which indicates that no special path is mentioned when using a FBI, instead the execution plan will show that an index is being used, which it would not do if a FBI had not been set up. The example below shows what I mean HTH John SQL create index john_idx1 on john(spid_type); SQL select spid_type from john where upper(substr(spid_type,2,1)) = 'Y' SQL / SPID_TYPE --- Symphony Symphony Symphony Symphony Symphony Symphony Symphony Symphony Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'JOHN' SQL drop index john_idx1; Index dropped. SQL create index john_idx1 on john(upper(substr(spid_type,2,1))); Index created. SQL analyze table john compute statistics; Table analyzed. SQL select spid_type from john where upper(substr(spid_type,2,1)) = 'Y'; SPID_TYPE --- Symphony Symphony Symphony Symphony Symphony Symphony Symphony Symphony 8 rows selected. Execution Plan -- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=7 Card=96 Bytes= 1344) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'JOHN' (Cost=7 Card=96 By *FBI in use tes=1344) 2 1 INDEX (RANGE SCAN) OF 'JOHN_IDX1' (NON-UNIQUE) (Cost=1 C *FBI in use ard=96) -Original Message-From: Babich , Sergey [mailto:[EMAIL PROTECTED]]Sent: 26 February 2002 20:51To: Multiple recipients of list ORACLE-LSubject: Function based indexes Hi, everyone, This may seem very simple to you, but what's the best way to see if a fresh FBI (sorry!) is used during the execution? Are they reported in the same manner to the SQL trace as other ones? Regards, Sergey = This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =
Function based indexes
Hi, everyone, This may seem very simple to you, but what's the best way to see if a fresh FBI (sorry!) is used during the execution? Are they reported in the same manner to the SQL trace as other ones? Regards, Sergey
RE: optimizer function based indexes
Joe, There are certainly some situations where it is advantageous to use an index in conjunction with LIKE and criteria containing a leading %. On a tuning gig not too long ago, I had the need, like you, to use a function based index where criteria was similar to yours -- upper(col1) like '%ABCDE%'. So, your email caught my eye. I am able to duplicate your problem on my test machine (W2K with 8.1.7.0.0 EE and 9.0.1.1.1 EE). Against 8.1.7, I have no problem using a hint to force the use of the function based index even when the criteria has both a leading and trailing % -- e.g. '%12345%'. I cannot, though, force the use of the function based index against 9i, at least in the same test case, when the criteria starts and ends with the % wildcard. If using a bind variable, I could get the index to be used -- and that's to be expected I suppose since knowledge of the value is unknown at parse time. And, any combination of trailing/leading underscore _ has no problem when using literals. And, against 9i, I can force the index to be used if the criteria has a leading % *without* a trailing %. So, only when the value is constrained on both ends by the % do I see the problem (just as you do). For grins, I also performed the test against 9i using a normal index. I could force the index when the criteria contained a leading and trailing %. So, this seems specific to a function based index. I'm going to play around with this a lot more and see if I come up with anything. One of the first things I did and will mention here is the output from a 10053 trace. 9.0.1.1.1: SNIP SINGLE TABLE ACCESS PATH Column: SYS_NC Col#: 3 Table: FOO Alias: FOO SNIP Access path: tsc Resc: 14601 Resp: 14601 Item A Access path: index (no sta/stp keys) SNIP 8.1.7.0.0: SNIP SINGLE TABLE ACCESS PATH Column: SYS_NC Col#: 3 Table: FOO Alias: FOO SNIP Access path: index (scan) SNIP Notice the inclusion of Item A in the 9i trace. Also notice the following line in the trace and the (no sta/stp keys) -- no start/stop keys? So, it looks like in 9i it is still considering a tablescan (tsc) in the section for the SYS_NC column whereas we see nothing like that in the 10053 trace under 8.1.7? In the full 10053 trace under 9i, the index cost was higher. So, even though we specify the hint, it still looks like when considering SYS_NC it also compares against a tablescan and decides to ingore the hint? Do a 10053 trace yourself on your systems and see what you turn up since I only included just a snippet of the traces. Anyway, I've tried things like cranking down the multiblock read count and other parameters, adding 20 large columns to the table, all in an effort to make a table scan more expensive. And I'm continuing to play around with documented (and undocumented) parameters to see what turns up. Though there haven't been many other responses to you email, I wonder if others are looking into this as well. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- Sent: Tuesday, October 02, 2001 3:15 AM To: Multiple recipients of list ORACLE-L hello i've update my database from 8.1.7 release 3 to 9i and now following statement don't use my optimizer hint in 9i (in 8i it works) upper_artikel_kurztext_idx is an function based index select /*+ index_asc(artikel upper_artikel_kurztext_idx */ * from artikel where upper(kurztext) LIKE '%WANN%' when i change the statement it works but i need above statement select /*+ index_asc(artikel upper_artikel_kurztext_idx */ * from artikel where upper(kurztext) LIKE 'WANN%' or select /*+ index_asc(artikel upper_artikel_kurztext_idx */ * from artikel where upper(kurztext) = 'WANN' the following parameters are set in init.ora QUERY_REWRITE_ENABLED=TRUE QUERY_REWRITE_INTEGRITY=TRUSTED OPTIMIZER_MODE = CHOOSE database is analyzed ! any ideas thanx joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins 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).
optimizer function based indexes
hello i've update my database from 8.1.7 release 3 to 9iand now following statement don't use my optimizer hint in 9i (in 8i itworks)upper_artikel_kurztext_idx is an function based indexselect /*+ index_asc(artikel upper_artikel_kurztext_idx */ * from artikelwhere upper(kurztext) LIKE '%WANN%'when i change the statement it works but i need above statementselect /*+ index_asc(artikel upper_artikel_kurztext_idx */ * from artikelwhere upper(kurztext) LIKE 'WANN%' orselect /*+ index_asc(artikel upper_artikel_kurztext_idx */ * from artikelwhere upper(kurztext) = 'WANN'the following parameters are set in init.oraQUERY_REWRITE_ENABLED=TRUEQUERY_REWRITE_INTEGRITY=TRUSTEDOPTIMIZER_MODE = CHOOSEdatabase is analyzed !any ideasthanx joe
Re: optimizer function based indexes
Hi, Have you checked OPTIMIZER_FEATURES_ENABLE? oli [EMAIL PROTECTED] wrote: hello i've update my database from 8.1.7 release 3 to 9i and now following statement don't use my optimizer hint in 9i (in 8i it works) upper_artikel_kurztext_idx is an function based index select /*+ index_asc(artikel upper_artikel_kurztext_idx */ * from artikel where upper(kurztext) LIKE '%WANN%' when i change the statement it works but i need above statement select /*+ index_asc(artikel upper_artikel_kurztext_idx */ * from artikel where upper(kurztext) LIKE 'WANN%' or select /*+ index_asc(artikel upper_artikel_kurztext_idx */ * from artikel where upper(kurztext) = 'WANN' the following parameters are set in init.ora QUERY_REWRITE_ENABLED=TRUE QUERY_REWRITE_INTEGRITY=TRUSTED OPTIMIZER_MODE = CHOOSE database is analyzed ! any ideas thanx joe Content-Type: text/html; charset=iso-8859-1; name=Anhang: 1 Content-Transfer-Encoding: quoted-printable Content-Description: -- Oliver Artelt Oracle Certified DBA cubeoffice GmbH Co.KG # jordanstrasse 7 # 39112 magdeburg telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391 6 11 28 19 email: [EMAIL PROTECTED] # web: http://www.cubeoffice.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Oliver Artelt 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: How to deal with needing function based indexes but stuck with St
Hello Chris, You also can store that name as lowercase like 'scott' and always use where .. person.name like lower( v_name ) || '%' .. When you need to display it, use something like initcap( person.name ). Actually, it depends on whether you need the store exact case entered by user nor not ( ex, surname like DeSoto ).. HTH, Michael www.atelo.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, May 07, 2001 16:33 Hello all, Oracle Standard Edition 8.0.5 How are people handling storing data, such as a name, in the database, and then being able to successfully retrieve it? It's possible that the same name can be stored twice, such as Scott and SCOTT. Normally, I believe that developers would use an upper function based index on the left side of the WHERE equation and a UPPER function on the right side to resolve queries. But since this project is running Standard Edition that doesn't seem to address this problem. So I was curious as to what other DBAs/developers/projects are doing?? TIA!! Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michael Netrusov 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: How to deal with needing function based indexes but stuck wit
Title: RE: How to deal with needing function based indexes but stuck with St -Original Message- From: Grabowy, Chris [mailto:[EMAIL PROTECTED]] Oracle Standard Edition 8.0.5 How are people handling storing data, such as a name, in the database, and then being able to successfully retrieve it? It's possible that the same name can be stored twice, such as Scott and SCOTT. Normally, I believe that developers would use an upper function based index on the left side of the WHERE equation and a UPPER function on the right side to resolve queries. But since this project is running Standard Edition that doesn't seem to address this problem. So I was curious as to what other DBAs/developers/projects are doing?? If you have a column that will be used in a search, you could a) Put a trigger on the column to force it to uppercase or lowercase before the column is inserted or updated; b) If the column needs to be stored exactly as it is entered (sometimes uppercase, sometimes lowercase) you could create a trigger that creates an uppercase or lowercase version of the column in a separate column, and put the index on the separate column. c) You could investigate the context cartridge and see if that would help your situation. (Is that available with the standard edition?) I'm sure other/better ideas will be forthcoming from the list. -- Jacques R. Kilchoer (949) 754-8816 Quest Software, Inc. 8001 Irvine Center Drive Irvine, California 92618 U.S.A. http://www.quest.com
