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)
--
//www.freelists.org/webpage/oracle-l
