 |
 |
Archives of the TeradataForum
Message Posted: Tue, 15 Jan 2002 @ 18:51:03 GMT
| 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
| |
