TD 14 Procedure help

Database
Enthusiast

TD 14 Procedure help

Hello,

I would like a really big favour.

I have a table which is partitioned on date level .  I join this table with an other  ,but since the second table has more than one values , i do not have partition elimination.

Can you provide me a simple store procedure where i save the dates from the second table and i assingn the values to the where clause of  the first table .

Meaning,

sel   * from fact f

inner join dimension d

on f.cal_date = d.cal_Date ;

If if have only 3 values on dimension table ,  , and i have partition on f.cal_Date by date ,

the i expext TD to check 3 partitions. How i can manage this with store procedure?

Meaning,

sel * from fact f

inner join dimension d

on f.cal_Date in ('2014-01-01' , '2014-01-02' ,'2014-01-03');

**

This ('2014-01-01' , '2014-01-02' ,'2014-01-03') could be saved on one parameter and run it as a single query..

or

run the same three times.

thank you.

2 REPLIES
Enthusiast

Re: TD 14 Procedure help

eejimkos,

With your query

sel   * from fact f

inner join dimension d

on f.cal_date = d.cal_Date ;

you should see Dynamic Partition Elimination

Your query will access only the 3 partitions.

If you don't see this in the explain could you please post it.

Rglass

Enthusiast

Re: TD 14 Procedure help

Hi,

Thanks for the reply ,

To be honest my query is delete one.

The target table A  has two level of partitions , level1 : clm2 , level2 : clm2

The table which i joined B , has no partition defined with PI clm2,clm3

By using directly the deletetion , i have inclusion dynamic hash join at the end.

If i will put the values into a volatile with UPI , then i have enhanced by dynamic partition elimination.

del from table a

where a.clm1 in (1,2,3)

and a.clm2  = b.clm2

and a.clm3  = c.clm3

Can you check the logic of the procedure , i made some tests, i hope it is working ok.(even with repeated deletions from the correct partitions)

REPLACE PROCEDURE DBADMIN.TEST( ) BEGIN

DECLARE V_TIME_ID INTEGER;

DECLARE CUR1 CURSOR FOR
SELECT clm2 FROM table 2 GROUP BY 1 ;

OPEN CUR1 ;
FETCH CUR1 INTO V_TIME_ID ;

MAINLOOP: WHILE (SQLCODE = 0 )
DO
DEl FROM table A
WHERE a.clm1 IN (200,618)--first partition
AND A.clm2 = V_TIME_ID --second partition
and a.clm3 = b.clm3

FETCH CUR1 INTO V_TIME_ID ;

END WHILE MAINLOOP;
CLOSE CUR1;
END;

call dbadmin.test();

Thank you once more for your time.