Same SQL; very different runtimes - why?
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, -HaroldReceived on Mon Jun 19 1995 - 00:00:00 CEST