[rajeshnaidu]

I have two Entities named STUDENT and TEACHER and a many-to-many relationship between them. So to maintain this relationship I have a map table STUDENT_TEACHER_MAP.

STUDENT(STUDENT_PK, STUDENT_NAME) --- 30,000 records
TEACHER(TEACHER_PK, TEACHER_NAME) --- 25 records
STUDENT_TEACHER_MAP(STUDENT_PK,TEACHER_PK) --- 30,000 records

Now I have a query to display the records of STUDENT_TEACHER_MAP table but results should show STUDENT_NAME and TEACHER_NAME.

Query is:

select STUDENT_NAME, TEACHER_NAME from STUDENT s, TEACHER t, STUDENT_TEACHER_MAP st_map where s.STUDENT_PK = st_map.STUDENT_PK and t.TEACHER_PK = st_map.TEACHER_PK

Here the problem is the query execution time is more. How can I reduce this time.

Even if the query is:

select STUDENT_NAME, TEACHER_NAME from STUDENT s, TEACHER t, STUDENT_TEACHER_MAP st_map where s.STUDENT_PK = st_map.STUDENT_PK and t.TEACHER_PK = st_map.TEACHER_PK and STUDENT_NAME = 'JOHN'

Here is the same problem, i.e more execution time of this query.