| T.R | Title | User | Personal
Name | Date | Lines |
|---|
| 3880.1 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Wed Apr 16 1997 13:15 | 38 |
| ~He tried to embed in the code the ddl necessary to do so, but he's got problems
~in compiling (REL_EXISTS). I found a reference in this notes file and in RDB_40
~(way back in the past...) where Ian asked for an SPR to correct the Rdb
~behavior that seemed not so clever. Did this change anyhow?
No this did *not* change. I never saw an SPR or any other formal suggestion...
~set transaction read write reserving my_table for shared write;
~execute immediate drop table my_table cascade;
~execute immediate create table my_table (...);
~loop
~ insert into my_table values (...);
~until eof;
~
~the problem is that the first insert fails with sqlcode=-1033 (didn't reserve
~the table you're inserting into). Is this expected behavior?
Yes this is expected, the table which was reserved was DROP'd, the new table
even though it has the same name is a *different* table.
In this case SHARED WRITE adds no value, so remove the reserving clause. In
Rdb8 you'll be able to use LOCK TABLE to add the new table into the list of
reserved tables.
In Rdb7 I suggest you use TRUNCATE TABLE to remove all the data, it has much
the same effect (on the data) as DROP TABLE CASCADE, but then you would avoid
the DROP/CREATE.
~His need is to perform everything (ddl + load + create index) in only one
~transaction, so that if the load fails they can rollback and restore the
~previous situation.
This customer is lucky he is using Rdb! In most other database servers (such
as Oracle7) the DDL is autocommitted with no chance to ROLLBACK.
cheers (I think this product is great :-)
Ian
|
| 3880.2 | | itvms1.it.oracle.com::MFERRARI | Murphy was an optimist | Wed Apr 16 1997 13:20 | 17 |
| Thanks Ian for your reply.
>In this case SHARED WRITE adds no value, so remove the reserving clause. In
This is exactly what we ended up doing. I *did* realize that the logical area
should be different, so it was actually expected.
Btw (as we're planning to switch to Rdb7 in the near future), is the TRUNCATE
statement rollback-able?
>cheers (I think this product is great :-)
It couldn't be any different, with such an engineering team! ;-)
Cheers,
/max
|
| 3880.3 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Wed Apr 16 1997 14:06 | 6 |
| ~Btw (as we're planning to switch to Rdb7 in the near future), is the TRUNCATE
~statement rollback-able?
Yes... naturally! :-)
Ian
|
| 3880.4 | | ORAREP::HERON::GODFRIND | Oracle Rdb Engineering | Wed Apr 23 1997 09:32 | 21 |
| >my customer is trying to speed up a program that must delete from a table
>1,800,000 records and then reload the table based on data stored in an RMS
>file. He thought of dropping the table cascade, recreate it, perform the load,
>and finally create the indices.
>...
>(execute immediate). His need is to perform everything (ddl + load + create
>index) in only one transaction, so that if the load fails they can rollback and
>restore the previous situation. So he does something on the lines of
>...
Will the load re-insert the same number of rows (i.e. again 1.8 million). If
so, the method the customer is thinking about may not be the best. The load
will require a very large RUJ, and undoing the load is likely to be very long
if the failure happens late in the load sequence.
My personal approach would rather be to load the data in small increments (say
100,000 rows at a time), commit each and retain some state info in the
database, so the load can be easily resumed where it stopped. Which is probably
just as fast as undoing everything done sofar.
/albert
|
| 3880.5 | It depends on the power you have... | itvms1.it.oracle.com::MFERRARI | Murphy was an optimist | Mon Apr 28 1997 06:27 | 21 |
| >will require a very large RUJ, and undoing the load is likely to be very long
>if the failure happens late in the load sequence.
Actually we had a chance to verify the rollback times (not voluntarily, it was
a coding error at the end of the program that caused the failure :-), and we
had the following results:
total time to drop/create/load: about 50 minutes
time to rollback the whole thing (the code failed while creating the index, so
after that all the data had been loaded): less than 15 minutes
Well, I should have said that we have an 8400 AXP with 10 350 Mhz CPU, 6 GB
of RAM and five 16-elements stripe sets... :-)
Seriously speaking, though, the major concern of the customer is the ability to
restore the rpevious situation in case something fails, and given the general
complexity of the system, the more is performed automatically by Rdb the better
for them. I do agree with you that the approach you suggested is definetly
better and neater, but... you know customers! :-)
Thanks,
/max
|