Database Features
Tips and Tricks on using Exasol - from SQL and Lua Scripts to transactions and performance
cancel
Showing results forΒ 
Search instead forΒ 
Did you mean:Β 
Question 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? Answer 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)
View full article
Question What is a replicated table and how can the replication border be modified? Answer 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. Replication border 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]
View full article
Background 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. Explanation 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: IMPERSONATE 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 IMPERSONATE sys; 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. EXA_DBA_AUDIT_IMPERSONATION: 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 from exa_dba_audit_impersonation ) 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; Additional References See here for a video that explains impersonation:   https://www.youtube.com/watch?v=h2Mrbd0r67k for documentation   https://docs.exasol.com/sql/impersonate.htm   
View full article
This article presents a fully home-made solution written in Lua to find textual differences.
View full article
This article shows give a workaround for addressing non-ascii characters with regular expressions by using the unicode equivalent. 
View full article
This article shows you how to view queries running at a point in time in the past
View full article
Top Contributors