Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsgate.cistron.nl!skynet.be!newspost001!tjb!not-for-mail Reply-To: "Matthias Hoys" From: "Matthias Hoys" Newsgroups: comp.databases.oracle.server References: <1120696686.956598.55860@f14g2000cwb.googlegroups.com> <1120710314.897520.39550@g14g2000cwa.googlegroups.com> Subject: Re: Function based index not being used Date: Thu, 7 Jul 2005 19:43:38 +0200 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2527 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 X-RFC2646: Format=Flowed; Original Lines: 79 Message-ID: <42cd69cb$0$26041$ba620e4c@news.skynet.be> Organization: -= Belgacom Usenet Service =- NNTP-Posting-Host: c8e1b766.news.skynet.be X-Trace: 1120758219 news.skynet.be 26041 81.244.98.198:1217 X-Complaints-To: usenet-abuse@skynet.be Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:246785 wrote in message news:1120710314.897520.39550@g14g2000cwa.googlegroups.com... > > > bsandell@gmail.com wrote: >> I am trying to create a function based index to speed up a query, but >> the index doesn't seem to be used. The user defined function is based >> on a java stored procedure, I'm not sure if that could be the problem? >> Also, the function is not owned by the user creating the index, but the >> function is executable by public. >> >> Any ideas why the index is not used? Thanks. >> >> -- Recreate the function as deterministic so it can be used in a >> function based index. >> >> CREATE OR REPLACE FUNCTION myFunction(inData VARCHAR2, ...) >> RETURN RAW >> DETERMINISTIC >> AS >> LANGUAGE java >> NAME 'com....myFunction (java.lang.String ...) return byte[]'; >> / >> >> >> -- Create table with sample data >> create table indxtest as select * from all_objects where rownum <= >> 1000; >> >> -- Create function-based index >> create index indxtest_func_idx2 on >> indxtest(otherUser.myFunction(object_name, ...)); >> >> -- try to select using the function based index >> select * from indxtest where otherUser.myFunction(object_name, ...) = >> 'aaa'; >> >> no rows selected >> >> Execution Plan >> ---------------------------------------------------------- >> 0 SELECT STATEMENT Optimizer=CHOOSE >> 1 0 TABLE ACCESS (FULL) OF 'INDXTEST' > > Function-based indexes require the CBO, which requires statistics on > your table. In the absence of statistics the CBO will make a 'guess' > as to what those statistics might be; usually this 'guess' is not the > best. You also have but 1000 rows in your table, and I'll take a guess > that optimizer_index_cost_adj has not been touched and remains at its > default of 100. At that value the cost of an index scan is roughly the > same as the cost of a full table scan. > > You need to compute statistics on your table, and adjust the > optimizer_index_cost_adj initialization parameter to a value less than > 100; I use a value of 15. Once this parameter is set you'll need to > restart the database for it to take effect. > > I expect once you have current statistics you'll see this index being > used. You'll need to keep them current by scheduling one of the > dbms_stats procedures through dbms_job. This has been discussed many > times in this newsgroup so you should search for any of the numerous > threads on this topic. > > I hope this helps. > > > David Fitzjarrell > You also need the QUERY REWRITE privilege and QUERY_REWRITE_ENABLED=TRUE in init.ora ... HTH Matthias Hoys