-
-
Notifications
You must be signed in to change notification settings - Fork 209
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Add TRUNCATE TABLE [CORE2479] #2892
Comments
Commented by: Sean Leyne (seanleyne) What logic would you propose for handling the validation of any Foreign Key relationships which are dependent on the table? (It is very likely for a table to have FK relationships) |
Commented by: K. A. (parshua) Block if the table is referenced by foreign keys. TRUNCATE marks/deallocates the pages, and does not use transaction log, so it is considered as a DDL command. By using the REUSE SPACE keyword, the pages will not return to page pool, otherwise, the pages will be deallocated. It's worth mentioning that the kind of tables which this command is used for, are tables which contain some kind of prolonged temp data in nature, like log tables and calculation tables. These kind of tables are not referenced by foreign keys and most of the time, they do not refer to other tables. They may be populated several times in a short period, and remain so for a longer period. In our case, we calculate results for quiz data taken by some hundred thousands of students, and we may erase and recalc several times in the quiz day (Due to some feedback about quiz questions). There are two tables: One for overall quiz result, and another for some analysis for every single quiz answer (a quiz consists of ~250 questions).Then after the final calculation and publishing the results (That same evening), the system will remain so until the next quiz which will be in a week or two. |
Commented by: Ann Harrison (awharrison) Be really careful with this one - it's been a significant problem in the Falcon storage Firebird has mechanisms for recognizing that a transaction (and connection?) has What we (Falcon/MySQL) decided to do about foreign key relationships was to Truncate is very handy when you're developing an application, but its utility in a |
Modified by: @dyemanovVersion: 3.0 Initial [ 10301 ] => |
Commented by: pabloj (pabloj) Truncate is very important for datawarehouses, an example is when you have to quickly empty a staging table to reload it. |
Commented by: @hvlad Feature was already discussed in fb-architect and we have an agreement that |
Modified by: @hvladassignee: Vlad Khorsun [ hvlad ] |
Commented by: Milan Babuskov (milan.babuskov) Perhaps the first implementation of this feature would only allow truncate tables that: - are not referenced by foreign keys I would gladly give up FK or triggers to have this feature. |
Modified by: @dyemanovFix Version: 3.0 Beta 2 [ 10586 ] |
Modified by: @dyemanovassignee: Vlad Khorsun [ hvlad ] => Dmitry Yemanov [ dimitr ] Fix Version: 3.0 Beta 2 [ 10586 ] => |
Modified by: @dyemanovFix Version: 4.0 Beta 1 [ 10750 ] |
Commented by: limingfeng (lmf1967) TRUNCATE TABLE command is very useful for our daily report. I am waiting for it| |
Commented by: Volker Rehn (vr2_s18) please also backport truncate to version 3 |
Commented by: limingfeng (lmf1967) backporting to version 2.5 is much apprecialted! |
Modified by: @dyemanovFix Version: 4.0 Beta 1 [ 10750 ] => |
Commented by: Volker Rehn (vr2_s18) Has TRUNCATE TABLE been postponed for Firebird 4.0 Beta 1? |
Commented by: @dyemanov Yes, it was. It needs some discussion in fb-devel. |
There are any plans to see this one in Firebird 5? |
Submitted by: K. A. (parshua)
Votes: 14
DELETE is both costly and slow.
DROP TABLE frees all pages and is not always possible due to dependencies.
Using TEMPORARY TABLES is not desired because in many situations the data must be persisted.
Proposal:
TRUNCATE TABLE TABLE_NAME [REUSE SPACE];
This will have all the aforementioned features without the cons.
The text was updated successfully, but these errors were encountered: