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)
What is a replicated table and how can the replication border be modified?
Replicates are used "on-the-fly" for local joins if a table is "small" regarding the threshold.
A replicated table accesses data directly from other nodes' database memories and keeps a local copy in its own DBRAM.
If a replicated table is modified, only changed data is reloaded into database memories of other nodes.
Modified tables and subselects cannot be used with smart table replication. Table replication border does not apply to those.
💡 Using large replicated tables might cause decreased performance. Queries with expensive table scans (filter expressions) or between / cross joins may fall into this category.
A table will be joined by smart table replication if it has fewer or equal rows than the threshold below. The replication borders can be modified through extra database parameters in the web interface:
-soft_replicationborder_in_numrows=<numrows> [ default is 100000 rows]
Impersonation is a new feature in Exasol 6.1. It allows for switching to a different effective user during a session. Use this to impersonate another user identity.
The new system privilege IMPERSONATE ANY USER has been granted to sys and to the DBA role. This allows sys respectively grantees of the DBA role to become any user without having to specify their password:
-- Example 1: sys is connected and becomes fred:
Otherwise, the IMPERSONATION ON <user_name> privilege can be granted to a user that should be allowed to impersonate that other user.
-- Example 2: bob is allowed to impersonate sys
GRANT IMPERSONATION ON sys TO bob;
Using the IMPERSONATE command, users can change the effective user within their sessions:
-- Example 3: bob impersonates sys, so that he has sys' privileges
SELECT current_user ; -- shows BOB
SELECT current_user ; -- shows SYS
The following system tables contain information about impersonations:
EXA_USER_SESSIONS, EXA_ALL_SESSIONS, EXA_DBA_SESSIONS:
The column USER_NAME shows the user connected to the database; that is the user who opened the session.
The column EFFECTIVE_USER shows the current effective user after impersonation. Queries are executed with the privileges of the effective user.
IMPERSONATOR: The user who impersonates (before executing the IMPERSONATE command).
IMPERSONATEE: The new effective user (after executing IMPERSONATE).
SESSION_ID, STMT_ID: The session id and statement id of the IMPERSONATE command withing this session.
Mind that EXA_DBA_AUDIT_... tables are only populated with data if auditing is enabled in the database settings in EXAoperation.
EXA_DBA_AUDIT_SQL does not contain any information about the effective user that executed a SQL statement.
EXA_DBA_AUDIT_SESSIONS shows only the user that opened the connection.
The following query adds an EFFECTIVE_USER column to the EXA_DBA_AUDIT_SQL. It shows for every query with whose user's privileges a query was executed:
with impersonations as
select stmt_id + 1 as first_stmt_id, lead(stmt_id, 1, 999999999999) over ( partition by session_id order by stmt_id ) as last_stmt_id,
impersonatee as effective_user, session_id
select nvl(ai.effective_user, se.user_name) effective_user, sq.*
from exa_dba_audit_sql sq
join exa_dba_audit_sessions se
on sq.session_id = se.session_id
left join impersonations ai
on sq.session_id = ai.session_id
and sq.stmt_id between ai.first_stmt_id and ai.last_stmt_id
where sq.session_id = current_session
order by stmt_id;
for a video that explains impersonation: https://www.youtube.com/watch?v=h2Mrbd0r67k
for documentation https://docs.exasol.com/sql/impersonate.htm