Efficient Query

[Mahtab Alam]

create table instructors(eid number,ename varchar2(30),gender char,did number,salary number); create table departments(dept_id number,dept_name varchar2(30)) insert into instructors values(111,'Mahtab Alam','M',1,12000); insert into instructors values(222,'Mohammad latif','M',1,15000); insert into instructors values(333,'Rafiq Khan','M',1,14000); insert into instructors values(666,'CS Girl1','F',1,118000); insert into instructors values(777,'CS Girl 2','F',1,14500); insert into instructors values(888,'Ahmed Rehman','M','2','19000'); insert into instructors values(1010,'IT Girl 1','F','2',21000); insert into instructors values(2020,'IT Girl 2','F','2',38000); insert into instructors values(3030,'Pankaj Kumar','M','2',18999); insert into instructors values(8080,'CS Girl 6','F',1,15000); insert into departments values(2,'IT'); insert into departments values(1,'CS');

Now I want to write a query that gives me the highest paid male employee in each department.
select eid,ename,did,gender,salary from instructors where (did,salary) in ( select did,max(salary) from instructors where gender='M' group by did ) and gender='M' ;
I get What I was looking for
EID ENAME DID GENDER SALARY
222 Mohammad latif 1 M 15000
888 Ahmed Rehman 2 M 19000

But I want to display dept_name of employee rather than employees department id.
So I wrote this one
select eid,ename,dept_name,gender,salary from instructors join departments on did=dept_id where (dept_name,salary)in( select dept_name,max(salary) from instructors join departments on did=dept_id where gender='M' group by dept_name ) and gender='M';
This gives me
EID ENAME DEPT_NAME GENDER SALARY
222 Mohammad latif CS M 15000
888 Ahmed Rehman IT M 19000

Above Query Have two joins , So i don`t think its efficient way.So i write this query.
select eid,ename,dept_name,gender,salary from instructors ir join departments on did=dept_id where (did,salary)=( select ir.did,max(salary) from instructors ins where gender='M' and ir.did=ins.did ) and gender='M';

So how one should go about writing a query that is efficient.

[Mahtab Alam]

One more question , can you tell me one query that have outer query and one correlated subquery and that correlated subquery uses group by clause .
So group by clause in inner subquery uses outer query`s column as grouping column.
Something like this

select col1,col2,..
from tab1 t1
where col1 = ( select max(col3)
from tab2 t2
group by t1.col1
)

[Martin Vashko]

Writing efficient queries is a very broad topic. If you're concerned about Oracle, I'd suggest to read Oracle's Performance guides:

Oracle® Database Performance Tuning Guide

Oracle® Database 2 Day + Performance Tuning Guide

If you haven't read it by now, start with the Concepts guide. You'll need it to be able to follow the other guides.

One thing that stands out in this particular case is the lack of constraints. Constraints, such as primary key, unique and foreign key constraints, give the database additional information about the data and allow better optimization of the queries.

Generally speaking, the query which uses less joins is probably more efficient. Oracle does a lot of optimizations, though, and it is just possible that the two queries would perform about the same. There might be a difference, however, if the department name isn't unique, in which case the two queries would give different results! So, in your case, the query with two joins might also be incorrect. (I would prefer the department ID over the name for comparison in this query. ID just feels better for identification, which is what you need here. Name is just a name, and there might be situations in which names become non-unique, this is much less probable with IDs.)

(Also note that your sample data contains too few rows for any performance issues to actually come up. Incidentally, a promotion for GenRocket, a test data generator, has just ended in our Testing forum; this tool would help in generating the amount of data you'd need. I'm mentioning this just so that you're aware of it, I don't want you to actually post scripts that would insert thousands of rows. )

Mahtab Alam wrote:One more question , can you tell me one query that have outer query and one correlated subquery and that correlated subquery uses group by clause .
So group by clause in inner subquery uses outer query`s column as grouping column.

I've though we've already discussed this thoroughly here. It's exactly the same question. If it's still unclear to you, please let's continue the discussion in the other thread.

[Mahtab Alam]

with staff_stats as( select did,gender from instructors group by did,gender ) ,staff_salary_stats as ( select did,gender,sum(salary) Total_salary from instructors group by did,gender) select * from staff_salary_stats;

ORA-32035: unreferenced query name defined in WITH clause

I read somewhere that with ,with clause you have to reference all the views you created.