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

Linoleum

Is it a way to force a.Code into a numeric data type by causing an implicit conversion? – Sherleysherline

a.Code is specified as a NUMBER(5) – Linoleum

Things become clearer once the code is laid out properly. – Racketeer

Yeah sorry about that. Thanks to Niessner for formatting appropriately. – Linoleum

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.

Allodial

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

Shaveling

@MarkBaker CODE is a NUMBER(5)... if that helps. However, I was able to specify asc or desc after and it worked! – Linoleum

Seems a strange way to do it - is there an advantage to doing it this way rather than just using to_number(a.code)? – Triviality

@Triviality - shorter to write? Otherwise there's no significant difference (besides formatting control with TO_NUMBER). – Shaveling

@Lucas B - if a.code is already a NUMBER then the +0 is redundant – Shaveling

Can anyone provide a reference to confirm MarkBaker's guess? – Linoleum

@LucasB - what guess are you referring to? – Racketeer

@APC, Mark says he is guessing. I was wondering if anyone had a reference that could CONFIRM his guess. – Linoleum

@Luca B: Mark's guess that a.code is a varchar2 is based on not having access to the definition of TBL_CODE_ZIP.CODE. We, here on StackOverflow, do not have access to your database nor the definitions of the objects within, so we are not going to be able to provide a reference. You said, that CODE is a NUMBER(5), which means his guess was incorrect, you have already provided the counter example.. – Nadler

@Shannon Sorry for any confusion. I meant a reference as in an external source, like Oracle's online documentation, etc... – Linoleum

Answer 3

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.

Racketeer

Would to_number(a.code) have the same effect, or is there a difference with +0? – Triviality

I think to_number would have the same effect and would be more obvious to the reader. – Linoleum

Answer 4

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.

Stabile

Yes, there is an index, called CODE_ZIP_CODE_IX that has a.Code in it, and in this query we are "hinting" to use that index. When I look at it through the Explain Plan comparing with and without +0 the plans are identical. – Linoleum

@Lucas, yes it won't necessarily make a difference now. Alternatively, the developer may have had a mistaken belief that this would make it faster because "it worked for me before" :) – Stabile

I agree, one of my co-workers thought this might be leftover from an implementation for Oracle 8. Thanks for the insight. – Linoleum

Answer 5

Fisrt of all sorry for answer because it is very old question now. However +0 is a hint to your database to ignore the index (if it is on a.Code column) for this specific query,

Some time index make retrieval fast while some time make it very slow depending on optimizer mode of database.

so now you have two options eigther use +0 hint or delete index if it on a.code you will get same speed.

Demetria