CODEWITHSUNDEEP
sqloracle-databaseoracle10gsql-execution-plan
Zeus
Zeus

Reputation: 6576

Explain plan and Query execution time differences

I have two tables TABLE_A and TABLE_B ( one to many. FK of table_a in table_b ). I have written the following 3 queries and each one of it will perform at different speeds on the tables but basically they all are doing the same.

Time: 3.916 seconds.

SELECT count(*)
FROM TABLE_A hconn
WHERE EXISTS
  (SELECT *
  FROM TABLE_B hipconn
  WHERE HIPCONN.A_ID = HCONN.A_ID
  );

Time: 3.52 seconds

 SELECT COUNT(*)
FROM TABLE_A hconn,
  TABLE_B HIPCONN
WHERE HCONN.A_ID = HIPCONN.A_ID;

Time: 2.72 seconds. 

 SELECT COUNT(*)
FROM TABLE_A HCONN
JOIN TABLE_B HIPCONN
ON HCONN.A_ID = HIPCONN.A_ID;

From the above timings, we can know that the last query is performing better than other. (I've tested them a bunch of times and they all perform in the same order mentioned but the last query performed well always).

I've started looking at the explain plan for the above queries to find out why it is happening.

Query explain plan, it prints out the same cost and time for all the above queries without any difference.(Explain plan below) I re-ran a couple of times, but the result is same for all the above queries.

Question: Why does the speed of the results vary when the explain plan showed that it takes same amount of time for all the queries? where am I going wrong?

Plan hash value: 600428245

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                                |     1 |    11 |       | 12913   (2)| 00:02:35 |
|   1 |  SORT AGGREGATE                |                                |     1 |    11 |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI         |                                |  2273K|    23M|    39M| 12913   (2)| 00:02:35 |
|   3 |    INDEX STORAGE FAST FULL SCAN| BIN$ACCkNNuTHKPgUJAKNJgj5Q==$0 |  2278K|    13M|       |  1685   (2)| 00:00:21 |
|   4 |    INDEX STORAGE FAST FULL SCAN| BIN$ACCkNNubHKPgUJAKNJgj5Q==$0 |  6448K|    30M|       |  4009   (2)| 00:00:49 |
-------------------------------------------------------------------------------------------------------------------------

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

   2 - access("HIPCONN"."A_ID"="HCONN"."A_ID")

Upvotes: 2

Views: 1530

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172618

You may use DBMS_XPLAN.DISPLAY_CURSOR to display the actual execution plan for the last SQL statement executed, since the queries may have more than one execution plan in the library cache.

Also you may enable a 10046 trace at level 12 to check why the queries are responding with different execution times.

Upvotes: 1

Related Questions