From: "Tanel Poder" Newsgroups: comp.databases.oracle.server References: <15c7b652.0303021747.2177140d@posting.google.com> <3E62D0A2.F102BBB4@exesolutions.com> <15c7b652.0303030636.56befc98@posting.google.com> <3e637051$1_2@news.estpak.ee> Subject: Re: (long) index on upper case Date: Mon, 3 Mar 2003 17:27:26 +0200 Lines: 135 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 NNTP-Posting-Host: 80.235.55.58 Message-ID: <3e6373b2_2@news.estpak.ee> X-Trace: news.estpak.ee 1046705074 80.235.55.58 (3 Mar 2003 17:24:34 +0200) X-Complaints-To: usenet@estpak.ee Path: news.easynews.com!newsfeed1.easynews.com!newsfeed2.easynews.com!easynews.com!easynews!newsfeed.news2me.com!newspeer1-gui.server.ntli.net!ntli.net!colt.net!news.tele.dk!news.tele.dk!small.news.tele.dk!newsfeed1.bredband.com!bredband!uio.no!newsfeed1.funet.fi!newsfeeds.funet.fi!newsfeed.uninet.ee!news.ut.ee!news.estpak.ee!not-for-mail Xref: newsfeed1.easynews.com comp.databases.oracle.server:178264 X-Received-Date: Mon, 03 Mar 2003 08:24:56 MST (news.easynews.com) Also, check on optimizer* parameters... See the end of this post, it shows the CBO is quite intelligent :) Tanel. SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production PL/SQL Release 8.1.7.1.0 - Production CORE 8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.1.0 - Production NLSRTL Version 3.4.1.0.0 - Production SQL> select * from x; N ---------- TANEL XERTY XERTY XERTY QWREWR LXLXE 6 rows selected. SQL> create index i on x(upper(n)); Index created. SQL> analyze table x compute statistics; Table analyzed. SQL> analyze index i compute statistics; Index analyzed. SQL> show parameter query NAME TYPE VALUE ------------------------------------ ------- ------------------------------ query_rewrite_enabled boolean TRUE query_rewrite_integrity string TRUSTED SQL> show parameter cost_adj NAME TYPE VALUE ------------------------------------ ------- ------------------------------ optimizer_index_cost_adj integer 100 SQL> set autotrace on explain; SQL> select n from x where upper(n)='TANEL'; N ---------- TANEL Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=12) 1 0 TABLE ACCESS (FULL) OF 'X' (Cost=1 Card=2 Bytes=12) SQL> alter session set optimizer_index_cost_adj=50; Session altered. SQL> select n from x where upper(n)='TANEL'; N ---------- TANEL Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=12) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'X' (Cost=1 Card=2 Bytes= 12) 2 1 INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE) (Cost=1 Card=2) SQL> SQL> select upper(n) from x where upper(n)='TANEL'; UPPER(N) ---------- TANEL Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=12) 1 0 INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE) (Cost=1 Card=2 Byte s=12) SQL> alter session set optimizer_index_cost_adj=100; Session altered. SQL> select upper(n) from x where upper(n)='TANEL'; UPPER(N) ---------- TANEL Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=12) 1 0 INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE) (Cost=1 Card=2 Byte s=12) SQL>