Re: Oracle scans table with "WHERE (1=0)"?

From: Mark Townsend <markbtownsend_at_comcast.net>
Date: Sun, 09 Apr 2006 14:49:07 -0700
Message-ID: <44398153.9040406@comcast.net>

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

|   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. Received on Sun Apr 09 2006 - 16:49:07 CDT