ALTER in procedure

Stefan Manegold Stefan.Manegold at cwi.nl
Tue Jul 23 10:23:00 CEST 2013


> Hi Stefan,
> Thank you for a brisk answer.
> I'm aware of the conflict with standards. We are mostly Oracle shop.
> 
> Reason for disabling constraints is speed of loading.

Be aware that MonetDB will check/validate the constraints in either case,
i.e., either during loading (copy into ?) in case constraints are enable,
or once you re-enabled them after loading with constraints disabled. 

Did you test the speed difference between loading with constraints enabled vs. loading with constraints disabled plus enabling (checking) the constraints afterwards?
(you can do this "by hand" in plain SQL, i.e., outside any procedure)

If so, what are your findings?

> But maybe we
> should use different approach like indexing. We have started to use
> MonetDB recently.
> We have a table hundreds millions of rows and we are tagging data in it.
> Example SQL:
>      INSERT INTO crmkartastitek (
>        cislo_karty,
>        stitek_id,
>        stitek_nazev,
>        stitek_upresneni
>       )
>       SELECT DISTINCT cislo_karty,v_stitek_id,v_stitek_nazev,v_stitek_upresneni
>        FROM (SELECT cislo_karty
>          FROM crmkartapohyby p
>          JOIN crmkartaaktivita k ON p.cislo_karty=k.cislo_karty
>          WHERE 1=1
>           AND k.aktivita > 0
>           AND p.pohyb IN ('VR','VV')
>           AND p.datum BETWEEN (p_datum - INTERVAL '12' MONTH) AND (p_datum - INTERVAL '1' DAY)
>          GROUP BY p.cislo_karty
>          HAVING SUM(castka_r + castka_d) > SUM(castka_v)
>        ) AS sumy_castek_pohybu;

What exactly it the problem (if any)?

Stefan

> The big table is crmkartapohyby. Data to the table is fed from Oracle
> through mclient java interface.
> 
> Thank you for any hints,
> Radovan
> 
> On 07/23/2013 09:55 AM, Stefan Manegold wrote:
> > Hi Radovan,
> >
> > I'm afraid that MonetDB does not allow any schema manipulation with SQL
> > procedures (or functions).
> > I'm actually not sure, whether the SQL standard allows that ...
> >
> > Having said that, why would you want to drop constraints while loading
> > data, only to add them afterwards, again?
> >
> > Best,
> > Stefan
> >
> _______________________________________________
> users-list mailing list
> users-list at monetdb.org
> http://mail.monetdb.org/mailman/listinfo/users-list
> 

-- 
| Stefan.Manegold at CWI.nl | DB Architectures   (DA) |
| www.CWI.nl/~manegold/  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |




More information about the users-list mailing list