When we query from the Oracle database, it may read more data than what we need, for example with a full table scan, and then filters the data. My question is, where does Oracle perform this filter? While reading from the disks, inside the buffer cache before handling the resultset to PGA, or inside the PGA?
2 Answers
Described above should happen in the PGA.
There are areas of the PGA - Sort area, Hash area, Bitmap Merge area (not all of them exist in every case).
which are privately allocated for memory that serve different purposes as their names suggest.
"For example, a sort operator uses the sort area to sort a set of rows. Similarly, a hash join operator uses a hash area to build a hash table from its left input, whereas a bitmap merge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes."
More about this matter can be found here, which has also been source of the quote above.
It's a bit more complex than that. Filtering can happen at different layers. For example, if you are using Exadata, filtering can happen via storage indexes, bloom filters, SIMD vector processing, all before that data is returned to the database server itself. Bloom filters can also be applied on the database server. What is the reason for your question? Are you trying to address a performance problem, or is this just a curiosity?
Related