Search code examples
sqloracle-databaseoracle11gquery-performance

How to speed up query execution in oracle when the tables are joined

I already asked this question, but it was closed by the system. I have the code, after I added a very important part to it, it is on a page where a lot of data takes 3-5 minutes to load for a very long time. Here is a demo where the highlighted part through https://dbfiddle.uk/?rdbms=oracle_18&fiddle=892dcef2af2f606a7c31d15dfcba7314 which it works for a long time. Is it possible to optimize the code so that it does not respond? I tried through the tables indices, but it didn't work, I would be grateful for the help.The whole problem with this part of the code

    AND rep.id not in ( select o.id 
  from IOT_STREAM_ANALYTICS_LOG_REPRESENTATION o,
       IOT_STREAM_ANALYTICS_LOG_REPRESENTATION parent
 where o.parent = parent.parent
   and parent.key = 'reader'
   and parent.VALUE_STRING !=  txn.reader_value_string)

This code corresponds to the fact that when a connection occurs, I do not want to join the table if the reader is not suitable for me. I wish I didn't have a subquery at all, and the result left)

Solution

  • It is hard to suggest here something without trace (the best option) or execution plan for such cases.

    All I can see here, the subquery can be rewritten as the following

    select id 
  from IOT_STREAM_ANALYTICS_LOG_REPRESENTATION o,
       IOT_STREAM_ANALYTICS_LOG_REPRESENTATION parent
 where o.parent = parent.parent
   and parent.key = 'reader'
   and VALUE_STRING !=  txn.reader_value_string;

    But I'm not sure if this makes any sence to join the table with itself using same column. Perhaps you wanted to join o.parent with u.id?

    ... where o.parent in 
         (select u.id /* <-- */ from ...

    As I said if this wouldn't help, provide us please the query plan or trace for that query