Stephane,
Thank you very much for explanation.
Now I
understood the case.
+0 is nothing at statment.
It is used only for
Oracle does not use de index.
Very good.
Best
Regards.
Eriovaldo
--------- Mensagem Original --------
De:
"Stephane Faroult" <sfaroult@oriolecorp.com>
Para: "Multiple
recipients of list ORACLE-L" <ORACLE-L@fatcity.com>
Assunto: RE:
Re: Resource for index
Data: 15/10/03 05:29
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@siteplanet.com.br>
>To: Multiple recipients of list
ORACLE-L
><ORACLE-L@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@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@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@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).
-------------------------------------------------Esta
mensagem foi enviada por Siteplanet WebMail.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Eca
INET: eca@siteplanet.com.br
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@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).
Received on Wed Oct 15 2003 - 07:44:41 CDT
