Secondary index uniqueness stopping INSERT SELECT statement - help!

Database

Secondary index uniqueness stopping INSERT SELECT statement - help!

I'm trying to increase the data in a table for load testing purposes. However, trying to increase the data by copying what is there and pasting it back in with a few changes doesn't work because of the unique indexes.

 

Is there a way around this? or a simpler way to create large amounts of data?

 

Running this statement below:

 

SyntaxEditor Code Snippet

INSERT    INTO Z0002_CUST_SL(CUSTOMERNO_SK, EFF_DT, EXPIR_DT, DATA_SRC_CD, DATA_SUB_SRC_CD,
        CUSTOMERUSERID, CUSTOMERNAME, ADDRESS1, ADDRESS2, ADDRESS3, EMAIL,
        HOMETELEPHONE, **bleep**, DOB, SPECIALNEEDS, IDENTITYCODE1, DATECREATED,
        OTHADDRESS1, OTHADDRESS2, OTHADDRESS3, OTHPOSTCODE, WORKTELEPHONE,
        DOD, EXEMPTION1, EXEMPTION2, EXEMPTION3, EXEMPTION4, EXEMPTION5,
        EXEMPTION6, EXEMPTION7, EXEMPTION8, EXEMPTION9, EXEMPTION10,
        IDENTITYCODE3, POSTCODE, IDENTITYCODE2, IDENTITYDETAIL1, IDENTITYDETAIL2,
        IDENTITYDETAIL3, PRIORITYREGISTER, MEMDATARECEIVED, NOMINATEDACCOUNTSHELD,
        COUNTRYOFRESIDENCECODE, COUNTRYOFRESIDENCE, NATIONALITYCODE,
        NATIONALITY, OCCUPATION, CUSTOMERTITLE, FORENAME1, FORENAME2,
        FORENAME3, SURNAME, NATIONALINSURANCENUMBER, TAXRSDNC_IN, MOBILETELEPHONE,
        LSTUPDT_TS, LSTUPDT_USER_ID_CD)SELECT
CUSTOMERNO_SK + 1, EFF_DT + 1, EXPIR_DT, DATA_SRC_CD, DATA_SUB_SRC_CD,
        CUSTOMERUSERID, CUSTOMERNAME, ADDRESS1, ADDRESS2, ADDRESS3, EMAIL,
        HOMETELEPHONE, **bleep**, DOB, SPECIALNEEDS, IDENTITYCODE1, DATECREATED,
        OTHADDRESS1, OTHADDRESS2, OTHADDRESS3, OTHPOSTCODE, WORKTELEPHONE,
        DOD, EXEMPTION1, EXEMPTION2, EXEMPTION3, EXEMPTION4, EXEMPTION5,
        EXEMPTION6, EXEMPTION7, EXEMPTION8, EXEMPTION9, EXEMPTION10,
        IDENTITYCODE3, POSTCODE, IDENTITYCODE2, IDENTITYDETAIL1, IDENTITYDETAIL2,
        IDENTITYDETAIL3, PRIORITYREGISTER, MEMDATARECEIVED, NOMINATEDACCOUNTSHELD,
        COUNTRYOFRESIDENCECODE, COUNTRYOFRESIDENCE, NATIONALITYCODE,
        NATIONALITY, OCCUPATION, CUSTOMERTITLE, FORENAME1, FORENAME2,
        FORENAME3, SURNAME, NATIONALINSURANCENUMBER, TAXRSDNC_IN, MOBILETELEPHONE,
        LSTUPDT_TS, LSTUPDT_USER_ID_CD
FROM Z0002_CUST_SL;

 The indexes are listed as:

CUSTOMERNO_SK [Primary] "Z00021" - 1320

CUSTOMERNO_SK, EFF_DT [Unique] "Z00022" - 1800

EFF_DT, CUSTOMERUSERID "Z00023" - 4

 

I think the CUSTOMERUSERID field is the one stopping it from running, however, it is an 8 character alphanumeric string.

 

Any help would be hugely appreciated, this is a bit of a road block for me right now.

 

Thanks

3 REPLIES
rjg
Supporter

Re: Secondary index uniqueness stopping INSERT SELECT statement - help!

Generally for load testing , increasing the concurrency of a valid workload on valid data would be best.

If you just want a big table

and you can't chage the index to non-unique or drop it the only choice is to insert unique data in the field.

 

Rglass

 

Re: Secondary index uniqueness stopping INSERT SELECT statement - help!

Hi Rglass,

 

Concurrency isn't the issue at the moment, large amount of data and very complex reports have us worried about performance of the reporting server. 

 

Due to time constraints calculations were done in the reporting area, rather than the teradata database (which would have meant no need for load testing).

 

I figured this would be the answer however, back to the drawing board!

Teradata Employee

Re: Secondary index uniqueness stopping INSERT SELECT statement - help!

Only the Unique index(es) need to be considered. It appears that onlly one of the indexes listed is unique so that is the one to apply a function to to create uniqueness.

 

Adding 1 as is shown in the sample above doesn't work because there are probably already customers with that number in the set. Instead of adding one, add MAX(customerno) to the customerno field to generate unique numbers.