Fun with NULL

I think this is interesting: SELECT * FROM dual WHERE (1 BETWEEN 0 AND NULL) — FALSE SELECT * FROM dual WHERE (0 BETWEEN 1 AND NULL) — FALSE SELECT * FROM dual WHERE NOT (1 BETWEEN 0 AND NULL) — FALSE SELECT * FROM dual WHERE NOT (0 BETWEEN 1 AND NULL) — TRUE […]

How to Restore a Table in Oracle

Simple and easy to use querying tool for Oracle SQL Tools has a little drawback. Trying to remove search string from the filter, you might hit Del. This does not remove the search string but drops the table. Of course it asks confirmation. But if you accidentally dropped the table, the following command might help: FLASHBACK TABLE […]

Use of Index and Characterset

This post demonstrates influence of characterset on use of indexes. See also Language and Use of Indexes in Oracle /* DROP TABLE t; ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; */ SELECT PARAMETER, VALUE FROM V$NLS_PARAMETERS; CREATE TABLE t (x VARCHAR2(255)); INSERT INTO t VALUES ('a'); INSERT INTO t VALUES ('b'); INSERT INTO t VALUES ('c'); INSERT INTO […]

Language and Use of Indexes in Oracle

Symptoms It takes a very long time to load a data warehouse on Oracle database with Kettle when the language in the Windows Region and Language settings is different from English (United States). Cause Oracle JDBC driver used by Kettle issues command ALTER SESSION SET NLS_LANGUAGE and changes default database language. Changing NLS_LANGUAGE also changes […]

Script to kill user sessions in Oracle

Script to kill all sessions of a user in Oracle. BEGIN FOR r IN (select sid,serial# from v$session where username = 'USER') LOOP EXECUTE IMMEDIATE 'alter system kill session "' || r.sid  || ',' || r.serial# || ""; END LOOP; END; Manual Retrieve session identifiers and session serial number (which uniquely identifies a session's objects): select […]

Comparison of database schemas

Suppose we need to compare two database schemas to find out changes such as new tables, columns, and change of data type. Test create user USER1 identified by USER1; create user USER2 identified by USER2; create table USER1.T1 (C int); create table USER2.T1 (C int); create table USER1.T2 (C int); create table USER2.T3 (C int); […]

How to print explain plan

DELETE PLAN_TABLE; EXPLAIN PLAN FOR SELECT * FROM t WHERE group2=1; SELECT * FROM TABLE(dbms_xplan.display()); Oracle utilities The Oracle utility creating PLAN_TABLE: D:\Oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql The Oracle utility displays the last explain plan. It displays also parallel query information if the plan happens to run parallel: D:\Oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplp.sql The utility that does not displays parallel query information (sequential). […]

Selectivity and Execution Plan

Cardinality is how many rows should be returned after evaluating predicates. Selectivity is cardinality divided by the total number of input rows. DBMS uses selectivity for choosing the right index. Density is characteristic of data in a column. It is equal to 1 divided by the number of distinct values. Let's consider few examples of […]