Same SQL; very different runtimes - why?

From: Harold Perry <hperry_at_bbn.com>
Date: 1995/06/19
Message-ID: <3s4b6n$b97_at_info-server.bbn.com>#1/1


Hello,

        I'm a novice and would appreciate your comments regarding a dramatic difference in SQL execution times between myself and a colleague.

        We each have local versions of the same database with about 100k records. When we execute the SQL:

SELECT FIELD1, FIELD2 FROM <TABLE> WHERE FIELD1 LIKE 'AAAAAAA.BBBBBBB.%'

we get very different execution times:

myself (using ORACLE 7.0.13 on an HP 9000 under Unix): < 1 sec

colleague (using ORACLE 6.0.36.5.0 (6.2) on a Vax 76

            series under VAX/VMS v.5.5-2): ~ 2 min

There is a unique index on FIELD1. Each FIELD1 value has the form:

        AAAAAAA.BBBBBBB.NNN subfield1 (AAAAAAA) is constant for each record and always contains the same 7 characters. subfield2 (BBBBBBB) varies and contains 7 characters. subfield3 (NNN) varies and contains 3 characters. Periods separate the subfields; no subfield contains a period.

From my reading of the LIKE operator (p. 3-8) in the SQL Language Reference Manual (ORACLE 7 Server - Dec 92) there shouldn't be any performance benefit from the index because ALL rows begin with the same letter. But it sure looks like the index has a dramatic effect.

What's going on here? Does ORACLE 7.0.13 index similar values more efficiently so LIKE can take advantage of it even when every value has the same 1st 8 characters? Is my SQL manual out of date? Can ORACLE 6.2 on a VAX be that much slower than 7.0.13 on HP/Unix?

        thanks for your assistance,
        -Harold
Received on Mon Jun 19 1995 - 00:00:00 CEST

Original text of this message