Show description

This statement is treated as DDL (Data Definition Language) SQL, which has other implications for transactions, such as implicitly committing other open transactions. See Chapter 9 for more details on recipes for transaction control and monitoring. Using the TRUNCATE command also resets the high-water mark for a table, which means when Oracle does optimizer calculations that include judging the effect of the used capacity in a table, it will treat the table as if it had never had data consume any space.

EMPLOYEES table. employees where last name = 'Fay' group by last name having count(*) = 1; With this recipe, we receive these results: LAST NAME COUNT(*) ------------------------- ---------Fay 1 Because there is exactly one LAST NAME value of Fay, we get a count of 1 and therefore see results. How It Works Only unique combinations of data will group to a count of 1. employees where last name = 'King' group by last name having count(*) = 1; This statement returns no results, meaning that the count of people with a surname of King is not 1; it’s some other number like 0, 2, or more.

Solution Deleting all rows from a table using the DELETE statement allows Oracle to fully log the action, so that you can roll back if you issue this statement by accident. But that same logging, and the time it takes, means that using the DELETE statement is sometimes too slow for the desired result. Oracle provides a complementary technique for removing all data from a table, called TRUNCATE. employees; How It Works No WHERE clause predicates or other modifiers are used when issuing the TRUNCATE statement.