Query optimization of oracle query

I don't have much knowledge on query optimization, I need some help here. here is sample code of our project, its long running oracle query to pull data from 2 different tables (student, student_info).

Is there any possibility to optimize this? How do the where clause 'and' operation work here?

Does it maintain any order while executing AND clause? How the below query make the different before and after by removing the code of line b.student_id in ('a123','b123','c123').

We don't have the privileges to add indexes on that table columns.

How can we improve the performance without creating indexes.

select a.student_id
       max(decode(a.marks_limit, 99.99,100,null )) as max_marks,
       b.student_city_code "NYC",
from student a, 
     student_info b
where a.student_id=b.student_id
  and a.student_id in ('a123','b123','c123')
  and b.student_id in ('a123','b123','c123')
  and  b.adress_modified > TO_TIMESTAMP('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')
  group by a.student_id, b.student_city_code;

3 answers

  • answered 2018-10-11 19:38 Lukasz Szozda

    You could:

    select a.student_id
        max(decode(a.marks_limit, 99.99,100,null )) as max_marks,
        b.student_city_code "NYC",
    from student a
    join student_info b
      on a.student_id=b.student_id     -- explicit join
    where a.student_id in ('a123','b123','c123') -- removing duplicate condition
      and b.adress_modified>TO_TIMESTAMP('2003/12/13 10:13:18','YYYY/MM/DD HH:MI:SS')
    group by a.student_id, b.student_city_code;
    

    And add indexes:

    CREATE INDEX id1 ON student(student_id);
    CREATE INDEX id2 ON student_info(student_id);
    CREATE INDEX id3 ON student_info(adress_modified);
    

  • answered 2018-10-11 19:40 Gordon Linoff

    First, write the query using proper, explicit, proper JOIN syntax with sensible table aliases and case expressions.

    After fixing it up, I would expect something like this:

    select s.student_id
           max(s.marks_limit) as max_marks,  -- I have no idea what the decode() is supposed to be doing
           si.student_city_code
    from student s join
         student_info si
         on s.student_id = si.student_id
    where s.student_id in ('a123', 'b123', 'c123')
     and   si.adress_modified > TIMESTAMP '2003-12-13T10:13:18'HH:MI:SS')
    group by s.student_id, si.student_city_code;
    

    I would start with indexes on student(student_id) and student_info(student_id address_modified, student_city_code).

  • answered 2018-10-11 19:42 scaisEdge

    Just some suggestions.

    Yuo have already a.student_id=b.student_id so the condizione b.student_id in ('a123,'b123','c123') is just an useful repetition You shoudl also using explict join notation and not old implict notation based on where clause

       select a.student_id
        max(decode(a.marks_limit, 99.99,100,null )) as max_marks,
        b.student_city_code "NYC",
        from student a
        INNER JOIN  student_info b ON a.student_id= b.student_id
        WHERE 
        and a.student_id in ('a123','b123','c123')
        and  b.adress_modified > TO_TIMESTAMP('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')
        group by a.student_id, b.student_city_code
    

    and for better performance you should check for a composite index on table

     student_info ( adress_modified, student_id )
    

    or

    student_info ( adress_modified, student_id, student_city_code )