What does +0 mean after an ORDER BY in Oracle

Question

I am trying to understand what the +0 at the end of this Oracle 9i query means:

SELECT /*+ INDEX (a CODE_ZIP_CODE_IX) */ 
       a.city, 
       a.state, 
       LPAD(a.code,5,0)  ZipCode, 
       b.County_Name     CoName, 
       c.Description     RegDesc, 
       d.Description     RegTypeDesc  
FROM TBL_CODE_ZIP a, 
     TBL_CODE_COUNTY b, 
     TBL_CODE_REGION c, 
     TBL_CODE_REGION_TYPE d  
WHERE a.City = 'LONDONDERRY' 
    AND a.State = 'NH' 
    AND lpad(a.Code,5,0) = '03038' 
    AND a.Region_Type_Code = 1 
    AND b.County(+) = a.County_Code  
    AND b.STATE(+) = a.STATE 
    AND c.Code(+) = a.Region_Code  
    AND d.Code(+) = a.Region_Type_Code  
ORDER BY a.Code +0

Any ideas?

NOTE: I don't think it has to do with ascending or descending since I can't add asc or desc between a.Code and +0 and I can add asc or desc after +0

Answer 1

My guess would be that a.code is a VARCHAR2 containing a numeric string, and the +0 is effectively casting it to a NUMBER so the sort will be numeric rather than alpha

You should be able to add ASC/DESC after the +0

Answer 2

Note: I had deleted this answer, because Mark B was the faster typist. However, I have re-instated it because I think there is some value in demonstrating what may have been the underlying intent of the SQL which Lucas posted.

---

Suppose CODE had been a VARCHAR2 column holding strings of digits (zip codes). The problem is that varchars sort as strings not numbers. Adding a zero to the CODE spawns an implicit cast to number, and hence sorts numerically:

SQL> select id, code
  2  from t72
  3  order by code
  4  /

        ID CODE
---------- -----
         1 1
         2 11
         3 111
         4 12

SQL> select id, code
  2  from t72
  3  order by code+0
  4  /

        ID CODE
---------- -----
         1 1
         2 11
         4 12
         3 111

SQL>

If the stored codes had been left-padded with zeroes then the cast would not have been necessary, as they would sort in numeric order anyway.

As others have observed, using TO_NUMBER() would have been the better choice. The +0 is less obvious than an explicit cast, and it is always good to be clear about intent.

Answer 3

Is there an index on TBL_CODE_ZIP.Code? I've seen queries that add 0 to a number (or '' to a string) in order to force the optimizer to avoid using an index for that part of the query. (Of course, the proper way to avoid using an index is to add an appropriate hint)

Maybe the original writer had a problem where the ORDER BY was being optimized to an index scan, which caused the query to run slower; so they added +0 to force a different access path and do an ordinary sort.

Answer 4

The + 0 was a trick back in the days of the rule based optimizer, which made it impossible to use an index on the numeric column. Similarly, they did a || '' for alphanumeric columns.

For your query, the only conclusion I can reach after inspecting it is that its creator was struggling with the performance. If (that's my assumption) index CODE_ZIP_CODE_IX is an index on TBL_CODE_ZIP(Code), then the query won't use it, even though it is hinted to use it. The creator probably wasn't aware that by using LPAD(a.code,5,0) instead of a.code, the index cannot be used. An order by clause takes its intermediate result set - which resides in memory - and sorts it. No index is needed for that. But with the + 0 it looks like he was thinking to disable it.

So, the tricks that were used were ineffective, and are now only misleading, as you have found out.

Regards, Rob.

PS1: It's better to use LPAD(TO_CHAR(a.code),5,'0') or TO_CHAR(a.code,'fm00009'). Then it is clear what you are doing with the datatype.

PS2: Your query might benefit from using a function based index on LPAD(TO_CHAR(a.code),5,'0'), or whatever expression you use to left pad your zipcode.