Funny Developer Tricks – (substr(cust_id,1,length(:b1))

Ha. This one was a little surprising. I ran across a SQL statement that gets fired off hundreds at a time in rapid succession. Each execution taking several seconds – too long for thousands of executions in a row. The statement looked like this (cleaned up to protect the guilty):

b1 := '10355P034001SGL00066';
b2 := '10355P034001SGL00066';
 
select count(cust_id) 
from customers
where substr(cust_id,1,length(:b1)) = :b2;

What was the developer trying to do? How can we fix it? Your comments are welcomed.

Well it only took Jeremiah a couple of minutes. Yes – it looks like the developer had learned to use the substr function, but not the LIKE operator. Maybe the developer didn’t know you could use the same bind variable twice in the same statement as well. Or maybe the variables could have different values, but that seems unlikely. Anyway, here’s the stats for the before and after.

SYS@FYIDOCS> @fss
Enter value for sql_text: 
Enter value for sql_id: f0n7vkcdhqp3v
 
SQL_ID        PLAN_HASH_VALUE      EXECS ROWS_PROCESSED  AVG_ETIME    AVG_CPU    AVG_PIO      AVG_LIO SQL_TEXT
------------- --------------- ---------- -------------- ---------- ---------- ---------- ------------ ------------------------------------------------------------
f0n7vkcdhqp3v       182380728      9,933           9933       7.68       1.89      83.06       11,627 select count(cust_id) from customers
                                                                                                      where substr(cust_id,1,length(:b1)) = :b2
 
 
 
SYS@FYIDOCS> @fss                   
Enter value for sql_text: %test f0n7vkcdhqp3v_3.sql%
Enter value for sql_id: 
 
SQL_ID        PLAN_HASH_VALUE      EXECS ROWS_PROCESSED  AVG_ETIME    AVG_CPU    AVG_PIO      AVG_LIO SQL_TEXT
------------- --------------- ---------- -------------- ---------- ---------- ---------- ------------ ------------------------------------------------------------
4j1apzncj8dps       611149136          1              1        .00        .00        .00            8 select count(cust_id) from customers
                                                                                                      where cust_id like :b2||'%'

11 Comments

  1. Oradebug says:

    Were they trying to get a count of customers where customer_id like ’10355P034001SGL00066%’?

    Jeremiah

  2. Gary says:

    Depends whether cust_id is indexed and how big the table is and how many queries they are doing.
    It isn’t a nice simple sequence but assuming the cust_id is the primary key and indexed then they could do

    select count(cust_id)
    from customers
    where cust_id like :b2||’%’;

    They’d still need to run it multiple times though.

    They could load all the values into a global temporary table and do

    select gtt.cust_id, count(c.cust_id)
    from gtt left outer join customers c on c.cust_id like gtt.cust_id||’%’;

  3. Riyaj says:

    Kerry

    But, that developer’s solution is very creative though :-) He/She understood the substr and length function too well.
    It reminds me of a customer problem I encountered. Client asked me to tune a long running PL/SQL procedure; code was very complex. All sorts of additions and comparison in a PL/SQL loop, including array processing to store values.
    After about an hour of staring at the code, it dawned on me that developer was trying to implement ‘Group by + sum’ in PL/SQL. Developer didn’t know about group by+sum operator in a SQL statement, of course, in her defense, she got moved from mainframe platform to Oracle just few weeks ago.
    Impressive PL/SQL coding for a beginner though :-)
    Cheers
    Riyaj

  4. osborne says:

    Gary,

    The cust_id was not the primary key, but there was an index on it. Thus the difference in lio’s (11,000 vs 8). The substr function was disabling the index. And yes they apparently have a process that goes through checking a bunch of them that would probably be much more efficiently handled in another fashion as you suggest. I am always suspicious of select count anyway, as this is often followed by a select from the table to get the data if the count is greater than 0. Nothing like doubling the work!

    Kerry

  5. joel garry says:

    Almost makes you wonder if Oracle should figure out a substr(x,1,n) could transform into an indexable form?

  6. osborne says:

    Riyaj,

    Yeah I thought it was creative too. I am actually betting it’s a new developer and they actually don’t know about LIKE. By the way, the bind variable that I tested with (from the parse) didn’t need the like anyway as the column matched the variable exactly, so I’m not sure yet why the substr even got put in. I’ll have to find out next week. ;)

    Kerry

  7. Anonymous says:

    “select count(cust_id)” is even more scary when followed by “insert into customers (cust_id) values (num+1)”… I thought they were trying to fake sequences.

  8. Connor says:

    Might be different now, but “back in the day”, an optimization trick for for

    where col like :b1||’%’

    was to add a clause:

    and substr(col,1,n) = :b1

    but only the cases where ‘n’ was known in advance and could be hard-coded. I think this is from the timeframe of Forms, where there were special optimizations for various lengths of leading “like” predicates because case-insenstive Forms searches were framed along the lines of:

    and col like ‘AA%’ or col like ‘aa%’ or col like ‘Aa%’ or col like ‘aA%’

    so being able to give the optimizer a clue as the length of the leading bind var was important.

  9. [...] Osborne encountered a fairly odd SQL query which performed a LIKE comparison without actually using the LIKE preposition. If there was a SQL [...]

  10. [...] Osborne shares a funny trick one of his developers tried. Without looking at the comments, can you figure out why he did [...]

  11. select count(cust_id) from customers
    where cust_id like :b2||’%’ and :b1 is not null;

Leave a Reply