You have questions of memory, but which memory are you talking about? First, there's the memory on the database server. Then there's the memory used on the client, which can be split in the memory used by the client libraries, and the memory used on the Perl side.

As for select var1,var2,var3 from table, yes, that will cause the database server to load the entire table into memory (although if var1,var2 and var3 form a key, it might just load the index), but that is because you want all rows. It would be a bad database if it loaded the entire table if you have a where clause that causes only a fraction of the rows to be returned (and have the index to avoid a table scan).

Do the $Sth->fetchrow() read one row from the array in memory? OR it return some pointer than the fetchrow read one record from that table on disk at the time?

That isn't up to the DBI to decide. But that's up to your DBD (database driver) and the installed client libraries.