How does the FILTER operation work?

  • From: Paul Houghton <Paul.Houghton@xxxxxxxxxxxxx>
  • To: Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 24 May 2018 12:06:22 +0000

Oracle 12.1.0.2. Linux x86_64.

I was asked why the following SQL was running for so long (~6 minutes). I can 
see a number of problems with it, and suggested that a way to tune it would be 
only to select the required data. For my own interest I want to understand why 
the cost of the insert is so high compared to the cost of the table scans. 

If I remove the "not exists" (see below), the access of the temp table 
disappears (as you would expect) and the insert cost goes right down, so it 
must be the filter that is the issue. However, I don't understand why it is so 
expensive. I can't find documentation about what it is doing. Surely it has to 
compare 21211 rows against the 1 row (Actually 0, but I understand the 
optimizer never says 0) returned by the not exists sub query. I would have 
thought this means that the cost should be more like the cost of accessing the 
data in the first place? Clearly the optimizer is correct as the query does 
take a long time to run.

Also, why is the cost of the full table scan of the photo table so different 
between the two queries? 

The queries are below. I edited the table names, (There are two, photo and 
temp) and the id, hopefully consistently.

Photo.photo is a blob. Emplid and emplid_search are varchar2(11). Oprid is 
varchar2(30). TEMP has no indexes. Photo has an index on emplid.

Thanks in advance for your insights.
 
  INSERT INTO TEMP
  SELECT DISTINCT 'id',
    PHT.emplid
  FROM PHOTO PHT
  WHERE 1               =1
  AND PHT.PHOTO IS NOT NULL
  AND NOT EXISTS
    (SELECT 'x'
    FROM TEMP TMP
    WHERE PHT.EMPLID=TMP.EMPLID_SRCH
    AND TMP.OPRID  = 'id'
    )
  ;  

  
-------------------------------------------------------------------------------------
  | Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| 
Time     |
  
-------------------------------------------------------------------------------------
  |   0 | INSERT STATEMENT         |          | 21210 |  3334K|  5802K  (2)| 
00:03:47 |
  |   1 |  LOAD TABLE CONVENTIONAL | TEMP     |       |       |            |    
      |
  |*  2 |   FILTER                 |          |       |       |            |    
      |
  |*  3 |    TABLE ACCESS FULL     | PHOTO    | 21211 |  3334K|   313   (1)| 
00:00:01 |
  |*  4 |    TABLE ACCESS FULL     | TEMP     |     1 |    17 |   380   (2)| 
00:00:01 |
  
-------------------------------------------------------------------------------------

  Predicate Information (identified by operation id):
  ---------------------------------------------------

   2 - filter( NOT EXISTS (SELECT 0 FROM " TEMP" "TMP" WHERE
              "TMP"."EMPLID_SRCH"=:B1 AND "TMP"."OPRID"='id'))
   3 - filter("PHT"."PHOTO" IS NOT NULL)
   4 - filter("TMP"."EMPLID_SRCH"=:B1 AND "TMP"."OPRID"='id')

...

  INSERT INTO TEMP
  SELECT DISTINCT 'id',
    PHT.emplid
  FROM PHOTO PHT
  WHERE 1               =1
  AND PHT.UC_PBI_PHOTO IS NOT NULL;

  
-------------------------------------------------------------------------------------
  | Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| 
Time     |
  
-------------------------------------------------------------------------------------
  |   0 | INSERT STATEMENT         |          | 21211 |  3334K|  3659   (1)| 
00:00:01 |
  |   1 |  LOAD TABLE CONVENTIONAL | TEMP     |       |       |            |    
      |
  |*  2 |   TABLE ACCESS FULL      | PHOTO    | 21211 |  3334K|  3659   (1)| 
00:00:01 |
  
-------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     2 - filter("PHT"."PHOTO" IS NOT NULL)
--
http://www.freelists.org/webpage/oracle-l


Other related posts: