RE: SQL functions and indexes

From: Gillies, Garry <garry_at_weir.co.uk>
Date: Tue, 28 Nov 2000 12:21:49 -0000
Message-Id: <10694.123043@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C05935.C81E46A0
Content-Type: text/plain;

charset="iso-8859-1"

The use of a function on an indexed column cripples the use of that index (Pre 8.1)

Consider the EMP table with an index on ENAME

SQL> select ename from emp where ename like 'M%';

ENAME

MARTIN
MILLER Execution Plan

       0      SELECT STATEMENT Optimizer=RULE 
       1    0   INDEX (RANGE SCAN) OF 'EMP_N1' (NON-UNIQUE)

2. SQL> select ename from emp where substr(ename,1,1) = 'M';

ENAME

MARTIN
MILLER Execution Plan

       0      SELECT STATEMENT Optimizer=RULE 
       1    0   TABLE ACCESS (FULL) OF 'EMP'

3. SQL> select ename from emp where ename = 'MILLER';

ENAME

MILLER Execution Plan

       0      SELECT STATEMENT Optimizer=RULE 
       1    0   INDEX (RANGE SCAN) OF 'EMP_N1' (NON-UNIQUE)

4. SQL> select ename from emp where ename || '' = 'MILLER';

ENAME

MILLER Execution Plan

       0      SELECT STATEMENT Optimizer=RULE 
       1    0   TABLE ACCESS (FULL) OF 'EMP'

Statements 2 & 4 are functionally identical to statements 1 & 3, but the index is not used because a function is being applied to the column.

As of release 8.1 you can have function based indexes, which complicates matters a little.

Hope This Helps

Garry

Last night doing some study for OCP exam I came across a note I had written in course material for PL/SQL, "SQL functions ignore table indexes and therefore can impact on performance.". So now I'm wondering if this is correct or did I take a confused message. Anyone know?

Sean :)

++++++++++++++++++++++++++++++++++++

All internet traffic to this site is
automatically scanned for viruses
and vandals.

++++++++++++++++++++++++++++++++++++

------_=_NextPart_001_01C05935.C81E46A0
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2651.75">
<TITLE>RE: SQL functions and indexes</TITLE>
</HEAD>
<BODY>

The use of a function on an indexed column cripples =
the use of that index (Pre 8.1)


Consider the EMP table with an index on ENAME


1.  SQL> select ename from emp where ename =
like 'M%'; 


    ENAME 
     ---------- 
     MARTIN 
     MILLER


    Execution Plan 
     =

---------------------------------------------------------- </FONT>

        =
0      SELECT STATEMENT Optimizer=3DRULE =

        =
1    0   INDEX (RANGE SCAN) OF 'EMP_N1' = (NON-UNIQUE) 

 
 

2.  SQL> select ename from emp where =
substr(ename,1,1) =3D 'M'; 


    ENAME 
     ---------- 
     MARTIN 
     MILLER 


    Execution Plan 
     =

---------------------------------------------------------- </FONT>

        =
0      SELECT STATEMENT Optimizer=3DRULE =

        =
1    0   TABLE ACCESS (FULL) OF 'EMP'


3.  SQL> select ename from emp where ename =
=3D 'MILLER'; 


    ENAME 
     ---------- 
     MILLER 


    Execution Plan 
     =

---------------------------------------------------------- </FONT>

4.  SQL> select ename from emp where ename || =
'' =3D 'MILLER'; 


    ENAME 
     ---------- 
     MILLER 


    Execution Plan 
     =

---------------------------------------------------------- </FONT>

        =
0      SELECT STATEMENT Optimizer=3DRULE =

        =
1    0   TABLE ACCESS (FULL) OF 'EMP' 


Statements 2 & 4 are functionally identical to =
statements 1 & 3, but
 the index is not used because a function is being =
applied to the
 column.


As of release 8.1 you can have function based =
indexes, which complicates
 matters a little.


Hope This Helps


Garry


Last night doing some study for OCP exam I came =
across a note I had written
 in course material for PL/SQL, "SQL functions =
ignore table indexes and
 therefore can impact on performance.".  So =
now I'm wondering if this is
 correct or did I take a confused message.  =
Anyone know?


Sean :)


<PRE>++++++++++++++++++++++++++++++++++++
Received on Tue Nov 28 2000 - 06:21:49 CST