Mark Townsend wrote:
> Joe Weinstein wrote:
>
>>
>>
>> Mark Townsend wrote:
>>
>>> Joe Weinstein wrote:
>>>
>>>> Mark Townsend wrote:
>>>>
>>>>> Joe Weinstein wrote:
>>>>>
>>>>>> Response from a TAR (SR).
>>>>>
>>>>>
>>>>>
>>>>> TAR number ? I would enjoy shaking some trees...
>>>>
>>>>
>>>>
>>>> Thanks: 5313790.993 Do let me know what you find out.
>>>
>>>
>>>
>
> Information from the optimizer team confirms exactly what Jonathan said,
> as follows -
> Mark,
>
> The plan will show a full table scan (or may be fast full scan if you
> have an index on the table) with a filter operation as the parent.
> The filter operation is added because of the condition (1=0).
> At run-time the filter will evaluate to FALSE and the underlying
> operation (full table scan of ACTIVITY_ENTRY) will not be started.
> Hence no rows will be locked. Here is an illustration with the query on
> table EMP (schema SCOTT):
>
> SQL> explain plan for SELECT 1 FROM emp WHERE (1=0) FOR UPDATE;
>
> Explained.
>
> SQL> @?/rdbms/admin/utlxpls
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------
>
> Plan hash value: 612782990
>
> --------------------------------------------------------------------
> | Id | Operation | Name | Rows | Cost (%CPU)| Time |
> --------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 0 (0)| |
> | 1 | FOR UPDATE | | | | |
> |* 2 | FILTER | | | | |
> | 3 | TABLE ACCESS FULL| EMP | 14 | 2 (0)| 00:00:01 |
> --------------------------------------------------------------------
>
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter(NULL IS NOT NULL)
>
>
> So I think the performance issue your customer is reporting is not
> related to a full table scan.
Much appreciated. I am in your
debt.
Joe Weinstein at BEA Systems
Received on Thu Apr 13 2006 - 10:15:45 CDT
