1

In Oracle database, when I get the execution plan, or explain plan, which row source is considered as the driving table? Is it the one above the other one? Or does this change in nested joins, merge joins, etc? Besides, does it change if I get the execution plan by SQL Developer, or explain plan command, etc? I know that the optimizer selects the smaller table as the driving table. But I know that, it does not do that all the times. So I am confused about that. By the way, what I ask is about the cost based optimization. So if you can explain on CBO, it will be appreciated. For example in the plan below, which one is the driving table? How we understand that it is the driving table? Is it because it is on top? Best Regards

select * from hr.employees e join hr.departments d on d.department_id = 
e.department_id where d.department_name like 'A%'

Plan hash value: 1021246405

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |       |       |     4 (100)|          |
|   1 |  NESTED LOOPS                |                   |    10 |   900 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |                   |    10 |   900 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | DEPARTMENTS       |     1 |    21 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   690 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   3 - filter("D"."DEPARTMENT_NAME" LIKE 'A%')
   4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

Note
-----
   - this is an adaptive plan
oramas
  • 881
  • 7
  • 12
  • 1
    there's more than 1 way to get a plan in SQL Developer, but in general I do not recommend using the Explain Plan feature, instead get the cached plan via the SQL_ID - use the drop down control on the plan button in the sql worksheet to see what i mean – thatjeffsmith Dec 12 '18 at 13:35
  • Thank you @thatjeffsmith. But when I connect with HR user, I cannot see the others. They are all grayed. But when I connect with system user, I can see them. Is is because of privige or something? How can I add HR user that option? – oramas Dec 12 '18 at 13:56
  • 1
    you need select privs on things like v$sql and v$sql_plan – thatjeffsmith Dec 12 '18 at 14:36
  • 1
    also, if you post the actual plan you want help help, i'm sure someone here will help decipher it for you, probably best to post text based plan, and not a picture of it – thatjeffsmith Dec 12 '18 at 14:36
  • Thank you @thatjeffsmith. I did that update. – oramas Dec 12 '18 at 14:50

0 Answers0