Varchar Memory Usage

[Steve Howard]

I've created a table with a single varchar(256) column. I then inserted 500 K rows of varying size with the vast majority being substantially less than 256 characters.

I then attempt the following queries:

#1) select avg(length(pname)) from (
select substr(page_name,1,40) as pname from memory_test
) a;

-> returns 25.6563 in 0.811 sec

#2) select avg(length(pname)) from (
select substr(page_name,1,length(page_name)) as pname from memory_test
) a;

-> returns 26.6383 in 1.123 sec

#3) select avg(length(pname)) from (
select page_name as pname from memory_test
) a;

-> returns 26.6383 in 23.151 sec

As I understand it, the difference in performance between #2 and #3 is caused by the allocation of enough memory in #3 to accommodate each page_name being 256 characters.

Am I assuming correctly that application of a substr allows for dynamic determination of the amount of memory required? Is there another way to accomplish this without using the substr?

I have confirmed that there is at least 1 long varchar in the table:

select max(length(page_name)) from memory_test;

returns 255

I realize that I could rewrite the query without requiring the derived table. I've simplified a more complex query in order to clarify my question.

Any guidance appreciated,
Steve