Hi!
I think where num_field + 0 = 1 suppresses index, not the other way.
Tanel.
> > im assuming this is an old 'trick' from RBO days. Alot of these are
still floating around. Wasnt there one when you
> wanted to force a full table scan you would go
> >
> > where num_field = 1 + 0;
> >
> > the +
You can find it a lot of codes on oracle finanacials where they used 'index
supression' on perfectly indexed columns. I don't know why.
That type of index supression spending 15 to 20 minutes and when it was
modifed it took less than a second as column was perfectly indexed. Those
codes were atl
- Original Message -
> im assuming this is an old 'trick' from RBO days. Alot of these are still floating
> around. Wasnt there one when you
wanted to force a full table scan you would go
>
> where num_field = 1 + 0;
>
> the + 0 forced the full table scan? I was on a project earlier this
Well we use SQR too ... but it smells of COBOL and BASIC ... plus its use (maybe
it is us) of gloal variables stinks.
Somehow I never liked it, whatever reports I wrote in my development days, I wrote a
pl/sql package to do processign and then use SQR to retrieve from temp table and print
Raj,
What's wrong with SQR? I used it for a few years and found it great.
Especially for batch processing (both report writing and batch updating).
We chose it when we ran away from Cobol about 9 years ago. At the time, our
choices were Oracle Rpt (can you say RPG?), the very first version of Or
]>
Sent by: [EMAIL PROTECTED]
10/21/2003 10:24 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: WHERE 1 = 1 (any info on this)
Raj,
What's wrong with SQR? I used it fo
I have seen people use this in pseudo-dynamic sql in a beast called SQR. Especially
when they have code like ...
select ...
union
select ...
union
select ...
union
select ... limit to your imagination.
Lot of people think that using 1=1 in absence of a valid limiting condition will let
them e
Or just use NULL as a return predicate in FGAC; it will be the same effect,
but may be slightly better.
Arup Nanda
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 9:49 AM
> I'm using that predicate in FGAC functions so
from both
sides for numbers or dates etc.
Of course I'm not building views, but simple dynamic select statements.
Gints
> -Original Message-
> From: Hately, Mike (LogicaCMG) [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 21, 2003 4:30 PM
> To: Multiple recipients of list O
Title: RE: WHERE 1 = 1 (any info on this)
BUT... a lot of the SQL still has rule hints in it for Apps... even though it is touted to be using the CBO extensively...
April Wells
Oracle DBA/Oracle Apps DBA
Corporate Systems
Amarillo Texas
I'm using that predicate in FGAC functions so the function always return
something.
An FGAC metalink note advise to this .
Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187
[EMAIL PROTECTED] <
<[EMAIL PROTECTED]>
> Date: 2003/10/21 Tue AM 08:45:02 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: WHERE 1 = 1 (any info on this)
>
> AFAIK, it has two origins:
>
> 1- To make editing text sql scripts easier,
> in terms of a
OK, here's the interesting thing. At 8.1.6.3 with optimizer_mode=rule the
statement I'm looking at returns very different explain plans depending on
whether the "1=1" clause is included. It's a complicated join and the
explain plans are over 300 lines so it's not easy to see what's happening.
I'll
I agree with Connor that it has zero effect on the optimizer, and that it
most likely was to make it easier to modify the view dynamically.
Either that or the author was some anal-nut-job that liked to see it for
some bizarre reason.
Tom Mercadante
Oracle Certified Professional
-Original Me
AFAIK, it has two origins:
1- To make editing text sql scripts easier,
in terms of alignment between the predicates.
2- As a trick to enhance the WHERE clause in
blocks in Forms, very old versions.
Other than that never heard it made the slightest
difference to the CBO.
Cheers
Nuno Souto
[EMAIL
=)
Agreed but they're altering a custom Oracle Applications view. They want to
change as little as possible but they need to understand why the author of
the view (who's now long gone) coded it in this way so that they know which
pitfall they're trying to avoid.
I think we may end up with a rewr
Quoting Pete Sharman's signature from my memory:
Controlling developers is harder then herding cats.
Tell your developers that there exists a thing called "CBO" which has
something called "hints" that can alleviate the need for such ridiculous
WHERE clauses.
On 2003.10.21 07:44, "Hately, Mike (Logi
I'm pretty sure the optimizer can pick up 1=1 anyway
and ignore it as an always-true condition - so you get
no benefit.
The most common cause I've seen for 1=1 is so when
developers are building dynamic where-clause, they
don't need to worry about adding 'where' versus 'and'
to the sql string bein
I've never used it with a monster query but I do use it's inverse all the
time.
Create table test1 as (select * from test2 where 1=0);
This creates a copy of the structure of the original. Leave off the where
and you also copy the data. Maybe they just want to make explicit that
which is impli
>
> il-rom.com To: Multiple
> recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Sent by: cc:
[EMAIL PROT
I sometime used this kind of aproach when I dinamically build the WHERE
expression.
Something like this:
where_condition := ' WHERE 1 = 1 '
LOOP
where_condition := ' AND ' || new_condition
END LOOP
The point is if there are any condition to add the first condition is always
true so it won't af
Hi Ed
It's useful in when generating DML on the fly and you
don't have the inclination or the ability to determine
if there are any other user-supplied (or otherwise generated)
predicates or where/when the last predicate has been
found. i.e.
1) Generate first part of Select (select foo from ba
23 matches
Mail list logo