RE: Function based indexes

From: Sandeep Kurliye <Sandeep_at_almoayyedintl.com.bh>
Date: Fri, 12 Jul 2002 00:08:18 -0800
Message-ID: <F001.00496115.20020712000818@fatcity.com>

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_cursor types.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: jkstill_at_cybcon.com

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: ListGuru_at_fatcity.com (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: Sandeep_at_almoayyedintl.com.bh

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: ListGuru_at_fatcity.com (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).

Received on Fri Jul 12 2002 - 03:08:18 CDT