Home » RDBMS Server » Performance Tuning » Tuning Multiple Not Exists (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Tuning Multiple Not Exists [message #655093] Mon, 22 August 2016 03:16 Go to next message
ricky_s
Messages: 21
Registered: February 2016
Location: New Delhi, India
Junior Member
Hey Guys,

I have a query that has multiple not exists conditions(4 to be exact). I want to improve its performance but the challenge that i am facing is that even if the 1st not exists condition is false, the explain plan is showing that it checks all the 4 not exists conditions.

Sharing a sample case with 2 not exists conditions. In this sample, empid(105) is present in EMP_INACTIVE table. But the explain plan(attached in next message) shows that both the tables were visited.


SELECT * FROM EMP A
WHERE
NOT EXISTS (SELECT 1 FROM EMP_INACTIVE WHERE EMPID = A.ID)
AND
NOT EXISTS (SELECT 1 FROM EMP_TRANSFERRED WHERE EMPID = A.ID)
AND
ID IN (105);


All the Necessary Scripts are attached.

Thanks..

  • Attachment: SCRIPT.sql
    (Size: 12.70KB, Downloaded 202 times)
Re: Tuning Multiple Not Exists [message #655094 is a reply to message #655093] Mon, 22 August 2016 03:18 Go to previous messageGo to next message
ricky_s
Messages: 21
Registered: February 2016
Location: New Delhi, India
Junior Member
Here's the explain plan for the above query.
Re: Tuning Multiple Not Exists [message #655096 is a reply to message #655094] Mon, 22 August 2016 03:57 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
If I give it a no_query_transformation hint, I get the result you want:
orclz> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID  89kpxk66s0nm2, child number 0
-------------------------------------
SELECT /*+ no_query_transformation */ * FROM EMP A WHERE NOT EXISTS
(SELECT 1 FROM EMP_INACTIVE WHERE EMPID = A.ID) AND NOT EXISTS (SELECT
1 FROM EMP_TRANSFERRED WHERE EMPID = A.ID) AND ID IN (105)

Plan hash value: 2374400023

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |      1 |        |      0 |00:00:00.01 |       3 |
|*  1 |  FILTER                      |                        |      1 |        |      0 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP                    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN         | MY_EMP_ID_PK           |      1 |      1 |      1 |00:00:00.01 |       1 |
|*  4 |   INDEX RANGE SCAN           | EMP_INACTIVE_INDX01    |      1 |      1 |      1 |00:00:00.01 |       1 |
|*  5 |   INDEX RANGE SCAN           | EMP_TRANSFERRED_INDX01 |      0 |      1 |      0 |00:00:00.01 |       0 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(( IS NULL AND  IS NULL))
   3 - access("ID"=105)
   4 - access("EMPID"=:B1)
   5 - access("EMPID"=:B1)


27 rows selected.

orclz>
That is a bit drastic though, and not really a good idea. But at least you can see that it is possible, so you should be able to push the CBO towards it. If it really is faster.

[Updated on: Mon, 22 August 2016 07:49] by Moderator

Report message to a moderator

Re: Tuning Multiple Not Exists [message #655106 is a reply to message #655096] Mon, 22 August 2016 06:53 Go to previous messageGo to next message
ricky_s
Messages: 21
Registered: February 2016
Location: New Delhi, India
Junior Member
John,

Thanks for looking into the query. "no_query_transformation" hint did not helped as expected.

What did improve the performance was the change in the logic, i modified the query so that all the not exists conditions became nested within each other.
This resulted in improvement of execution factor by at-least 20 times.

TBH, i would have used a hint only as a last resort if nothing else was working.

Thanks
Re: Tuning Multiple Not Exists [message #655109 is a reply to message #655106] Mon, 22 August 2016 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
i modified the query so that all the not exists conditions became nested within each other.
So what is the query now?

Re: Tuning Multiple Not Exists [message #655110 is a reply to message #655109] Mon, 22 August 2016 07:55 Go to previous messageGo to next message
ricky_s
Messages: 21
Registered: February 2016
Location: New Delhi, India
Junior Member
Hi Michel,

I modified the query to :

SELECT * FROM EMP A
WHERE
NOT EXISTS (SELECT 1 FROM EMP_INACTIVE B WHERE B.EMPID = A.ID
AND NOT EXISTS (SELECT 1 FROM EMP_TRANSFERRED WHERE EMPID = B.ID)
)
ID IN (105);

I really don't know that is happening with optimizer.

Until last week the query was working fine, all of a sudden it started taking 10* the time it was taking earlier. Now, we are testing with the above approach and it seems to be going good as of now.



Re: Tuning Multiple Not Exists [message #655111 is a reply to message #655110] Mon, 22 August 2016 07:56 Go to previous messageGo to next message
ricky_s
Messages: 21
Registered: February 2016
Location: New Delhi, India
Junior Member
it is b.empid in the 4th line
Re: Tuning Multiple Not Exists [message #655112 is a reply to message #655106] Mon, 22 August 2016 07:58 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
Are you saying that my no_query_transformation did not force Oracle to run the query with a filter? That it still re-wrote the query to use joins? I'm surprised.
Re: Tuning Multiple Not Exists [message #655113 is a reply to message #655112] Mon, 22 August 2016 08:05 Go to previous messageGo to next message
ricky_s
Messages: 21
Registered: February 2016
Location: New Delhi, India
Junior Member
John,

It ran with the filter but did not make any change as far as the execution time was concerned.
Re: Tuning Multiple Not Exists [message #655272 is a reply to message #655113] Fri, 26 August 2016 00:48 Go to previous messageGo to next message
ricky_s
Messages: 21
Registered: February 2016
Location: New Delhi, India
Junior Member
Can you guys guide me if the General Information details on the following page are correct(regarding operator precedence).

http://psoug.org/reference/conditions.html

Re: Tuning Multiple Not Exists [message #655277 is a reply to message #655272] Fri, 26 August 2016 01:00 Go to previous message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read Database SQL Reference and tell us.

Previous Topic: improve performance using partitioning
Next Topic: Driving site hint on a single remote table
Goto Forum:
  


Current Time: Sun Oct 21 18:05:59 CDT 2018