|
-
There are couple of things here that have to be clarified.
1. This is not directly connected to your question, but the idea is present in some of the messages in this thread: "Full table scan (FTS) of big table would fill the buffer cache, thus flushing out blocks from other tables completely". This is not true, because during full table scan buffers are read into the "least recently used" (LRU) end of the buffer cache, meaning that they will be the first to be flushed out of the buffer cashe. Let's assume we have buffer cashe of 1000 blocks, 100 of which are free. When FTS begin, first 100 blocks are read into the empty buffers in the cache and they are all put in the LRU end of the free list. When next set of blocks (number is defined by MULTIPLE_BLOCK_READ_COUNT)) have to be read, Oracle must flush the same number of buffers from the cache and it chooses those from the LRO end of the list. Incidentaly, this are the same blocks that have where read previously in the same FTS operation. So in effect, FTS cycles through a limited number of bufers in the cache, thus preventin a single FTS to flood the whole buffer cache.
2. The optimizer mode (RULE, CHOOSE, FIRST_ROWS, ALL_ROWS) has absolutely no effect wheter the rows will be returned to user as soon as each of them is found or when all the rows are processed. This is determined by the type of a particular operation - "row operations" will return row by row as soon as rows are found, while "statement operations" cuse resulting rows to be returned as a whole result set at the end of the operation, once all the rows are processed. Typical examples of "statement operations" are INTERSECT, MINUS, UNION, all kind of SORT operations, HASH JOIN, MERGE JOIN.... Typical examples of "row operations" are TABLE/INDEX SCANs, TABLE BY ROWID, FILTER, NESTED LOOP, AND-EQUAL, CONCATENATE.... So when FTS is executed (if it is a simpleFTS, without ORDER BY or being a part of HASH/MERGE JOIN), each row is returned to the user as soon as it is found, regardles of optimizer mode. But, of course, by choosing diferent optimizer mode you can dramatically influence on the decision whether FTS will be used or not.
3. To go directly to the answer of your question:
a) When there is no "statement operation" during the execution of the query (as FTS in your example, or INDEX_SCAN+TABLE_BY_ROWID or simmilar), as soon as single block or MULTIPLE_BLOCK_READ_COUNT set of blocks are read into the cache, they are examined for the rows that met all the conditions of the query. Those rows are returned immediately to the users. In the mean time, user proces reads another set of blocks into the cache, removing previously read blocks from the cache if necessarry. This goes on until all the table blocks are processed.
b) When there is a "statement operation" involved in a querry (for example, if you use ORDER BY in your query), oracle must perform a sorting (if you check explain plans of queries using any kind of statement operations like MINUS, MERGE JOIN, SORT ORDER BY, SORT AGGREGATE, SORT UNIQUE etc, you'll find out there is allways a sorting operation involved). So in this case, when the block is read into the cache, the resulting rows are found and put into the sort area of the shared pool, where they are sorted. This goes on untill all blocks involved are processed. If during this process sort area is filled up with the resulted rows, they are written into the temporary tablespace segment. After all the blocks have been processed, the final sort of the temporary segment is done (if necessary) and the whole result set is returned to the user.
So no matter how many blocks have to be read and how large the result set is, the resulting rows are returned to the user either immediately row-by-row, or at the end of the operation from the sorting area (assisted by temporary tablespace) as a complete resulting set.
HTH,
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|