How does Oracle perform a read operation?

Question

Suppose we have a table that contains information about a person. Columns like NAME or SURNAME are small (I mean their size is not very large), but the columns containing the person’s photo or video (blob columns) can be very large. Therefore, when we execute the select operation:

select * from person 

he will extract all this information. But in most cases, we only need to get the person’s name or surname, so we fulfill this request:

 select name, surname from person 

Q : Will Oracle read the entire record (including blob columns) and then just filter out the first and last name columns, or will it only read the first and last name columns?

In addition, even if we create a separate table for such large data (photos and videos of a person) and have a foreign key for this table in the user table and want to get only a photo, we therefore execute this query:

 select photo from person p join largePesonData d on p.largeDataID = d.largeDataID where p.id = 1 

Will Oracle read the whole record in the personal table and the entire record in the largePesonData file, or will it just read the photo column in largePesonData?

Answer 1

Oracle reads data in blocks. Suppose your block size is 8192 bytes and the average row size is 100 bytes. This means that each block will fill in 8192/100 = 81 rows (this is inaccurate, since there are some overheads from the block header, but I'm trying to simplify everything).

So, when you choose a first name, last name on behalf of; In fact, you are extracting at least a block with all its data (81 lines), and later after it is displayed, you will receive only the requested data.

Two exceptions:

• BLOB Column - "select name, surename from person" will not retrieve the contents of the BLOB because the BLOB columns contain a link to the actual BLOB (which is located somewhere else in the table space or even in the TS anoter)
• Indexed Columns If you created an index in a table using the first and last names of the columns, it is possible that Oracle will check this particular index and retrieve only these two columns.