Varchar expanding to its maximum length in fexp - ALSO USING THIS FIELD IN ORDER BY

Teradata Applications
Fan

Varchar expanding to its maximum length in fexp - ALSO USING THIS FIELD IN ORDER BY

hi all ,

I increased the varchar length from 255 to 1500 in a table:

before :the column was declared with varchar(255) in table_A

after :i changed it to varchar(1500) in table_A

but this simple change degraded fexp's performence.

FEXP was something like :

select 

cast (description as varchar(1000))

from

(

select

description

from TABLE_A

) AS A

ORDER BY DESCRIPTION

So can anyone pls suggest some shanges so that performence is not degraded due to this varchar expansion to its fullest as we are using this in order by as well

can we have some derived table ,whose field is declared as varchar(maximum_length(description)) -- something like this , it does not describes any syntax , its just an idea

becasue then we dont have to bother about varchar expansion , as the actual data saved in this description field < 255 bytes only

but the business requires its length to be 1500 in TABLE_A

so need to change the length but without performece degradation in fexp.

so is there any method to do that

8 REPLIES
Teradata Employee

Re: Varchar expanding to its maximum length in fexp - ALSO USING THIS FIELD IN ORDER BY

It's the ORDER BY that causes an expansion to the max length in spool.

Yes, if you know the actual max length you can CAST the field shorter within the derived table so the ORDER BY won't expand as much.

Fan

Re: Varchar expanding to its maximum length in fexp - ALSO USING THIS FIELD IN ORDER BY

can we cast it in a variable manner like below , so that the expansion doesnt causes any performense issue:

 

DECLARE VARIABLE INTEGER;

SET VARIABLE = SEL MAX(CHARACTER_LENGTH(DESCIPTION)) FROM Table_A;

select 

cast (description as varchar(1000))

from

(

select

cast (description as varchar(:variable))

from TABLE_A

) AS A

ORDER BY DESCRIPTION;

 

i know that the above sql wont work , but can we have something like this  as an idea , where we can cast the description as a dynamic varchar, so as to avoid varchar expansion.

Teradata Employee

Re: Varchar expanding to its maximum length in fexp - ALSO USING THIS FIELD IN ORDER BY

You could do this:

select

cast(description as varchar(1000))

from

(

select

trim(trailing from substring(description from 1 for (select max(character_length(description)) from Table_A))) as description

from Table_A

) as A

order by description;

I'm not sure the cost of the singleton subquery plus the function calls will be worthwhile compared to the original query, though.

Fan

Re: Varchar expanding to its maximum length in fexp - ALSO USING THIS FIELD IN ORDER BY

Thanks FRED :)

Junior Contributor

Re: Varchar expanding to its maximum length in fexp - ALSO USING THIS FIELD IN ORDER BY

A string is always expanded to the defined size, SUBSTRING will not change that.

You wrote "the actual data saved in this description field < 255 bytes", you can simply CAST within the ORDER BY:

select
description
from TABLE_A
ORDER BY cast (description as varchar(255))

Do you actually need a perfectly sorted result?

A human being wil not check if it's still sorted correctly after the nth character, so cast (description as varchar(50)) might be ok.

Otherwise this should work: run the MAX(CHAR_LENGTH) before, export the length to a file. Then .ACCEPT len in the FExp script and dynamically use cast (description as varchar(&len))

Teradata Employee

Re: Varchar expanding to its maximum length in fexp - ALSO USING THIS FIELD IN ORDER BY

Thanks for the correction! I should have remembered that SUBSTRING / SUBSTR result has the same size as the original field.

You are correct, of course. CAST is required and you can't use a scalar subquery in the type description, so it would need to be a script variable.

Fan

Re: Varchar expanding to its maximum length in fexp - ALSO USING THIS FIELD IN ORDER BY

Thanks a lot DNOETH & FRED  :)

But if we will use CAST alone and if description exceeds 255 chrachters then it will give an error like : Right truncation of string data

so we need to use as below in order by :

CAST (SUBSTR(DESCRIPTION,1,255) AS VARCHAR (255))



Regards

ABHITD

Junior Contributor

Re: Varchar expanding to its maximum length in fexp - ALSO USING THIS FIELD IN ORDER BY

Oops, you're running ANSI-mode sessions.

Of course, then you need CAST(SUBSTRING).