Re: (long) index on upper case

From: Tanel Poder <tanel_at_@peldik.com>
Date: Mon, 3 Mar 2003 17:27:26 +0200
Message-ID: <3e6373b2_2@news.estpak.ee>

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> Received on Mon Mar 03 2003 - 09:27:26 CST