AJI

General
Enthusiast

AJI

I m currently working on AJI,I wonder how the quires hit the AJI.I will get report from cognos, It consists of quires. I should run EXPLAIN on each quirey whether it hits the AJI or not?

My question is?

Suppose I created one AJI for example as shown below. Now, i got quirey from cognos.
wht r the conditions for a qury to hit the AJI?

1)Does the columns of query should match with AJI columns?
2)Does the columns of query should match with AJI join coumns?
3)Does the columns of AJI should match with where condition of query?

CREATE JOIN INDEX DEV_EIS_T.jidxSO_Itm_PNCN ,NO FALLBACK ,CHECKSUM = DEFAULT AS

SELECT COUNT(*)(FLOAT, NAMED CountStar ),

DEV_EIS_T.f.CRD ,
DEV_EIS_T.f.SO_DocTypeCd ,
DEV_EIS_T.f.SlsOrgCd ,

SUM(CAST(((( CASE WHEN (NOT (DEV_EIS_T.f.SO_ItmWhlslAmt IS NULL )) THEN (DEV_EIS_T.f.SO_ItmWhlslAmt ) ELSE (0.0 ) END ))* DEV_EIS_T.fx.CrncBdgtExchgRate ) AS FLOAT))(FLOAT, NAMED SO_ItmWhlslAmt ),

SUM(CAST(((( CASE WHEN (NOT (DEV_EIS_T.f.TxDocCrncAmt IS NULL )) THEN (DEV_EIS_T.f.TxDocCrncAmt ) ELSE (0.0 ) END ))* DEV_EIS_T.fx.CrncBdgtExchgRate ) AS FLOAT))(FLOAT, NAMED TxDocCrncAmt ),

SUM(CAST(((( CASE WHEN (NOT (DEV_EIS_T.f.DocCrncCostAmt_BUOM IS NULL )) THEN (DEV_EIS_T.f.DocCrncCostAmt_BUOM ) ELSE (0.0 ) END ))* DEV_EIS_T.fx.CrncBdgtExchgRate ) AS FLOAT))(FLOAT, NAMED DocCrncCostAmt_BUOM ),

SUM(CAST(((( CASE WHEN (NOT (DEV_EIS_T.f.ZeroDivCpyWhlslAmt IS NULL )) THEN (DEV_EIS_T.f.ZeroDivCpyWhlslAmt ) ELSE (0.0 ) END ))* DEV_EIS_T.fx.CrncBdgtExchgRate ) AS FLOAT))(FLOAT, NAMED ZeroDivCpyWhlslAmt ),

SUM(( CASE WHEN (NOT (DEV_EIS_T.f.ZeroDivCpyCnfrmdCumQty_BUOM IS NULL )) THEN (DEV_EIS_T.f.ZeroDivCpyCnfrmdCumQty_BUOM ) ELSE (0.0 ) END ))(FLOAT, NAMED ZeroDivCpyCnfrmdCumQty_BUOM ),

SUM(( CASE WHEN (NOT (DEV_EIS_T.f.ZeroDivCpyCnfrmdCumQty_SUOM IS NULL )) THEN (DEV_EIS_T.f.ZeroDivCpyCnfrmdCumQty_SUOM ) ELSE (0.0 ) END ))(FLOAT, NAMED ZeroDivCpyCnfrmdCumQty_SUOM )

FROM

(DEV_EIS_T.SO_Itm f INNER JOIN DEV_EIS_T.CrncBdgtExchgRate fx ON DEV_EIS_T.f.TrcnCrncCd = DEV_EIS_T.fx.FROMCrncCd )

GROUP BY
DEV_EIS_T.f.CRD ,
DEV_EIS_T.f.SO_DocTypeCd ,
DEV_EIS_T.f.SlsOrgCd

PRIMARY INDEX ( CRD )
PARTITION BY ( RANGE_N((HASHBUCKET(HASHROW(SO_DocTypeCd )))/ 16 BETWEEN
0 AND 65533 EACH 5000 ,
NO RANGE, UNKNOWN),RANGE_N(CRD BETWEEN DATE '0001-01-01' AND
DATE '2005-12-31' EACH INTERVAL '1000' YEAR ,
DATE '2006-01-01' AND DATE '2010-12-31' EACH INTERVAL '1' YEAR ,
DATE '2011-01-01' AND DATE '8999-12-31' EACH INTERVAL '1000' YEAR ,
NO RANGE, UNKNOWN) );

plzzzzzzzzzzzzzzzzzzzzz try to respond asap???

thnkssssssss alottt
Tags (1)
2 REPLIES
Enthusiast

Re: AJI

Hi ,

I am also in need of the above resolution. Can someone please respond on the same.

1)Does the columns of query should match with AJI columns?

2)Does the columns of query should match with AJI join coumns?

3)Does the columns of AJI should match with where condition of query?

thanks

amg
Fan

Re: AJI

Thumb rule is to have all the columns used in query (select, where, group by clause) to be in AJIs group by and select clause.

E.g.

If the query is

Select c11, c12, sum(c19)

from t1

inner join t2 on c14=c24

where c13 =2 group by c21

then

Create AJI as

select c11, c12, c13, c21, sum(c19)

from t1

inner join t2 on c14=c24

group by c11, c12, c13, c21

Hope this helps