Delete is a data manipulation language, or DML operator for removing one or more rows from a table. You can also delete a user or a database:
DELETE FROM table_name [ALL | WHERE condition];
For this recipe to complete you need to connect to the Teradata instance and open SQLA.
- Identify the table from which data needs to be deleted.
Deletefull tables or only required rows:
DELETE FROM test01.web_clicks ALL; -- All rows deleted DELETE FROM test01.web_clicks where site=01 and partition_date=date-1 -- Rows with Site=0 only deletes and use partition column to avoid full table scan.
There are a few basic considerations that need to be taken into account while writing a delete query; access to the data needs to be optimized, which means the access path the optimizer chooses should be efficient:
- Full table scan: Use primary index column, PPI, if available, when doing a
deleteof a million rows. - Index access: Use index, if available...