Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 15 Jan 2002 @ 18:51:03 GMT


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


Subj:   RE: SQL Tuning - Spool Space Problem
 
From:   Jay Quinn

Below is the final revision sorting all the tricks and tips that everyone advised. The last script is the only SQL check I could run that didn't spool out, written by one of our top DBA's, it ran in 22 secs! :) My thanks to everyone, I've learned a lot. --Jay

/* Corp Tax SQL */
/* Last Revised 01/15/02 */
/* Show table billing.tslb003_bill_charge */
/* Table Creation */
create table thirty_day_tables.jq8274_bill_charge
        (ban decimal(9,0) not null,
        ent_seq_no decimal(9,0),
        actv_seq_no decimal(3,0),
        product_type char(2),
        feature_code char(6),
        ftr_revenue_code char(3),
        actv_amt decimal(9,2),
        esel_amt decimal(9,2),
        esal_amt decimal(9,2),
        asel_amt decimal(9,2),
        asal_amt decimal(9,2),
        intl_amt decimal(9,2),
        tax_fed_amt decimal(9,2),
        tax_tot_stt_amt decimal(9,2),
        tax_tot_loc_amt decimal(9,2),
        actv_date date format 'YYYYMMDD',
        actv_code char(4))
primary index (ban);

grant all on thirty_day_tables.jq8274_bill_charge to jq8274 with grant option;

/* Table Population */
insert into thirty_day_tables.jq8274_bill_charge
select  ban,
        ent_seq_no,
        actv_seq_no,
        product_type,
        feature_code,
        ftr_revenue_code,
        actv_amt,
        esel_amt,
        esal_amt,
        asel_amt,
        asal_amt,
        intl_amt,
        tax_fed_amt,
        tax_tot_stt_amt,
        tax_tot_loc_amt,
        actv_date,
        actv_code
from vslb003_bill_charge
where (sys_creation_date <= '20011031'
and sys_creation_date >= '20011001');

/* show table customer.tslc003_bill_adjustment */
/* Table Creation */
create table thirty_day_tables.jq8274_bill_adjustment
        (BAN decimal(9,0) not null,
        ENT_SEQ_NO decimal(9,0),
        ACTV_SEQ_NO decimal(3,0),
        PRODUCT_TYPE char(2),
        FEATURE_CODE char(6),
        FTR_REVENUE_CODE char(3),
        ACTV_AMT decimal(9,2),
        ESEL_AMT decimal(9,2),
        ESAL_AMT decimal(9,2),
        INTL_AMT decimal(9,2),
        ASEL_AMT decimal(9,2),
        ASAL_AMT decimal(9,2),
        TAX_FEDERAL_AMT decimal(9,2),
        TAX_TOT_STT_AMT decimal(9,2),
        TAX_TOT_LOC_AMT decimal(9,2),
        ACTV_DATE date format 'YYYYMMDD',
        ACTV_CODE char(4))
primary index (ban);

grant all on thirty_day_tables.jq8274_bill_adjustment to jq8274 with grant option;

/* Table Population */
insert into thirty_day_tables.jq8274_bill_adjustment
select
        BAN,
        ENT_SEQ_NO,
        ACTV_SEQ_NO,
        PRODUCT_TYPE,
        FEATURE_CODE,
        FTR_REVENUE_CODE,
        ACTV_AMT,
        ESEL_AMT,
        ESAL_AMT,
        INTL_AMT,
        ASEL_AMT,
        ASAL_AMT,
        TAX_FEDERAL_AMT,
        TAX_TOT_STT_AMT,
        TAX_TOT_LOC_AMT,
        ACTV_DATE,
        ACTV_CODE
from vslc003_bill_adjustment
where (sys_creation_date <= '20011031'
and sys_creation_date >= '20011001');

/* show table customer.tslc041_tax */
/* Table Creation */
create table thirty_day_tables.jq8274_tax
        (BAN decimal(9,0) not null,
        ENT_SEQ_NO decimal(9,0),
        TAX_SERV_GEO char(9),
        TAX_ADR_GEO char(9),
        TAX_ACTL_SIZE decimal(2,0),
        TAX_AUTH_1 char(1),
        TAX_TYPE_1 char(2),
        TAX_AMT_1 decimal(9,2),
        TAX_RATE_1 decimal(5,2),
        TAX_AUTH_2 char(1),
        TAX_TYPE_2 char(2),
        TAX_AMT_2 decimal(9),
        TAX_RATE_2 decimal(5,2),
        TAX_AUTH_3 char(1),
        TAX_TYPE_3 char(2),
        TAX_AMT_3 decimal(9,2),
        TAX_RATE_3 decimal(5,2),
        TAX_AUTH_4 char(1),
        TAX_TYPE_4 char(2),
        TAX_AMT_4 decimal(9,2),
        TAX_RATE_4 decimal(5,2),
        TAX_AUTH_5 char(1),
        TAX_TYPE_5 char(2),
        TAX_AMT_5 decimal(9,2),
        TAX_RATE_5 decimal(5,2),
        TAX_AUTH_6 char(1),
        TAX_TYPE_6 char(2),
        TAX_AMT_6 decimal(9,2),
        TAX_RATE_6 decimal(5,2),
        TAX_AUTH_7 char(1),
        TAX_TYPE_7 char(2),
        TAX_AMT_7 decimal(9,2),
        TAX_RATE_7 decimal(5,2),
        TAX_AUTH_8 char(1),
        TAX_TYPE_8 char(2),
        TAX_AMT_8 decimal(9,2),
        TAX_RATE_8 decimal(5,2),
        TAX_AUTH_9 char(1),
        TAX_TYPE_9 char(2),
        TAX_AMT_9 decimal(9,2),
        TAX_RATE_9 decimal(5,2),
        GROSS_AMT_FOR_TAX_1 decimal(9,2),
        TAX_EXMP_SRC_1 char(1),
        TAX_EXMP_AMT_1 decimal(9,2),
        GROSS_AMT_FOR_TAX_2 decimal(9,2),
        TAX_EXMP_SRC_2 char(1),
        TAX_EXMP_AMT_2 decimal(9,2),
        GROSS_AMT_FOR_TAX_3 decimal(9,2),
        TAX_EXMP_SRC_3 char(1),
        TAX_EXMP_AMT_3 decimal(9,2),
        GROSS_AMT_FOR_TAX_4 decimal(9,2),
        TAX_EXMP_SRC_4 char(1),
        TAX_EXMP_AMT_4 decimal(9,2),
        GROSS_AMT_FOR_TAX_5 decimal(9,2),
        TAX_EXMP_SRC_5 char(1),
        TAX_EXMP_AMT_5 decimal(9,2),
        GROSS_AMT_FOR_TAX_6 decimal(9,2),
        TAX_EXMP_SRC_6 char(1),
        TAX_EXMP_AMT_6 decimal(9,2),
        GROSS_AMT_FOR_TAX_7 decimal(9,2),
        TAX_EXMP_SRC_7 char(1),
        TAX_EXMP_AMT_7 decimal(9,2),
        GROSS_AMT_FOR_TAX_8 decimal(9,2),
        TAX_EXMP_SRC_8 char(1),
        TAX_EXMP_AMT_8 decimal(9,2),
        GROSS_AMT_FOR_TAX_9 decimal(9,2),
        TAX_EXMP_SRC_9 char(1),
        TAX_EXMP_AMT_9 decimal(9,2))
primary index (ban);

grant all on thirty_day_tables.jq8274_tax to jq8274 with grant option;

/* Table Population */
insert into thirty_day_tables.jq8274_tax
select
        BAN,
        ENT_SEQ_NO,
        TAX_SERV_GEO,
        TAX_ADR_GEO,
        TAX_ACTL_SIZE,
        TAX_AUTH_1,
        TAX_TYPE_1,
        TAX_AMT_1,
        TAX_RATE_1,
        TAX_AUTH_2,
        TAX_TYPE_2,
        TAX_AMT_2,
        TAX_RATE_2,
        TAX_AUTH_3,
        TAX_TYPE_3,
        TAX_AMT_3,
        TAX_RATE_3,
        TAX_AUTH_4,
        TAX_TYPE_4,
        TAX_AMT_4,
        TAX_RATE_4,
        TAX_AUTH_5,
        TAX_TYPE_5,
        TAX_AMT_5,
        TAX_RATE_5,
        TAX_AUTH_6,
        TAX_TYPE_6,
        TAX_AMT_6,
        TAX_RATE_6,
        TAX_AUTH_7,
        TAX_TYPE_7,
        TAX_AMT_7,
        TAX_RATE_7,
        TAX_AUTH_8,
        TAX_TYPE_8,
        TAX_AMT_8,
        TAX_RATE_8,
        TAX_AUTH_9,
        TAX_TYPE_9,
        TAX_AMT_9,
        TAX_RATE_9,
        GROSS_AMT_FOR_TAX_1,
        TAX_EXMP_SRC_1,
        TAX_EXMP_AMT_1,
        GROSS_AMT_FOR_TAX_2,
        TAX_EXMP_SRC_2,
        TAX_EXMP_AMT_2,
        GROSS_AMT_FOR_TAX_3,
        TAX_EXMP_SRC_3,
        TAX_EXMP_AMT_3,
        GROSS_AMT_FOR_TAX_4,
        TAX_EXMP_SRC_4,
        TAX_EXMP_AMT_4,
        GROSS_AMT_FOR_TAX_4,
        TAX_EXMP_SRC_5,
        TAX_EXMP_AMT_5,
        GROSS_AMT_FOR_TAX_6,
        TAX_EXMP_SRC_6,
        TAX_EXMP_AMT_6,
        GROSS_AMT_FOR_TAX_7,
        TAX_EXMP_SRC_7,
        TAX_EXMP_AMT_7,
        GROSS_AMT_FOR_TAX_8,
        TAX_EXMP_SRC_8,
        TAX_EXMP_AMT_8,
        GROSS_AMT_FOR_TAX_9,
        TAX_EXMP_SRC_9,
        TAX_EXMP_AMT_9
from vslc041_tax
where (sys_creation_date <= '20011031'
and sys_creation_date >= '20011001');

/* End Create and Populate Scripts */
/* Collect Statistics */
collect statistics on thirty_day_tables.jq8274_bill_charge index (ban);

collect statistics on thirty_day_tables.jq8274_bill_adjustment index (ban);

collect statistics on thirty_day_tables.jq8274_tax index (ban);

/* Drop Tables */
--drop table thirty_day_tables.jq8274_bill_charge;

--drop table thirty_day_tables.jq8274_bill_adjustment;

--drop table thirty_day_tables.jq8274_tax;

/* Row Count(s) */
select count(*) from thirty_day_tables.jq8274_bill_charge;

select count(distinct(ban)) from thirty_day_tables.jq8274_bill_charge;

select count(*) from thirty_day_tables.jq8274_bill_adjustment;

select count(distinct(ban)) from thirty_day_tables.jq8274_bill_adjustment;

select count(*) from thirty_day_tables.jq8274_tax;

select count(distinct(ban)) from thirty_day_tables.jq8274_tax;

/* SQL Test Check */
select count(*)
from (select a.ban
        from (select ban from thirty_day_tables.jq8274_bill_charge
        group by 1) a,
        (select ban
        from thirty_day_tables.jq8274_bill_adjustment
        group by 1) b,
        (select ban
        from thirty_day_tables.jq8274_tax
        group by 1) c
        where a.ban = b.ban
        and a.ban = c.ban
        group by 1) x


     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016