Empty a database table with no indexes

[D.Cook]

I'm not sure if this is possible, but I'm hoping there might be a way to empty a database table which does not have indexes built?
The reason I want to do this is because a data load was cancelled, so I want to clear this particular table and start loading from that table onwards.

I'm using V8.3A so truncating the area is not possible..

I have tried a simple for each table x: delete x. end. But this requires indexes to be built.
So why not build the indexes for that one table? Because I'm using V8.3A, which seems to process the whole database before building just one index, and it's taking longer than me writing this post.

 

[TomBascom]

Nope, you're stuck. Start over.

You have neatly summarized some very good reasons to upgrade.

 

[D.Cook]

If only it was up to me!

And if only this database wasn't 30gb...

 

[MaximMonin]

Delete from x.
works faster than
for each x:
delete x.
end.

 

[D.Cook]

Delete from x. works faster

I haven't seen that one before, interestingly it's not in the ABL reference. But, just like any other method of accessing records it requires a built index on the table

Unfortunately I've come upon this situation again today, however I think I have made a breakthrough! I deleted the table from the schema (even after the char-based Data Dictionary warned me that it could not be done without built indexes), then added the table back. I'm loading the data now, so will assume with crossed fingers that the old data isn't still lingering and my index build later fails...

So I'm thinking, next time I want to truncate a table, whether the indexes are built or not, I'll just delete and re-add it. I hope I'm not missing something here.

 

[RealHeavyDude]

I haven't seen that one before, interestingly it's not in the ABL reference. But, just like any other method of accessing records it requires a built index on the table

It's not ABL - it's medieval SLQ89 syntax that is understood by the 4GL compiler. In the dark ages this was the beginning of the SQL support and it's still there ... backwards compatibility I think. No wonder you didn't find it in any reference.

Regards, RealHeavyDude.

 

[MaximMonin]

Anyway... sql89 "delete from X" do not fetch records while deliting. So it is why it works faster. Only db server works w/o transfering record buffers to client.

 

[TomBascom]

Why is it that you have had this problem twice in less than a week?

Also, now that I've had a cup of coffee and this has come up again... depending on what is going on you can probably just restart the load with the failed table. You will get a whole bunch of duplicate record errors when the index rebuild runs but they will eventually sort themselves out.

One reason that my initial reaction was "you're screwed" is that I personally almost always run loads with -i. Which means that most errors trash the target db. I do that because it is generally enough faster to make it worth it. Of course YMMV.

 

[D.Cook]

I have been using ABL too long when I no longer recognise SQL statements!

Why is it that you have had this problem twice in less than a week?

Sometimes I don't think before I click.

I've not come across the -i parameter before -- thanks for the tip.

 

[gerie]

I used this once for each table and that worked that time:

On delete of addres override do: end. For each addres exclusive-lock: delete addres. end.