Re: Query and UPPER function

From: Paul Zola <pzola_at_us.oracle.com>
Date: 1996/07/02
Message-ID: <4rc6o8$euc_at_inet-nntp-gw-1.us.oracle.com>#1/1

In <4rburr$ae3_at_inet-nntp-gw-1.us.oracle.com> surman_at_oracle.com (Scott Urman) writes:

} In article <31D93DA2.4E2A_at_wang.com>, John Moriarty <john.moriarty_at_wang.com> writes:

[Summary: the following query is slow on a 300,00 row table]

} |>
} |> Select FIRST_NAME,LAST_NAME,MIDDLE_NAME
} |> From patient
} |> Where UPPER(LAST_NAME) LIKE 'ANDERSON%'
} |> and UPPER(FIRST_NAME) LIKE 'HAROLD%'
} |> Order By LAST_NAME;
} |>
 

}
} This is because the UPPER function disables the index. Any function applied
} to a column will do this (you can verify this by looking at the explain plan).
}
[Summary: Scott suggests using triggers to build a capitalized version of the column so as not to disable the index]
}

There's also the second classic way to perform this query, which is:

   SQL> select FIRST_NAME,LAST_NAME,MIDDLE_NAME

	from patient
        where (
                LAST_NAME like 'AN%' or
                LAST_NAME like 'An%' or
                LAST_NAME like 'aN%' or
                LAST_NAME like 'an%' )
            and upper(LAST_NAME) like 'ANDERSON%';

The portion of the WHERE clause within the parentheses will allow the optimizer to use the index for a range scan, while not affecting the rows returned in the result set. This can dramatically improve the query performance.

        -p

---

Paul Zola Technical Specialist World-Wide Technical Support

---

GCS H--- s:++ g++ au+ !a w+ v++ C+++ UAV++$ UUOC+++$ UHS++++$ P+>++ E-- N++ n+

    W--(+>++)$ M+ V- po- Y+ !5 !j R- G? !tv b++(+++) !D B-- e++ u** h f-->+ r*

---

Disclaimer: 	Opinions and statements are mine, and do not necessarily
		reflect the opinions of Oracle Corporation.

Received on Tue Jul 02 1996 - 00:00:00 CEST