While practicing DBMS and SQL using Oracle Database, when I tried to fire 2 select queries on a table the database always wait for the first query to finish executing and keeps the other one in pipeline apparently.
Consider a table MY_TABLE having 1 million records with a column 'id' that holds the serial number of records.
Now my queries are:-
Query #1 - select * from MY_TABLE where id<500001; --I am fetching first 500,000 records here
Query #2 - select * from MY_TABLE where id>500000; --I am fetching next 500,000 records here
Since these are select queries, these must be acquiring a read lock on the table which is a shared lock. Then why this phenomenon happens? Please note the sample space or domain for both queries are mutually exclusive to the best of my knowledge here because of the filters that I applied via where clause and this further aggravates my confusion.
Also, I am visualizing this in form of that, there must be some process which is evaluating my query and then doing a handshake with the memory(i.e. resource) for fetching the result. So, any resource in shared lock mode should be accessible to all process which hold that lock.
Secondly, is there any way to override this behavior or execute multiple select queries concurrently.
Note:- I want to chunk down a particular task(i.e. data of a table) and enhance the speed of my script.
The database doesn't keep queries in a pipeline, it's simply the fact that your client is only sending one query at a time. The database will quite happily run multiple queries against the same data at the same time, e.g. from separate sessions.