I use MS Query via an ODBC driver to connect to an Oracle database.
If as sometimes happens I use task manager to 'End Task' MS Query because I do not want to wait for the returned data what happens to the query request executing on the Oracle database?
Does the request terminate as soon as I 'disconnect' MS Query or does the query complete and the retuirn of data fail?
The question I am asking really is am I using system/database resources once I have terminated the connection at the client end?
it depends on the hints you use in your query.
if you use /*+ all_rows */ then yes the query will continue to run even after you kill your app with brute force. this is because the all_rows hint tells the database to build the entire result set first before sending a row back and because of this your application has not had a chance to terminate its connection and clean up thus the query continues to run. if you are not using the hint above, first_rows is the default and the query plan will remain allocated until your connection times out but is not executed until the client requests rows. even then then query plan is only executed for a limited about of time until the number of rows requested is satisfied.
so to make a short story long, you will be using the connection and session resources after you kill your app with brute force but you will not be burning any cycles on your query (just session maintainance) if you do not use the all_rows hint.
i use all_rows at work because it improves response time of my process. i would rather take a 5 minute penalty up front and be able to supply my system with a nonstop flow of data versus a 5-10 second stall over 100-200 rows requested.