Database

A database is a central repository for an organization’s resources. An efficient database enables easier administration and enhances the security of the database. However, it is essential to understand the basic controls for the creation of the database tables and the fields. The AUTOTRACE command is a critical element in database administration. In the Oracle database, the AUTOTRACE executes the query and queries the plan table automatically. For the user to use the AUTOTRACE, the user must have the PLUSTRACE role which has its location in ORACLE_HOME/sqlplus/admin directory.
Creating a Table called Students
The script creates the table Students with fields such as StudentId, LastName, FirstName, AdvisorName and the Faculty.
CREATE TABLE Students
(
StudentID int,
LastName varchar(200),
FirstName varchar(200),
AdvisorName varchar(200),
Faculty varchar(200)
);
Creating a Table called Faculty
The script creates the table Faculty with fields such as StudentId, LastName, FirstName, AdvisorName and the Faculty.
CREATE TABLE Faculty
(
StudentID int,
LastName varchar(200),
FirstName varchar(200),
AdvisorName varchar(200),
Faculty varchar(200)
);
Inserting Data
INSERT INTO Faculty (StudentID, LastName, FirstName, AdvisorName, Faculty) VALUES (StudentID, LastName, FirstName, AdvisorName, Faculty);
Select Command from the Table
The script selects all from the Faculty table with a specific AdvisorName.
select * from Faculty WHERE AdvisorName='{$ AdvisorName }’;
Inserting Data
INSERT INTO Students (StudentID, LastName, FirstName, AdvisorName, Faculty) VALUES (StudentID, LastName, FirstName, AdvisorName, Faculty);
Select
select * from Students WHERE AdvisorName='{$ AdvisorName }’;
Execution plan involves a sequence of operations for running the statements such as the insert and select statements from a database.

EXPLAIN PLAN FOR
SELECT * FROM Students;
Operation Name
SELECT JOIN TABLES TABLE ACCESS FULL|StudentsTABLE ACCESS FULL|FacultyJoining the Table Faculty and the table Students
SELECT S. FIRSTNAME, F. FIRSTNAME
FROM Students S,
Faculty F
WHERE S.StudentId = F.StudentIdAND S.AdvisorName = ‘Advisor’;
Using the AutotraceSET AUTOTRACE on
VARIABLE StudentId;
SELECT
S.AdvisorName,
F.AdvisorName,
FROM
Students S,
Faculty F
WHERE WHERE S.StudentId = F.StudentIdAND S.AdvisorName = ‘Advisor’;
The Execution Plan after Creation of the Index
Creating the Index
CREATE INDEX ID
On Students (ID)
CREATE INDEX ID
On Faculty (ID)
ID Operation Name
0 SELECT 1 JOIN TABLES 2 TABLE ACCESS FULL|Students3 TABLE ACCESS FULL|FacultyIn summation, database administration is a fundamental aspect in the data repositories. It is evident that execution plans and use of the index are necessary for the database.