Good morning, I have an assignment that asks for the following: Tasks Using the

Question

Good morning, I have an assignment that asks for the following:

Tasks

Using the student and faculty tables create a select query that outputs all students for a specific advisor. Generate the execution plan by first setting Autotrace on. Create an index that allows for a better join between the two tables. Again generate and display the revised execution plan using Autotrace.

The commands for using Autotrace are as follows:

SET AUTOTRACE ON;

SET AUTOTRACE OFF;

tasks, additional:

show the execution plan, create an index and then generate and show the execution plan again. You must submit the code as scripts that the instructor can run from an SQL prompt on an Oracle DBMS.

I know I need to create indexes for my existing 'Student' and 'Faculty' tables, I have test data in these. I believe the next step is done using EXPLAIN command?

Explanation / Answer

Hi,

I am writing you all the create table , insert script and EXPLAIN PLAN scripts. Please use it with SQL developer and run the scripts to get the plan for your system-

CREATE TABLE "ADVISOR"
(   "ADVISOR_ID" VARCHAR2(10),
   "ADV_NAME" VARCHAR2(30),
   "SUBJECT" VARCHAR2(10)
) ;
CREATE TABLE "STUDENT"
(   "ID" VARCHAR2(10),
   "NAME" VARCHAR2(30),
   "MARKS" NUMBER(10,0),
   "ADVISOR_ID" VARCHAR2(10)
) ;
insert into student values('123','vinay',340,'30');
insert into student values('124','ravi',340,'20');
insert into student values('125','alax',340,'10');
insert into student values('126','sandy',340,'30');
insert into student values('127','marshal',340,'20');
insert into student values('128','porky',340,'40');

insert into advisor values('10','aaa','maths');
insert into advisor values('20','bbb','science');
insert into advisor values('30','ccc','english');
insert into advisor values('40','ddd','geology');
insert into advisor values('50','eee','bio');
insert into advisor values('60','fff','english');

SET AUTOTRACE ON;

EXPLAIN PLAN for select * from student s join advisor a on s.advisor_id=a.advisor_id

create index ind1 on student(advisor_id)

EXPLAIN PLAN for select * from student s join advisor a on s.advisor_id=a.advisor_id

Regards,

Vinay Singh

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.