Prev: problem with remote desktop
Next: 10 Reasons Why YOU Should Join Us
From: Shakespeare on 25 Nov 2009 16:04
joel garry schreef:
> On Nov 25, 8:19 am, Mark D Powell <Mark.Powe...(a)hp.com> wrote:
>> On Nov 24, 7:22 pm, m...(a)pixar.com wrote:
>>
>>
>> In you your example why do you have where 1 = 1 ? Kind of unnecessary
>> isn't it.
>
> I have an example pinned up on my cube of an OCI generated code that
> has 48 1=1 statements in it. It would be even more unnecessary for me
> to try to get rid of them. :-)
>
> I'm sure there must be more somewhere in this kind of code, that one
> just happened to catch my eye in EM one day. Doesn't seem to bother
> the optimizer at all.
>
> jg
> --
> @home.com is bogus.
> Death of wikipedia, news at 11. http://news.cnet.com/8301-1023_3-10403467-93.html
>

Most times, these queries are generated by some tool that needs a where
clause anyway, and 'AND's or 'OR's the user specified clauses to it, and
they put in the 1=1 for when a user does not enter any condition..

Shakespeare
(What's in = What's in)
From: Thomas Kellerer on 25 Nov 2009 16:29


mh(a)pixar.com wrote on 25.11.2009 01:22:
> I've got an application being put together with cake/php. It's
> pretty nice, but their data pager does this:
>
> SELECT COUNT(*) AS COUNT
> FROM foo f
> LEFT JOIN bar b
> ON (f.asset_group_id = b.asset_group_id)
> WHERE 1 = 1
>
> Any way possible to speed this up?

If the statement is really that simple than you might get away with a materialized view:

CREATE MATERIALZED VIEW v_foo
ENABLE QUERY REWRITE
SELECT COUNT(*) AS table_count
FROM foo f
LEFT JOIN bar b
ON (f.asset_group_id = b.asset_group_id);

Oracle will see that it can use the view to satisfy your select and all it needs to do is return the single row from the MV.

You need to make sure the view is up-to-date though.

The problem is most probably that you won't be able to declare it as "refresh fast on commit" but it might be worth trying.

Thomas
From: Volker Borchert on 26 Nov 2009 14:22
Shakespeare wrote:
> Most times, these queries are generated by some tool that needs a where
> clause anyway, and 'AND's or 'OR's the user specified clauses to it, and
> they put in the 1=1 for when a user does not enter any condition..

Surprisingly, the optimizer sometimes seems to do a bad job about such
constant clauses. As in "where 1 = 2" resulting in a full table scan...

--

"I'm a doctor, not a mechanic." Dr Leonard McCoy <mccoy(a)ncc1701.starfleet.fed>
"I'm a mechanic, not a doctor." Volker Borchert <v_borchert(a)despammed.com>
From: Shakespeare on 26 Nov 2009 15:20
Volker Borchert schreef:
> Shakespeare wrote:
>> Most times, these queries are generated by some tool that needs a where
>> clause anyway, and 'AND's or 'OR's the user specified clauses to it, and
>> they put in the 1=1 for when a user does not enter any condition..
>
> Surprisingly, the optimizer sometimes seems to do a bad job about such
> constant clauses. As in "where 1 = 2" resulting in a full table scan...
>

Yep. You never know if somewhere/sometime in your query 1 will become
equal to 2..... Better safe than sorry.

Shakespeare
From: Gerard H. Pille on 26 Nov 2009 15:32
Volker Borchert wrote:
> Shakespeare wrote:
>> Most times, these queries are generated by some tool that needs a where
>> clause anyway, and 'AND's or 'OR's the user specified clauses to it, and
>> they put in the 1=1 for when a user does not enter any condition..
>
> Surprisingly, the optimizer sometimes seems to do a bad job about such
> constant clauses. As in "where 1 = 2" resulting in a full table scan...
>

You're kidding, right?
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: problem with remote desktop
Next: 10 Reasons Why YOU Should Join Us