Path: news.easynews.com!easynews!hub1.nntpserver.com!telocity-west!TELOCITY!enews.sgi.com!newsfeed.nettuno.it!not-for-mail From: Cristian Veronesi Newsgroups: comp.databases.oracle.server Subject: Re: how to use function-based indexes? Date: Wed, 12 Dec 2001 09:37:34 +0100 Organization: C.R.P.A. S.p.A. (All standard disclaimers applied) Lines: 38 Message-ID: <3C17174E.922A7642@crpa.it> References: <3C161340.D3125449@crpa.it> <3C161C88.D90F4A75@crpa.it> <3c162283$0$230$ed9e5944@reading.news.pipex.net> <3C162538.F77972CB@crpa.it> <3c165eb1$0$561$afc38c87@news.optusnet.com.au> <9v6a6r02drd@drn.newsguy.com> NNTP-Posting-Host: eraclito.crpa.it Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: pinco.nettuno.it 1008146244 18324 212.22.134.33 (12 Dec 2001 08:37:24 GMT) X-Complaints-To: news@nettuno.it NNTP-Posting-Date: 12 Dec 2001 08:37:24 GMT X-Mailer: Mozilla 4.7 [en] (Win95; I) X-Accept-Language: en,pl,ja Xref: easynews comp.databases.oracle.server:127918 X-Received-Date: Wed, 12 Dec 2001 01:59:44 MST (news.easynews.com) Thomas Kyte wrote: > also to use function based indexes -- we HAVE to use CBO. RBO doesn't = "see" > them. I'd suspect here they are using rbo. I would like to thank Jim, Howard and Thomas for their answers. Some more details: - table ACQUIRENTI has 10904 rows - I tried to issue the following command in order to force optimizer to use CBO: SQL> ALTER SESSION SET OPTIMIZER_MODE =3D all_rows; =2E.. but nothing changed. Moreover, I found the following note in the online doc "Oracle9i Database Performance Guide and Reference": "Note: Even if the parameter OPTIMIZER_MODE is set to RULE, the use of these features [Partitioned tables and indexes, Function-based indexes etc.] enables the CBO." - I tried to set column ACQUIRENTE_RAG_SOC to NOT NULL and I tried to match the function case: nothing changed. Thanks again for your help. Best regards, Cristian -- = Cristian Veronesi ><((((=BA> http://www.crpa.it There are no good wars, with the following exceptions: The American = Revolution, World War II, and the Star Wars Trilogy. (Bart Simpson)