Wild Card/Generic retrieval

[Darren Reely]

I found this interesting enough to 'play' a little with it. I choose the 
main table in our Oracle test system containing 1,482,455 rows.  I 
altered the queries for my known data and limited them to counting.

In this scenario it is obvious to avoid MATCHPATTERN. I've never been 
impressed with its performance. Most of the options proposed came in at 
around 53 seconds, MATHCPATTERN added 2 minutes to those times!

Not listed here is a combination of the Oracle SQL select as a cursor 
for a new version of the quiz file. It took 2 minutes 47 seconds.

At the end of each select statement are the timings.

Oracle SQL - approximately 4 seconds

select count(*)
from lot_step_summ
where lot_id like '%A';

OR

select count(*)
from (select * from lot_step_summ where lot_id like '%A');


  COUNT(*)
----------
    145878


Powerhouse times

set report nolimit
access lot_step_summ in mfg
;select lot_step_summ if MATCHPATTERN(TRUNCATE(lot_id),"@A")  ;; 2 
minutes 53 seconds
;select lot_step_summ If 0 = index(lot_id,"A") - size(truncate(lot_id)) 
  ;; 53 seconds fetches too many records, no surprise
;select lot_step_summ if 'A' = REVERSE(TRUNCATE( lot_id ))[1:1]  ;; 53 
seconds
select if "A" = lot_id[ SIZE(TRUNCATE(lot_id)) : 
1]                           ;; 56 seconds
define mycount  num = 1
final footing mycount subtotal
go

09/10/2003                            mfg                              
PAGE   1

   MYCOUNT
  (SUBTOTAL)

    145878


Darren