RE: Re: Resource for index

Sorry for having made myself misunderstood. + 0 does, in your case, NOTHING AT ALL. Nothing bad, nothing good. You can keep it or remove it.

A + 0 can do something when :

1. Put after the name of a number column, eg WHERE SAL + 0 = 2000
2. And if there is an index on this column.

(none of the conditions is verified in your case).

What does it then? It prevents Oracle from using the index. The reason is that indexes are trees which are descended using comparisons (if the key value is bigger than the value I am looking for, I recursively search this subtree, otherwise I recursively search this other subtree). Oracle is not smart enough to see whether an operation changes the order (eg * -1) or doesn't (eg * 1 or + 0). In doubt, it takes the safest option and says that the operation will screw up comparisons (a technical term) and doesn't use the index. The same occurs with type conversions, because 'smaller' means quite different things for a string, a number or a date. It can be useful when you are using the rule-based optimizer (RBO) and have an index created for reasons unrelated to performance (a foreign key column, for instance - or when you are running queries against tables of a canned application).

HTH, SF

>----- ------- Original Message ------- -----
>From: "Eriovaldo Andrietta" <eca_at_siteplanet.com.br>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Tue, 14 Oct 2003 17:44:25
>
>Dear Stephane,
>
>At first, my apologize by the word "Resource", it
>was the first word that I
>found out and wrote it.
>
>The case is that this command +0 , you can put it
>in the sentence line of
>where and the sintaxe is correct.
>Oracle executes the statment and don't gives error
>message.
>
>My doubt is : What this +0 does in the sql ?
>Imagine a column unindexed that you can not create
>a index, because it will
>do a bad performance in all the system, you can use
>+0 that it will break
>the index . It is what i eard.
>But i am searching about this +0 and until now, i
>didn't find out nothing
>about it.
>For me it doesn't exist.
>
>Did you do a test ?
>You can put it at the statment that will run ,
>normaly.
>
>And more, if you to use explain plan the table
>(unindexed) will have a FULL
>TABLE SCAN.
>
>So, I ask :
>What is this +0 ? Resource or not , it is my hard
>doubt.
>
>Regards
>Eriovaldo
>
>
>
>
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L"
><ORACLE-L_at_fatcity.com>
>Sent: Monday, October 13, 2003 4:04 PM
>
>
>> > Friends :
>> >
>> > I have a part of statment as below :
>> >
>> > select column1 from table1
>> > where column1 = v_parameter;
>> >
>> > The column1 is not indexed, so the table
>full scan will be executed.
>> > Right?
>> > Well, I would like to know if anyone knows
>the resource :
>> >
>> > select column1 from table1
>> > where column1 = v_parameter +0
>> >
>> > What does it mean (+0).
>> > What kind of resource is it ?
>> > What does it do ?
>> >
>> > Best Regards
>> >
>> > Eriovaldo
>>
>> Eriovaldo,
>>
>> Don't understand too well what you mean by
>'resource'. One of the
>> most cunning performance tips of yore was to
>either concatenate an empty
>> string to a string or date column or add a zero
>to a number column to
>> prevent the RBO from using an index on this
>column (BTW it's a trick
>> which can still be useful at times) when this
>index was known to be not
>> very selective.
>> Quite obviously, applying this to the constant
>part of an equality is
>> totally pointless. And since the column is
>unindexed, it wouldn't have
>> much utility either on the other side of the
>equality.
>> Another case of ill-digested and ill-understood
>trick.
>>
>> --
>> Regards,
>>
>> Stephane Faroult
>> Oriole Software
>> --

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).