Archives of the TeradataForum

Message Posted: Thu, 28 Dec 2006 @ 14:47:51 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi:

No, it works on populated tables quite nicely. I ran an empirical test by creating a new table (CREATE TABLE... AS ... WITH DATA), then changed the index name on the new (populated) table. Rows counts are the same on both tables. Documentation is at http://www.info.ncr.com/. See Page 1-19 of the SQL Reference: Data Definition Statements Manual V2R5.1, B035-1144-083A, November 2003.

     create  table UCVMart.AGENTS_POLICY_mj
        as UCVMart.AGENTS_POLICY  with data;

     alter table UCVMart.AGENTS_POLICY_mj
        modify PRIMARY INDEX XPI_AGENTS_POLICY1 ( POLICY_NR );


     Count(*)
     1,884,984

Here is the original table named UCVMart.AGENTS_POLICY with index name "XPI_AGENTS_POLICY" . Voila!

     CREATE SET TABLE UCVMart.AGENTS_POLICY ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           AGENTS_POLICY_KEY_ID INTEGER NOT NULL,
           FROM_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL,
           THRU_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '9999-12-31',
           POLICY_NR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
           POLICY_KEY_ID INTEGER NOT NULL,
           AGENT_NM VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
           AGENT_CD VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,

           COMMISSION_SPLIT_PC DECIMAL(3,2),
           STATUS_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
           STATUS_DE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
           DIST_PC DECIMAL(3,2),
           PRODUCER_STATUS_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
           PRODUCER_STATUS_DE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
           ADDRESS_TYPE_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
           ADDRESS_LINE_TX_1 VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,
           ADDRESS_LINE_TX_2 VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,
           ADDRESS_LINE_TX_3 VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,
           ADDRESS_LINE_TX_4 VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,
           ZIP_CD VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
           CITY_NM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           STATE_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
           STATE_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

           PARTY_PHONE_NR VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           PRTY_EMAIL_AD VARCHAR(80) CHARACTER SET LATIN NOT CASESPECIFIC,
           PARTY_FAX_NR VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           AGENT_ROLE_DE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
           PRIMARY_AGENCY_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
           MANAGER_CD CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
           SPLIT_CNTRL_SEQ_NR SMALLINT)
     PRIMARY INDEX XPI_AGENTS_POLICY ( POLICY_NR );

Here is the copied table UCVMart.AGENTS_POLICY_mj with changed index name "XPI_AGENTS_POLICY1"

     CREATE SET TABLE UCVMart.AGENTS_POLICY_mj ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           AGENTS_POLICY_KEY_ID INTEGER NOT NULL,
           FROM_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL,
           THRU_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '9999-12-31',
           POLICY_NR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
           POLICY_KEY_ID INTEGER NOT NULL,
           AGENT_NM VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
           AGENT_CD VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,

           COMMISSION_SPLIT_PC DECIMAL(3,2),
           STATUS_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
           STATUS_DE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
           DIST_PC DECIMAL(3,2),
           PRODUCER_STATUS_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
           PRODUCER_STATUS_DE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
           ADDRESS_TYPE_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
           ADDRESS_LINE_TX_1 VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,
           ADDRESS_LINE_TX_2 VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,
           ADDRESS_LINE_TX_3 VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,
           ADDRESS_LINE_TX_4 VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC,
           ZIP_CD VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
           CITY_NM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           STATE_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
           STATE_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

           PARTY_PHONE_NR VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           PRTY_EMAIL_AD VARCHAR(80) CHARACTER SET LATIN NOT CASESPECIFIC,
           PARTY_FAX_NR VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           AGENT_ROLE_DE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
           PRIMARY_AGENCY_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
           MANAGER_CD CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
           SPLIT_CNTRL_SEQ_NR SMALLINT)
     PRIMARY INDEX XPI_AGENTS_POLICY1 ( POLICY_NR );

Mark Jahnke
Teradata DBA

	<Prev	Next>		<<First	<Prev	Next>	Last>>