RE: fragment question
Michal Hajek wrote:
>
> When fragmenting by expression, can the following be done :
> a)....
> fragment by expression
> dat1 < "01011990" in dbspace1,
> remainder in dbspace2
>
> I believe it can be done. When row is updated, is it moved
> to appropriate dbspace ?
>
> But
> b)...
> fragment by expression
> dat1 < (today - 365) in dbspace1, -- (rows "older" then 1 year)
> remainder in dbspace2
>
> 1) can be such expression used ?
> 2) will the "old" rows move to dbspace1 without explicit update ?
>
> I suppose this will not work, but I'd like to be sure.
>
> Thanks, Michal
> --
> --------------------------------------------------------------
> Michal Hajek mailto:hajek@nspuh.cz
> Sprava NIS http://www.nspuh.cz
> NsP Uherske Hradiste phone : voice +420 0632 529 204
> Purkynova 365 fax +420 0632 551 014
> 686 68 Uherske Hradiste Czech Republic
> --------------------------------------------------------------
>
Michal:
All your suppositions are correct. In your first example the row will be =
moved from dbspace1 to dbspace2 if the date is changed. Your second =
example will not work.
Be very careful using dates in fragment expressions. It can create proble=
ms for users with varying $DBDATEs, because of the way the expression is =
stored. I discovered the way around this was to store the expression as =
its numeric equivalent (ie the internal representation). For your =
example, it would be
FRAGMENT BY EXPRESSION
dat1 < 32873 IN dbspace1,
REMAINDER IN dbspace2
To obtain the numeric equivalent, use
SELECT DISTINCT TRUNC(DATE("01011990"))
FROM sysusers ;
For more info about date based fragmentation and issues etc, have a look =
in Deja News for previous posts of mine. It's been discussed at quite =
some length!
Hope that helps
RET
+------------------------------------------+
| Richard Thomas |
| DBA - Marketing Information Systems |
| Optus IT |
| email: richard_thomas@yes.optus.com.au |
| Ph: +61 2 9342 7188 |
| "My opinions are my opinions" |
+------------------------------------------+