11.1.4 Retrieving and updating data in a base table
This subsection describes how to retrieve and update data in a base table by using SQL statements.
- To retrieve data in a base table
-
To retrieve data from a base table, execute the SELECT data manipulation SQL statement.
For details about the SELECT statement, see SELECT (retrieve rows) in the manual HADB SQL Reference.
- Adding data to a base table (by inserting rows)
-
To add data to a base table, execute the INSERT data manipulation SQL statement.
For details about the INSERT statement in INSERT (insert rows) in the manual HADB SQL Reference.
- Important
-
If you add a large amount of data to a column store table, use the adbimport command rather than the INSERT statement. If you use the INSERT statement to add data to a column store table, the added data is stored in row store format. This might degrade the retrieval performance for the column store table. This is why we recommend that you use the adbimport command when adding a large amount of data to a column store table. For details about how to add data by using the adbimport command, see 11.1.7 Storing data in a base table (data import).
- To update data in a base table (by updating rows)
-
To update data stored in a base table, execute the UPDATE data manipulation SQL statement.
For details about the UPDATE statement, see UPDATE (update rows) in the manual HADB SQL Reference.
- Deleting data in a base table (by deleting rows)
-
To delete data stored in a base table, execute the DELETE data manipulation SQL statement.
For details about the DELETE statement, see DELETE (delete rows) in the manual HADB SQL Reference.
- Note
-
When you delete all the data stored in a base table, we recommend that you use the TRUNCATE TABLE statement rather than using the DELETE statement.
For details about the TRUNCATE TABLE statement, see 11.1.5 Deleting all rows from a base table.
- Important
-
If you use an SQL statement (INSERT, UPDATE, or DELETE statement) to repeatedly perform data addition, update, or deletion for a column store table, the retrieval performance for the column store table might be degraded. To prevent degradation of retrieval performance, we recommend that you take the following actions:
-
Enable the updated-row columnizing facility.
-
Periodically obtain the information about the need for reorganization of the table or chunk to check whether the table or chunk needs to be reorganized.
For details about the updated-row columnizing facility, see 11.18 Using the updated-row columnizing facility (maintaining the retrieval performance for column store tables).
For details about how to check whether the table or chunk needs to be reorganized, see 11.1.9 Checking whether a single-chunk table needs to be reorganized or 11.4.13 Checking whether a multi-chunk table needs to be reorganized.
-