It's an interesting thought. Since the beginning, the ARGetListEntry()
call has returned two things: (1) the Entry ID and (2) the Short
Description field, by default. You can change number 2, of course to
some combination of 128 characters, as long as you don't refer to a long
char, diary field, etc.

Sooo.... I'm not sure I understand what you're saying. Should we
return (1) the Entry ID and (2) the Entry ID? Or (1) the Entry ID and
(2) null? And why does it matter, again? I'm not getting it...

After all, in clients, the ARGetListEntry() call is followed up
immediately by one or more ARGetEntry() calls. I can't quite see the
point of adding the Request ID/Entry ID to the second part of the
returned arguments... Time to get more coffee.

--Tim

Axton wrote:
> Rick:
> I am working with Oracle 9.2.0.6 and these results are from that version.
>
> Tim:
> Is it less to do more, meaning that executing the select statement
> will be a cheaper than not executing a sql statement? Since you are
> such a passionate guy, what are your thoughts on the footnote in the
> original message?
>
>>*** I want to push the idea that the results list for forms should
>>always be set to only FID 1 unless it is required for either (1) pick
>>lists for multiple matches or (2) direct end user interaction (and no,
>>that does not include admin only interaction for things like
>>configuration data).
>
>
> James:
> The short description field is the default results list value if one
> is not defined.
>
> Axton
>
> On 7/20/05, Rick Cook wrote:
>
>>Axton,
>>
>>To add a bit of info, it was stated by someone that only Oracle below versions 9i patch 4 run the table scan. The other DBMS' and more current versions of Oracle handle it with indexes as appropriate.
>>
>>Rick
>>
>>
>>
>>From: Action Request System discussion list(ARSList) on behalf of Axton
>>Sent: Wed 7/20/2005 11:08 AM
>>To: arslist@ARSLIST.ORG
>>Subject: Push Fields with 1=0 in 'Push Fields If'
>>
>>
>>
>>I want to half way dispel some myths that were going around at RUG
>>this year. There were some presentations that indicated a 1=0 in a
>>push fields action always results in a table scan. Let me shed a
>>little light on this.
>>
>>- Background
>>- garbage form with ~4k rows
>>- ~20 columns/field with data
>>- Only index on this form is the system generate FID index
>>- Default-Order-By is enabled in this server
>>
>>- Fact 1: The presence of any value in the 'Push Field If' qual bar of
>>a push fields action causes remedy to execute a sql statement to
>>verify the presence of a record matching that condition, even if it is
>>0=1. Whether this results in a table scan or not is convered on the
>>next point.
>>- Proof for fact 1: If the push fields qual is blank, no select
>>statement is sent to the db. Here is a select statement generated if
>>1=0 is in the push fields qual (assume that no results list is
>>defined):
>>
>>SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>
>>Execution Plan
>>----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097)
>> 1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
>> 2 1 FILTER
>> 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957 Bytes=83097)
>>
>>
>>- Fact 2: The presence of 1=0 in the push fields qual does not always
>>result in a table scan.
>>- Proof for fact 2: If the results list of the form only contains
>>indexed columns, then an index scan is performed instead of a table
>>scan. Assume the results list on the form now contains only the
>>entry-id. The resulting sql select statement yeilds the following:
>>
>>SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>
>>Execution Plan
>>----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312)
>> 1 0 FILTER
>> 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
>>Bytes=63312)
>>
>>
>>Since the entry-id field always has an index, the above sql statement
>>did not trigger a full table scan. It only triggered an INDEX (FULL
>>SCAN) OF 'IT775' (UNIQUE).
>>
>>*** I want to push the idea that the results list for forms should
>>always be set to only FID 1 unless it is required for either (1) pick
>>lists for multiple matches or (2) direct end user interaction (and no,
>>that does not include admin only interaction for things like
>>configuration data).
>>
>>
>>Now as to whether the presence of 1=0 always results in a table scan;
>>technically it does not. More importantly though, is it a bad
>>practice to use 1=0? Well, it costs more than no select regardless of
>>whether it causes a table scan or an index scan.
>>
>>Axton
>>
>>
>>UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>(Support: mailto:support@arslist.org)
>>
>>
>>
>>
>>
>>
>>UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>(Support: mailto:support@arslist.org)
>>
>
>
>


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

The Oracle optimizer *says* it did a full table scan, but it's a lying,
lazy S.O.B. What it meant to say was, "If I had run a query in this
case it would have generated a full table scan."

--Tim

Rick Cook wrote:
> Tim/Axton
>
> One example to try is to have a Filter look for DB.RequestID on
> Submit. It will always be false, since the ID isn't set until after
> submit. But, Oracle will try it anyway, and scan the table in the
> attempt to find what cannot be found. I agree that not all
> optimizers are that dumb, but from what I've heard, that one, at
> least in that instance, is.
>
> Rick
>
>
>
> From: Action Request System discussion list(ARSList) on behalf of Tim
> Widowfield Sent: Wed 7/20/2005 11:58 AM To: arslist@ARSLIST.ORG
> Subject: Re: Push Fields with 1=0 in 'Push Fields If'
>
>
>
> Man, talk about the thread that won't go away... Look, database
> optimizers aren't all that smart, but they certainly aren't stupid.
>
> Think of it this way. Suppose you go to your local library. You
> walk up to the librarian at the front desk and say, "Give me all the
> books you have where the author's name does not equal the author's
> name."
>
> She answers immediately, "We don't have any."
>
> You ask, "Did you search through your entire card catalog?"
>
> She rolls her eyes and says, "Of course." Then she calls up her
> friends and tells them about the idiot who came to bother her today.
>
> Well, (1 = 0) is the same thing. It is always false. The database
> looks at it and says, "False," and returns no rows. Notice: It
> doesn't have to prove the where clause is false -- by *definition* it
> is false.
>
> Now if you ask the Oracle optimizer (on any other RDBMS, YMMV), it'll
> say, "Oh, yeah, sure. I ran a full table scan." All that means is
> it didn't have an indexed table to look at. Well, no kidding!
> Axton, to your point, where every column is indexed, that's the
> optimizer for you. It knew every column was indexed so it reported
> that it went to the index. But I assure you that it didn't.
>
> Can the database prove that (1 = 0) is false by searching any table
> in the known universe? No. *Should* the database attempt to prove
> that (1 = 0) is false by searching any table in the known universe?
> Bueller...Bueller.....ANYONE?
>
> The key is to find out whether the database truly executed a search.
> Did it hit the disk and really scan the table? The answer is "no."
> Check for the number of blocks read. Zero!
>
>
> --Tim
>
>
> Axton wrote:
>
>> I want to half way dispel some myths that were going around at RUG
>> this year. There were some presentations that indicated a 1=0 in a
>> push fields action always results in a table scan. Let me shed a
>> little light on this.
>>
>> - Background - garbage form with ~4k rows - ~20 columns/field with
>> data - Only index on this form is the system generate FID index -
>> Default-Order-By is enabled in this server
>>
>> - Fact 1: The presence of any value in the 'Push Field If' qual bar
>> of a push fields action causes remedy to execute a sql statement to
>> verify the presence of a record matching that condition, even if
>> it is 0=1. Whether this results in a table scan or not is convered
>> on the next point. - Proof for fact 1: If the push fields qual is
>> blank, no select statement is sent to the db. Here is a select
>> statement generated if 1=0 is in the push fields qual (assume that
>> no results list is defined):
>>
>> SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>
>> Execution Plan
>> ---------------------------------------------------------- 0
>> SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097) 1
>> 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097) 2 1
>> FILTER 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
>> Bytes=83097)
>>
>>
>> - Fact 2: The presence of 1=0 in the push fields qual does not
>> always result in a table scan. - Proof for fact 2: If the results
>> list of the form only contains indexed columns, then an index scan
>> is performed instead of a table scan. Assume the results list on
>> the form now contains only the entry-id. The resulting sql select
>> statement yeilds the following:
>>
>> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>
>> Execution Plan
>> ---------------------------------------------------------- 0
>> SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312) 1
>> 0 FILTER 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE)
>> (Cost=26 Card=3957 Bytes=63312)
>>
>>
>> Since the entry-id field always has an index, the above sql
>> statement did not trigger a full table scan. It only triggered an
>> INDEX (FULL SCAN) OF 'IT775' (UNIQUE).
>>
>> *** I want to push the idea that the results list for forms should
>> always be set to only FID 1 unless it is required for either (1)
>> pick lists for multiple matches or (2) direct end user interaction
>> (and no, that does not include admin only interaction for things
>> like configuration data).
>>
>>
>> Now as to whether the presence of 1=0 always results in a table
>> scan; technically it does not. More importantly though, is it a
>> bad practice to use 1=0? Well, it costs more than no select
>> regardless of whether it causes a table scan or an index scan.
>>
>> Axton
>>
>>
>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>> (Support: mailto:support@arslist.org)
>>
>
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>
>
>
>
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

I'll make a note of it. ;-) I should stick to "Caddy Shack" and
"Animal House" references.

The time it takes to run the query is non-zero. But that does not prove
whether there was a full table scan.

--Tim


Brian Goralczyk wrote:
> **
> Tim,
>
> Two points, I look at the time it took for the query. And
> Bueller...Bueller was when he was calling role. Anyone....Anyone....
> now that is in reference to a question he asked. Sorry, just had to
> point it out.
>
> Brian
>
> */Tim Widowfield /* wrote:
>
> Man, talk about the thread that won't go away... Look, database
> optimizers aren't all that smart, but they certainly aren't stupid.
>
> Think of it this way. Suppose you go to your local library. You walk
> up to the librarian at the front desk and say, "Give me all the books
> you have where the author's name does not equal the author's name."
>
> She answers immediately, "We don't have any."
>
> You ask, "Did you search through your entire card catalog?"
>
> She rolls her eyes and says, "Of course." Then she calls up her friends
> and tells them about the idiot who came to bother her today.
>
> Well, (1 = 0) is the same thing. It is always false. The database
> looks at it and says, "False," and returns no rows. Notice: It doesn't
> have to prove the where clause is false -- by *definition* it is false.
>
> Now if you ask the Oracle optimizer (on any other RDBMS, YMMV), it'll
> say, "Oh, yeah, sure. I ran a full table scan." All that means is it
> didn't have an indexed table to look at. Well, no kidding! Axton, to
> your point, where every column is indexed, that's the optimizer for you.
> It knew every column was indexed so it reported that it went to the
> index. But I assure you that it didn't.
>
> Can the database prove that (1 = 0) is false by searching any table in
> the known universe? No. *Should* the database attempt to prove that (1
> = 0) is false by searching any table in the known universe?
> Bueller...Bueller.....ANYONE?
>
> The key is to find out whether the database truly executed a search.
> Did it hit the disk and really scan the table? The answer is "no."
> Check for the number of blocks read. Zero!
>
>
> --Tim
>
>
> Axton wrote:
> > I want to half way dispel some myths that were going around at RUG
> > this year. There were some presentations that indicated a 1=0 in a
> > push fields action always results in a table scan. Let me shed a
> > little light on this.
> >
> > - Background
> > - garbage form with ~4k rows
> > - ~20 columns/field with data
> > - Only index on this form is the system generate FID index
> > - Default-Order-By is enabled in this server
> >
> > - Fact 1: The presence of any value in the 'Push Field If' qual
> bar of
> > a push fields action causes remedy to execute a sql statement to
> > verify the presence of a record matching that condition, even if
> it is
> > 0=1. Whether this results in a table scan or not is convered on the
> > next point.
> > - Proof for fact 1: If the push fields qual is blank, no select
> > statement is sent to the db. Here is a select statement generated if
> > 1=0 is in the push fields qual (assume that no results list is
> > defined):
> >
> > SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097)
> > 1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
> > 2 1 FILTER
> > 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957 Bytes=83097)
> >
> >
> > - Fact 2: The presence of 1=0 in the push fields qual does not always
> > result in a table scan.
> > - Proof for fact 2: If the results list of the form only contains
> > indexed columns, then an index scan is performed instead of a table
> > scan. Assume the results list on the form now contains only the
> > entry-id. The resulting sql select statement yeilds the following:
> >
> > SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312)
> > 1 0 FILTER
> > 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
> > Bytes=63312)
> >
> >
> > Since the entry-id field always has an index, the above sql statement
> > did not trigger a full table scan. It only triggered an INDEX (FULL
> > SCAN) OF 'IT775' (UNIQUE).
> >
> > *** I want to push the idea that the results list for forms should
> > always be set to only FID 1 unless it is required for either (1) pick
> > lists for multiple matches or (2) direct end user interaction
> (and no,
> > that does not include admin only interaction for things like
> > configuration data).
> >
> >
> > Now as to whether the presence of 1=0 always results in a table scan;
> > technically it does not. More importantly though, is it a bad
> > practice to use 1=0? Well, it costs more than no select regardless of
> > whether it causes a table scan or an index scan.
> >
> > Axton
> >
> >
>
> > UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> > (Support: mailto:support@arslist.org)
> >
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>
> This posting was submitted via the Web
> interface


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Axton,

I evaded your first question, didn't I? Well, yes, running no select
statement at all is cheaper than running a select statement that is
always false. It might be measured in nanoseconds, but it it's larger
than zero. Entering no Push If qualification at all will cause a new
record to be pushed in all conditions, *AND* there's no database scan.

Was this true in 4.0 and 4.5? My memory fails... I thought the initial
implementation of Push Fields required a query of some kind, which is
where that oddity of (1 = 0) came from.


--Tim


Axton wrote:
> Rick: I am working with Oracle 9.2.0.6 and these results are from
> that version.
>
> Tim: Is it less to do more, meaning that executing the select
> statement will be a cheaper than not executing a sql statement?
> Since you are such a passionate guy, what are your thoughts on the
> footnote in the original message?
>
>> *** I want to push the idea that the results list for forms should
>> always be set to only FID 1 unless it is required for either (1)
>> pick lists for multiple matches or (2) direct end user interaction
>> (and no, that does not include admin only interaction for things
>> like configuration data).
>
>
> James: The short description field is the default results list value
> if one is not defined.
>
> Axton
>
> On 7/20/05, Rick Cook wrote:
>
>> Axton,
>>
>> To add a bit of info, it was stated by someone that only Oracle
>> below versions 9i patch 4 run the table scan. The other DBMS' and
>> more current versions of Oracle handle it with indexes as
>> appropriate.
>>
>> Rick
>>
>>
>>
>> From: Action Request System discussion list(ARSList) on behalf of
>> Axton Sent: Wed 7/20/2005 11:08 AM To: arslist@ARSLIST.ORG Subject:
>> Push Fields with 1=0 in 'Push Fields If'
>>
>>
>>
>> I want to half way dispel some myths that were going around at RUG
>> this year. There were some presentations that indicated a 1=0 in a
>> push fields action always results in a table scan. Let me shed a
>> little light on this.
>>
>> - Background - garbage form with ~4k rows - ~20 columns/field with
>> data - Only index on this form is the system generate FID index -
>> Default-Order-By is enabled in this server
>>
>> - Fact 1: The presence of any value in the 'Push Field If' qual bar
>> of a push fields action causes remedy to execute a sql statement to
>> verify the presence of a record matching that condition, even if
>> it is 0=1. Whether this results in a table scan or not is convered
>> on the next point. - Proof for fact 1: If the push fields qual is
>> blank, no select statement is sent to the db. Here is a select
>> statement generated if 1=0 is in the push fields qual (assume that
>> no results list is defined):
>>
>> SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>
>> Execution Plan
>> ---------------------------------------------------------- 0
>> SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097) 1
>> 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097) 2 1
>> FILTER 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
>> Bytes=83097)
>>
>>
>> - Fact 2: The presence of 1=0 in the push fields qual does not
>> always result in a table scan. - Proof for fact 2: If the results
>> list of the form only contains indexed columns, then an index scan
>> is performed instead of a table scan. Assume the results list on
>> the form now contains only the entry-id. The resulting sql select
>> statement yeilds the following:
>>
>> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>
>> Execution Plan
>> ---------------------------------------------------------- 0
>> SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312) 1
>> 0 FILTER 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE)
>> (Cost=26 Card=3957 Bytes=63312)
>>
>>
>> Since the entry-id field always has an index, the above sql
>> statement did not trigger a full table scan. It only triggered an
>> INDEX (FULL SCAN) OF 'IT775' (UNIQUE).
>>
>> *** I want to push the idea that the results list for forms should
>> always be set to only FID 1 unless it is required for either (1)
>> pick lists for multiple matches or (2) direct end user interaction
>> (and no, that does not include admin only interaction for things
>> like configuration data).
>>
>>
>> Now as to whether the presence of 1=0 always results in a table
>> scan; technically it does not. More importantly though, is it a
>> bad practice to use 1=0? Well, it costs more than no select
>> regardless of whether it causes a table scan or an index scan.
>>
>> Axton
>>
>>
>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>> (Support: mailto:support@arslist.org)
>>
>>
>>
>>
>>
>>
>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>> (Support: mailto:support@arslist.org)
>>
>
>
>


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

**
While that is true, I find that if I run the two possible queries, and look at the time difference, one will always stand out.

In reference to one of your other emails...one of the reasons for the 1=0 was that on a system that had wildcard searches turned off, have no criteria would cause problems. And 1=0 was quicker than referencing any field. I don't know if that has changed in any newer version as I haven't taken to time to test it.

Just my thoughts.

Brian

Tim Widowfield wrote:

I'll make a note of it. ;-) I should stick to "Caddy Shack" and
"Animal House" references.

The time it takes to run the query is non-zero. But that does not prove
whether there was a full table scan.

--Tim


Brian Goralczyk wrote:
> **
> Tim,
>
> Two points, I look at the time it took for the query. And
> Bueller...Bueller was when he was calling role. Anyone....Anyone....
> now that is in reference to a question he asked. Sorry, just had to
> point it out.
>
> Brian
>
> */Tim Widowfield /* wrote:
>
> Man, talk about the thread that won't go away... Look, database
> optimizers aren't all that smart, but they certainly aren't stupid.
>
> Think of it this way. Suppose you go to your local library. You walk
> up to the librarian at the front desk and say, "Give me all the books
> you have where the author's name does not equal the author's name."
>
> She answers immediately, "We don't have any."
>
> You ask, "Did you search through your entire card catalog?"
>
> She rolls her eyes and says, "Of course." Then she calls up her friends
> and tells them about the idiot who came to bother her today.
>
> Well, (1 = 0) is the same thing. It is always false. The database
> looks at it and says, "False," and returns no rows. Notice: It doesn't
> have to prove the where clause is false -- by *definition* it is false.
>
> Now if you ask the Oracle optimizer (on any other RDBMS, YMMV), it'll
> say, "Oh, yeah, sure. I ran a full table scan." All that means is it
> didn't have an indexed table to look at. Well, no kidding! Axton, to
> your point, where every column is indexed, that's the optimizer for you.
> It knew every column was indexed so it reported that it went to the
> index. But I assure you that it didn't.
>
> Can the database prove that (1 = 0) is false by searching any table in
> the known universe? No. *Should* the database attempt to prove that (1
> = 0) is false by searching any table in the known universe?
> Bueller...Bueller.....ANYONE?
>
> The key is to find out whether the database truly executed a search.
> Did it hit the disk and really scan the table? The answer is "no."
> Check for the number of blocks read. Zero!
>
>
> --Tim
>
>
> Axton wrote:
> > I want to half way dispel some myths that were going around at RUG
> > this year. There were some presentations that indicated a 1=0 in a
> > push fields action always results in a table scan. Let me shed a
> > little light on this.
> >
> > - Background
> > - garbage form with ~4k rows
> > - ~20 columns/field with data
> > - Only index on this form is the system generate FID index
> > - Default-Order-By is enabled in this server
> >
> > - Fact 1: The presence of any value in the 'Push Field If' qual
> bar of
> > a push fields action causes remedy to execute a sql statement to
> > verify the presence of a record matching that condition, even if
> it is
> > 0=1. Whether this results in a table scan or not is convered on the
> > next point.
> > - Proof for fact 1: If the push fields qual is blank, no select
> > statement is sent to the db. Here is a select statement generated if
> > 1=0 is in the push fields qual (assume that no results list is
> > defined):
> >
> > SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097)
> > 1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
> > 2 1 FILTER
> > 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957 Bytes=83097)
> >
> >
> > - Fact 2: The presence of 1=0 in the push fields qual does not always
> > result in a table scan.
> > - Proof for fact 2: If the results list of the form only contains
> > indexed columns, then an index scan is performed instead of a table
> > scan. Assume the results list on the form now contains only the
> > entry-id. The resulting sql select statement yeilds the following:
> >
> > SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312)
> > 1 0 FILTER
> > 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
> > Bytes=63312)
> >
> >
> > Since the entry-id field always has an index, the above sql statement
> > did not trigger a full table scan. It only triggered an INDEX (FULL
> > SCAN) OF 'IT775' (UNIQUE).
> >
> > *** I want to push the idea that the results list for forms should
> > always be set to only FID 1 unless it is required for either (1) pick
> > lists for multiple matches or (2) direct end user interaction
> (and no,
> > that does not include admin only interaction for things like
> > configuration data).
> >
> >
> > Now as to whether the presence of 1=0 always results in a table scan;
> > technically it does not. More importantly though, is it a bad
> > practice to use 1=0? Well, it costs more than no select regardless of
> > whether it causes a table scan or an index scan.
> >
> > Axton
> >
> >
>
> > UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> > (Support: mailto:support@arslist.org)
> >
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>
> This posting was submitted via the Web
> interface


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)


This posting was submitted via the Web interface

**

Axton:

That is interesting. Thanks for the information.

Also, if I remember my Oracle training correctly, if you run a query that is like this

Select # from dual if 1=0;

Oracle will send an error message stating no records could be found. Maybe the problem is in the logic that Remedy uses to determine what to do and could be better build.

Something on the line of:

If the Run if, Set if or Push if qualification is 1=0 then always run the No Matches or Else Action rather than send it to the database.

Of course that may present a programming nightmare, but most of us already use 1=0 or 1=1 to force either a no-match or always matches scenario.

What do you think of this?

James McKenzie
Remedy Engineer
C-E LCMC HQISEC, US Army
L-3 GSI


-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton
Sent: Wednesday, July 20, 2005 12:24 PM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=0 in 'Push Fields If'


Rick:
I am working with Oracle 9.2.0.6 and these results are from that version.

Tim:
Is it less to do more, meaning that executing the select statement will be a cheaper than not executing a sql statement? Since you are such a passionate guy, what are your thoughts on the footnote in the original message?

> *** I want to push the idea that the results list for forms should
> always be set to only FID 1 unless it is required for either (1) pick
> lists for multiple matches or (2) direct end user interaction (and no,
> that does not include admin only interaction for things like
> configuration data).

James:
The short description field is the default results list value if one is not defined.

Axton

On 7/20/05, Rick Cook wrote:
> Axton,
>
> To add a bit of info, it was stated by someone that only Oracle below
> versions 9i patch 4 run the table scan. The other DBMS' and more
> current versions of Oracle handle it with indexes as appropriate.
>
> Rick
>
>
>
> From: Action Request System discussion list(ARSList) on behalf of
> Axton
> Sent: Wed 7/20/2005 11:08 AM
> To: arslist@ARSLIST.ORG
> Subject: Push Fields with 1=0 in 'Push Fields If'
>
>
>
> I want to half way dispel some myths that were going around at RUG
> this year. There were some presentations that indicated a 1=0 in a
> push fields action always results in a table scan. Let me shed a
> little light on this.
>
> - Background
> - garbage form with ~4k rows
> - ~20 columns/field with data
> - Only index on this form is the system generate FID index
> - Default-Order-By is enabled in this server
>
> - Fact 1: The presence of any value in the 'Push Field If' qual bar of
> a push fields action causes remedy to execute a sql statement to
> verify the presence of a record matching that condition, even if it is
> 0=1. Whether this results in a table scan or not is convered on the
> next point.
> - Proof for fact 1: If the push fields qual is blank, no select
> statement is sent to the db. Here is a select statement generated if
> 1=0 is in the push fields qual (assume that no results list is
> defined):
>
> SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097)
> 1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
> 2 1 FILTER
> 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957 Bytes=83097)
>
>
> - Fact 2: The presence of 1=0 in the push fields qual does not always
> result in a table scan.
> - Proof for fact 2: If the results list of the form only contains
> indexed columns, then an index scan is performed instead of a table
> scan. Assume the results list on the form now contains only the
> entry-id. The resulting sql select statement yeilds the following:
>
> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312)
> 1 0 FILTER
> 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
> Bytes=63312)
>
>
> Since the entry-id field always has an index, the above sql statement
> did not trigger a full table scan. It only triggered an INDEX (FULL
> SCAN) OF 'IT775' (UNIQUE).
>
> *** I want to push the idea that the results list for forms should
> always be set to only FID 1 unless it is required for either (1) pick
> lists for multiple matches or (2) direct end user interaction (and no,
> that does not include admin only interaction for things like
> configuration data).
>
>
> Now as to whether the presence of 1=0 always results in a table scan;
> technically it does not. More importantly though, is it a bad
> practice to use 1=0? Well, it costs more than no select regardless of
> whether it causes a table scan or an index scan.
>
> Axton
>
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>
>
>
>
>
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>


--
Axton Grams
AxtonGrams<åt>gmailcom


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

This posting was submitted via the Web interface

There were no physical reads:

SQL> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC;
no rows selected

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312)
1 0 FILTER
2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
Bytes=63312)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
159 bytes sent via SQL*Net to client
270 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

The results list of only entry-id causes any GLE to only have to read
an indexed value without a bunch of (possibly) irrelevant data. For
example, if I have a table field with columns x,y, and z; why do you
also need to also fetch columns a, b, and c? The same applies to all
set fields actions, query menus, and so on. Is the tax for the
physical read of unneeded data negligible when you are potentially
increasing the amount of data read by an average percentage factor of
maybe 5-15 percent?

Axton

On 7/20/05, Tim Widowfield wrote:
> The Oracle optimizer *says* it did a full table scan, but it's a lying,
> lazy S.O.B. What it meant to say was, "If I had run a query in this
> case it would have generated a full table scan."
>
> --Tim
>
> Rick Cook wrote:
> > Tim/Axton
> >
> > One example to try is to have a Filter look for DB.RequestID on
> > Submit. It will always be false, since the ID isn't set until after
> > submit. But, Oracle will try it anyway, and scan the table in the
> > attempt to find what cannot be found. I agree that not all
> > optimizers are that dumb, but from what I've heard, that one, at
> > least in that instance, is.
> >
> > Rick
> >
> >
> >
> > From: Action Request System discussion list(ARSList) on behalf of Tim
> > Widowfield Sent: Wed 7/20/2005 11:58 AM To: arslist@ARSLIST.ORG
> > Subject: Re: Push Fields with 1=0 in 'Push Fields If'
> >
> >
> >
> > Man, talk about the thread that won't go away... Look, database
> > optimizers aren't all that smart, but they certainly aren't stupid.
> >
> > Think of it this way. Suppose you go to your local library. You
> > walk up to the librarian at the front desk and say, "Give me all the
> > books you have where the author's name does not equal the author's
> > name."
> >
> > She answers immediately, "We don't have any."
> >
> > You ask, "Did you search through your entire card catalog?"
> >
> > She rolls her eyes and says, "Of course." Then she calls up her
> > friends and tells them about the idiot who came to bother her today.
> >
> > Well, (1 = 0) is the same thing. It is always false. The database
> > looks at it and says, "False," and returns no rows. Notice: It
> > doesn't have to prove the where clause is false -- by *definition* it
> > is false.
> >
> > Now if you ask the Oracle optimizer (on any other RDBMS, YMMV), it'll
> > say, "Oh, yeah, sure. I ran a full table scan." All that means is
> > it didn't have an indexed table to look at. Well, no kidding!
> > Axton, to your point, where every column is indexed, that's the
> > optimizer for you. It knew every column was indexed so it reported
> > that it went to the index. But I assure you that it didn't.
> >
> > Can the database prove that (1 = 0) is false by searching any table
> > in the known universe? No. *Should* the database attempt to prove
> > that (1 = 0) is false by searching any table in the known universe?
> > Bueller...Bueller.....ANYONE?
> >
> > The key is to find out whether the database truly executed a search.
> > Did it hit the disk and really scan the table? The answer is "no."
> > Check for the number of blocks read. Zero!
> >
> >
> > --Tim
> >
> >
> > Axton wrote:
> >
> >> I want to half way dispel some myths that were going around at RUG
> >> this year. There were some presentations that indicated a 1=0 in a
> >> push fields action always results in a table scan. Let me shed a
> >> little light on this.
> >>
> >> - Background - garbage form with ~4k rows - ~20 columns/field with
> >> data - Only index on this form is the system generate FID index -
> >> Default-Order-By is enabled in this server
> >>
> >> - Fact 1: The presence of any value in the 'Push Field If' qual bar
> >> of a push fields action causes remedy to execute a sql statement to
> >> verify the presence of a record matching that condition, even if
> >> it is 0=1. Whether this results in a table scan or not is convered
> >> on the next point. - Proof for fact 1: If the push fields qual is
> >> blank, no select statement is sent to the db. Here is a select
> >> statement generated if 1=0 is in the push fields qual (assume that
> >> no results list is defined):
> >>
> >> SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
> >>
> >> Execution Plan
> >> ---------------------------------------------------------- 0
> >> SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097) 1
> >> 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097) 2 1
> >> FILTER 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
> >> Bytes=83097)
> >>
> >>
> >> - Fact 2: The presence of 1=0 in the push fields qual does not
> >> always result in a table scan. - Proof for fact 2: If the results
> >> list of the form only contains indexed columns, then an index scan
> >> is performed instead of a table scan. Assume the results list on
> >> the form now contains only the entry-id. The resulting sql select
> >> statement yeilds the following:
> >>
> >> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
> >>
> >> Execution Plan
> >> ---------------------------------------------------------- 0
> >> SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312) 1
> >> 0 FILTER 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE)
> >> (Cost=26 Card=3957 Bytes=63312)
> >>
> >>
> >> Since the entry-id field always has an index, the above sql
> >> statement did not trigger a full table scan. It only triggered an
> >> INDEX (FULL SCAN) OF 'IT775' (UNIQUE).
> >>
> >> *** I want to push the idea that the results list for forms should
> >> always be set to only FID 1 unless it is required for either (1)
> >> pick lists for multiple matches or (2) direct end user interaction
> >> (and no, that does not include admin only interaction for things
> >> like configuration data).
> >>
> >>
> >> Now as to whether the presence of 1=0 always results in a table
> >> scan; technically it does not. More importantly though, is it a
> >> bad practice to use 1=0? Well, it costs more than no select
> >> regardless of whether it causes a table scan or an index scan.
> >>
> >> Axton
> >>
> >>
> >> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> >> (Support: mailto:support@arslist.org)
> >>
> >
> >
> >
> > UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> > (Support: mailto:support@arslist.org)
> >
> >
> >
> >
> >
> >
> > UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> > (Support: mailto:support@arslist.org)
> >
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>


--
Axton Grams
AxtonGrams<åt>gmailcom


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

I want to half way dispel some myths that were going around at RUG
this year. There were some presentations that indicated a 1=0 in a
push fields action always results in a table scan. Let me shed a
little light on this.

- Background
- garbage form with ~4k rows
- ~20 columns/field with data
- Only index on this form is the system generate FID index
- Default-Order-By is enabled in this server

- Fact 1: The presence of any value in the 'Push Field If' qual bar of
a push fields action causes remedy to execute a sql statement to
verify the presence of a record matching that condition, even if it is
0=1. Whether this results in a table scan or not is convered on the
next point.
- Proof for fact 1: If the push fields qual is blank, no select
statement is sent to the db. Here is a select statement generated if
1=0 is in the push fields qual (assume that no results list is
defined):

SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097)
1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957 Bytes=83097)


- Fact 2: The presence of 1=0 in the push fields qual does not always
result in a table scan.
- Proof for fact 2: If the results list of the form only contains
indexed columns, then an index scan is performed instead of a table
scan. Assume the results list on the form now contains only the
entry-id. The resulting sql select statement yeilds the following:

SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312)
1 0 FILTER
2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
Bytes=63312)


Since the entry-id field always has an index, the above sql statement
did not trigger a full table scan. It only triggered an INDEX (FULL
SCAN) OF 'IT775' (UNIQUE).

*** I want to push the idea that the results list for forms should
always be set to only FID 1 unless it is required for either (1) pick
lists for multiple matches or (2) direct end user interaction (and no,
that does not include admin only interaction for things like
configuration data).


Now as to whether the presence of 1=0 always results in a table scan;
technically it does not. More importantly though, is it a bad
practice to use 1=0? Well, it costs more than no select regardless of
whether it causes a table scan or an index scan.

Axton


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

On the same topic, you should profile and see what happens when you have a filter that fires on both Submit and Modify where the Run If qualification has something similar to 'field' != 'DB.field'

The same kind of table/index scan issue arises on submit. A query of "SELECT [fields] FROM T WHERE C1 IS NULL" is run.

At least that is what I have observed in our 5.1.2 & Oracle 9 environment. :|

-Scott


-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton
Sent: Wednesday, July 20, 2005 11:09 AM
To: arslist@ARSLIST.ORG
Subject: Push Fields with 1=0 in 'Push Fields If'

I want to half way dispel some myths that were going around at RUG
this year. There were some presentations that indicated a 1=0 in a
push fields action always results in a table scan. Let me shed a
little light on this.

- Background
- garbage form with ~4k rows
- ~20 columns/field with data
- Only index on this form is the system generate FID index
- Default-Order-By is enabled in this server

- Fact 1: The presence of any value in the 'Push Field If' qual bar of
a push fields action causes remedy to execute a sql statement to
verify the presence of a record matching that condition, even if it is
0=1. Whether this results in a table scan or not is convered on the
next point.
- Proof for fact 1: If the push fields qual is blank, no select
statement is sent to the db. Here is a select statement generated if
1=0 is in the push fields qual (assume that no results list is
defined):

SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097)
1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957 Bytes=83097)


- Fact 2: The presence of 1=0 in the push fields qual does not always
result in a table scan.
- Proof for fact 2: If the results list of the form only contains
indexed columns, then an index scan is performed instead of a table
scan. Assume the results list on the form now contains only the
entry-id. The resulting sql select statement yeilds the following:

SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312)
1 0 FILTER
2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
Bytes=63312)


Since the entry-id field always has an index, the above sql statement
did not trigger a full table scan. It only triggered an INDEX (FULL
SCAN) OF 'IT775' (UNIQUE).

*** I want to push the idea that the results list for forms should
always be set to only FID 1 unless it is required for either (1) pick
lists for multiple matches or (2) direct end user interaction (and no,
that does not include admin only interaction for things like
configuration data).


Now as to whether the presence of 1=0 always results in a table scan;
technically it does not. More importantly though, is it a bad
practice to use 1=0? Well, it costs more than no select regardless of
whether it causes a table scan or an index scan.

Axton


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

**

Axton:

It is quite interesting that the 'no results' query tries to retrieve the Short Defination filed (ID 8) whereas the 'retrieve only Entry ID' query does not.

James McKenzie
Remedy Engineer
C-E LCMC HQISEC, US Army
L-3 GSI

-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton
Sent: Wednesday, July 20, 2005 11:09 AM
To: arslist@ARSLIST.ORG
Subject: Push Fields with 1=0 in 'Push Fields If'


I want to half way dispel some myths that were going around at RUG this year. There were some presentations that indicated a 1=0 in a push fields action always results in a table scan. Let me shed a little light on this.

- Background
- garbage form with ~4k rows
- ~20 columns/field with data
- Only index on this form is the system generate FID index
- Default-Order-By is enabled in this server

- Fact 1: The presence of any value in the 'Push Field If' qual bar of a push fields action causes remedy to execute a sql statement to verify the presence of a record matching that condition, even if it is 0=1. Whether this results in a table scan or not is convered on the next point.

- Proof for fact 1: If the push fields qual is blank, no select statement is sent to the db. Here is a select statement generated if 1=0 is in the push fields qual (assume that no results list is

defined):

SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097)
1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957 Bytes=83097)


- Fact 2: The presence of 1=0 in the push fields qual does not always result in a table scan.
- Proof for fact 2: If the results list of the form only contains indexed columns, then an index scan is performed instead of a table scan. Assume the results list on the form now contains only the entry-id. The resulting sql select statement yeilds the following:

SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312)
1 0 FILTER
2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
Bytes=63312)


Since the entry-id field always has an index, the above sql statement did not trigger a full table scan. It only triggered an INDEX (FULL

SCAN) OF 'IT775' (UNIQUE).

*** I want to push the idea that the results list for forms should always be set to only FID 1 unless it is required for either (1) pick lists for multiple matches or (2) direct end user interaction (and no, that does not include admin only interaction for things like configuration data).


Now as to whether the presence of 1=0 always results in a table scan; technically it does not. More importantly though, is it a bad practice to use 1=0? Well, it costs more than no select regardless of whether it causes a table scan or an index scan.

Axton


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

This posting was submitted via the Web interface

Axton,

To add a bit of info, it was stated by someone that only Oracle below versions 9i patch 4 run the table scan. The other DBMS' and more current versions of Oracle handle it with indexes as appropriate.

Rick



From: Action Request System discussion list(ARSList) on behalf of Axton
Sent: Wed 7/20/2005 11:08 AM
To: arslist@ARSLIST.ORG
Subject: Push Fields with 1=0 in 'Push Fields If'



I want to half way dispel some myths that were going around at RUG
this year. There were some presentations that indicated a 1=0 in a
push fields action always results in a table scan. Let me shed a
little light on this.

- Background
- garbage form with ~4k rows
- ~20 columns/field with data
- Only index on this form is the system generate FID index
- Default-Order-By is enabled in this server

- Fact 1: The presence of any value in the 'Push Field If' qual bar of
a push fields action causes remedy to execute a sql statement to
verify the presence of a record matching that condition, even if it is
0=1. Whether this results in a table scan or not is convered on the
next point.
- Proof for fact 1: If the push fields qual is blank, no select
statement is sent to the db. Here is a select statement generated if
1=0 is in the push fields qual (assume that no results list is
defined):

SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097)
1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957 Bytes=83097)


- Fact 2: The presence of 1=0 in the push fields qual does not always
result in a table scan.
- Proof for fact 2: If the results list of the form only contains
indexed columns, then an index scan is performed instead of a table
scan. Assume the results list on the form now contains only the
entry-id. The resulting sql select statement yeilds the following:

SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312)
1 0 FILTER
2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
Bytes=63312)


Since the entry-id field always has an index, the above sql statement
did not trigger a full table scan. It only triggered an INDEX (FULL
SCAN) OF 'IT775' (UNIQUE).

*** I want to push the idea that the results list for forms should
always be set to only FID 1 unless it is required for either (1) pick
lists for multiple matches or (2) direct end user interaction (and no,
that does not include admin only interaction for things like
configuration data).


Now as to whether the presence of 1=0 always results in a table scan;
technically it does not. More importantly though, is it a bad
practice to use 1=0? Well, it costs more than no select regardless of
whether it causes a table scan or an index scan.

Axton


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)






UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Man, talk about the thread that won't go away... Look, database
optimizers aren't all that smart, but they certainly aren't stupid.

Think of it this way. Suppose you go to your local library. You walk
up to the librarian at the front desk and say, "Give me all the books
you have where the author's name does not equal the author's name."

She answers immediately, "We don't have any."

You ask, "Did you search through your entire card catalog?"

She rolls her eyes and says, "Of course." Then she calls up her friends
and tells them about the idiot who came to bother her today.

Well, (1 = 0) is the same thing. It is always false. The database
looks at it and says, "False," and returns no rows. Notice: It doesn't
have to prove the where clause is false -- by *definition* it is false.

Now if you ask the Oracle optimizer (on any other RDBMS, YMMV), it'll
say, "Oh, yeah, sure. I ran a full table scan." All that means is it
didn't have an indexed table to look at. Well, no kidding! Axton, to
your point, where every column is indexed, that's the optimizer for you.
It knew every column was indexed so it reported that it went to the
index. But I assure you that it didn't.

Can the database prove that (1 = 0) is false by searching any table in
the known universe? No. *Should* the database attempt to prove that (1
= 0) is false by searching any table in the known universe?
Bueller...Bueller.....ANYONE?

The key is to find out whether the database truly executed a search.
Did it hit the disk and really scan the table? The answer is "no."
Check for the number of blocks read. Zero!


--Tim


Axton wrote:
> I want to half way dispel some myths that were going around at RUG
> this year. There were some presentations that indicated a 1=0 in a
> push fields action always results in a table scan. Let me shed a
> little light on this.
>
> - Background
> - garbage form with ~4k rows
> - ~20 columns/field with data
> - Only index on this form is the system generate FID index
> - Default-Order-By is enabled in this server
>
> - Fact 1: The presence of any value in the 'Push Field If' qual bar of
> a push fields action causes remedy to execute a sql statement to
> verify the presence of a record matching that condition, even if it is
> 0=1. Whether this results in a table scan or not is convered on the
> next point.
> - Proof for fact 1: If the push fields qual is blank, no select
> statement is sent to the db. Here is a select statement generated if
> 1=0 is in the push fields qual (assume that no results list is
> defined):
>
> SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097)
> 1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
> 2 1 FILTER
> 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957 Bytes=83097)
>
>
> - Fact 2: The presence of 1=0 in the push fields qual does not always
> result in a table scan.
> - Proof for fact 2: If the results list of the form only contains
> indexed columns, then an index scan is performed instead of a table
> scan. Assume the results list on the form now contains only the
> entry-id. The resulting sql select statement yeilds the following:
>
> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312)
> 1 0 FILTER
> 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
> Bytes=63312)
>
>
> Since the entry-id field always has an index, the above sql statement
> did not trigger a full table scan. It only triggered an INDEX (FULL
> SCAN) OF 'IT775' (UNIQUE).
>
> *** I want to push the idea that the results list for forms should
> always be set to only FID 1 unless it is required for either (1) pick
> lists for multiple matches or (2) direct end user interaction (and no,
> that does not include admin only interaction for things like
> configuration data).
>
>
> Now as to whether the presence of 1=0 always results in a table scan;
> technically it does not. More importantly though, is it a bad
> practice to use 1=0? Well, it costs more than no select regardless of
> whether it causes a table scan or an index scan.
>
> Axton
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Tim/Axton

One example to try is to have a Filter look for DB.RequestID on Submit. It will always be false, since the ID isn't set until after submit. But, Oracle will try it anyway, and scan the table in the attempt to find what cannot be found. I agree that not all optimizers are that dumb, but from what I've heard, that one, at least in that instance, is.

Rick



From: Action Request System discussion list(ARSList) on behalf of Tim Widowfield
Sent: Wed 7/20/2005 11:58 AM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=0 in 'Push Fields If'



Man, talk about the thread that won't go away... Look, database
optimizers aren't all that smart, but they certainly aren't stupid.

Think of it this way. Suppose you go to your local library. You walk
up to the librarian at the front desk and say, "Give me all the books
you have where the author's name does not equal the author's name."

She answers immediately, "We don't have any."

You ask, "Did you search through your entire card catalog?"

She rolls her eyes and says, "Of course." Then she calls up her friends
and tells them about the idiot who came to bother her today.

Well, (1 = 0) is the same thing. It is always false. The database
looks at it and says, "False," and returns no rows. Notice: It doesn't
have to prove the where clause is false -- by *definition* it is false.

Now if you ask the Oracle optimizer (on any other RDBMS, YMMV), it'll
say, "Oh, yeah, sure. I ran a full table scan." All that means is it
didn't have an indexed table to look at. Well, no kidding! Axton, to
your point, where every column is indexed, that's the optimizer for you.
It knew every column was indexed so it reported that it went to the
index. But I assure you that it didn't.

Can the database prove that (1 = 0) is false by searching any table in
the known universe? No. *Should* the database attempt to prove that (1
= 0) is false by searching any table in the known universe?
Bueller...Bueller.....ANYONE?

The key is to find out whether the database truly executed a search.
Did it hit the disk and really scan the table? The answer is "no."
Check for the number of blocks read. Zero!


--Tim


Axton wrote:
> I want to half way dispel some myths that were going around at RUG
> this year. There were some presentations that indicated a 1=0 in a
> push fields action always results in a table scan. Let me shed a
> little light on this.
>
> - Background
> - garbage form with ~4k rows
> - ~20 columns/field with data
> - Only index on this form is the system generate FID index
> - Default-Order-By is enabled in this server
>
> - Fact 1: The presence of any value in the 'Push Field If' qual bar of
> a push fields action causes remedy to execute a sql statement to
> verify the presence of a record matching that condition, even if it is
> 0=1. Whether this results in a table scan or not is convered on the
> next point.
> - Proof for fact 1: If the push fields qual is blank, no select
> statement is sent to the db. Here is a select statement generated if
> 1=0 is in the push fields qual (assume that no results list is
> defined):
>
> SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097)
> 1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
> 2 1 FILTER
> 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957 Bytes=83097)
>
>
> - Fact 2: The presence of 1=0 in the push fields qual does not always
> result in a table scan.
> - Proof for fact 2: If the results list of the form only contains
> indexed columns, then an index scan is performed instead of a table
> scan. Assume the results list on the form now contains only the
> entry-id. The resulting sql select statement yeilds the following:
>
> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312)
> 1 0 FILTER
> 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
> Bytes=63312)
>
>
> Since the entry-id field always has an index, the above sql statement
> did not trigger a full table scan. It only triggered an INDEX (FULL
> SCAN) OF 'IT775' (UNIQUE).
>
> *** I want to push the idea that the results list for forms should
> always be set to only FID 1 unless it is required for either (1) pick
> lists for multiple matches or (2) direct end user interaction (and no,
> that does not include admin only interaction for things like
> configuration data).
>
>
> Now as to whether the presence of 1=0 always results in a table scan;
> technically it does not. More importantly though, is it a bad
> practice to use 1=0? Well, it costs more than no select regardless of
> whether it causes a table scan or an index scan.
>
> Axton
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)






UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Rick:
I am working with Oracle 9.2.0.6 and these results are from that version.

Tim:
Is it less to do more, meaning that executing the select statement
will be a cheaper than not executing a sql statement? Since you are
such a passionate guy, what are your thoughts on the footnote in the
original message?
> *** I want to push the idea that the results list for forms should
> always be set to only FID 1 unless it is required for either (1) pick
> lists for multiple matches or (2) direct end user interaction (and no,
> that does not include admin only interaction for things like
> configuration data).

James:
The short description field is the default results list value if one
is not defined.

Axton

On 7/20/05, Rick Cook wrote:
> Axton,
>
> To add a bit of info, it was stated by someone that only Oracle below versions 9i patch 4 run the table scan. The other DBMS' and more current versions of Oracle handle it with indexes as appropriate.
>
> Rick
>
>
>
> From: Action Request System discussion list(ARSList) on behalf of Axton
> Sent: Wed 7/20/2005 11:08 AM
> To: arslist@ARSLIST.ORG
> Subject: Push Fields with 1=0 in 'Push Fields If'
>
>
>
> I want to half way dispel some myths that were going around at RUG
> this year. There were some presentations that indicated a 1=0 in a
> push fields action always results in a table scan. Let me shed a
> little light on this.
>
> - Background
> - garbage form with ~4k rows
> - ~20 columns/field with data
> - Only index on this form is the system generate FID index
> - Default-Order-By is enabled in this server
>
> - Fact 1: The presence of any value in the 'Push Field If' qual bar of
> a push fields action causes remedy to execute a sql statement to
> verify the presence of a record matching that condition, even if it is
> 0=1. Whether this results in a table scan or not is convered on the
> next point.
> - Proof for fact 1: If the push fields qual is blank, no select
> statement is sent to the db. Here is a select statement generated if
> 1=0 is in the push fields qual (assume that no results list is
> defined):
>
> SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097)
> 1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
> 2 1 FILTER
> 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957 Bytes=83097)
>
>
> - Fact 2: The presence of 1=0 in the push fields qual does not always
> result in a table scan.
> - Proof for fact 2: If the results list of the form only contains
> indexed columns, then an index scan is performed instead of a table
> scan. Assume the results list on the form now contains only the
> entry-id. The resulting sql select statement yeilds the following:
>
> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312)
> 1 0 FILTER
> 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
> Bytes=63312)
>
>
> Since the entry-id field always has an index, the above sql statement
> did not trigger a full table scan. It only triggered an INDEX (FULL
> SCAN) OF 'IT775' (UNIQUE).
>
> *** I want to push the idea that the results list for forms should
> always be set to only FID 1 unless it is required for either (1) pick
> lists for multiple matches or (2) direct end user interaction (and no,
> that does not include admin only interaction for things like
> configuration data).
>
>
> Now as to whether the presence of 1=0 always results in a table scan;
> technically it does not. More importantly though, is it a bad
> practice to use 1=0? Well, it costs more than no select regardless of
> whether it causes a table scan or an index scan.
>
> Axton
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>
>
>
>
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>


--
Axton Grams
AxtonGrams<åt>gmailcom


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

**
Tim,

Two points, I look at the time it took for the query. And Bueller...Bueller was when he was calling role. Anyone....Anyone.... now that is in reference to a question he asked. Sorry, just had to point it out.

Brian

Tim Widowfield wrote:

Man, talk about the thread that won't go away... Look, database
optimizers aren't all that smart, but they certainly aren't stupid.

Think of it this way. Suppose you go to your local library. You walk
up to the librarian at the front desk and say, "Give me all the books
you have where the author's name does not equal the author's name."

She answers immediately, "We don't have any."

You ask, "Did you search through your entire card catalog?"

She rolls her eyes and says, "Of course." Then she calls up her friends
and tells them about the idiot who came to bother her today.

Well, (1 = 0) is the same thing. It is always false. The database
looks at it and says, "False," and returns no rows. Notice: It doesn't
have to prove the where clause is false -- by *definition* it is false.

Now if you ask the Oracle optimizer (on any other RDBMS, YMMV), it'll
say, "Oh, yeah, sure. I ran a full table scan." All that means is it
didn't have an indexed table to look at. Well, no kidding! Axton, to
your point, where every column is indexed, that's the optimizer for you.
It knew every column was indexed so it reported that it went to the
index. But I assure you that it didn't.

Can the database prove that (1 = 0) is false by searching any table in
the known universe? No. *Should* the database attempt to prove that (1
= 0) is false by searching any table in the known universe?
Bueller...Bueller.....ANYONE?

The key is to find out whether the database truly executed a search.
Did it hit the disk and really scan the table? The answer is "no."
Check for the number of blocks read. Zero!


--Tim


Axton wrote:
> I want to half way dispel some myths that were going around at RUG
> this year. There were some presentations that indicated a 1=0 in a
> push fields action always results in a table scan. Let me shed a
> little light on this.
>
> - Background
> - garbage form with ~4k rows
> - ~20 columns/field with data
> - Only index on this form is the system generate FID index
> - Default-Order-By is enabled in this server
>
> - Fact 1: The presence of any value in the 'Push Field If' qual bar of
> a push fields action causes remedy to execute a sql statement to
> verify the presence of a record matching that condition, even if it is
> 0=1. Whether this results in a table scan or not is convered on the
> next point.
> - Proof for fact 1: If the push fields qual is blank, no select
> statement is sent to the db. Here is a select statement generated if
> 1=0 is in the push fields qual (assume that no results list is
> defined):
>
> SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097)
> 1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
> 2 1 FILTER
> 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957 Bytes=83097)
>
>
> - Fact 2: The presence of 1=0 in the push fields qual does not always
> result in a table scan.
> - Proof for fact 2: If the results list of the form only contains
> indexed columns, then an index scan is performed instead of a table
> scan. Assume the results list on the form now contains only the
> entry-id. The resulting sql select statement yeilds the following:
>
> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312)
> 1 0 FILTER
> 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
> Bytes=63312)
>
>
> Since the entry-id field always has an index, the above sql statement
> did not trigger a full table scan. It only triggered an INDEX (FULL
> SCAN) OF 'IT775' (UNIQUE).
>
> *** I want to push the idea that the results list for forms should
> always be set to only FID 1 unless it is required for either (1) pick
> lists for multiple matches or (2) direct end user interaction (and no,
> that does not include admin only interaction for things like
> configuration data).
>
>
> Now as to whether the presence of 1=0 always results in a table scan;
> technically it does not. More importantly though, is it a bad
> practice to use 1=0? Well, it costs more than no select regardless of
> whether it causes a table scan or an index scan.
>
> Axton
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)


This posting was submitted via the Web interface

Nope, nope, nope. See:

http://www.cs.bris.ac.uk/maintain/OracleDocs/server.816/a76992/sql.htm

Not only was the Oracle optimizer smart enough back in version 8 to not
search a table where the condition is false, it even knew how to do
transitive boolean arithmetic. Quoting from the above web page:

The "WHERE deptno=30" predicate used in the query is
pushed down to the queries in the UNION ALL view. For
a WHERE clause such as "WHERE deptno=10 and deptno=30",
the optimizer applies transitivity rules to generate an
extra predicate of "10=30". This extra predicate is
always false; thus, the table (emp@d10) need not be
accessed.

Now that's pretty cool.

As I alluded to before, run a trace. The optimizer may say "FULL SCAN,"
but it's fibbing.

--Tim

Rick Cook wrote:
> Axton,
>
> To add a bit of info, it was stated by someone that only Oracle below
> versions 9i patch 4 run the table scan. The other DBMS' and more
> current versions of Oracle handle it with indexes as appropriate.
>
> Rick
>
>
>
> From: Action Request System discussion list(ARSList) on behalf of
> Axton Sent: Wed 7/20/2005 11:08 AM To: arslist@ARSLIST.ORG Subject:
> Push Fields with 1=0 in 'Push Fields If'
>
>
>
> I want to half way dispel some myths that were going around at RUG
> this year. There were some presentations that indicated a 1=0 in a
> push fields action always results in a table scan. Let me shed a
> little light on this.
>
> - Background - garbage form with ~4k rows - ~20 columns/field with
> data - Only index on this form is the system generate FID index -
> Default-Order-By is enabled in this server
>
> - Fact 1: The presence of any value in the 'Push Field If' qual bar
> of a push fields action causes remedy to execute a sql statement to
> verify the presence of a record matching that condition, even if it
> is 0=1. Whether this results in a table scan or not is convered on
> the next point. - Proof for fact 1: If the push fields qual is blank,
> no select statement is sent to the db. Here is a select statement
> generated if 1=0 is in the push fields qual (assume that no results
> list is defined):
>
> SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>
> Execution Plan
> ---------------------------------------------------------- 0
> SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097) 1
> 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097) 2 1 FILTER
> 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
> Bytes=83097)
>
>
> - Fact 2: The presence of 1=0 in the push fields qual does not always
> result in a table scan. - Proof for fact 2: If the results list of
> the form only contains indexed columns, then an index scan is
> performed instead of a table scan. Assume the results list on the
> form now contains only the entry-id. The resulting sql select
> statement yeilds the following:
>
> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>
> Execution Plan
> ---------------------------------------------------------- 0
> SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312) 1
> 0 FILTER 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26
> Card=3957 Bytes=63312)
>
>
> Since the entry-id field always has an index, the above sql statement
> did not trigger a full table scan. It only triggered an INDEX (FULL
> SCAN) OF 'IT775' (UNIQUE).
>
> *** I want to push the idea that the results list for forms should
> always be set to only FID 1 unless it is required for either (1) pick
> lists for multiple matches or (2) direct end user interaction (and
> no, that does not include admin only interaction for things like
> configuration data).
>
>
> Now as to whether the presence of 1=0 always results in a table scan;
> technically it does not. More importantly though, is it a bad
> practice to use 1=0? Well, it costs more than no select regardless
> of whether it causes a table scan or an index scan.
>
> Axton
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>
>
>
>
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

I was intrigued by the results list question, so I ran a little test.
Axton is right, I believe. It must be cheaper to select the request id,
even twice, than to select the request id and the short description.

Here is an extract from a merge of api and sql log:
*/+GLEWF ARGetListEntryWithFields -- schema DefaultResultsList
*/SELECT T1246.C1,C8 FROM T1246 ORDER BY 1 ASC
*/-GLEWF OK
*/+GLEWF ARGetListEntryWithFields -- schema ReqidOnlyResultsList
*/SELECT T1248.C1,T1248.C1 FROM T1248 ORDER BY 1 ASC
*/-GLEWF OK

I wondered if I could trim this down so that there are NO results list
fields, by deleting the Request Id from ReqidOnlyResultsList. The
result was that Remedy put the short description back.

ARServer 5.0.1
aruser 5.1.2
Sybase 12.5.3
Solaris 8

Jim O'H.

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Tim Widowfield
Sent: Wednesday, July 20, 2005 12:42 PM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=0 in 'Push Fields If'

It's an interesting thought. Since the beginning, the ARGetListEntry()
call has returned two things: (1) the Entry ID and (2) the Short
Description field, by default. You can change number 2, of course to
some combination of 128 characters, as long as you don't refer to a long
char, diary field, etc.

Sooo.... I'm not sure I understand what you're saying. Should we
return (1) the Entry ID and (2) the Entry ID? Or (1) the Entry ID and
(2) null? And why does it matter, again? I'm not getting it...

After all, in clients, the ARGetListEntry() call is followed up
immediately by one or more ARGetEntry() calls. I can't quite see the
point of adding the Request ID/Entry ID to the second part of the
returned arguments... Time to get more coffee.

--Tim

Axton wrote:
> Rick:
> I am working with Oracle 9.2.0.6 and these results are from that
version.
>
> Tim:
> Is it less to do more, meaning that executing the select statement
> will be a cheaper than not executing a sql statement? Since you are
> such a passionate guy, what are your thoughts on the footnote in the
> original message?
>
>>*** I want to push the idea that the results list for forms should
>>always be set to only FID 1 unless it is required for either (1) pick
>>lists for multiple matches or (2) direct end user interaction (and no,

>>that does not include admin only interaction for things like
>>configuration data).
>
>
> James:
> The short description field is the default results list value if one
> is not defined.
>
> Axton
>
> On 7/20/05, Rick Cook wrote:
>
>>Axton,
>>
>>To add a bit of info, it was stated by someone that only Oracle below
versions 9i patch 4 run the table scan. The other DBMS' and more
current versions of Oracle handle it with indexes as appropriate.
>>
>>Rick
>>
>>
>>
>>From: Action Request System discussion list(ARSList) on behalf of
>>Axton
>>Sent: Wed 7/20/2005 11:08 AM
>>To: arslist@ARSLIST.ORG
>>Subject: Push Fields with 1=0 in 'Push Fields If'
>>
>>
>>
>>I want to half way dispel some myths that were going around at RUG
>>this year. There were some presentations that indicated a 1=0 in a
>>push fields action always results in a table scan. Let me shed a
>>little light on this.
>>
>>- Background
>>- garbage form with ~4k rows
>>- ~20 columns/field with data
>>- Only index on this form is the system generate FID index
>>- Default-Order-By is enabled in this server
>>
>>- Fact 1: The presence of any value in the 'Push Field If' qual bar of

>>a push fields action causes remedy to execute a sql statement to
>>verify the presence of a record matching that condition, even if it is

>>0=1. Whether this results in a table scan or not is convered on the
>>next point.
>>- Proof for fact 1: If the push fields qual is blank, no select
>>statement is sent to the db. Here is a select statement generated if
>>1=0 is in the push fields qual (assume that no results list is
>>defined):
>>
>>SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>
>>Execution Plan
>>----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957
Bytes=83097)
>> 1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
>> 2 1 FILTER
>> 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
Bytes=83097)
>>
>>
>>- Fact 2: The presence of 1=0 in the push fields qual does not always
>>result in a table scan.
>>- Proof for fact 2: If the results list of the form only contains
>>indexed columns, then an index scan is performed instead of a table
>>scan. Assume the results list on the form now contains only the
>>entry-id. The resulting sql select statement yeilds the following:
>>
>>SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>
>>Execution Plan
>>----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957
Bytes=63312)
>> 1 0 FILTER
>> 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
>>Bytes=63312)
>>
>>
>>Since the entry-id field always has an index, the above sql statement
>>did not trigger a full table scan. It only triggered an INDEX (FULL
>>SCAN) OF 'IT775' (UNIQUE).
>>
>>*** I want to push the idea that the results list for forms should
>>always be set to only FID 1 unless it is required for either (1) pick
>>lists for multiple matches or (2) direct end user interaction (and no,

>>that does not include admin only interaction for things like
>>configuration data).
>>
>>
>>Now as to whether the presence of 1=0 always results in a table scan;
>>technically it does not. More importantly though, is it a bad
>>practice to use 1=0? Well, it costs more than no select regardless of

>>whether it causes a table scan or an index scan.
>>
>>Axton
>>
>>
>> UNSUBSCRIBE or access ARSlist Archives at
>>http://www.ARSLIST.org
>>(Support: mailto:support@arslist.org)
>>
>>
>>
>>
>>
>>
>> UNSUBSCRIBE or access ARSlist Archives at
>>http://www.ARSLIST.org
>>(Support: mailto:support@arslist.org)
>>
>
>
>



UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

One other thing to remember is this only relates to Push actions where
the options are set like:

If No Requests Match: Create a New Request
If Any Requests Match: Take No Action

Any other setting of the 2 options will cause a blank 'Push Field If' to
do a query without a where clause to the database.
(i.e. SELECT T25.C1,T25.C1 FROM T25 ORDER BY 1 ASC)

Fred


-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Axton
Sent: Wednesday, July 20, 2005 1:09 PM
To: arslist@ARSLIST.ORG
Subject: Push Fields with 1=0 in 'Push Fields If'

I want to half way dispel some myths that were going around at RUG this
year. There were some presentations that indicated a 1=0 in a push
fields action always results in a table scan. Let me shed a little
light on this.

- Background
- garbage form with ~4k rows
- ~20 columns/field with data
- Only index on this form is the system generate FID index
- Default-Order-By is enabled in this server

- Fact 1: The presence of any value in the 'Push Field If' qual bar of a
push fields action causes remedy to execute a sql statement to verify
the presence of a record matching that condition, even if it is 0=1.
Whether this results in a table scan or not is convered on the next
point.
- Proof for fact 1: If the push fields qual is blank, no select
statement is sent to the db. Here is a select statement generated if
1=0 is in the push fields qual (assume that no results list is
defined):

SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957
Bytes=83097)
1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
Bytes=83097)


- Fact 2: The presence of 1=0 in the push fields qual does not always
result in a table scan.
- Proof for fact 2: If the results list of the form only contains
indexed columns, then an index scan is performed instead of a table
scan. Assume the results list on the form now contains only the
entry-id. The resulting sql select statement yeilds the following:

SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957
Bytes=63312)
1 0 FILTER
2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
Bytes=63312)


Since the entry-id field always has an index, the above sql statement
did not trigger a full table scan. It only triggered an INDEX (FULL
SCAN) OF 'IT775' (UNIQUE).

*** I want to push the idea that the results list for forms should
always be set to only FID 1 unless it is required for either (1) pick
lists for multiple matches or (2) direct end user interaction (and no,
that does not include admin only interaction for things like
configuration data).


Now as to whether the presence of 1=0 always results in a table scan;
technically it does not. More importantly though, is it a bad practice
to use 1=0? Well, it costs more than no select regardless of whether it
causes a table scan or an index scan.

Axton



UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Here's what I'm thinking:

I will start leaving the push field if blank simply to prevent the select
statement from executing. Seems to be the better of the two options, though
maybe not by much.

I think the presenter for the tutorials were wrong in their presentation
when they stated that a 0=1 causes a table scan.

An ORA-xxx error is returned if no data is found, visible through oracle
trace logs. Remedy then interprets this and handles it accordingly. I
think Remedy handled this properly. The programming already exists on the
db side, so why reinvent the wheel.

Axton

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of McKenzie, James J C-E LCMC
HQISEC/L3
Sent: Wednesday, July 20, 2005 4:02 PM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=0 in 'Push Fields If'

**

Axton:

That is interesting. Thanks for the information.

Also, if I remember my Oracle training correctly, if you run a query that is
like this

Select # from dual if 1=0;

Oracle will send an error message stating no records could be found. Maybe
the problem is in the logic that Remedy uses to determine what to do and
could be better build.

Something on the line of:

If the Run if, Set if or Push if qualification is 1=0 then always run the No
Matches or Else Action rather than send it to the database.

Of course that may present a programming nightmare, but most of us already
use 1=0 or 1=1 to force either a no-match or always matches scenario.

What do you think of this?

James McKenzie
Remedy Engineer
C-E LCMC HQISEC, US Army
L-3 GSI


-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Axton
Sent: Wednesday, July 20, 2005 12:24 PM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=0 in 'Push Fields If'


Rick:
I am working with Oracle 9.2.0.6 and these results are from that version.

Tim:
Is it less to do more, meaning that executing the select statement will be a
cheaper than not executing a sql statement? Since you are such a passionate
guy, what are your thoughts on the footnote in the original message?

> *** I want to push the idea that the results list for forms should
> always be set to only FID 1 unless it is required for either (1) pick
> lists for multiple matches or (2) direct end user interaction (and no,
> that does not include admin only interaction for things like
> configuration data).

James:
The short description field is the default results list value if one is not
defined.

Axton

On 7/20/05, Rick Cook wrote:
> Axton,
>
> To add a bit of info, it was stated by someone that only Oracle below
> versions 9i patch 4 run the table scan. The other DBMS' and more
> current versions of Oracle handle it with indexes as appropriate.
>
> Rick
>
>
>
> From: Action Request System discussion list(ARSList) on behalf of
> Axton
> Sent: Wed 7/20/2005 11:08 AM
> To: arslist@ARSLIST.ORG
> Subject: Push Fields with 1=0 in 'Push Fields If'
>
>
>
> I want to half way dispel some myths that were going around at RUG
> this year. There were some presentations that indicated a 1=0 in a
> push fields action always results in a table scan. Let me shed a
> little light on this.
>
> - Background
> - garbage form with ~4k rows
> - ~20 columns/field with data
> - Only index on this form is the system generate FID index
> - Default-Order-By is enabled in this server
>
> - Fact 1: The presence of any value in the 'Push Field If' qual bar of
> a push fields action causes remedy to execute a sql statement to
> verify the presence of a record matching that condition, even if it is
> 0=1. Whether this results in a table scan or not is convered on the
> next point.
> - Proof for fact 1: If the push fields qual is blank, no select
> statement is sent to the db. Here is a select statement generated if
> 1=0 is in the push fields qual (assume that no results list is
> defined):
>
> SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097)

> 1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
> 2 1 FILTER
> 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
Bytes=83097)
>
>
> - Fact 2: The presence of 1=0 in the push fields qual does not always
> result in a table scan.
> - Proof for fact 2: If the results list of the form only contains
> indexed columns, then an index scan is performed instead of a table
> scan. Assume the results list on the form now contains only the
> entry-id. The resulting sql select statement yeilds the following:
>
> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312)

> 1 0 FILTER
> 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
> Bytes=63312)
>
>
> Since the entry-id field always has an index, the above sql statement
> did not trigger a full table scan. It only triggered an INDEX (FULL
> SCAN) OF 'IT775' (UNIQUE).
>
> *** I want to push the idea that the results list for forms should
> always be set to only FID 1 unless it is required for either (1) pick
> lists for multiple matches or (2) direct end user interaction (and no,
> that does not include admin only interaction for things like
> configuration data).
>
>
> Now as to whether the presence of 1=0 always results in a table scan;
> technically it does not. More importantly though, is it a bad
> practice to use 1=0? Well, it costs more than no select regardless of
> whether it causes a table scan or an index scan.
>
> Axton
>
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>
>
>
>
>
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>


--
Axton Grams
AxtonGrams<åt>gmailcom



UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

This posting was submitted via the Web
interface


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

The target forms I have in mind are things like SHR:Assignment,
SHR:Association, SHR:ConsolidatedList, etc. Those places in the application
where the end user never executes a direct search but updates (set/push)
constantly.

The idea is to reduce the overall physical reads, hence reducing the total
i/o by n percentage points. I have not tested all the api calls where this
presents itself, but I know that these calls exhibit this behavior through
the listed workflow actions:

- Does not apply to search menus (ARExpandQueryMenu)
- Does not apply to table fields (ARGetListEntryWithFields)
- Does apply to ARGetListEntryWithFields calls that do not specify the
getListFields argument
- Does apply to ARGetListEntry (GLE)
- Set Fields actions
- Push Fields actions where there is a 'Push Field If' defined
- More than likely applies to ARGetListEntryBlocks
- Applies to ARDBCGetListEntryWithFields if no getListFields is defined
- Applies to ARGetListEntryWithFields if no getListFields is defined (Thanks
Jim)


So now I guess the question is whether there is a measurable return on
setting the results list to the EID only.

Axton

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of O'Hara, Jim
Sent: Wednesday, July 20, 2005 4:53 PM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=0 in 'Push Fields If'

I was intrigued by the results list question, so I ran a little test.
Axton is right, I believe. It must be cheaper to select the request id,
even twice, than to select the request id and the short description.

Here is an extract from a merge of api and sql log:
*/+GLEWF ARGetListEntryWithFields -- schema DefaultResultsList
*/SELECT T1246.C1,C8 FROM T1246 ORDER BY 1 ASC
*/-GLEWF OK
*/+GLEWF ARGetListEntryWithFields -- schema ReqidOnlyResultsList
*/SELECT T1248.C1,T1248.C1 FROM T1248 ORDER BY 1 ASC
*/-GLEWF OK

I wondered if I could trim this down so that there are NO results list
fields, by deleting the Request Id from ReqidOnlyResultsList. The
result was that Remedy put the short description back.

ARServer 5.0.1
aruser 5.1.2
Sybase 12.5.3
Solaris 8

Jim O'H.

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Tim Widowfield
Sent: Wednesday, July 20, 2005 12:42 PM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=0 in 'Push Fields If'

It's an interesting thought. Since the beginning, the ARGetListEntry()
call has returned two things: (1) the Entry ID and (2) the Short
Description field, by default. You can change number 2, of course to
some combination of 128 characters, as long as you don't refer to a long
char, diary field, etc.

Sooo.... I'm not sure I understand what you're saying. Should we
return (1) the Entry ID and (2) the Entry ID? Or (1) the Entry ID and
(2) null? And why does it matter, again? I'm not getting it...

After all, in clients, the ARGetListEntry() call is followed up
immediately by one or more ARGetEntry() calls. I can't quite see the
point of adding the Request ID/Entry ID to the second part of the
returned arguments... Time to get more coffee.

--Tim

Axton wrote:
> Rick:
> I am working with Oracle 9.2.0.6 and these results are from that
version.
>
> Tim:
> Is it less to do more, meaning that executing the select statement
> will be a cheaper than not executing a sql statement? Since you are
> such a passionate guy, what are your thoughts on the footnote in the
> original message?
>
>>*** I want to push the idea that the results list for forms should
>>always be set to only FID 1 unless it is required for either (1) pick
>>lists for multiple matches or (2) direct end user interaction (and no,

>>that does not include admin only interaction for things like
>>configuration data).
>
>
> James:
> The short description field is the default results list value if one
> is not defined.
>
> Axton
>
> On 7/20/05, Rick Cook wrote:
>
>>Axton,
>>
>>To add a bit of info, it was stated by someone that only Oracle below
versions 9i patch 4 run the table scan. The other DBMS' and more
current versions of Oracle handle it with indexes as appropriate.
>>
>>Rick
>>
>>
>>
>>From: Action Request System discussion list(ARSList) on behalf of
>>Axton
>>Sent: Wed 7/20/2005 11:08 AM
>>To: arslist@ARSLIST.ORG
>>Subject: Push Fields with 1=0 in 'Push Fields If'
>>
>>
>>
>>I want to half way dispel some myths that were going around at RUG
>>this year. There were some presentations that indicated a 1=0 in a
>>push fields action always results in a table scan. Let me shed a
>>little light on this.
>>
>>- Background
>>- garbage form with ~4k rows
>>- ~20 columns/field with data
>>- Only index on this form is the system generate FID index
>>- Default-Order-By is enabled in this server
>>
>>- Fact 1: The presence of any value in the 'Push Field If' qual bar of

>>a push fields action causes remedy to execute a sql statement to
>>verify the presence of a record matching that condition, even if it is

>>0=1. Whether this results in a table scan or not is convered on the
>>next point.
>>- Proof for fact 1: If the push fields qual is blank, no select
>>statement is sent to the db. Here is a select statement generated if
>>1=0 is in the push fields qual (assume that no results list is
>>defined):
>>
>>SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>
>>Execution Plan
>>----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957
Bytes=83097)
>> 1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
>> 2 1 FILTER
>> 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
Bytes=83097)
>>
>>
>>- Fact 2: The presence of 1=0 in the push fields qual does not always
>>result in a table scan.
>>- Proof for fact 2: If the results list of the form only contains
>>indexed columns, then an index scan is performed instead of a table
>>scan. Assume the results list on the form now contains only the
>>entry-id. The resulting sql select statement yeilds the following:
>>
>>SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>
>>Execution Plan
>>----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957
Bytes=63312)
>> 1 0 FILTER
>> 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
>>Bytes=63312)
>>
>>
>>Since the entry-id field always has an index, the above sql statement
>>did not trigger a full table scan. It only triggered an INDEX (FULL
>>SCAN) OF 'IT775' (UNIQUE).
>>
>>*** I want to push the idea that the results list for forms should
>>always be set to only FID 1 unless it is required for either (1) pick
>>lists for multiple matches or (2) direct end user interaction (and no,

>>that does not include admin only interaction for things like
>>configuration data).
>>
>>
>>Now as to whether the presence of 1=0 always results in a table scan;
>>technically it does not. More importantly though, is it a bad
>>practice to use 1=0? Well, it costs more than no select regardless of

>>whether it causes a table scan or an index scan.
>>
>>Axton
>>
>>
>> UNSUBSCRIBE or access ARSlist Archives at
>>http://www.ARSLIST.org
>>(Support: mailto:support@arslist.org)
>>
>>
>>
>>
>>
>>
>> UNSUBSCRIBE or access ARSlist Archives at
>>http://www.ARSLIST.org
>>(Support: mailto:support@arslist.org)
>>
>
>
>



UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)



UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

We are set up to disallow unqualified searches and this is not presenting a
problem.

Axton

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Brian Goralczyk
Sent: Wednesday, July 20, 2005 4:14 PM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=0 in 'Push Fields If'

**
While that is true, I find that if I run the two possible queries, and look
at the time difference, one will always stand out.

In reference to one of your other emails...one of the reasons for the 1=0
was that on a system that had wildcard searches turned off, have no criteria
would cause problems. And 1=0 was quicker than referencing any field. I
don't know if that has changed in any newer version as I haven't taken to
time to test it.

Just my thoughts.

Brian

Tim Widowfield wrote:

I'll make a note of it. ;-) I should stick to "Caddy Shack" and
"Animal House" references.

The time it takes to run the query is non-zero. But that does not
prove
whether there was a full table scan.

--Tim


Brian Goralczyk wrote:
> **
> Tim,
>
> Two points, I look at the time it took for the query. And
> Bueller...Bueller was when he was calling role.
Anyone....Anyone....
> now that is in reference to a question he asked. Sorry, just had
to
> point it out.
>
> Brian
>
> */Tim Widowfield /* wrote:
>
> Man, talk about the thread that won't go away... Look, database
> optimizers aren't all that smart, but they certainly aren't
stupid.
>
> Think of it this way. Suppose you go to your local library. You
walk
> up to the librarian at the front desk and say, "Give me all the
books
> you have where the author's name does not equal the author's
name."
>
> She answers immediately, "We don't have any."
>
> You ask, "Did you search through your entire card catalog?"
>
> She rolls her eyes and says, "Of course." Then she calls up her
friends
> and tells them about the idiot who came to bother her today.
>
> Well, (1 = 0) is the same thing. It is always false. The database
> looks at it and says, "False," and returns no rows. Notice: It
doesn't
> have to prove the where clause is false -- by *definition* it is
false.
>
> Now if you ask the Oracle optimizer (on any other RDBMS, YMMV),
it'll
> say, "Oh, yeah, sure. I ran a full table scan." All that means is
it
> didn't have an indexed table to look at. Well, no kidding! Axton,
to
> your point, where every column is indexed, that's the optimizer
for you.
> It knew every column was indexed so it reported that it went to
the
> index. But I assure you that it didn't.
>
> Can the database prove that (1 = 0) is false by searching any
table in
> the known universe? No. *Should* the database attempt to prove
that (1
> = 0) is false by searching any table in the known universe?
> Bueller...Bueller.....ANYONE?
>
> The key is to find out whether the database truly executed a
search.
> Did it hit the disk and really scan the table? The answer is "no."
> Check for the number of blocks read. Zero!
>
>
> --Tim
>
>
> Axton wrote:
> > I want to half way dispel some myths that were going around at
RUG
> > this year. There were some presentations that indicated a 1=0 in
a
> > push fields action always results in a table scan. Let me shed a
> > little light on this.
> >
> > - Background
> > - garbage form with ~4k rows
> > - ~20 columns/field with data
> > - Only index on this form is the system generate FID index
> > - Default-Order-By is enabled in this server
> >
> > - Fact 1: The presence of any value in the 'Push Field If' qual
> bar of
> > a push fields action causes remedy to execute a sql statement to
> > verify the presence of a record matching that condition, even if
> it is
> > 0=1. Whether this results in a table scan or not is convered on
the
> > next point.
> > - Proof for fact 1: If the push fields qual is blank, no select
> > statement is sent to the db. Here is a select statement
generated if
> > 1=0 is in the push fields qual (assume that no results list is
> > defined):
> >
> > SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957
Bytes=83097)
> > 1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
> > 2 1 FILTER
> > 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957 Bytes=83097)
> >
> >
> > - Fact 2: The presence of 1=0 in the push fields qual does not
always
> > result in a table scan.
> > - Proof for fact 2: If the results list of the form only
contains
> > indexed columns, then an index scan is performed instead of a
table
> > scan. Assume the results list on the form now contains only the
> > entry-id. The resulting sql select statement yeilds the
following:
> >
> > SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957
Bytes=63312)
> > 1 0 FILTER
> > 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
> > Bytes=63312)
> >
> >
> > Since the entry-id field always has an index, the above sql
statement
> > did not trigger a full table scan. It only triggered an INDEX
(FULL
> > SCAN) OF 'IT775' (UNIQUE).
> >
> > *** I want to push the idea that the results list for forms
should
> > always be set to only FID 1 unless it is required for either (1)
pick
> > lists for multiple matches or (2) direct end user interaction
> (and no,
> > that does not include admin only interaction for things like
> > configuration data).
> >
> >
> > Now as to whether the presence of 1=0 always results in a table
scan;
> > technically it does not. More importantly though, is it a bad
> > practice to use 1=0? Well, it costs more than no select
regardless of
> > whether it causes a table scan or an index scan.
> >
> > Axton
> >
> >
>


> > UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> > (Support: mailto:support@arslist.org)
> >
>
>


> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>
> This posting was submitted via the
Web
> interface




UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)


This posting was submitted via the Web
interface


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

**
Well then it seems that the problem has gone away, and that is a good thing.

Axton wrote:

We are set up to disallow unqualified searches and this is not presenting a
problem.

Axton

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Brian Goralczyk
Sent: Wednesday, July 20, 2005 4:14 PM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=0 in 'Push Fields If'

**
While that is true, I find that if I run the two possible queries, and look
at the time difference, one will always stand out.

In reference to one of your other emails...one of the reasons for the 1=0
was that on a system that had wildcard searches turned off, have no criteria
would cause problems. And 1=0 was quicker than referencing any field. I
don't know if that has changed in any newer version as I haven't taken to
time to test it.

Just my thoughts.

Brian

Tim Widowfield wrote:

I'll make a note of it. ;-) I should stick to "Caddy Shack" and
"Animal House" references.

The time it takes to run the query is non-zero. But that does not
prove
whether there was a full table scan.

--Tim


Brian Goralczyk wrote:
> **
> Tim,
>
> Two points, I look at the time it took for the query. And
> Bueller...Bueller was when he was calling role.
Anyone....Anyone....
> now that is in reference to a question he asked. Sorry, just had
to
> point it out.
>
> Brian
>
> */Tim Widowfield /* wrote:
>
> Man, talk about the thread that won't go away... Look, database
> optimizers aren't all that smart, but they certainly aren't
stupid.
>
> Think of it this way. Suppose you go to your local library. You
walk
> up to the librarian at the front desk and say, "Give me all the
books
> you have where the author's name does not equal the author's
name."
>
> She answers immediately, "We don't have any."
>
> You ask, "Did you search through your entire card catalog?"
>
> She rolls her eyes and says, "Of course." Then she calls up her
friends
> and tells them about the idiot who came to bother her today.
>
> Well, (1 = 0) is the same thing. It is always false. The database
> looks at it and says, "False," and returns no rows. Notice: It
doesn't
> have to prove the where clause is false -- by *definition* it is
false.
>
> Now if you ask the Oracle optimizer (on any other RDBMS, YMMV),
it'll
> say, "Oh, yeah, sure. I ran a full table scan." All that means is
it
> didn't have an indexed table to look at. Well, no kidding! Axton,
to
> your point, where every column is indexed, that's the optimizer
for you.
> It knew every column was indexed so it reported that it went to
the
> index. But I assure you that it didn't.
>
> Can the database prove that (1 = 0) is false by searching any
table in
> the known universe? No. *Should* the database attempt to prove
that (1
> = 0) is false by searching any table in the known universe?
> Bueller...Bueller.....ANYONE?
>
> The key is to find out whether the database truly executed a
search.
> Did it hit the disk and really scan the table? The answer is "no."
> Check for the number of blocks read. Zero!
>
>
> --Tim
>
>
> Axton wrote:
> > I want to half way dispel some myths that were going around at
RUG
> > this year. There were some presentations that indicated a 1=0 in
a
> > push fields action always results in a table scan. Let me shed a
> > little light on this.
> >
> > - Background
> > - garbage form with ~4k rows
> > - ~20 columns/field with data
> > - Only index on this form is the system generate FID index
> > - Default-Order-By is enabled in this server
> >
> > - Fact 1: The presence of any value in the 'Push Field If' qual
> bar of
> > a push fields action causes remedy to execute a sql statement to
> > verify the presence of a record matching that condition, even if
> it is
> > 0=1. Whether this results in a table scan or not is convered on
the
> > next point.
> > - Proof for fact 1: If the push fields qual is blank, no select
> > statement is sent to the db. Here is a select statement
generated if
> > 1=0 is in the push fields qual (assume that no results list is
> > defined):
> >
> > SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957
Bytes=83097)
> > 1 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097)
> > 2 1 FILTER
> > 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957 Bytes=83097)
> >
> >
> > - Fact 2: The presence of 1=0 in the push fields qual does not
always
> > result in a table scan.
> > - Proof for fact 2: If the results list of the form only
contains
> > indexed columns, then an index scan is performed instead of a
table
> > scan. Assume the results list on the form now contains only the
> > entry-id. The resulting sql select statement yeilds the
following:
> >
> > SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957
Bytes=63312)
> > 1 0 FILTER
> > 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE) (Cost=26 Card=3957
> > Bytes=63312)
> >
> >
> > Since the entry-id field always has an index, the above sql
statement
> > did not trigger a full table scan. It only triggered an INDEX
(FULL
> > SCAN) OF 'IT775' (UNIQUE).
> >
> > *** I want to push the idea that the results list for forms
should
> > always be set to only FID 1 unless it is required for either (1)
pick
> > lists for multiple matches or (2) direct end user interaction
> (and no,
> > that does not include admin only interaction for things like
> > configuration data).
> >
> >
> > Now as to whether the presence of 1=0 always results in a table
scan;
> > technically it does not. More importantly though, is it a bad
> > practice to use 1=0? Well, it costs more than no select
regardless of
> > whether it causes a table scan or an index scan.
> >
> > Axton
> >
> >
>


> > UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> > (Support: mailto:support@arslist.org)
> >
>
>


> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>
> This posting was submitted via the
Web
> interface




UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)


This posting was submitted via the Web
interface


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)




Brian Goralczyk
Mobile 574-249-0018
Email: bdg0584@yahoo.com
Website: www.acegolfclubs.com

"If we let the opinions of others keep us from doing what we know is right,
than we have betrayed both ourselves as well as those we interact with"
This posting was submitted via the Web interface

One other advantage that comes to mind if not using 1=0 is that in
active links you are saving 1 round trip from the client for each
occurance.

Axton


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Here is the query I constructed to return a list of the active links
and filters that exhibit this:

select 'ACTLINK' as ObjType
, name
from actlink
where actlinkid in (
select actlinkid
from actlinkpush
where assignshort like '%4\1\2\2\1\2\2\0%'
or assignshort like '%4\1\2\2\0\2\2\1%')
group by name
union
select 'FILTER' as ObjType
, name
from filter
where filterid in (
select filterid
from filterpush
where assignshort like '%4\1\2\2\1\2\2\0%'
or assignshort like '%4\1\2\2\0\2\2\1%')
group by name

Axton


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Tim,

We faced a serious problem in a client, here in Brasil! We did all push
fields without condition and the Oracle was scanning all table, before
creating the new record. It was in Oracle 8.1.7, ARS 5.1.2, as I remember!
We had to change all filters and place the '0=1' in all of them! I am not
DBA, so I cannot enter in your discussion, but I wanted to present a real
example of the way that the things work, at least in our case.

Regards

Rogerio Bronzoni Aguiar
CMagnani Serviços de Informática


Tim Widowfield escreveu:

> Axton,
>
> I evaded your first question, didn't I? Well, yes, running no select
> statement at all is cheaper than running a select statement that is
> always false. It might be measured in nanoseconds, but it it's larger
> than zero. Entering no Push If qualification at all will cause a new
> record to be pushed in all conditions, *AND* there's no database scan.
>
> Was this true in 4.0 and 4.5? My memory fails... I thought the initial
> implementation of Push Fields required a query of some kind, which is
> where that oddity of (1 = 0) came from.
>
>
> --Tim
>
>
> Axton wrote:
>> Rick: I am working with Oracle 9.2.0.6 and these results are from
>> that version.
>>
>> Tim: Is it less to do more, meaning that executing the select
>> statement will be a cheaper than not executing a sql statement?
>> Since you are such a passionate guy, what are your thoughts on the
>> footnote in the original message?
>>
>>> *** I want to push the idea that the results list for forms should
>>> always be set to only FID 1 unless it is required for either (1)
>>> pick lists for multiple matches or (2) direct end user interaction
>>> (and no, that does not include admin only interaction for things
>>> like configuration data).
>>
>>
>> James: The short description field is the default results list value
>> if one is not defined.
>>
>> Axton
>>
>> On 7/20/05, Rick Cook wrote:
>>
>>> Axton,
>>>
>>> To add a bit of info, it was stated by someone that only Oracle
>>> below versions 9i patch 4 run the table scan. The other DBMS' and
>>> more current versions of Oracle handle it with indexes as
>>> appropriate.
>>>
>>> Rick
>>>
>>>
>>>
>>> From: Action Request System discussion list(ARSList) on behalf of
>>> Axton Sent: Wed 7/20/2005 11:08 AM To: arslist@ARSLIST.ORG Subject:
>>> Push Fields with 1=0 in 'Push Fields If'
>>>
>>>
>>>
>>> I want to half way dispel some myths that were going around at RUG
>>> this year. There were some presentations that indicated a 1=0 in a
>>> push fields action always results in a table scan. Let me shed a
>>> little light on this.
>>>
>>> - Background - garbage form with ~4k rows - ~20 columns/field with
>>> data - Only index on this form is the system generate FID index -
>>> Default-Order-By is enabled in this server
>>>
>>> - Fact 1: The presence of any value in the 'Push Field If' qual bar
>>> of a push fields action causes remedy to execute a sql statement to
>>> verify the presence of a record matching that condition, even if
>>> it is 0=1. Whether this results in a table scan or not is convered
>>> on the next point. - Proof for fact 1: If the push fields qual is
>>> blank, no select statement is sent to the db. Here is a select
>>> statement generated if 1=0 is in the push fields qual (assume that
>>> no results list is defined):
>>>
>>> SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>>
>>> Execution Plan
>>> ---------------------------------------------------------- 0
>>> SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097) 1
>>> 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097) 2 1
>>> FILTER 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
>>> Bytes=83097)
>>>
>>>
>>> - Fact 2: The presence of 1=0 in the push fields qual does not
>>> always result in a table scan. - Proof for fact 2: If the results
>>> list of the form only contains indexed columns, then an index scan
>>> is performed instead of a table scan. Assume the results list on
>>> the form now contains only the entry-id. The resulting sql select
>>> statement yeilds the following:
>>>
>>> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>>
>>> Execution Plan
>>> ---------------------------------------------------------- 0
>>> SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312) 1
>>> 0 FILTER 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE)
>>> (Cost=26 Card=3957 Bytes=63312)
>>>
>>>
>>> Since the entry-id field always has an index, the above sql
>>> statement did not trigger a full table scan. It only triggered an
>>> INDEX (FULL SCAN) OF 'IT775' (UNIQUE).
>>>
>>> *** I want to push the idea that the results list for forms should
>>> always be set to only FID 1 unless it is required for either (1)
>>> pick lists for multiple matches or (2) direct end user interaction
>>> (and no, that does not include admin only interaction for things
>>> like configuration data).
>>>
>>>
>>> Now as to whether the presence of 1=0 always results in a table
>>> scan; technically it does not. More importantly though, is it a
>>> bad practice to use 1=0? Well, it costs more than no select
>>> regardless of whether it causes a table scan or an index scan.
>>>
>>> Axton
>>>
>>>
>>>
>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>> (Support: mailto:support@arslist.org)
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>> (Support: mailto:support@arslist.org)
>>>
>>
>>
>>
>
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Rogerio,

At least in 6.0 and beyond, when AR Server sees a blank Push Fields If,
it doesn't even send it to the server. I had thought that previous
versions treated the blank query as an unqualified query, but I couldn't
remember for sure. Your experience would indicate that it did.

Also, to everyone out there, I was wrong about Oracle. Even in the most
recent 9.x release (I can't speak for 10g) it really does look at the
table. So don't believe everything you read in the docs -- "Trust, but
verify."

In previous tests, I think I must have always managed to trace queries
where the table was already cached in memory, which gave the false
impression that it wasn't touching the table.

Here's an example of a table that wasn't cached:

SQL> set autotrace on;
SQL> select * from aradmin.T35 where 1=0;

no rows selected

Statistics
----------------------------------------------------------
352 recursive calls
0 db block gets
81 consistent gets
1 physical reads
0 redo size
800 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
0 rows processed


But now that it's cached, what happens? Here are the stats:

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

Sad, but true. On this one, Oracle is as dumb as a sack of hammers.

No Daniel, you can't have the plaque back!

--Tim


Rogério Bronzoni wrote:
> Tim,
>
> We faced a serious problem in a client, here in Brasil! We did all push
> fields without condition and the Oracle was scanning all table, before
> creating the new record. It was in Oracle 8.1.7, ARS 5.1.2, as I remember!
> We had to change all filters and place the '0=1' in all of them! I am not
> DBA, so I cannot enter in your discussion, but I wanted to present a real
> example of the way that the things work, at least in our case.
>
> Regards
>
> Rogerio Bronzoni Aguiar
> CMagnani Serviços de Informática
>
>
> Tim Widowfield escreveu:
>
>> Axton,
>>
>> I evaded your first question, didn't I? Well, yes, running no select
>> statement at all is cheaper than running a select statement that is
>> always false. It might be measured in nanoseconds, but it it's larger
>> than zero. Entering no Push If qualification at all will cause a new
>> record to be pushed in all conditions, *AND* there's no database scan.
>>
>> Was this true in 4.0 and 4.5? My memory fails... I thought the initial
>> implementation of Push Fields required a query of some kind, which is
>> where that oddity of (1 = 0) came from.
>>
>>
>> --Tim
>>
>>
>> Axton wrote:
>>
>>> Rick: I am working with Oracle 9.2.0.6 and these results are from
>>> that version.
>>>
>>> Tim: Is it less to do more, meaning that executing the select
>>> statement will be a cheaper than not executing a sql statement?
>>> Since you are such a passionate guy, what are your thoughts on the
>>> footnote in the original message?
>>>
>>>> *** I want to push the idea that the results list for forms should
>>>> always be set to only FID 1 unless it is required for either (1)
>>>> pick lists for multiple matches or (2) direct end user interaction
>>>> (and no, that does not include admin only interaction for things
>>>> like configuration data).
>>>
>>>
>>>
>>> James: The short description field is the default results list value
>>> if one is not defined.
>>>
>>> Axton
>>>
>>> On 7/20/05, Rick Cook wrote:
>>>
>>>> Axton,
>>>>
>>>> To add a bit of info, it was stated by someone that only Oracle
>>>> below versions 9i patch 4 run the table scan. The other DBMS' and
>>>> more current versions of Oracle handle it with indexes as
>>>> appropriate.
>>>>
>>>> Rick
>>>>
>>>>
>>>>
>>>> From: Action Request System discussion list(ARSList) on behalf of
>>>> Axton Sent: Wed 7/20/2005 11:08 AM To: arslist@ARSLIST.ORG Subject:
>>>> Push Fields with 1=0 in 'Push Fields If'
>>>>
>>>>
>>>>
>>>> I want to half way dispel some myths that were going around at RUG
>>>> this year. There were some presentations that indicated a 1=0 in a
>>>> push fields action always results in a table scan. Let me shed a
>>>> little light on this.
>>>>
>>>> - Background - garbage form with ~4k rows - ~20 columns/field with
>>>> data - Only index on this form is the system generate FID index -
>>>> Default-Order-By is enabled in this server
>>>>
>>>> - Fact 1: The presence of any value in the 'Push Field If' qual bar
>>>> of a push fields action causes remedy to execute a sql statement to
>>>> verify the presence of a record matching that condition, even if
>>>> it is 0=1. Whether this results in a table scan or not is convered
>>>> on the next point. - Proof for fact 1: If the push fields qual is
>>>> blank, no select statement is sent to the db. Here is a select
>>>> statement generated if 1=0 is in the push fields qual (assume that
>>>> no results list is defined):
>>>>
>>>> SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>>>
>>>> Execution Plan
>>>> ---------------------------------------------------------- 0
>>>> SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097) 1
>>>> 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097) 2 1
>>>> FILTER 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
>>>> Bytes=83097)
>>>>
>>>>
>>>> - Fact 2: The presence of 1=0 in the push fields qual does not
>>>> always result in a table scan. - Proof for fact 2: If the results
>>>> list of the form only contains indexed columns, then an index scan
>>>> is performed instead of a table scan. Assume the results list on
>>>> the form now contains only the entry-id. The resulting sql select
>>>> statement yeilds the following:
>>>>
>>>> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>>>
>>>> Execution Plan
>>>> ---------------------------------------------------------- 0
>>>> SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312) 1
>>>> 0 FILTER 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE)
>>>> (Cost=26 Card=3957 Bytes=63312)
>>>>
>>>>
>>>> Since the entry-id field always has an index, the above sql
>>>> statement did not trigger a full table scan. It only triggered an
>>>> INDEX (FULL SCAN) OF 'IT775' (UNIQUE).
>>>>
>>>> *** I want to push the idea that the results list for forms should
>>>> always be set to only FID 1 unless it is required for either (1)
>>>> pick lists for multiple matches or (2) direct end user interaction
>>>> (and no, that does not include admin only interaction for things
>>>> like configuration data).
>>>>
>>>>
>>>> Now as to whether the presence of 1=0 always results in a table
>>>> scan; technically it does not. More importantly though, is it a
>>>> bad practice to use 1=0? Well, it costs more than no select
>>>> regardless of whether it causes a table scan or an index scan.
>>>>
>>>> Axton
>>>>
>>>>
>>>>
>>>>
>>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>>> (Support: mailto:support@arslist.org)
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>>> (Support: mailto:support@arslist.org)
>>>>
>>>
>>>
>>>
>>
>>
>>
>>
>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>> (Support: mailto:support@arslist.org)
>
>
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Good to know. I rescend my statement about the presentation being
inaccurate. This really is the thread that won't go away, no?

So does this mean that if the order by clause is appended and the
results list set to FID 1 that an index scan will occur instead of a
table scan? If so, this should be a much cheaper operation.

Also, the transitivity you introduced earlier on, does it only apply
to UNION ALL?

Axton

On 7/21/05, Tim Widowfield wrote:
> Rogerio,
>
> At least in 6.0 and beyond, when AR Server sees a blank Push Fields If,
> it doesn't even send it to the server. I had thought that previous
> versions treated the blank query as an unqualified query, but I couldn't
> remember for sure. Your experience would indicate that it did.
>
> Also, to everyone out there, I was wrong about Oracle. Even in the most
> recent 9.x release (I can't speak for 10g) it really does look at the
> table. So don't believe everything you read in the docs -- "Trust, but
> verify."
>
> In previous tests, I think I must have always managed to trace queries
> where the table was already cached in memory, which gave the false
> impression that it wasn't touching the table.
>
> Here's an example of a table that wasn't cached:
>
> SQL> set autotrace on;
> SQL> select * from aradmin.T35 where 1=0;
>
> no rows selected
>
> Statistics
> ----------------------------------------------------------
> 352 recursive calls
> 0 db block gets
> 81 consistent gets
> 1 physical reads
> 0 redo size
> 800 bytes sent via SQL*Net to client
> 368 bytes received via SQL*Net from client
> 1 SQL*Net roundtrips to/from client
> 6 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
>
> But now that it's cached, what happens? Here are the stats:
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 0 consistent gets
> 0 physical reads
> 0 redo size
> 800 bytes sent via SQL*Net to client
> 368 bytes received via SQL*Net from client
> 1 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
> Sad, but true. On this one, Oracle is as dumb as a sack of hammers.
>
> No Daniel, you can't have the plaque back!
>
> --Tim
>
>
> Rogério Bronzoni wrote:
> > Tim,
> >
> > We faced a serious problem in a client, here in Brasil! We did all push
> > fields without condition and the Oracle was scanning all table, before
> > creating the new record. It was in Oracle 8.1.7, ARS 5.1.2, as I remember!
> > We had to change all filters and place the '0=1' in all of them! I am not
> > DBA, so I cannot enter in your discussion, but I wanted to present a real
> > example of the way that the things work, at least in our case.
> >
> > Regards
> >
> > Rogerio Bronzoni Aguiar
> > CMagnani Serviços de Informática
> >
> >
> > Tim Widowfield escreveu:
> >
> >> Axton,
> >>
> >> I evaded your first question, didn't I? Well, yes, running no select
> >> statement at all is cheaper than running a select statement that is
> >> always false. It might be measured in nanoseconds, but it it's larger
> >> than zero. Entering no Push If qualification at all will cause a new
> >> record to be pushed in all conditions, *AND* there's no database scan.
> >>
> >> Was this true in 4.0 and 4.5? My memory fails... I thought the initial
> >> implementation of Push Fields required a query of some kind, which is
> >> where that oddity of (1 = 0) came from.
> >>
> >>
> >> --Tim
> >>
> >>
> >> Axton wrote:
> >>
> >>> Rick: I am working with Oracle 9.2.0.6 and these results are from
> >>> that version.
> >>>
> >>> Tim: Is it less to do more, meaning that executing the select
> >>> statement will be a cheaper than not executing a sql statement?
> >>> Since you are such a passionate guy, what are your thoughts on the
> >>> footnote in the original message?
> >>>
> >>>> *** I want to push the idea that the results list for forms should
> >>>> always be set to only FID 1 unless it is required for either (1)
> >>>> pick lists for multiple matches or (2) direct end user interaction
> >>>> (and no, that does not include admin only interaction for things
> >>>> like configuration data).
> >>>
> >>>
> >>>
> >>> James: The short description field is the default results list value
> >>> if one is not defined.
> >>>
> >>> Axton
> >>>
> >>> On 7/20/05, Rick Cook wrote:
> >>>
> >>>> Axton,
> >>>>
> >>>> To add a bit of info, it was stated by someone that only Oracle
> >>>> below versions 9i patch 4 run the table scan. The other DBMS' and
> >>>> more current versions of Oracle handle it with indexes as
> >>>> appropriate.
> >>>>
> >>>> Rick
> >>>>
> >>>>
> >>>>
> >>>> From: Action Request System discussion list(ARSList) on behalf of
> >>>> Axton Sent: Wed 7/20/2005 11:08 AM To: arslist@ARSLIST.ORG Subject:
> >>>> Push Fields with 1=0 in 'Push Fields If'
> >>>>
> >>>>
> >>>>
> >>>> I want to half way dispel some myths that were going around at RUG
> >>>> this year. There were some presentations that indicated a 1=0 in a
> >>>> push fields action always results in a table scan. Let me shed a
> >>>> little light on this.
> >>>>
> >>>> - Background - garbage form with ~4k rows - ~20 columns/field with
> >>>> data - Only index on this form is the system generate FID index -
> >>>> Default-Order-By is enabled in this server
> >>>>
> >>>> - Fact 1: The presence of any value in the 'Push Field If' qual bar
> >>>> of a push fields action causes remedy to execute a sql statement to
> >>>> verify the presence of a record matching that condition, even if
> >>>> it is 0=1. Whether this results in a table scan or not is convered
> >>>> on the next point. - Proof for fact 1: If the push fields qual is
> >>>> blank, no select statement is sent to the db. Here is a select
> >>>> statement generated if 1=0 is in the push fields qual (assume that
> >>>> no results list is defined):
> >>>>
> >>>> SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
> >>>>
> >>>> Execution Plan
> >>>> ---------------------------------------------------------- 0
> >>>> SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097) 1
> >>>> 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097) 2 1
> >>>> FILTER 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
> >>>> Bytes=83097)
> >>>>
> >>>>
> >>>> - Fact 2: The presence of 1=0 in the push fields qual does not
> >>>> always result in a table scan. - Proof for fact 2: If the results
> >>>> list of the form only contains indexed columns, then an index scan
> >>>> is performed instead of a table scan. Assume the results list on
> >>>> the form now contains only the entry-id. The resulting sql select
> >>>> statement yeilds the following:
> >>>>
> >>>> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
> >>>>
> >>>> Execution Plan
> >>>> ---------------------------------------------------------- 0
> >>>> SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312) 1
> >>>> 0 FILTER 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE)
> >>>> (Cost=26 Card=3957 Bytes=63312)
> >>>>
> >>>>
> >>>> Since the entry-id field always has an index, the above sql
> >>>> statement did not trigger a full table scan. It only triggered an
> >>>> INDEX (FULL SCAN) OF 'IT775' (UNIQUE).
> >>>>
> >>>> *** I want to push the idea that the results list for forms should
> >>>> always be set to only FID 1 unless it is required for either (1)
> >>>> pick lists for multiple matches or (2) direct end user interaction
> >>>> (and no, that does not include admin only interaction for things
> >>>> like configuration data).
> >>>>
> >>>>
> >>>> Now as to whether the presence of 1=0 always results in a table
> >>>> scan; technically it does not. More importantly though, is it a
> >>>> bad practice to use 1=0? Well, it costs more than no select
> >>>> regardless of whether it causes a table scan or an index scan.
> >>>>
> >>>> Axton
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> >>>> (Support: mailto:support@arslist.org)
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> >>>> (Support: mailto:support@arslist.org)
> >>>>
> >>>
> >>>
> >>>
> >>
> >>
> >>
> >>
> >> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> >> (Support: mailto:support@arslist.org)
> >
> >
> >
> >
> > UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> > (Support: mailto:support@arslist.org)
> >
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>


--
Axton Grams
AxtonGrams<åt>gmailcom


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

To your first question, it beats me.

To your second question, a closer look at the documentation shows some
wiggle room. They say, "The extra predicate is always false; thus, the
table ... need not be accessed." You could interpret that to mean they
don't *have* to access it, but they do anyway.

Let's say you have a where clause in which simple transitivity (without
a union statement) proves the statement to be always false:

SQL> select * from aradmin.T5 where C1='000000000000005' AND
C1='000000000000006';

no rows selected

Statistics
----------------------------------------------------------
304 recursive calls
0 db block gets
68 consistent gets
2 physical reads
0 redo size
727 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
0 rows processed

How sad. Bartender, pour me another.


--Tim


Axton wrote:
> Good to know. I rescend my statement about the presentation being
> inaccurate. This really is the thread that won't go away, no?
>
> So does this mean that if the order by clause is appended and the
> results list set to FID 1 that an index scan will occur instead of a
> table scan? If so, this should be a much cheaper operation.
>
> Also, the transitivity you introduced earlier on, does it only apply
> to UNION ALL?
>
> Axton
>
> On 7/21/05, Tim Widowfield wrote:
>
>>Rogerio,
>>
>>At least in 6.0 and beyond, when AR Server sees a blank Push Fields If,
>>it doesn't even send it to the server. I had thought that previous
>>versions treated the blank query as an unqualified query, but I couldn't
>>remember for sure. Your experience would indicate that it did.
>>
>>Also, to everyone out there, I was wrong about Oracle. Even in the most
>>recent 9.x release (I can't speak for 10g) it really does look at the
>>table. So don't believe everything you read in the docs -- "Trust, but
>>verify."
>>
>>In previous tests, I think I must have always managed to trace queries
>>where the table was already cached in memory, which gave the false
>>impression that it wasn't touching the table.
>>
>>Here's an example of a table that wasn't cached:
>>
>>SQL> set autotrace on;
>>SQL> select * from aradmin.T35 where 1=0;
>>
>>no rows selected
>>
>>Statistics
>>----------------------------------------------------------
>> 352 recursive calls
>> 0 db block gets
>> 81 consistent gets
>> 1 physical reads
>> 0 redo size
>> 800 bytes sent via SQL*Net to client
>> 368 bytes received via SQL*Net from client
>> 1 SQL*Net roundtrips to/from client
>> 6 sorts (memory)
>> 0 sorts (disk)
>> 0 rows processed
>>
>>
>>But now that it's cached, what happens? Here are the stats:
>>
>>Statistics
>>----------------------------------------------------------
>> 0 recursive calls
>> 0 db block gets
>> 0 consistent gets
>> 0 physical reads
>> 0 redo size
>> 800 bytes sent via SQL*Net to client
>> 368 bytes received via SQL*Net from client
>> 1 SQL*Net roundtrips to/from client
>> 0 sorts (memory)
>> 0 sorts (disk)
>> 0 rows processed
>>
>>Sad, but true. On this one, Oracle is as dumb as a sack of hammers.
>>
>>No Daniel, you can't have the plaque back!
>>
>>--Tim
>>
>>
>>Rogério Bronzoni wrote:
>>
>>>Tim,
>>>
>>>We faced a serious problem in a client, here in Brasil! We did all push
>>>fields without condition and the Oracle was scanning all table, before
>>>creating the new record. It was in Oracle 8.1.7, ARS 5.1.2, as I remember!
>>>We had to change all filters and place the '0=1' in all of them! I am not
>>>DBA, so I cannot enter in your discussion, but I wanted to present a real
>>>example of the way that the things work, at least in our case.
>>>
>>>Regards
>>>
>>>Rogerio Bronzoni Aguiar
>>>CMagnani Serviços de Informática
>>>
>>>
>>>Tim Widowfield escreveu:
>>>
>>>
>>>>Axton,
>>>>
>>>>I evaded your first question, didn't I? Well, yes, running no select
>>>>statement at all is cheaper than running a select statement that is
>>>>always false. It might be measured in nanoseconds, but it it's larger
>>>>than zero. Entering no Push If qualification at all will cause a new
>>>>record to be pushed in all conditions, *AND* there's no database scan.
>>>>
>>>>Was this true in 4.0 and 4.5? My memory fails... I thought the initial
>>>>implementation of Push Fields required a query of some kind, which is
>>>>where that oddity of (1 = 0) came from.
>>>>
>>>>
>>>>--Tim
>>>>
>>>>
>>>>Axton wrote:
>>>>
>>>>
>>>>>Rick: I am working with Oracle 9.2.0.6 and these results are from
>>>>>that version.
>>>>>
>>>>>Tim: Is it less to do more, meaning that executing the select
>>>>>statement will be a cheaper than not executing a sql statement?
>>>>>Since you are such a passionate guy, what are your thoughts on the
>>>>>footnote in the original message?
>>>>>
>>>>>
>>>>>>*** I want to push the idea that the results list for forms should
>>>>>>always be set to only FID 1 unless it is required for either (1)
>>>>>>pick lists for multiple matches or (2) direct end user interaction
>>>>>>(and no, that does not include admin only interaction for things
>>>>>>like configuration data).
>>>>>
>>>>>
>>>>>
>>>>>James: The short description field is the default results list value
>>>>>if one is not defined.
>>>>>
>>>>>Axton
>>>>>
>>>>>On 7/20/05, Rick Cook wrote:
>>>>>
>>>>>
>>>>>>Axton,
>>>>>>
>>>>>>To add a bit of info, it was stated by someone that only Oracle
>>>>>>below versions 9i patch 4 run the table scan. The other DBMS' and
>>>>>>more current versions of Oracle handle it with indexes as
>>>>>>appropriate.
>>>>>>
>>>>>>Rick
>>>>>>
>>>>>>
>>>>>>
>>>>>>From: Action Request System discussion list(ARSList) on behalf of
>>>>>>Axton Sent: Wed 7/20/2005 11:08 AM To: arslist@ARSLIST.ORG Subject:
>>>>>>Push Fields with 1=0 in 'Push Fields If'
>>>>>>
>>>>>>
>>>>>>
>>>>>>I want to half way dispel some myths that were going around at RUG
>>>>>>this year. There were some presentations that indicated a 1=0 in a
>>>>>> push fields action always results in a table scan. Let me shed a
>>>>>>little light on this.
>>>>>>
>>>>>>- Background - garbage form with ~4k rows - ~20 columns/field with
>>>>>>data - Only index on this form is the system generate FID index -
>>>>>>Default-Order-By is enabled in this server
>>>>>>
>>>>>>- Fact 1: The presence of any value in the 'Push Field If' qual bar
>>>>>>of a push fields action causes remedy to execute a sql statement to
>>>>>> verify the presence of a record matching that condition, even if
>>>>>>it is 0=1. Whether this results in a table scan or not is convered
>>>>>>on the next point. - Proof for fact 1: If the push fields qual is
>>>>>>blank, no select statement is sent to the db. Here is a select
>>>>>>statement generated if 1=0 is in the push fields qual (assume that
>>>>>>no results list is defined):
>>>>>>
>>>>>>SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>>>>>
>>>>>>Execution Plan
>>>>>>---------------------------------------------------------- 0
>>>>>>SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097) 1
>>>>>>0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097) 2 1
>>>>>>FILTER 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
>>>>>>Bytes=83097)
>>>>>>
>>>>>>
>>>>>>- Fact 2: The presence of 1=0 in the push fields qual does not
>>>>>>always result in a table scan. - Proof for fact 2: If the results
>>>>>>list of the form only contains indexed columns, then an index scan
>>>>>>is performed instead of a table scan. Assume the results list on
>>>>>>the form now contains only the entry-id. The resulting sql select
>>>>>>statement yeilds the following:
>>>>>>
>>>>>>SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>>>>>
>>>>>>Execution Plan
>>>>>>---------------------------------------------------------- 0
>>>>>>SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312) 1
>>>>>>0 FILTER 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE)
>>>>>>(Cost=26 Card=3957 Bytes=63312)
>>>>>>
>>>>>>
>>>>>>Since the entry-id field always has an index, the above sql
>>>>>>statement did not trigger a full table scan. It only triggered an
>>>>>>INDEX (FULL SCAN) OF 'IT775' (UNIQUE).
>>>>>>
>>>>>>*** I want to push the idea that the results list for forms should
>>>>>>always be set to only FID 1 unless it is required for either (1)
>>>>>>pick lists for multiple matches or (2) direct end user interaction
>>>>>>(and no, that does not include admin only interaction for things
>>>>>>like configuration data).
>>>>>>
>>>>>>
>>>>>>Now as to whether the presence of 1=0 always results in a table
>>>>>>scan; technically it does not. More importantly though, is it a
>>>>>>bad practice to use 1=0? Well, it costs more than no select
>>>>>>regardless of whether it causes a table scan or an index scan.
>>>>>>
>>>>>>Axton
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>>>>>(Support: mailto:support@arslist.org)
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>>>>>(Support: mailto:support@arslist.org)
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>>>(Support: mailto:support@arslist.org)
>>>
>>>
>>>
>>>
>>>UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>>(Support: mailto:support@arslist.org)
>>>
>>
>>
>>UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>(Support: mailto:support@arslist.org)
>>
>
>
>


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

I think the "physical read" is the smoking gun. The database is indeed
touched, and the results are cached. Of course, one possibility is the
optimizer says to itself, "If they query a table once, they'll query it
a hundred times, so I'll just cache it if it's fairly small." But I'm
only guessing.


--Tim.



McKenzie, James J C-E LCMC HQISEC/L3 wrote:
> **
>
> Folks:
>
> It looks like SQL*Net is being used, but that does not mean that SQL*Net
> ever touched the database. I think this could be done better by Remedy
> itself and save a lot of processing time, both on the Remedy system and
> if the database is remote, on the database server, by simple math
> processing. It appears that Remedy is not doing this, shame on them.
>
> James McKenzie
> Remedy Engineer
> C-E LCMC HQISEC, US Army
> L-3 GSI
>
>
> -----Original Message-----
> From: Action Request System discussion list(ARSList)
> [mailto:arslist@ARSLIST.ORG] On Behalf Of Tim Widowfield
> Sent: Thursday, July 21, 2005 11:57 AM
> To: arslist@ARSLIST.ORG
> Subject: Re: Push Fields with 1=0 in 'Push Fields If'
>
>
> To your first question, it beats me.
>
> To your second question, a closer look at the documentation shows some
> wiggle room. They say, "The extra predicate is always false; thus, the
> table ... need not be accessed." You could interpret that to mean they
> don't *have* to access it, but they do anyway.
>
> Let's say you have a where clause in which simple transitivity (without
> a union statement) proves the statement to be always false:
>
> SQL> select * from aradmin.T5 where C1='000000000000005' AND
> C1='000000000000006';
>
> no rows selected
>
> Statistics
> ----------------------------------------------------------
> 304 recursive calls
> 0 db block gets
> 68 consistent gets
> 2 physical reads
> 0 redo size
> 727 bytes sent via SQL*Net to client
> 368 bytes received via SQL*Net from client
> 1 SQL*Net roundtrips to/from client
> 6 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
> How sad. Bartender, pour me another.
>
>
> --Tim
>
>
> Axton wrote:
> > Good to know. I rescend my statement about the presentation being
> > inaccurate. This really is the thread that won't go away, no?
> >
> > So does this mean that if the order by clause is appended and the
> > results list set to FID 1 that an index scan will occur instead of a
> > table scan? If so, this should be a much cheaper operation.
> >
> > Also, the transitivity you introduced earlier on, does it only apply
> > to UNION ALL?
> >
> > Axton
> >
> > On 7/21/05, Tim Widowfield wrote:
> >
> >>Rogerio,
> >>
> >>At least in 6.0 and beyond, when AR Server sees a blank Push Fields
> >>If, it doesn't even send it to the server. I had thought that
> >>previous versions treated the blank query as an unqualified query, but
> >>I couldn't remember for sure. Your experience would indicate that it
> >>did.
> >>
> >>Also, to everyone out there, I was wrong about Oracle. Even in the
> >>most recent 9.x release (I can't speak for 10g) it really does look at
> >>the table. So don't believe everything you read in the docs --
> >>"Trust, but verify."
> >>
> >>In previous tests, I think I must have always managed to trace queries
> >>where the table was already cached in memory, which gave the false
> >>impression that it wasn't touching the table.
> >>
> >>Here's an example of a table that wasn't cached:
> >>
> >>SQL> set autotrace on;
> >>SQL> select * from aradmin.T35 where 1=0;
> >>
> >>no rows selected
> >>
> >>Statistics
> >>----------------------------------------------------------
> >> 352 recursive calls
> >> 0 db block gets
> >> 81 consistent gets
> >> 1 physical reads
> >> 0 redo size
> >> 800 bytes sent via SQL*Net to client
> >> 368 bytes received via SQL*Net from client
> >> 1 SQL*Net roundtrips to/from client
> >> 6 sorts (memory)
> >> 0 sorts (disk)
> >> 0 rows processed
> >>
> >>
> >>But now that it's cached, what happens? Here are the stats:
> >>
> >>Statistics
> >>----------------------------------------------------------
> >> 0 recursive calls
> >> 0 db block gets
> >> 0 consistent gets
> >> 0 physical reads
> >> 0 redo size
> >> 800 bytes sent via SQL*Net to client
> >> 368 bytes received via SQL*Net from client
> >> 1 SQL*Net roundtrips to/from client
> >> 0 sorts (memory)
> >> 0 sorts (disk)
> >> 0 rows processed
> >>
> >>Sad, but true. On this one, Oracle is as dumb as a sack of hammers.
> >>
> >>No Daniel, you can't have the plaque back!
> >>
> >>--Tim
> >>
> >>
> >>Rogério Bronzoni wrote:
> >>
> >>>Tim,
> >>>
> >>>We faced a serious problem in a client, here in Brasil! We did all
> >>>push fields without condition and the Oracle was scanning all table,
> >>>before creating the new record. It was in Oracle 8.1.7, ARS 5.1.2, as
> >>>I remember! We had to change all filters and place the '0=1' in all
> >>>of them! I am not DBA, so I cannot enter in your discussion, but I
> >>>wanted to present a real example of the way that the things work, at
> >>>least in our case.
> >>>
> >>>Regards
> >>>
> >>>Rogerio Bronzoni Aguiar
> >>>CMagnani Serviços de Informática
> >>>
> >>>
> >>>Tim Widowfield escreveu:
> >>>
> >>>
> >>>>Axton,
> >>>>
> >>>>I evaded your first question, didn't I? Well, yes, running no
> >>>>select statement at all is cheaper than running a select statement
> >>>>that is always false. It might be measured in nanoseconds, but it
> >>>>it's larger than zero. Entering no Push If qualification at all
> >>>>will cause a new record to be pushed in all conditions, *AND*
> >>>>there's no database scan.
> >>>>
> >>>>Was this true in 4.0 and 4.5? My memory fails... I thought the
> >>>>initial implementation of Push Fields required a query of some kind,
> >>>>which is where that oddity of (1 = 0) came from.
> >>>>
> >>>>
> >>>>--Tim
> >>>>
> >>>>
> >>>>Axton wrote:
> >>>>
> >>>>
> >>>>>Rick: I am working with Oracle 9.2.0.6 and these results are from
> >>>>>that version.
> >>>>>
> >>>>>Tim: Is it less to do more, meaning that executing the select
> >>>>>statement will be a cheaper than not executing a sql statement?
> >>>>>Since you are such a passionate guy, what are your thoughts on the
> >>>>>footnote in the original message?
> >>>>>
> >>>>>
> >>>>>>*** I want to push the idea that the results list for forms should
> >>>>>>always be set to only FID 1 unless it is required for either (1)
> >>>>>>pick lists for multiple matches or (2) direct end user interaction
> >>>>>>(and no, that does not include admin only interaction for things
> >>>>>>like configuration data).
> >>>>>
> >>>>>
> >>>>>
> >>>>>James: The short description field is the default results list
> >>>>>value if one is not defined.
> >>>>>
> >>>>>Axton
> >>>>>
> >>>>>On 7/20/05, Rick Cook wrote:
> >>>>>
> >>>>>
> >>>>>>Axton,
> >>>>>>
> >>>>>>To add a bit of info, it was stated by someone that only Oracle
> >>>>>>below versions 9i patch 4 run the table scan. The other DBMS' and
> >>>>>>more current versions of Oracle handle it with indexes as
> >>>>>>appropriate.
> >>>>>>
> >>>>>>Rick
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>From: Action Request System discussion list(ARSList) on behalf of
> >>>>>>Axton Sent: Wed 7/20/2005 11:08 AM To: arslist@ARSLIST.ORG
> >>>>>>Subject: Push Fields with 1=0 in 'Push Fields If'
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>I want to half way dispel some myths that were going around at RUG
> >>>>>>this year. There were some presentations that indicated a 1=0 in
> >>>>>>a push fields action always results in a table scan. Let me shed
> >>>>>>a little light on this.
> >>>>>>
> >>>>>>- Background - garbage form with ~4k rows - ~20 columns/field with
> >>>>>>data - Only index on this form is the system generate FID index -
> >>>>>>Default-Order-By is enabled in this server
> >>>>>>
> >>>>>>- Fact 1: The presence of any value in the 'Push Field If' qual
> >>>>>>bar of a push fields action causes remedy to execute a sql
> >>>>>>statement to verify the presence of a record matching that
> >>>>>>condition, even if it is 0=1. Whether this results in a table
> >>>>>>scan or not is convered on the next point. - Proof for fact 1: If
> >>>>>>the push fields qual is blank, no select statement is sent to the
> >>>>>>db. Here is a select statement generated if 1=0 is in the push
> >>>>>>fields qual (assume that no results list is defined):
> >>>>>>
> >>>>>>SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
> >>>>>>
> >>>>>>Execution Plan
> >>>>>>---------------------------------------------------------- 0
> >>>>>>SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097) 1
> >>>>>>0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097) 2 1
> >>>>>>FILTER 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
> >>>>>>Bytes=83097)
> >>>>>>
> >>>>>>
> >>>>>>- Fact 2: The presence of 1=0 in the push fields qual does not
> >>>>>>always result in a table scan. - Proof for fact 2: If the results
> >>>>>>list of the form only contains indexed columns, then an index scan
> >>>>>>is performed instead of a table scan. Assume the results list on
> >>>>>>the form now contains only the entry-id. The resulting sql select
> >>>>>>statement yeilds the following:
> >>>>>>
> >>>>>>SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
> >>>>>>
> >>>>>>Execution Plan
> >>>>>>---------------------------------------------------------- 0
> >>>>>>SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312) 1
> >>>>>>0 FILTER 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE)
> >>>>>>(Cost=26 Card=3957 Bytes=63312)
> >>>>>>
> >>>>>>
> >>>>>>Since the entry-id field always has an index, the above sql
> >>>>>>statement did not trigger a full table scan. It only triggered an
> >>>>>>INDEX (FULL SCAN) OF 'IT775' (UNIQUE).
> >>>>>>
> >>>>>>*** I want to push the idea that the results list for forms should
> >>>>>>always be set to only FID 1 unless it is required for either (1)
> >>>>>>pick lists for multiple matches or (2) direct end user interaction
> >>>>>>(and no, that does not include admin only interaction for things
> >>>>>>like configuration data).
> >>>>>>
> >>>>>>
> >>>>>>Now as to whether the presence of 1=0 always results in a table
> >>>>>>scan; technically it does not. More importantly though, is it a
> >>>>>>bad practice to use 1=0? Well, it costs more than no select
> >>>>>>regardless of whether it causes a table scan or an index scan.
> >>>>>>
> >>>>>>Axton
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> >>>>>>(Support: mailto:support@arslist.org)
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> >>>>>>(Support: mailto:support@arslist.org)
> >>>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> >>>>(Support: mailto:support@arslist.org)
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> >>>(Support: mailto:support@arslist.org)
> >>>
> >>
> >>
> >>
> >>UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> >>(Support: mailto:support@arslist.org)
> >>
> >
> >
> >
>
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>
> This posting was submitted via the Web
> interface


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

**

Folks:

It looks like SQL*Net is being used, but that does not mean that SQL*Net ever touched the database. I think this could be done better by Remedy itself and save a lot of processing time, both on the Remedy system and if the database is remote, on the database server, by simple math processing. It appears that Remedy is not doing this, shame on them.

James McKenzie
Remedy Engineer
C-E LCMC HQISEC, US Army
L-3 GSI


-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Tim Widowfield
Sent: Thursday, July 21, 2005 11:57 AM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=0 in 'Push Fields If'


To your first question, it beats me.

To your second question, a closer look at the documentation shows some wiggle room. They say, "The extra predicate is always false; thus, the table ... need not be accessed." You could interpret that to mean they don't *have* to access it, but they do anyway.

Let's say you have a where clause in which simple transitivity (without a union statement) proves the statement to be always false:

SQL> select * from aradmin.T5 where C1='000000000000005' AND
C1='000000000000006';

no rows selected

Statistics
----------------------------------------------------------
304 recursive calls
0 db block gets
68 consistent gets
2 physical reads
0 redo size
727 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
0 rows processed

How sad. Bartender, pour me another.


--Tim


Axton wrote:
> Good to know. I rescend my statement about the presentation being
> inaccurate. This really is the thread that won't go away, no?
>
> So does this mean that if the order by clause is appended and the
> results list set to FID 1 that an index scan will occur instead of a
> table scan? If so, this should be a much cheaper operation.
>
> Also, the transitivity you introduced earlier on, does it only apply
> to UNION ALL?
>
> Axton
>
> On 7/21/05, Tim Widowfield wrote:
>
>>Rogerio,
>>
>>At least in 6.0 and beyond, when AR Server sees a blank Push Fields
>>If, it doesn't even send it to the server. I had thought that
>>previous versions treated the blank query as an unqualified query, but
>>I couldn't remember for sure. Your experience would indicate that it
>>did.
>>
>>Also, to everyone out there, I was wrong about Oracle. Even in the
>>most recent 9.x release (I can't speak for 10g) it really does look at
>>the table. So don't believe everything you read in the docs --
>>"Trust, but verify."
>>
>>In previous tests, I think I must have always managed to trace queries
>>where the table was already cached in memory, which gave the false
>>impression that it wasn't touching the table.
>>
>>Here's an example of a table that wasn't cached:
>>
>>SQL> set autotrace on;
>>SQL> select * from aradmin.T35 where 1=0;
>>
>>no rows selected
>>
>>Statistics
>>----------------------------------------------------------
>> 352 recursive calls
>> 0 db block gets
>> 81 consistent gets
>> 1 physical reads
>> 0 redo size
>> 800 bytes sent via SQL*Net to client
>> 368 bytes received via SQL*Net from client
>> 1 SQL*Net roundtrips to/from client
>> 6 sorts (memory)
>> 0 sorts (disk)
>> 0 rows processed
>>
>>
>>But now that it's cached, what happens? Here are the stats:
>>
>>Statistics
>>----------------------------------------------------------
>> 0 recursive calls
>> 0 db block gets
>> 0 consistent gets
>> 0 physical reads
>> 0 redo size
>> 800 bytes sent via SQL*Net to client
>> 368 bytes received via SQL*Net from client
>> 1 SQL*Net roundtrips to/from client
>> 0 sorts (memory)
>> 0 sorts (disk)
>> 0 rows processed
>>
>>Sad, but true. On this one, Oracle is as dumb as a sack of hammers.
>>
>>No Daniel, you can't have the plaque back!
>>
>>--Tim
>>
>>
>>Rogério Bronzoni wrote:
>>
>>>Tim,
>>>
>>>We faced a serious problem in a client, here in Brasil! We did all
>>>push fields without condition and the Oracle was scanning all table,
>>>before creating the new record. It was in Oracle 8.1.7, ARS 5.1.2, as
>>>I remember! We had to change all filters and place the '0=1' in all
>>>of them! I am not DBA, so I cannot enter in your discussion, but I
>>>wanted to present a real example of the way that the things work, at
>>>least in our case.
>>>
>>>Regards
>>>
>>>Rogerio Bronzoni Aguiar
>>>CMagnani Serviços de Informática
>>>
>>>
>>>Tim Widowfield escreveu:
>>>
>>>
>>>>Axton,
>>>>
>>>>I evaded your first question, didn't I? Well, yes, running no
>>>>select statement at all is cheaper than running a select statement
>>>>that is always false. It might be measured in nanoseconds, but it
>>>>it's larger than zero. Entering no Push If qualification at all
>>>>will cause a new record to be pushed in all conditions, *AND*
>>>>there's no database scan.
>>>>
>>>>Was this true in 4.0 and 4.5? My memory fails... I thought the
>>>>initial implementation of Push Fields required a query of some kind,
>>>>which is where that oddity of (1 = 0) came from.
>>>>
>>>>
>>>>--Tim
>>>>
>>>>
>>>>Axton wrote:
>>>>
>>>>
>>>>>Rick: I am working with Oracle 9.2.0.6 and these results are from
>>>>>that version.
>>>>>
>>>>>Tim: Is it less to do more, meaning that executing the select
>>>>>statement will be a cheaper than not executing a sql statement?
>>>>>Since you are such a passionate guy, what are your thoughts on the
>>>>>footnote in the original message?
>>>>>
>>>>>
>>>>>>*** I want to push the idea that the results list for forms should
>>>>>>always be set to only FID 1 unless it is required for either (1)
>>>>>>pick lists for multiple matches or (2) direct end user interaction
>>>>>>(and no, that does not include admin only interaction for things
>>>>>>like configuration data).
>>>>>
>>>>>
>>>>>
>>>>>James: The short description field is the default results list
>>>>>value if one is not defined.
>>>>>
>>>>>Axton
>>>>>
>>>>>On 7/20/05, Rick Cook wrote:
>>>>>
>>>>>
>>>>>>Axton,
>>>>>>
>>>>>>To add a bit of info, it was stated by someone that only Oracle
>>>>>>below versions 9i patch 4 run the table scan. The other DBMS' and
>>>>>>more current versions of Oracle handle it with indexes as
>>>>>>appropriate.
>>>>>>
>>>>>>Rick
>>>>>>
>>>>>>
>>>>>>
>>>>>>From: Action Request System discussion list(ARSList) on behalf of
>>>>>>Axton Sent: Wed 7/20/2005 11:08 AM To: arslist@ARSLIST.ORG
>>>>>>Subject: Push Fields with 1=0 in 'Push Fields If'
>>>>>>
>>>>>>
>>>>>>
>>>>>>I want to half way dispel some myths that were going around at RUG
>>>>>>this year. There were some presentations that indicated a 1=0 in
>>>>>>a push fields action always results in a table scan. Let me shed
>>>>>>a little light on this.
>>>>>>
>>>>>>- Background - garbage form with ~4k rows - ~20 columns/field with
>>>>>>data - Only index on this form is the system generate FID index -
>>>>>>Default-Order-By is enabled in this server
>>>>>>
>>>>>>- Fact 1: The presence of any value in the 'Push Field If' qual
>>>>>>bar of a push fields action causes remedy to execute a sql
>>>>>>statement to verify the presence of a record matching that
>>>>>>condition, even if it is 0=1. Whether this results in a table
>>>>>>scan or not is convered on the next point. - Proof for fact 1: If
>>>>>>the push fields qual is blank, no select statement is sent to the
>>>>>>db. Here is a select statement generated if 1=0 is in the push
>>>>>>fields qual (assume that no results list is defined):
>>>>>>
>>>>>>SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>>>>>
>>>>>>Execution Plan
>>>>>>---------------------------------------------------------- 0
>>>>>>SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097) 1
>>>>>>0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097) 2 1
>>>>>>FILTER 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
>>>>>>Bytes=83097)
>>>>>>
>>>>>>
>>>>>>- Fact 2: The presence of 1=0 in the push fields qual does not
>>>>>>always result in a table scan. - Proof for fact 2: If the results
>>>>>>list of the form only contains indexed columns, then an index scan
>>>>>>is performed instead of a table scan. Assume the results list on
>>>>>>the form now contains only the entry-id. The resulting sql select
>>>>>>statement yeilds the following:
>>>>>>
>>>>>>SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>>>>>
>>>>>>Execution Plan
>>>>>>---------------------------------------------------------- 0
>>>>>>SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312) 1
>>>>>>0 FILTER 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE)
>>>>>>(Cost=26 Card=3957 Bytes=63312)
>>>>>>
>>>>>>
>>>>>>Since the entry-id field always has an index, the above sql
>>>>>>statement did not trigger a full table scan. It only triggered an
>>>>>>INDEX (FULL SCAN) OF 'IT775' (UNIQUE).
>>>>>>
>>>>>>*** I want to push the idea that the results list for forms should
>>>>>>always be set to only FID 1 unless it is required for either (1)
>>>>>>pick lists for multiple matches or (2) direct end user interaction
>>>>>>(and no, that does not include admin only interaction for things
>>>>>>like configuration data).
>>>>>>
>>>>>>
>>>>>>Now as to whether the presence of 1=0 always results in a table
>>>>>>scan; technically it does not. More importantly though, is it a
>>>>>>bad practice to use 1=0? Well, it costs more than no select
>>>>>>regardless of whether it causes a table scan or an index scan.
>>>>>>
>>>>>>Axton
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>>>>>(Support: mailto:support@arslist.org)
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>>>>>(Support: mailto:support@arslist.org)
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>>UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>>>(Support: mailto:support@arslist.org)
>>>
>>>
>>>
>>>
>>>
>>>UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>>(Support: mailto:support@arslist.org)
>>>
>>
>>
>>
>>UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>(Support: mailto:support@arslist.org)
>>
>
>
>


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

This posting was submitted via the Web interface

Cost Based

Axton

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W
Sent: Thursday, July 21, 2005 3:24 PM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=0 in 'Push Fields If'

(For the Thread that will never die...)

What Optimizer Mode is your Oracle set to (Rule or Cost-based)?

Fred

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Tim Widowfield
Sent: Thursday, July 21, 2005 1:07 PM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=0 in 'Push Fields If'

Rogerio,

At least in 6.0 and beyond, when AR Server sees a blank Push Fields If, it
doesn't even send it to the server. I had thought that previous versions
treated the blank query as an unqualified query, but I couldn't remember for
sure. Your experience would indicate that it did.

Also, to everyone out there, I was wrong about Oracle. Even in the most
recent 9.x release (I can't speak for 10g) it really does look at the table.
So don't believe everything you read in the docs -- "Trust, but verify."

In previous tests, I think I must have always managed to trace queries where
the table was already cached in memory, which gave the false impression that
it wasn't touching the table.

Here's an example of a table that wasn't cached:

SQL> set autotrace on;
SQL> select * from aradmin.T35 where 1=0;

no rows selected

Statistics
----------------------------------------------------------
352 recursive calls
0 db block gets
81 consistent gets
1 physical reads
0 redo size
800 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
0 rows processed


But now that it's cached, what happens? Here are the stats:

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

Sad, but true. On this one, Oracle is as dumb as a sack of hammers.

No Daniel, you can't have the plaque back!

--Tim


Rogério Bronzoni wrote:
> Tim,
>
> We faced a serious problem in a client, here in Brasil! We did all
> push fields without condition and the Oracle was scanning all table,
> before creating the new record. It was in Oracle 8.1.7, ARS 5.1.2, as I
remember!
> We had to change all filters and place the '0=1' in all of them! I am
> not DBA, so I cannot enter in your discussion, but I wanted to present
> a real example of the way that the things work, at least in our case.
>
> Regards
>
> Rogerio Bronzoni Aguiar
> CMagnani Serviços de Informática
>
>
> Tim Widowfield escreveu:
>
>> Axton,
>>
>> I evaded your first question, didn't I? Well, yes, running no select
>> statement at all is cheaper than running a select statement that is
>> always false. It might be measured in nanoseconds, but it it's
>> larger than zero. Entering no Push If qualification at all will
>> cause a new record to be pushed in all conditions, *AND* there's no
database scan.
>>
>> Was this true in 4.0 and 4.5? My memory fails... I thought the
>> initial implementation of Push Fields required a query of some kind,
>> which is where that oddity of (1 = 0) came from.
>>
>>
>> --Tim
>>
>>
>> Axton wrote:
>>
>>> Rick: I am working with Oracle 9.2.0.6 and these results are from
>>> that version.
>>>
>>> Tim: Is it less to do more, meaning that executing the select
>>> statement will be a cheaper than not executing a sql statement?
>>> Since you are such a passionate guy, what are your thoughts on the
>>> footnote in the original message?
>>>
>>>> *** I want to push the idea that the results list for forms should
>>>> always be set to only FID 1 unless it is required for either (1)
>>>> pick lists for multiple matches or (2) direct end user interaction
>>>> (and no, that does not include admin only interaction for things
>>>> like configuration data).
>>>
>>>
>>>
>>> James: The short description field is the default results list value
>>> if one is not defined.
>>>
>>> Axton
>>>
>>> On 7/20/05, Rick Cook wrote:
>>>
>>>> Axton,
>>>>
>>>> To add a bit of info, it was stated by someone that only Oracle
>>>> below versions 9i patch 4 run the table scan. The other DBMS' and
>>>> more current versions of Oracle handle it with indexes as
>>>> appropriate.
>>>>
>>>> Rick
>>>>
>>>>
>>>>
>>>> From: Action Request System discussion list(ARSList) on behalf of
>>>> Axton Sent: Wed 7/20/2005 11:08 AM To: arslist@ARSLIST.ORG Subject:
>>>> Push Fields with 1=0 in 'Push Fields If'
>>>>
>>>>
>>>>
>>>> I want to half way dispel some myths that were going around at RUG
>>>> this year. There were some presentations that indicated a 1=0 in a
>>>> push fields action always results in a table scan. Let me shed a
>>>> little light on this.
>>>>
>>>> - Background - garbage form with ~4k rows - ~20 columns/field with
>>>> data - Only index on this form is the system generate FID index -
>>>> Default-Order-By is enabled in this server
>>>>
>>>> - Fact 1: The presence of any value in the 'Push Field If' qual bar
>>>> of a push fields action causes remedy to execute a sql statement to
>>>> verify the presence of a record matching that condition, even if it
>>>> is 0=1. Whether this results in a table scan or not is convered on
>>>> the next point. - Proof for fact 1: If the push fields qual is
>>>> blank, no select statement is sent to the db. Here is a select
>>>> statement generated if 1=0 is in the push fields qual (assume that
>>>> no results list is defined):
>>>>
>>>> SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>>>
>>>> Execution Plan
>>>> ---------------------------------------------------------- 0 SELECT
>>>> STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097) 1
>>>> 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097) 2 1
>>>> FILTER 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
>>>> Bytes=83097)
>>>>
>>>>
>>>> - Fact 2: The presence of 1=0 in the push fields qual does not
>>>> always result in a table scan. - Proof for fact 2: If the results
>>>> list of the form only contains indexed columns, then an index scan
>>>> is performed instead of a table scan. Assume the results list on
>>>> the form now contains only the entry-id. The resulting sql select
>>>> statement yeilds the following:
>>>>
>>>> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>>>
>>>> Execution Plan
>>>> ---------------------------------------------------------- 0 SELECT
>>>> STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312) 1
>>>> 0 FILTER 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE)
>>>> (Cost=26 Card=3957 Bytes=63312)
>>>>
>>>>
>>>> Since the entry-id field always has an index, the above sql
>>>> statement did not trigger a full table scan. It only triggered an
>>>> INDEX (FULL SCAN) OF 'IT775' (UNIQUE).
>>>>
>>>> *** I want to push the idea that the results list for forms should
>>>> always be set to only FID 1 unless it is required for either (1)
>>>> pick lists for multiple matches or (2) direct end user interaction
>>>> (and no, that does not include admin only interaction for things
>>>> like configuration data).
>>>>
>>>>
>>>> Now as to whether the presence of 1=0 always results in a table
>>>> scan; technically it does not. More importantly though, is it a
>>>> bad practice to use 1=0? Well, it costs more than no select
>>>> regardless of whether it causes a table scan or an index scan.
>>>>
>>>> Axton
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>>> (Support: mailto:support@arslist.org)
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>>> (Support: mailto:support@arslist.org)
>>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>> (Support: mailto:support@arslist.org)
>
>
>
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>



UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)



UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

(For the Thread that will never die...)

What Optimizer Mode is your Oracle set to (Rule or Cost-based)?

Fred

-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Tim Widowfield
Sent: Thursday, July 21, 2005 1:07 PM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=0 in 'Push Fields If'

Rogerio,

At least in 6.0 and beyond, when AR Server sees a blank Push Fields If, it doesn't even send it to the server. I had thought that previous versions treated the blank query as an unqualified query, but I couldn't remember for sure. Your experience would indicate that it did.

Also, to everyone out there, I was wrong about Oracle. Even in the most recent 9.x release (I can't speak for 10g) it really does look at the table. So don't believe everything you read in the docs -- "Trust, but verify."

In previous tests, I think I must have always managed to trace queries where the table was already cached in memory, which gave the false impression that it wasn't touching the table.

Here's an example of a table that wasn't cached:

SQL> set autotrace on;
SQL> select * from aradmin.T35 where 1=0;

no rows selected

Statistics
----------------------------------------------------------
352 recursive calls
0 db block gets
81 consistent gets
1 physical reads
0 redo size
800 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
0 rows processed


But now that it's cached, what happens? Here are the stats:

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

Sad, but true. On this one, Oracle is as dumb as a sack of hammers.

No Daniel, you can't have the plaque back!

--Tim


Rogério Bronzoni wrote:
> Tim,
>
> We faced a serious problem in a client, here in Brasil! We did all
> push fields without condition and the Oracle was scanning all table,
> before creating the new record. It was in Oracle 8.1.7, ARS 5.1.2, as I remember!
> We had to change all filters and place the '0=1' in all of them! I am
> not DBA, so I cannot enter in your discussion, but I wanted to present
> a real example of the way that the things work, at least in our case.
>
> Regards
>
> Rogerio Bronzoni Aguiar
> CMagnani Serviços de Informática
>
>
> Tim Widowfield escreveu:
>
>> Axton,
>>
>> I evaded your first question, didn't I? Well, yes, running no select
>> statement at all is cheaper than running a select statement that is
>> always false. It might be measured in nanoseconds, but it it's
>> larger than zero. Entering no Push If qualification at all will
>> cause a new record to be pushed in all conditions, *AND* there's no database scan.
>>
>> Was this true in 4.0 and 4.5? My memory fails... I thought the
>> initial implementation of Push Fields required a query of some kind,
>> which is where that oddity of (1 = 0) came from.
>>
>>
>> --Tim
>>
>>
>> Axton wrote:
>>
>>> Rick: I am working with Oracle 9.2.0.6 and these results are from
>>> that version.
>>>
>>> Tim: Is it less to do more, meaning that executing the select
>>> statement will be a cheaper than not executing a sql statement?
>>> Since you are such a passionate guy, what are your thoughts on the
>>> footnote in the original message?
>>>
>>>> *** I want to push the idea that the results list for forms should
>>>> always be set to only FID 1 unless it is required for either (1)
>>>> pick lists for multiple matches or (2) direct end user interaction
>>>> (and no, that does not include admin only interaction for things
>>>> like configuration data).
>>>
>>>
>>>
>>> James: The short description field is the default results list value
>>> if one is not defined.
>>>
>>> Axton
>>>
>>> On 7/20/05, Rick Cook wrote:
>>>
>>>> Axton,
>>>>
>>>> To add a bit of info, it was stated by someone that only Oracle
>>>> below versions 9i patch 4 run the table scan. The other DBMS' and
>>>> more current versions of Oracle handle it with indexes as
>>>> appropriate.
>>>>
>>>> Rick
>>>>
>>>>
>>>>
>>>> From: Action Request System discussion list(ARSList) on behalf of
>>>> Axton Sent: Wed 7/20/2005 11:08 AM To: arslist@ARSLIST.ORG Subject:
>>>> Push Fields with 1=0 in 'Push Fields If'
>>>>
>>>>
>>>>
>>>> I want to half way dispel some myths that were going around at RUG
>>>> this year. There were some presentations that indicated a 1=0 in a
>>>> push fields action always results in a table scan. Let me shed a
>>>> little light on this.
>>>>
>>>> - Background - garbage form with ~4k rows - ~20 columns/field with
>>>> data - Only index on this form is the system generate FID index -
>>>> Default-Order-By is enabled in this server
>>>>
>>>> - Fact 1: The presence of any value in the 'Push Field If' qual bar
>>>> of a push fields action causes remedy to execute a sql statement to
>>>> verify the presence of a record matching that condition, even if it
>>>> is 0=1. Whether this results in a table scan or not is convered on
>>>> the next point. - Proof for fact 1: If the push fields qual is
>>>> blank, no select statement is sent to the db. Here is a select
>>>> statement generated if 1=0 is in the push fields qual (assume that
>>>> no results list is defined):
>>>>
>>>> SELECT T775.C1,T775.C8 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>>>
>>>> Execution Plan
>>>> ---------------------------------------------------------- 0 SELECT
>>>> STATEMENT Optimizer=CHOOSE (Cost=37 Card=3957 Bytes=83097) 1
>>>> 0 SORT (ORDER BY) (Cost=37 Card=3957 Bytes=83097) 2 1
>>>> FILTER 3 2 TABLE ACCESS (FULL) OF 'T775' (Cost=6 Card=3957
>>>> Bytes=83097)
>>>>
>>>>
>>>> - Fact 2: The presence of 1=0 in the push fields qual does not
>>>> always result in a table scan. - Proof for fact 2: If the results
>>>> list of the form only contains indexed columns, then an index scan
>>>> is performed instead of a table scan. Assume the results list on
>>>> the form now contains only the entry-id. The resulting sql select
>>>> statement yeilds the following:
>>>>
>>>> SELECT T775.C1,T775.C1 FROM T775 WHERE (1 = 0) ORDER BY 1 ASC
>>>>
>>>> Execution Plan
>>>> ---------------------------------------------------------- 0 SELECT
>>>> STATEMENT Optimizer=CHOOSE (Cost=26 Card=3957 Bytes=63312) 1
>>>> 0 FILTER 2 1 INDEX (FULL SCAN) OF 'IT775' (UNIQUE)
>>>> (Cost=26 Card=3957 Bytes=63312)
>>>>
>>>>
>>>> Since the entry-id field always has an index, the above sql
>>>> statement did not trigger a full table scan. It only triggered an
>>>> INDEX (FULL SCAN) OF 'IT775' (UNIQUE).
>>>>
>>>> *** I want to push the idea that the results list for forms should
>>>> always be set to only FID 1 unless it is required for either (1)
>>>> pick lists for multiple matches or (2) direct end user interaction
>>>> (and no, that does not include admin only interaction for things
>>>> like configuration data).
>>>>
>>>>
>>>> Now as to whether the presence of 1=0 always results in a table
>>>> scan; technically it does not. More importantly though, is it a
>>>> bad practice to use 1=0? Well, it costs more than no select
>>>> regardless of whether it causes a table scan or an index scan.
>>>>
>>>> Axton
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>>> (Support: mailto:support@arslist.org)
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>>>> (Support: mailto:support@arslist.org)
>>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
>> (Support: mailto:support@arslist.org)
>
>
>
>
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
> (Support: mailto:support@arslist.org)
>


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Here is an additional query that looks for 1=2 or 2=1 (seems
prevalent). The original looks for 0=1 or 1=0.

select 'ACTLINK' as ObjType
, enable
, name
from actlink
where actlinkid in (
select actlinkid
from actlinkpush
where assignshort like '%4\1\2\2\2\2\2\1%'
or assignshort like '%4\1\2\2\1\2\2\1%')
group by enable, name
union
select 'FILTER' as ObjType
, enable
, name
from filter
where filterid in (
select filterid
from filterpush
where assignshort like '%4\1\2\2\2\2\2\1%'
or assignshort like '%4\1\2\2\1\2\2\2%')
group by enable, name

Axton

On 7/21/05, Axton wrote:
> Here is the query I constructed to return a list of the active links
> and filters that exhibit this:
>
> select 'ACTLINK' as ObjType
> , name
> from actlink
> where actlinkid in (
> select actlinkid
> from actlinkpush
> where assignshort like '%4\1\2\2\1\2\2\0%'
> or assignshort like '%4\1\2\2\0\2\2\1%')
> group by name
> union
> select 'FILTER' as ObjType
> , name
> from filter
> where filterid in (
> select filterid
> from filterpush
> where assignshort like '%4\1\2\2\1\2\2\0%'
> or assignshort like '%4\1\2\2\0\2\2\1%')
> group by name
>
> Axton
>


--
Axton Grams
AxtonGrams<åt>gmailcom


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Guys,

Could someone summarize the conclusion here?

Thanks,

jw

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Axton
Sent: Friday, July 22, 2005 11:57 AM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=0 in 'Push Fields If'

Here is an additional query that looks for 1=2 or 2=1 (seems
prevalent). The original looks for 0=1 or 1=0.

select 'ACTLINK' as ObjType
, enable
, name
from actlink
where actlinkid in (
select actlinkid
from actlinkpush
where assignshort like '%4\1\2\2\2\2\2\1%'
or assignshort like '%4\1\2\2\1\2\2\1%')
group by enable, name
union
select 'FILTER' as ObjType
, enable
, name
from filter
where filterid in (
select filterid
from filterpush
where assignshort like '%4\1\2\2\2\2\2\1%'
or assignshort like '%4\1\2\2\1\2\2\2%')
group by enable, name

Axton

On 7/21/05, Axton wrote:
> Here is the query I constructed to return a list of the active links
> and filters that exhibit this:
>
> select 'ACTLINK' as ObjType
> , name
> from actlink
> where actlinkid in (
> select actlinkid
> from actlinkpush
> where assignshort like '%4\1\2\2\1\2\2\0%'
> or assignshort like '%4\1\2\2\0\2\2\1%')
> group by name
> union
> select 'FILTER' as ObjType
> , name
> from filter
> where filterid in (
> select filterid
> from filterpush
> where assignshort like '%4\1\2\2\1\2\2\0%'
> or assignshort like '%4\1\2\2\0\2\2\1%')
> group by name
>
> Axton
>


--
Axton Grams
AxtonGrams<åt>gmailcom



UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

It doesn't do a table scan unless you're on Oracle 9i patch 3 or less but
you shouldn't need to do it anyways (for push fields) if you're on 5.1.2 or
better as with a blank qualification.

-----Original Message-----
From: Jack Wise [mailto:jack.wise@NETFIRST.COM]
Sent: Friday, July 22, 2005 3:46 PM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=0 in 'Push Fields If'

Guys,

Could someone summarize the conclusion here?

Thanks,

jw

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Axton
Sent: Friday, July 22, 2005 11:57 AM
To: arslist@ARSLIST.ORG
Subject: Re: Push Fields with 1=3D0 in 'Push Fields If'

Here is an additional query that looks for 1=3D2 or 2=3D1 (seems
prevalent). The original looks for 0=3D1 or 1=3D0.

select 'ACTLINK' as ObjType
, enable
, name
from actlink
where actlinkid in (
select actlinkid
from actlinkpush
where assignshort like '%4\1\2\2\2\2\2\1%'
or assignshort like '%4\1\2\2\1\2\2\1%')
group by enable, name
union
select 'FILTER' as ObjType
, enable
, name
from filter
where filterid in (
select filterid
from filterpush
where assignshort like '%4\1\2\2\2\2\2\1%'
or assignshort like '%4\1\2\2\1\2\2\2%')
group by enable, name

Axton

On 7/21/05, Axton wrote:
> Here is the query I constructed to return a list of the active links
> and filters that exhibit this:
>=20
> select 'ACTLINK' as ObjType
> , name
> from actlink
> where actlinkid in (
> select actlinkid
> from actlinkpush
> where assignshort like '%4\1\2\2\1\2\2\0%'
> or assignshort like '%4\1\2\2\0\2\2\1%')
> group by name
> union
> select 'FILTER' as ObjType
> , name
> from filter
> where filterid in (
> select filterid
> from filterpush
> where assignshort like '%4\1\2\2\1\2\2\0%'
> or assignshort like '%4\1\2\2\0\2\2\1%')
> group by name
>=20
> Axton
>=20


--=20
Axton Grams
AxtonGrams<=E5t>gmailcom

=


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)



UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)