Archives of the TeradataForum

Message Posted: Mon, 15 Jul 2002 @ 18:31:57 GMT

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

I'm sure others will notice more obscure things, but here are a few things that jump out at me.

- A lot of the steps give their estimates with "low" or "no" confidence. You should probably collect statistics on all columns used in the WHERE clauses (and all indexes, of course).

- Is the view LD_ACCESS_VIEWS.VSLB001_BILL identical to the underlying table? Step 4.1 gives its join condition as "BAN = (BAN (CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT '---------9.')(CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, NAMED BAN ))", which strongly suggests that the view is converting BAN to char(10). That would be bad.

- Do you have to select from VSLB001_BILL into a derived table? As far as I can see, that table could just be folded into a five-way join. With the proper statistics, the optimizer could then choose the best access plan.

The five-way join would look something like this:

SELECT
trim(A.BAN) (char(9)),
CPA.INTRALATA_CIC,
A.CYCLE_RUN_YEAR,
A.TOTAL_DUE_AMT,
count(A.BAN),
sum(A.BF_DATA_MON_SRV_CHG),
sum(A.BF_MON_SRV_CHG),
sum(A.CURR_CREDIT_AMT),
sum(A.TOTAL_BILLED_ADJUST),
sum(A.CURR_CHARGE_AMT),
sum(A.CURR_OC_CHRG_AMT),
sum(A.CURR_RC_CHRG_AMT),
sum(A.CURR_UC_CHRG_AMT),
sum(A.PRODUCTS_NUM_CALLS),
sum(A.PRODUCTS_NUM_MINS),
A.CYCLE_CODE,
A.CYCLE_RUN_MONTH,
PP.PICC_STATE

FROM LD_ACCESS_VIEWS.VSLB001_BILL A,
LD_ACCESS_VIEWS.VSLC027_CUST_PROD_ASSIGNMENT CPA,
LD_ACCESS_VIEWS.VSLR017_PRICE_PLAN PP,
LD_ACCESS_VIEWS.VSLC007_BILLING_ACCOUNT BA,
LD_ACCESS_VIEWS.VSLC033_CUST_SERVICE_AGRMT CSA
WHERE
A.CYCLE_RUN_YEAR = 2001
AND A.CYCLE_RUN_MONTH = 2
AND A.CYCLE_CODE = 2
AND ( CPA.CUSTOMER_BAN=BA.BAN )
AND ( A.BAN=BA.BAN )
AND ( BA.BAN=CSA.BAN )
AND ( CSA.PRICE_PLAN=PP.PRICE_PLAN )
AND ( PP.PICC_STATE = 'TX' )

GROUP BY 1, 2, 3, 4, 16, 17, 18;

--wgr

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