Prev: Much RAM or fast RAM?
Next: Stored Procedure performance issue - PLEASE HELP!
From: oddbande on 31 Jan 2010 13:35
On Jan 31, 6:43 pm, Naresh Chainani <fornar...(a)gmail.com> wrote:
> On Jan 29, 6:01 am, oddbande <odd.ander...(a)ergo.no> wrote:
>
>
>
>
>
> > I am new to using table partitioning, but now we want to use it on a
> > couple of tables. But when trying this we have experienced something I
> > consider strange, when db2 doesn't act as i would expect.
>
> > The environment is DB2 9.7 (fixpack 1) on Linux for z/OS (64-bits).
>
> > We have created a table like this:
> > CREATE TABLE "MLTEST01"."HENDELSE_I_FORSTE1"  (
> >                   "KOLLI" VARCHAR(35) NOT NULL ,
> >                   "I_FORSTE_TIDSPUNKT" TIMESTAMP NOT NULL ,
> >                   "I_FORSTE_ENHET" VARCHAR(6) NOT NULL ,
> >                   "I_FORSTE_KODESELSKAPHEND" VARCHAR(3) ,
> >                   "I_FORSTE_POSTREF" VARCHAR(35) ,
> >                   "I_FORSTE_IDENTMVU" VARCHAR(10) ,
> >                   "I_FORSTE_KODELANDHEND" CHAR(2) ,
> >                   "I_FORSTE_POSTNR" VARCHAR(10) ,
> >                   "I_FORSTE_STATUSEMS" CHAR(1) ,
> >                   "I_FORSTE_STATUS" CHAR(1) ,
> >                   "I_FORSTE_KODEARSAKLM" VARCHAR(3) ,
> >                   "I_FORSTE_KODETILTAKLM" VARCHAR(3) ,
> >                   "I_FORSTE_SIGNMOTTAKER" VARCHAR(28)
> >                    )
> >                  IN "TSJAHEND" INDEX IN "IXJAHEND"
> >                  PARTITION BY RANGE (I_FORSTE_TIDSPUNKT)
> >                    ( PARTITION UKE532009 STARTING ('2009-12-28-00.00.00.000000')
> > INCLUSIVE,
> >                      PARTITION UKE012010 STARTING ('2010-01-04-00.00.00.000000')
> > INCLUSIVE,
> >                      PARTITION UKE022010 STARTING ('2010-01-11-00.00.00.000000')
> > INCLUSIVE,
> >                      PARTITION UKE032010 STARTING ('2010-01-18-00.00.00.000000')
> > INCLUSIVE,
> >                      PARTITION UKE042010 STARTING ('2010-01-25-00.00.00.000000')
> >                      ENDING ('2010-02-01-00.00.00..000000') EXCLUSIVE
> >                    )
> >                    ;
>
> > And one index:
> > CREATE INDEX "MLTEST01"."HENDELSE_I_FORSTE__TIDSPUNKT" ON
> > "MLTEST01"."HENDELSE_I_FORSTE1"
> >                 ("I_FORSTE_TIDSPUNKT" ASC,
> >                  "KOLLI" ASC)
> >                 CLUSTER
> >                 PCTFREE 10 MINPCTUSED 10
> >                 COMPRESS NO ALLOW REVERSE SCANS;
>
> > We have tried to run db2expln on one statement and db2 doesn't seem to
> > be able to eliminate partitions from the scan if the sql statement is
> > like this:
> > select * from mltest01.hendelse_i_forste1  where i_forste_tidspunkt
>
> > >= current timestamp - 2 days
>
> > Then it says in the explain output:
> > List Prefetch Preparation
> > |  Access Table Name = MLTEST01.HENDELSE_I_FORSTE1  ID = -6,-32767
> > |  |  #Columns = 13
> > |  |  Data-Partitioned Table
> > |  |  Skip Inserted Rows
> > |  |  Avoid Locking Committed Data
> > |  |  Currently Committed for Cursor Stability
> > |  |  RID List Fetch Scan
> > |  |  All data partitions will be accessed
>
> > Seems that there is no partitions excluded from the scan.
>
> > But if the search predicate is hard coded like this:
> > select * from mltest01.hendelse_i_forste1  where i_forste_tidspunkt
>
> > >= '2010-01-27-14.30.00.000000'
>
> > then the explain output says:
> > |  Data Partition Elimination Info:
> > |  |  Range 1:
> > |  |  |  #Key Columns = 1
> > |  |  |  |  Start Key: Inclusive Value
> > |  |  |  |  |  1: 2010-01-27 14:30:00.000000
> > |  |  |  |  Stop Key: End of Data Partition Range
> > |  Active Data Partitions: 4
>
> > Then db2 only scans partition number 4 where the data we want is
> > located.
>
> > Why is it so? Is it not possible to use a computed search predicate as
> > we did (current timestamp - n days)? We gets this predicate from an
> > application - will it work as expected if the application performs the
> > computing and only sends in the computed value as a parameter?
>
> > Regards
> > Odd Bjørn Andersen
> > ErgoGroup AS
> > Oslo, Norway
>
> db2expln reports on any partition elimination that was done at compile
> time. In the hard coded predicate case, you saw exactly which
> partitions will be accessed since the partition elimination was done
> at compile time.
>
> In the CURRENT TIMESTAMP predicate, the value of this special register
> is obtained at execution time and thus DB2 Compiler has no idea what
> the expression is going to resolve to. Only during runtime, when the
> expression is computed, DB2 does partition elimination at runtime.
> Other cases where runtime partition elimination applies is host
> variables or parameter markers or look-up of inner table of a nested
> loop join.
>
> Naresh- Hide quoted text -
>
> - Show quoted text -

I hope you are right:-) And what you say seems sensible. So I trust
that things will work fine when we will try this on a larger scale.

Regards
Odd Bjørn
First  |  Prev  | 
Pages: 1 2
Prev: Much RAM or fast RAM?
Next: Stored Procedure performance issue - PLEASE HELP!