SQL>
SQL> create table students
2 ( studentID number constraint students_pk primary key,
3 name varchar2(10) );
Table created.
SQL>
SQL> create table documentMaster
2 ( documentId number constraint document_pk primary key,
3 description varchar2(10) );
Table created.
SQL>
SQL> create table admission_docs
2 ( studentID references students,
3 documentId references documentMaster,
4 dt date,
5 constraint admission_pk primary key(studentID, documentId));
Table created.
SQL>
SQL> insert into students
2 select object_id, object_name
3 from all_objects;
SQL>
SQL> insert into documentMaster
2 select ROWNUM, 'doc ' || ROWNUM
3 from all_users
4 where ROWNUM <= 5;
5 rows created.
SQL>
SQL> insert into admission_docs
2 select object_id, mod(ROWNUM,3)+1, created
3 from all_objects, (select 1 from all_users where ROWNUM <= 3);
SQL>
SQL> analyze table students compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed.
SQL>
SQL> analyze table documentMaster compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed.
SQL>
SQL> analyze table admission_docs compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed.
SQL>
SQL> set autotrace on
SQL> variable bv number
SQL> exec :bv := 1234
PL/SQL procedure successfully completed.
SQL>
SQL> select a.* , decode(b.dt,null,'No','Yes') submitted, b.dt
2 from (
3 select *
4 from students, documentMaster
5 where students.studentID = :bv
6 ) a, admission_docs b
7 where a.studentID = b.studentID(+)
8 and a.documentId = b.documentId (+)
9 /
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3775454522
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 5 | 550 | 2
|
| 1 | NESTED LOOPS OUTER | | 5 | 550 | 2
|
| 2 | VIEW | | 5 | 330 | 2
|
| 3 | NESTED LOOPS | | 5 | 225 | 2
|
| 4 | TABLE ACCESS BY INDEX ROWID| STUDENTS | 1 | 33 | 1
|
|* 5 | INDEX UNIQUE SCAN | STUDENTS_PK | 1 | |
|
| 6 | TABLE ACCESS FULL | DOCUMENTMASTER | 5 | 60 | 1
|
| 7 | TABLE ACCESS BY INDEX ROWID | ADMISSION_DOCS | 1 | 44 |
|
|* 8 | INDEX UNIQUE SCAN | ADMISSION_PK | 1 | |
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("STUDENTS"."STUDENTID"=TO_NUMBER(:BV))
8 - access("A"."STUDENTID"="B"."STUDENTID"(+) AND
"A"."DOCUMENTID"="B"."DOCUMENTID"(+))
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
564 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> drop table students cascade constraints;
Table dropped.
SQL>
SQL> drop table documentMaster cascade constraints;
Table dropped.
SQL>
SQL> drop table admission_docs;
Table dropped.