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