** for System user with aministrator right SQL> set autotrace on explain; ** a simple query that selects the table names from the system table named 'user_tables' SQL> select table_name from user_tables; TABLE_NAME ------------------------------ AQ$_INTERNET_AGENTS AQ$_INTERNET_AGENT_PRIVS AQ$_QUEUES AQ$_QUEUE_TABLES AQ$_SCHEDULES DEF$_AQCALL DEF$_AQERROR DEF$_CALLDEST DEF$_DEFAULTDEST DEF$_DESTINATION DEF$_ERROR TABLE_NAME ------------------------------ DEF$_LOB DEF$_ORIGIN DEF$_PROPAGATOR DEF$_PUSHED_TRANSACTIONS DEF$_TEMP$LOB HELP LOGMNRC_DBNAME_UID_MAP LOGMNRC_GSII LOGMNRC_GTCS LOGMNRC_GTLO LOGMNR_AGE_SPILL$ TABLE_NAME ------------------------------ LOGMNR_ATTRCOL$ LOGMNR_ATTRIBUTE$ LOGMNR_CCOL$ LOGMNR_CDEF$ LOGMNR_COL$ LOGMNR_COLTYPE$ LOGMNR_DICTIONARY$ LOGMNR_DICTSTATE$ LOGMNR_HEADER1$ LOGMNR_HEADER2$ LOGMNR_ICOL$ TABLE_NAME ------------------------------ LOGMNR_IND$ LOGMNR_INDCOMPART$ LOGMNR_INDPART$ LOGMNR_INDSUBPART$ LOGMNR_LOB$ LOGMNR_LOBFRAG$ LOGMNR_LOG$ LOGMNR_OBJ$ LOGMNR_PROCESSED_LOG$ LOGMNR_RESTART_CKPT$ LOGMNR_RESTART_CKPT_TXINFO$ TABLE_NAME ------------------------------ LOGMNR_SESSION$ LOGMNR_SPILL$ LOGMNR_TAB$ LOGMNR_TABCOMPART$ LOGMNR_TABPART$ LOGMNR_TABSUBPART$ LOGMNR_TS$ LOGMNR_TYPE$ LOGMNR_UID$ LOGMNR_USER$ LOGSTDBY$APPLY_MILESTONE TABLE_NAME ------------------------------ LOGSTDBY$APPLY_PROGRESS LOGSTDBY$EVENTS LOGSTDBY$PARAMETERS LOGSTDBY$PLSQL LOGSTDBY$SCN LOGSTDBY$SKIP LOGSTDBY$SKIP_SUPPORT LOGSTDBY$SKIP_TRANSACTION MVIEW$_ADV_AJG MVIEW$_ADV_BASETABLE MVIEW$_ADV_CLIQUE TABLE_NAME ------------------------------ MVIEW$_ADV_ELIGIBLE MVIEW$_ADV_EXCEPTIONS MVIEW$_ADV_FILTER MVIEW$_ADV_FILTERINSTANCE MVIEW$_ADV_FJG MVIEW$_ADV_GC MVIEW$_ADV_INDEX MVIEW$_ADV_INFO MVIEW$_ADV_JOURNAL MVIEW$_ADV_LEVEL MVIEW$_ADV_LOG TABLE_NAME ------------------------------ MVIEW$_ADV_OUTPUT MVIEW$_ADV_PARAMETERS MVIEW$_ADV_PARTITION MVIEW$_ADV_PLAN MVIEW$_ADV_PRETTY MVIEW$_ADV_ROLLUP MVIEW$_ADV_SQLDEPEND MVIEW$_ADV_TEMP MVIEW$_ADV_WORKLOAD PLAN_TABLE REPCAT$_AUDIT_ATTRIBUTE TABLE_NAME ------------------------------ REPCAT$_AUDIT_COLUMN REPCAT$_COLUMN_GROUP REPCAT$_CONFLICT REPCAT$_DDL REPCAT$_EXCEPTIONS REPCAT$_EXTENSION REPCAT$_FLAVORS REPCAT$_FLAVOR_OBJECTS REPCAT$_GENERATED REPCAT$_GROUPED_COLUMN REPCAT$_INSTANTIATION_DDL TABLE_NAME ------------------------------ REPCAT$_KEY_COLUMNS REPCAT$_OBJECT_PARMS REPCAT$_OBJECT_TYPES REPCAT$_PARAMETER_COLUMN REPCAT$_PRIORITY REPCAT$_PRIORITY_GROUP REPCAT$_REFRESH_TEMPLATES REPCAT$_REPCAT REPCAT$_REPCATLOG REPCAT$_REPCOLUMN REPCAT$_REPGROUP_PRIVS TABLE_NAME ------------------------------ REPCAT$_REPOBJECT REPCAT$_REPPROP REPCAT$_REPSCHEMA REPCAT$_RESOLUTION REPCAT$_RESOLUTION_METHOD REPCAT$_RESOLUTION_STATISTICS REPCAT$_RESOL_STATS_CONTROL REPCAT$_RUNTIME_PARMS REPCAT$_SITES_NEW REPCAT$_SITE_OBJECTS REPCAT$_SNAPGROUP TABLE_NAME ------------------------------ REPCAT$_TEMPLATE_OBJECTS REPCAT$_TEMPLATE_PARMS REPCAT$_TEMPLATE_REFGROUPS REPCAT$_TEMPLATE_SITES REPCAT$_TEMPLATE_STATUS REPCAT$_TEMPLATE_TARGETS REPCAT$_TEMPLATE_TYPES REPCAT$_USER_AUTHORIZATIONS REPCAT$_USER_PARM_VALUES SQLPLUS_PRODUCT_PROFILE 131 rows selected. **** Oracle explans the plan is used to compute the answer: Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 NESTED LOOPS (OUTER) 3 2 NESTED LOOPS (OUTER) 4 3 NESTED LOOPS (OUTER) 5 4 NESTED LOOPS (OUTER) 6 5 NESTED LOOPS 7 6 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' 8 7 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE) 9 6 TABLE ACCESS (CLUSTER) OF 'TAB$' 10 9 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE) 11 5 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' 12 11 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE) 13 4 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE) 14 3 TABLE ACCESS (CLUSTER) OF 'USER$' 15 14 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE) 16 2 TABLE ACCESS (CLUSTER) OF 'SEG$' 17 16 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE) 18 1 TABLE ACCESS (CLUSTER) OF 'TS$' 19 18 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE) *** The following queries were issued on the SRS database (Courses, Transcripts, Students; ...) which have the same structure as in the text book. SQL> select * from tson.courses; CRSCODE TITLE ---------- ------------------------------ CS110 Intro to CS CS482 DB 1 CS582 DB 2 CS272 Data Structure CS510 Automata CS570 Algorithm - The beloved course CS370 Compiler 7 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'COURSES' SQL> select * from tson.courses, tson.transcripts where tson.courses.crscode = tson.transcripts.crscode; CRSCODE TITLE STUDID CRSCODE SEMESTER G ---------- ------------------------------ ---------- ---------- ------------ - CS272 Data Structure 111111111 CS272 Fall2002 A CS110 Intro to CS 111111111 CS110 Fall2005 B CS482 DB 1 222222222 CS482 Fall2004 A CS272 Data Structure 222222222 CS272 Fall2002 B CS370 Compiler 222222222 CS370 Spring2005 I CS370 Compiler 111111111 CS370 Spring2006 I 6 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF 'TRANSCRIPTS' 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'COURSES' 4 3 INDEX (UNIQUE SCAN) OF 'SYS_C004034' (UNIQUE) SQL> select * from tson.courses, tson.transcripts where tson.courses.crscode = tson.transcripts.crscode and studid=111111111; CRSCODE TITLE STUDID CRSCODE SEMESTER G ---------- ------------------------------ ---------- ---------- ------------ - CS110 Intro to CS 111111111 CS110 Fall2005 B CS272 Data Structure 111111111 CS272 Fall2002 A CS370 Compiler 111111111 CS370 Spring2006 I Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TRANSCRIPTS' 3 2 INDEX (RANGE SCAN) OF 'SYS_C004037' (UNIQUE) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'COURSES' 5 4 INDEX (UNIQUE SCAN) OF 'SYS_C004034' (UNIQUE) SQL> select * from tson.courses, tson.transcripts, tson.students where tson.courses.crscode = tson.transcripts.crscode and tson.transcripts.studid = tson.students.id and tson.students.id>111111111; CRSCODE TITLE STUDID CRSCODE SEMESTER G ---------- ------------------------------ ---------- ---------- ------------ - ID NAME ---------- ------------------------------ ADDRESS -------------------------------------------------------------------------------- STATUS ---------- CS482 DB 1 222222222 CS482 Fall2004 A 222222222 Deborah Fame Street 2 Senior CRSCODE TITLE STUDID CRSCODE SEMESTER G ---------- ------------------------------ ---------- ---------- ------------ - ID NAME ---------- ------------------------------ ADDRESS -------------------------------------------------------------------------------- STATUS ---------- CS272 Data Structure 222222222 CS272 Fall2002 B 222222222 Deborah Fame Street 2 Senior CRSCODE TITLE STUDID CRSCODE SEMESTER G ---------- ------------------------------ ---------- ---------- ------------ - ID NAME ---------- ------------------------------ ADDRESS -------------------------------------------------------------------------------- STATUS ---------- CS370 Compiler 222222222 CS370 Spring2005 I 222222222 Deborah Fame Street 2 Senior Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 NESTED LOOPS 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'STUDENTS' 4 3 INDEX (RANGE SCAN) OF 'SYS_C004033' (UNIQUE) 5 2 TABLE ACCESS (BY INDEX ROWID) OF 'TRANSCRIPTS' 6 5 INDEX (RANGE SCAN) OF 'STUDID' (NON-UNIQUE) 7 1 TABLE ACCESS (BY INDEX ROWID) OF 'COURSES' 8 7 INDEX (UNIQUE SCAN) OF 'SYS_C004034' (UNIQUE)