CODEWITHSUNDEEP
oracle-databaseinner-joindatabase-performance
akash777.sharma
akash777.sharma

Reputation: 702

Ordering of multiple inner joins in a query to improve performance on the basis of where clause Oracle

I am working in oracle.

I have more than 2 tables to perform inner join. And according to me their ordering matters for query performance.

Below is the query :

select * from 
 FROM A a 
INNER JOIN B b
ON a.b_ID=b.id
INNER JOIN C c
ON c.id=a.c_Id
INNER JOIN D d
ON a.d_ID=d.id
INNER JOIN E e
ON e.d_id =d.id
where e.name='abc' AND e.company_name='xyz';

In my case I don't require full table scan of tables A, B, C, D.

I want to apply predicate filter of name and company_name to be applied first and then apply inner join of tables A,B,C,D (in execution plan).

My question is : Is that possible?

Also , If I change the order of inner join on the basis of final where clause , can that improve performance (like below query)?

select * from 
E e INNER JOIN D d
ON e.d_id =d.id
INNER JOIN A a
ON a.d_ID=d.id
INNER JOIN B b
ON a.b_ID=b.id
INNER JOIN C c
ON c.id=a.c_Id
where e.name='abc' AND e.company_name='xyz';

Even after applying this change, I found that on some DB environments, execution plan is same for two queries.

Is there any way in which I can order the steps of execution plan while query execution, like explicitly specifying the ordering of inner join?

Thanks

Upvotes: 0

Views: 1997

Answers (2)

akash777.sharma
akash777.sharma

Reputation: 702

Thanks @sers

I also found this solution of using /*+ORDERED */ earlier than the solution you provided.But I just wanted some proof of increased performace.

So I just executed 

explain plan for sql_query;
select plan_table_output from table(dbms_xplan.display('plan_table',null,'typical'));

I cannot show the actual table output (so omitting table names) , but I will post other performance factors here :

enter image description here

I know that oracle can figure out the best plan , but forcing the execution plan helped me to improve performance.Also in my case this is the worst case scenario.

Upvotes: 1

sers
sers

Reputation: 3679

If you really want to go for it, you can use an optimizer hint: https://docs.oracle.com/cd/B12037_01/server.101/b10752/hintsref.htm#5555

But generally I would not recommend it - if the table statistics are up to date, the database should well be able to determine the best execution plan (especially for such an easy query).

Upvotes: 2

Related Questions