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)