'Oracle performance lowered by using parenthesis
I've read this: Parenthesis() and SQL Query Performance
I have posted this: Oracle not using indexes if queried in wrong order
What we are experiencing right now is beyond my understanding, and I am asking for explanation.
Query:
SELECT * FROM PREMIUMACCOUNTBOOKING p WHERE PARENTFOREIGNKEY = 499699430 AND CLASSID = 511 ORDER BY BOOKINGNO;
lasts 3 ms.
Explanation plan:
Plan hash value: 3357352015
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 632 | 9 (12)| 00:00:01 |
| 1 | SORT ORDER BY | | 4 | 632 | 9 (12)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| PREMIUMACCOUNTBOOKING | 4 | 632 | 8 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PREMIUMACCOUNTBOOKI_511_2 | 4 | | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PARENTFOREIGNKEY"=499699430 AND "CLASSID"=511)
Now, query SELECT * FROM PREMIUMACCOUNTBOOKING WHERE PARENTFOREIGNKEY=499699430 AND (CLASSID=511) ORDER BY BOOKINGNO;
lasts 40 seconds! Repeatedly. No improvement even after several executions.
Explanation plan:
Plan hash value: 3357352015
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 632 | 9 (12)| 00:00:01 |
| 1 | SORT ORDER BY | | 4 | 632 | 9 (12)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| PREMIUMACCOUNTBOOKING | 4 | 632 | 8 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PREMIUMACCOUNTBOOKI_511_2 | 4 | | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PARENTFOREIGNKEY"=499699430 AND "CLASSID"=511)
I do not see any difference, except in actual execution time.
What can I do to identify the reason for this behaviour?
Thank you, GG
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|