Is the ROWID always assigned in an ascending order? Is it possible to determine the sequence of inserted records with the ROWID?
The ROWIDs of a table are managed by the DBMS. They are assigned in an ascending order per node and stay distinct within a table. For different tables, they could be the same.
DML statements such as UPDATE, DELETE, TRUNCATE or MERGE might internally reorder data storage, invalidating and reassigning all the ROWIDs. Contrary to that, structural table changes such as adding a column leave the ROWIDs unchanged. Altering distribution keys of or reorganizing a table will certainly reassign ROWID values.
Therefore the ROWID can't be used to determine the exact sequence or age of a record, it is designed to be a short term identifier for rows to be used for duplicate elimination.
The ROWID pseudo column can only be used on table objects, not on views or subselects. If you try it on a view, you will receive an appropriate error message
We want to replace all NULL values within a certain table with the default value specified for the according column. And we don't want to create the necessary update statements manually.
A small metadata-driven procedure script (Lua) that issues the required update statements. (See Attachment)
performs a rollback after the metadata request, to avoid a read-write conflict scenario.
performs all updates within a single transaction.
will not abort when an update on one of the columns fails.
performs a commit when all columns have been handled, regardless of any errors encountered.
...on column selection
The script includes all columns that do have a DEFAULT value set. It excludes all columns with a NOT NULL constraint (ignoring the actual state of the constraint). Obviously, such a column can not contain any NULL values that need updating.
...on row selection
Due to Exasol's memory management and data processing, the script handles each column separately. This minimizes both the amount of memory required for processing and the amount of data blocks being written. The script does not contain any delta functionality, it will process all rows of the table each time it is called.
Just create the script in any schema you like (CREATE SCRIPT permission required). It does not have any dependencies.
create or replace /* procedure */ script REPLACE_NULL( schema_name , table_name )
When calling the script, it expects two parameters: A schema name and a table name:
execute script REPLACE_NULL( 'my schema ' , 'my table ' );
💡 Both schema and table name are expected as string and will be case-sensitive.
open schema SR9000;
-- Rows affected: 0
create table Invoice( invoice_id int , invoice_date date default date '2017-01-01' );
-- Rows affected: 0
insert into Invoice values (1, null ), (2, null ), (3, '2017-02-01' );
-- Rows affected: 3
execute script REPLACE_NULL( 'SR9000' , 'Invoice' );
--  " No columns found for " SR9000 "." Invoice "" caught in script " SR9000 "." REPLACE_NULL" at line 23 ( Session : 1585944483210400591)
... yes. We created the table using a regular identifier, so it ended up as uppercase...
execute script REPLACE_NULL( 'SR9000' , 'INVOICE' );
2 rows updated
Filter on DOUBLE columns returns unexpected results.
The DOUBLE values you see in EXAplus may differ from the actual database values due to the JDBC double handling and rendering.
As the DOUBLE data type is only an approximative numeric type, filters on DOUBLE columns may return unexpected results, due to the approximative nature of this data type.
The datatype DOUBLE in the Exasol DB (including 6.x) is defined as an 64-Bit floating point value which represents values with a combination of an exponent and a fraction in binary form. This means that not every existing (numeric) value can be exactly represented by this type.
We recommend to filter only on DECIMAL columns to avoid described problems.
The actual value range of this type can be seen in our documentation:
How does Exasol determine the data type of a multiplication of different dataypes?
Example: what data type is the result when you multiply database columns number (18,3) by number (18,9)?
Can numeric overflows occur?
In operations with multiple operands (e.g. the operators +,-,/,*) the operands are implicitly converted to the biggest occurring data type (e.g. DOUBLE is bigger than DECIMAL) before executing the operation. This rule is also called numeric precedence.
Numeric overflow can occur and result in a data exception - numeric value out of range.
The result for this specific calculation would be decimal(36,12). Regularly the precision and scale were added. Examples:
(12,0) * (15,3) = (27,3)
(12,0) * (15,9) = (27,9)
(18,3) * (18,9) = (36,12)
For sums the scale is taken from the factor with the highest preciseness.
(12,0) + (15,9) = (22,9)
(15,3) + (15,9) = (22,9)