All Forums Teradata Applications
ABHITD 4 posts Joined 01/16
13 Jan 2016
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

Fred 1096 posts Joined 08/04
14 Jan 2016

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.

ABHITD 4 posts Joined 01/16
15 Jan 2016

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.

Fred 1096 posts Joined 08/04
15 Jan 2016

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.

ABHITD 4 posts Joined 01/16
16 Jan 2016

Thanks FRED :)

dnoeth 4628 posts Joined 11/04
17 Jan 2016

 
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))

Dieter

Fred 1096 posts Joined 08/04
17 Jan 2016

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.
 

ABHITD 4 posts Joined 01/16
18 Jan 2016

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

dnoeth 4628 posts Joined 11/04
18 Jan 2016

Oops, you're running ANSI-mode sessions.
Of course, then you need CAST(SUBSTRING).

Dieter

You must sign in to leave a comment.