Question

/*explain */ Select *

From Student join enrollment on student.ID = enrollment.Student_ID

            join section on section.ID = enrollment.section_ID

            join department on major = department.name

            join faculty on faculty.id = section.faculty_ID

            join address on address.id = student.address_ID

            join Course on section.course_Number = course.course_number and section.dept_id = course.dept_ID

Where

--we want to make sure we have name information for students if we want to reach out to them

Student.Name_Last Not Like ('')

-- the theater department has asked to be out of this study

and Student.Major <> 'Theater'

--no students who have failed as we're looking for passing grades

and Grade > '1.33'

--we want to make sure we only have instructors, and the theater department is not part of this study

and Faculty.job in

            (Select job

            From Faculty

            Where Job not in ('Administrative','General Services','Human Resources')

            and Dept <> 'THT')

and Section_ID >=1

--summer courses don't always reflect accurately given their tight schedule and rapid fire delivery of materials

and Semester <> 'Summer'

--we don't want bias of an adivosr giving better grades

and Student.Advisor_ID <> Section.Faculty_ID

-- we don't want bias if a student is possibly a faculty members child

and Student.Address_ID <> Faculty.Address_ID

Order by Student.Name_Last, Grade desc, Faculty.Name_Last, Major

1. Based on the output from the explain command, determine the steps you would take (building indexes on certain columns, modifying the query, both, etc.) to get the same output, but improve overall performance.
2. Execute three steps that you think would make for improvement in the performance of this query. Report their run time in the table below.

Evaluate the impact of the steps you took, what their potential benefits and setbacks may be, and what you would advise as the next steps to improve the performance of this query.

Answer 1

ANSWER:

Execute three steps that you think would make for improvement in the performance of this query.

1.While creating index, it should be taken into consideration which all columns will be used to make SQL queries and create one or more indexes on those columns. Practically, indexes are also a type of tables, which keep primary key or index field and a pointer to each record into the actual table.

eg:- CREATE UNIQUE INDEX STUDENT_INDEX ON STUDENT(ID, Last_Name, Major, Grade);

Similarly we need to create indexes for other Tables and while creating indexes mention the column names that are being used in the SQL Query.

2. We can remove unwanted Relation/Tables from the JOIN Query eg:- Address, if we arent displaying any columns related to ADDRESS Table, we can remove so that cost of JOIN Query execution will be reduced and the query will execute in relative less time.

3. We can have certain constraints in the CREATE TABLE itself so that we dont put such filters in SQL Select Query:- LAST_NAME VARCHAR2(100) NOT NULL, if we have this Constraint in CREATE TABLE STUDENT, we can avoid this WHERE clause Student.Name_Last Not Like (''). Thus we can remove unwanted filters from the Select Query so as to enable less filtering of results obtained. This can speedup Query execution.