CODEWITHSUNDEEP
sqloracle-database
Maverick
Maverick

Reputation: 71

Optimize sub query

Suppose there three columns ename , city , salary. There are millions of rows in this table named emp.

    ename  city    salary 
    ak    newyork  $5000
    bk    abcd     $4000
    ck    Delhi    $4000
    ....................
    ...................
   Maverick  newyork  $8000

I want to retrieve all employees having the same city name as Maverick.

select * from emp where 
city = (select city from emp where ename= 'maverick' )

I know it will work, but for performance reasons, this query is not good because there are two where clauses present in this query.

I need a query having better performance than above query.

Upvotes: 0

Views: 73

Answers (4)

Brian DeMilia
Brian DeMilia

Reputation: 13248

This would give you the same output but I doubt it will perform any better.

You could compare the plans though.

select x.*
  from emp x
  join (select city from emp where ename = 'maverick') y
    on x.city = y.city

You can also add 2 indexes, one on the ENAME column, and a separate one on the CITY column.

create index emp_idx_ename on emp(ename);
create index emp_idx_city on emp(city);

The first index will speed up the inline view whose results are being joined to, because it is searching the table on employee.

The second index will speed up the parent query, because it is searching the table for a given city.

You could create a composite index on emp(city, ename) as others have suggested since you're select only the city column where the ename is X, allowing the query in the inline view to use only the index and not the table, which I didn't initially think of. It may provide an additional boost, more or less, depending on the size of the table, although the index will also be larger.

To make sure the indexes will immediately use updated statistics related to that table, I would also run the following after you create the above indexes, so that your query will immediately start using them:

analyze table emp compute statistics;

Upvotes: 1

Patrick Hofman
Patrick Hofman

Reputation: 156948

Sometimes complexity wins from the desire to narrow down the query further. Just isn't possible to optimize this query further.

You could opt to add an index to create better performance. The index should come on city and ename.

Try this to create these indexes:

create index emp_city -- for the outer where clause
on emp
( city
)

create index emp_ename_city -- for the sub query
on emp
( ename
, city
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Oracle is probably going to do a good job getting the optimal execution plan for this query:

select *
from emp 
where city = (select city from emp where ename= 'maverick' ) ;

What would help the query are two indexes:

create index idx_emp_ename_city on emp(ename, city)
create index idx_emp_ename_city on emp(city)

The first would be used for the subquery. The second to look up all the matching rows. Without indexes, Oracle is going to have to read the table at least once (I think at least twice) and that is going to affect performance on such a large table.

Upvotes: 1

rpc1
rpc1

Reputation: 688

You could use with statement... Users sugest you many dicisions

WITH new_city_tab AS (
  SELECT city AS ncity
  FROM   emp WHERE ename='Maverick'
  GROUP BY city)
SELECT *
FROM   emp e,
       new_city_tab c
WHERE  E.city = c.ncity;

Upvotes: 0

Related Questions