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

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.

Answer 2

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