 | |
#11
 
| |||
| |||
|
Re: Materialize view. -
11-07-2011
, 02:37 AM
|
What you cannot smell for sure, is your own stink |
That which lives off gnawing old bones and whose breath is fouler than a
cesspit.
The dreaded Stuckletroll of legend.
The being that Cannot Be Wrong, because if it once is, it will vanish up
its arsehole in a putrid stench of flatulence.
Poke it with a stick, by all means. It has a certain puerile amusement.
But let yourself not get trapped by its words. They only seem to make
sense to the veriest newbie, on which it feeds.
#12
| |||
| |||
|
|
I need to materialize a view each time the main table is filled with records wich happens once a day. I know that MYSQL is not able to materialize views. |
Quote:
|
So, my idea is to: - Set up something like a trigger after the event of the main table (the one that de view depends on), is truncated and then refilled with new records. -This trigger should: - delete old materialized view, (delete a table) - and then create a new table (materialized view) with the view structure filling it up with records. |
don't care that the view table does not reflect those updates) then you
don't need any trigger. Just fill your main table like normal and then
run the steps mentioned above:
1. truncate the view table
2. populate the view table with INSERT ... SELECT ... (where the SELECT
part is, what would be the view definition otherwise)
Hints:
- truncate is faster than delete; it's implemented as DROP + CREATE
- if you have many or expensive indexes on the view table, then you
might want to disable them before you fill the table (enable later)
- the above steps can be implemented as stored procedure
The solution with triggers must be used when there are ongoing updates
to the main table and you want the view to reflect them immediately.
For this you would create DELETE, INSERT and UPDATE triggers on the
main table. Then implement the trigger actions to modify the view table
accordingly. I.e. if a row is deleted from the main table, the trigger
must delete the respective row(s) from the view table. Details depend
on the view definition.
Last not least: please RTFM!
http://dev.mysql.com/doc/refman/5.5/...ete-speed.html
http://dev.mysql.com/doc/refman/5.5/...ert-speed.html
http://dev.mysql.com/doc/refman/5.5/...-routines.html
http://dev.mysql.com/doc/refman/5.5/en/triggers.html
XL
#13
| |||
| |||
|
|
"Centrino" <centrino (AT) nonelandia (DOT) com> wrote: I need to materialize a view each time the main table is filled with records wich happens once a day. I know that MYSQL is not able to materialize views. The main table is never written to except once a day? So, my idea is to: - Set up something like a trigger after the event of the main table (the one that de view depends on), is truncated and then refilled with new records. -This trigger should: - delete old materialized view, (delete a table) - and then create a new table (materialized view) with the view structure filling it up with records. If the main table is never written between the daily updates (or if you don't care that the view table does not reflect those updates) then you don't need any trigger. Just fill your main table like normal and then run the steps mentioned above: 1. truncate the view table 2. populate the view table with INSERT ... SELECT ... (where the SELECT part is, what would be the view definition otherwise) Hints: - truncate is faster than delete; it's implemented as DROP + CREATE - if you have many or expensive indexes on the view table, then you might want to disable them before you fill the table (enable later) - the above steps can be implemented as stored procedure The solution with triggers must be used when there are ongoing updates to the main table and you want the view to reflect them immediately. For this you would create DELETE, INSERT and UPDATE triggers on the main table. Then implement the trigger actions to modify the view table accordingly. I.e. if a row is deleted from the main table, the trigger must delete the respective row(s) from the view table. Details depend on the view definition. Last not least: please RTFM! http://dev.mysql.com/doc/refman/5.5/...ete-speed.html http://dev.mysql.com/doc/refman/5.5/...ert-speed.html http://dev.mysql.com/doc/refman/5.5/...-routines.html http://dev.mysql.com/doc/refman/5.5/en/triggers.html XL |
#14
| ||||
| ||||
|
|
You miss the tag ... you may be a good smoke seller but not a good consultant. |
they do.
Quote:
|
How the hell do you dare to say that the view query is malformed ?? You are temerary !! Then your desitions, are too ... for the "benefit" of your poor customers. |
idea how to solve the REAL problem.
Quote:
|
You.... you do not even know the nature of our database nor the complex nature of the large mathematical calcullus involved on it. No, it is not a simple mortage calculus, it is a very ,very complex phisical model wich works perfectly well on DB2 but not in MySQL !!!! |
performance problems with them. And if it runs so well on DB2, why
aren't you using it?
Quote:
|
And how the hell do you dare to compare my salary with yours ??? Not knowing nor my position not even yours. You father and mother of the smoke sellers ? |
postings.
And your refusal to observe common netiquette.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================
#15
| |||
| |||
|
|
Centrino wrote: What you cannot smell for sure, is your own stink I see you have encountered that-which-lives-below-the-bridge. That which lives off gnawing old bones and whose breath is fouler than a cesspit. The dreaded Stuckletroll of legend. The being that Cannot Be Wrong, because if it once is, it will vanish up its arsehole in a putrid stench of flatulence. Poke it with a stick, by all means. It has a certain puerile amusement. But let yourself not get trapped by its words. They only seem to make sense to the veriest newbie, on which it feeds. |
Have you found another ditch digging job? Or haven't you figured out
which end of the shovel to use yet?
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================
#16
| |||
| |||
|
|
P.S. Pleade don't top post.g Please don't post. |
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iQEcBAEBAgAGBQJOuDmDAAoJEIlLttVntI9MY3kH/2P1B0wKQoKvtWjklmRl92kR
UsSMGhRMYN9ji0EDVvF+j/ulXba3QXqDOs4sxbSQnBn4DQjE4KjRZCK1UGWTZ6VY
qhKMqKAaOEdLgIr/mPMkCV3jSSriYEvGLXhh9Y7qArcZs56n1YNs0ImcBDzOUQmL
ndwJJvHLwB4RH6WQYFcGhpssnPwL8c2ixyqjz/q0ABcs3z2yVNxJADyZh5wFUt+p
EBQcI70ZatXW9vqwhIFxqPJZLSSlnvDLYYZVwmS9CIWvfAVACZ 1lRVSuC3LP8+lo
TJNhWNzUZ9VGu21GQhNhkIiDX3Z1hUhL1/vkStEX3gJHXBgCMUzW6QNhKnG0Q6M=
=d0lf
-----END PGP SIGNATURE-----
#17
| |||
| |||
|
|
"Jerry Stuckle" <jstuckle (AT) attglobal (DOT) net> wrote P.S. Pleade don't top post.g Please don't post. +1 |
#18
| |||
| |||
|
|
Jacek Krysztofik wrote: "Jerry Stuckle" <jstuckle (AT) attglobal (DOT) net> wrote P.S. Pleade don't top post.g Please don't post. +1 |
--
John
#19
| |||
| |||
|
|
On Mon, 07 Nov 2011 20:03:27 +0000, The Natural Philosopher tnp (AT) invalid (DOT) invalid> wrote: Jacek Krysztofik wrote: "Jerry Stuckle"<jstuckle (AT) attglobal (DOT) net> wrote P.S. Pleade don't top post.g Please don't post. +1 Pathetic. |
substance to this (or any other) newsgroup.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.