Re: Forms performance [need help!]

From: Steven P. Muench <smuench_at_oracle.com>
Date: 09 Oct 1994 03:19:16 GMT
Message-ID: <SMUENCH.94Oct8201916_at_doh.oracle.com>

A quick test by any doubters at the sql plus command line will prove that there's nothing "shaky" about this proposition:

(1) CREATE INDEX TEST ON DEPT(DNAME);
(2) ALTER SESSION SET SQL_TRACE TRUE;
(3) SELECT DNAME FROM DEPT

      WHERE ( (DNAME LIKE 'ac%' OR
               DNAME LIKE 'AC%' OR
               DNAME LIKE 'Ac%' OR
               DNAME LIKE 'aC%')    AND
               UPPER(DNAME) = 'ACCOUNTING');

(4) TKPROF the log file produced with the EXPLAIN=SCOTT/TIGER option.

You'll see it uses the index.

Bottom line, compared to just using the predicate UPPER(DNAME) = 'ACCOUNTING' and presuming you *need* the declarative capability of letting your users query in a case- insensitive way, it's faster.

--

Steve Muench         Email: smuench_at_oracle.com
Forms Development      CIS: 73404,676
Product Manager
Oracle Corporation

Received on Sun Oct 09 1994 - 04:19:16 CET