1

I have the following query:

select * from application_log log
where log.tag_value = 'xxx' 
or log.tag_value in (select transaction.id
from transaction transaction where transaction.sale_id = 'xxx')
order by log.log_date asc;

and based on the explain plan the subquery is transformed to something like that:

EXISTS (SELECT 0 FROM TRANSACTION TRANSACTION
WHERE TRANSACTION.SALE_ID='xxx' AND TRANSACTION.ID=:B1)

So IN is replaced with EXISTS and subquery is not executed once but for each record in application_log table which is very slow if there are over one million of records in application_log table.

I can replace OR with UNION which is very fast, but as Hibernate (JPA) does not support UNION this is not solution for me.

select * from application_log log
where  
log.tag_value in (select 'xxx' from dual union select transaction.id
from transaction transaction where transaction.sale_id = 'xxx')
order by log.log_date asc;

So is there some Oracle hint which tells Oracle to not "optimize" query using EXISTS or some other way how I could rewrite this query?

martinsefcik
  • 346
  • 9
  • 20
  • 2
    If Oracle is doing that transformation it is because it thinks that it will be the most efficient way to fetch the results. Have you checked that your db statistics are ok? Your table names make me think that their size is not stable but ever increasing. So you have to ensure that stats are reasonably current if you want Oracle to make the right decisions. – gpeche Apr 06 '18 at 07:04
  • post exec plans – ibre5041 Apr 06 '18 at 10:59
  • @ibre5041what is the best way to print some textual version of exec plan in Oracle? I'm using Oracle SQL Developer and it is displayed only visually in UI. – martinsefcik Apr 10 '18 at 07:14
  • @gpeche I have no idea how I should check if statistics are OK. And yes, table sizes are always growing. – martinsefcik Apr 10 '18 at 07:17
  • @martinsefcik for a start, you should check that the stats registered in `ALL_TAB_COLS` and `ALL_TAB_COL_STATISTICS` for your `APPLICATION_LOG` and `TRANSACTION` tables match the actual data in those tables. Oracle optimizer uses that info to guess what is the best query execution plan, so if the info is incorrect/out of date, the execution plan will not be optimal. Ideally your DBA can check the stats and update them if necessary. See: https://docs.oracle.com/cd/E18283_01/server.112/e17110/statviews_2101.htm https://docs.oracle.com/cd/E18283_01/server.112/e17110/statviews_2102.htm – gpeche Apr 11 '18 at 07:02

1 Answers1

0

You could transform your subquery into a join:

SELECT * 
FROM application_log log
    LEFT JOIN transaction transaction ON log.tag_value=transaction.id AND transaction.sale_id = 'xxx'
WHERE log.tag_value = 'xxx' OR transaction.id IS NOT NULL
ORDER BY log.log_date ASC;
niyou
  • 875
  • 1
  • 11
  • 23
  • It is approximately 3 times faster (8 seconds) than query with `OR` + `IN` (24 seconds), but still too slow compared to query with `UNION` (0.1 sec). – martinsefcik Apr 11 '18 at 09:25