Search code examples
sqloracleoracle-sqldeveloper

issue for manually stopping an oracle SQL query in SQL Developer

Just for curiosity, can anyone tell me would I cause any issue to the Oracle database if I manually stop running a query (select statement) by processing the red button in Oracle SQL Developer like below, assuming that the query is a very heavy loading query normally takes over a few minutes to run.

enter image description here

The query is just a very normal select statement like following:

-- Getting 3 years worth of data from a busy Production DB

select 
    <something>
from
    Table1@ProductionDB T1
    left join Table2@ProductionDB T2 on
       T2.colA = T1.ColA
    left join Table3@ProductionDB T3 on
       T3.Col1B = T2.ColB
where
  T3.date between to_date('01-JAN-2017','dd-MON-YYYY') and to_date('01-JAN-2020','dd-MON-YYYY')

The Remote DB is a busy production DB that connected to thousand clients and have in/out communication every sec to the database through an application server. Would it cause any issues to Oracle such as, Oracle memory management or others, to the remote database if the user terminated the query manually from Oracle SQL Developer.

Thanks in advance!

Solution

  • I presume that the remote DB will be relieved as certain resources wouldn't be needed any more.

    Though, as you didn't post entire query, just asking: if those tables are accessed via database link, did you try to use the driving_site hint? Perhaps it'll help the query to execute faster. Something like

    select /*+ driving_site (t1) */
  <something>
from ...