CODEWITHSUNDEEP
sqloracle-databaseindexingquery-optimization
Rasulbek Abdurasulov
Rasulbek Abdurasulov

Reputation: 111

Oracle Database optimization with INDEXing

I have three tables (student, faculty and grades) and I want to optimize following query with indexing some of columns or with the other words creating secondary physical structures. My query is this: 

select * from STUDENT S, FACULTY F
where S.facul=F.facul AND S.grade NOT IN (
select overall from grades G
where overall > 500 and overall < 1900);

I can't use like "where S.grade <= 500 OR S.grade >= 1900", because in the GRADES table there are lot of other options which I will use later. So it is important selecting from GRADES table.

I am a beginner on Oracle SQL, so please, help me and be more specific.

Upvotes: 1

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

This is your query (rewritten a bit):

select *
from STUDENT S join
     FACULTY F
     on S.facul = F.facul 
where S.grade NOT IN (select g.overall
                      from grades G
                      where overall > 500 and overall < 1900
                     );

The not in is really useless. You can write this as:

select *
from STUDENT S join
     FACULTY F
     on S.facul = F.facul 
where S.grade <= 500 OR S.grade >= 1900 ;

Then, you want indexes. I think STUDENT(grade, facul) and FACTULRY(facul) should work.

Upvotes: 3

Related Questions