Possibly Interesting Execution Plan

29 10 2010

October 29, 2010

I recently found what I believe to be an interesting execution plan, but then maybe I am just having difficulty thinking of blog article topics.  Here is the execution plan:

Plan hash value: 904523798

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |     1 |    80 |       |  5464   (1)| 00:01:06 |
|   1 |  HASH GROUP BY                 |                           |     1 |    80 |       |  5464   (1)| 00:01:06 |
|   2 |   NESTED LOOPS                 |                           |     1 |    80 |       |  5463   (1)| 00:01:06 |
|*  3 |    HASH JOIN                   |                           |    36 |  2340 |  6376K|  5401   (1)| 00:01:05 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T_DATA_CORP_AUTHORIZATION |   408 |  9792 |       |    75   (0)| 00:00:01 |
|   5 |      NESTED LOOPS              |                           |   116K|  5003K|       |  2535   (1)| 00:00:31 |
|*  6 |       TABLE ACCESS FULL        | T_DATA_FILE_DETAILS       |   286 |  5720 |       |   202   (4)| 00:00:03 |
|*  7 |       INDEX RANGE SCAN         | PK_DATA_CORPAUTHORIZATION |   408 |       |       |     6   (0)| 00:00:01 |
|   8 |     INDEX FAST FULL SCAN       | IDX_FILE_REF_PLOC         |   911K|    18M|       |  1120   (1)| 00:00:14 |
|*  9 |    TABLE ACCESS BY INDEX ROWID | T_DATA_RECORD_DETAILS     |     1 |    15 |       |     2   (0)| 00:00:01 |
|* 10 |     INDEX UNIQUE SCAN          | PK_DATA_RECORD_DETAILS    |     1 |       |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("D"."FILE_ID"="R"."FILE_ID" AND "D"."RECORD_REFERENCE_NO"="R"."RECORD_REFERENCE_NO" AND
              "D"."FILE_ID"="M"."FILE_ID")
   6 - filter(TO_DATE(INTERNAL_FUNCTION("M"."FILE_UPLOADED_ON"))>=TO_DATE('2010-10-01 00:00:00',
              'yyyy-mm-dd hh24:mi:ss') AND TO_DATE(INTERNAL_FUNCTION("M"."FILE_UPLOADED_ON"))<=TO_DATE('2010-10-28
              00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
   7 - access("R"."FILE_ID"="M"."FILE_ID")
   9 - filter(("N"."PRINTING_STATUS" IS NULL OR "N"."PRINTING_STATUS"<>'C') AND
              "N"."CORPORATE_AUTHORIZATION_DONE"='Y')
  10 - access("N"."FILE_ID"="R"."FILE_ID" AND "N"."RECORD_REFERENCE_NO"="R"."RECORD_REFERENCE_NO")

What do you think?  Perhaps someone will find something that is more interesting about the execution plan than what I found?  Or maybe it is just a boring execution plan that is nothing special, and I am just using this as an excuse for a blog article topic.  It might help to see the original SQL statement, but maybe not.








Follow

Get every new post delivered to your Inbox.

Join 141 other followers