0

I'm issuing the following query:

Select Id, AssignedOn,
(
  SELECT gn.Name FROM GeoNode gn WHERE gn.Id = 
  (
    SELECT MIN(t.StartPointId) KEEP (dense_rank FIRST ORDER BY t.StartTime1)
    FROM Task t
    WHERE t.Id IN 
    (
      SELECT TaskId 
      FROM ResourceAssignment 
      WHERE TargetResourceId = ra.TargetResourceId AND SourceResourceId = ra.SourceResourceId
    )
  ) 
) as DepartureNodeName
FROM 
    ResourceAssignment ra;

the "Explain Plan" shows me TABLE ACCESS (FULL) on "TASK" table, I want to get rid of it.

The query basically wants to take StartPointId where StartTime1 is minimum from Task table based on saved Task ids.

Edit:

Execution plan is given bellow, you can see the cost and access of Task table:

Plan hash value: 3000073173

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                                |   281 | 11521 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID    | GEONODE                        |     1 |    24 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN             | PK_GEONODE                     |     1 |       |     1   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE               |                                |     1 |    25 |            |          |
|*  4 |     FILTER                      |                                |       |       |            |          |
|   5 |      TABLE ACCESS FULL          | TASK                           | 15618 |   381K|    68   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS BY INDEX ROWID| RESOURCEASSIGNMENT             |     1 |    30 |     2   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN          | PK_RESASSIGNMENT_SOURCE_TARGET |     5 |       |     1   (0)| 00:00:01 |
|   8 |  TABLE ACCESS FULL              | RESOURCEASSIGNMENT             |   281 | 11521 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$2 / GN@SEL$2
   2 - SEL$2 / GN@SEL$2
   3 - SEL$3
   5 - SEL$3 / T@SEL$3
   6 - SEL$4 / RESOURCEASSIGNMENT@SEL$4
   7 - SEL$4 / RESOURCEASSIGNMENT@SEL$4
   8 - SEL$1 / RA@SEL$1

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

   2 - access("GN"."ID"= (SELECT MIN("T"."STARTPOINTID") KEEP (DENSE_RANK FIRST  ORDER BY 
              "T"."STARTTIME1") FROM "TASK" "SYS_ALIAS_1" WHERE  EXISTS (SELECT 0 FROM "RESOURCEASSIGNMENT" 
              "RESOURCEASSIGNMENT" WHERE "TARGETRESOURCEID"=:B1 AND "SOURCERESOURCEID"=:B2 AND "TASKID"=:B3)))
   4 - filter( EXISTS (SELECT 0 FROM "RESOURCEASSIGNMENT" "RESOURCEASSIGNMENT" WHERE 
              "TARGETRESOURCEID"=:B1 AND "SOURCERESOURCEID"=:B2 AND "TASKID"=:B3))
   6 - filter("TASKID"=:B1)
   7 - access("SOURCERESOURCEID"=:B1 AND "TARGETRESOURCEID"=:B2)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "GN".ROWID[ROWID,10], "GN"."NAME"[NVARCHAR2,1000]
   2 - "GN".ROWID[ROWID,10]
   3 - (#keys=0) MIN("T"."STARTPOINTID") KEEP (DENSE_RANK FIRST  ORDER BY "T"."STARTTIME1")[22]
   4 - "T"."STARTPOINTID"[NUMBER,22], "T"."STARTTIME1"[NUMBER,22]
   5 - "T"."ID"[NUMBER,22], "T"."STARTPOINTID"[NUMBER,22], "T"."STARTTIME1"[NUMBER,22]
   6 - "RESOURCEASSIGNMENT".ROWID[ROWID,10], "TASKID"[NUMBER,22]
   7 - "RESOURCEASSIGNMENT".ROWID[ROWID,10]
   8 - "ID"[NUMBER,22], "SYS_ALIAS_7"."SOURCERESOURCEID"[NUMBER,22], 
       "SYS_ALIAS_7"."TARGETRESOURCEID"[NUMBER,22], "ASSIGNEDON"[TIMESTAMP,11]
Ali
  • 1,648
  • 2
  • 26
  • 48
  • 2
    Why do you think the optimiser is wrong to choose a full table scan? It may be the most efficient option; without seeing your tables or data it looks like you're likely to hit a large portion of the task table, if not all of it. – Alex Poole Sep 13 '19 at 14:57
  • Because of this I guess the time it takes to execute the query increases quite significantly when I have large number of records in the table as the explain plan shows "Cardinality=15618" and "Cost=68". – Ali Sep 13 '19 at 15:00
  • 1
    I would be curious to understand why you've got two references to `ResourceAssignment`, one in the outer `from` and one in the inner `in` list. Are there multiple rows in that table with the same `SourceResourceID` and `TargetResourceID`? It would seem like that ought to be unique based on the name of the table in which case you can simplify the inner query. – Justin Cave Sep 13 '19 at 15:05
  • @JustinCave Actually it's a snippet from large query, we basically have multiple records per TaskId where `SourceResourceId` and `TargetResourceId` is same. – Ali Sep 13 '19 at 15:13
  • 3
    *"the time it takes to execute the query increases quite significantly when I have large number of records"*. Er yeah. It generally takes more time to do more work. Query optimization is a matter of many different factors. You have not provided sufficient detail for anybody to answer your question. Please read [this excellent post on asking Oracle tuning questions](https://stackoverflow.com/a/34975420/146325). It explains the information you need to provide for us. It may also help you to solve your problem by yourself. – APC Sep 13 '19 at 15:22
  • 2
    I can't see the explain plan in your question. – krokodilko Sep 13 '19 at 16:02
  • 1
    Please **edit your question** and include the explain plan output **AS TEXT** in the question. Also, gather stats on all the tables involved prior to running the explain plan. Thanks. – Bob Jarvis - Слава Україні Sep 13 '19 at 17:10
  • Be aware that the "Cost" of an explain plan is probably not what you're thinking of. It's an internal parameter used to compare plans, not a measure of how long the plan will take. – eaolson Sep 14 '19 at 19:03
  • Is `Task.Id` indexed? Did you try hint optimizer to use index on `Task.Id`? – Konstantin Sorokin Sep 16 '19 at 12:53

0 Answers0