Category Archives: Oracle

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

This has a good explanation however I am still embarrassed ūüôā

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 table_name TO BEFORE DROP;

The latest version of the tool (1.6) has a nice feature – read-only connection to database.

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 t VALUES ('d');

CREATE INDEX t_idx ON t (x);

-- the index is used
UPDATE t SET x='a' WHERE x='a';

ALTER SESSION SET NLS_LANGUAGE='CZECH';

-- the index is still used if NLS_CHARACTERSET=WE8MSWIN1252
-- the index is not used if NLS_CHARACTERSET=WE8ISO8859P1
UPDATE t SET x='a' WHERE x='a';

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 oracle parameter NLS_SORT¬†from BINARY to the specified language.
  • When NLS_COMP is equal to ANSI and the value of NLS_SORT is not BINARY, the linguistic comparison is used for WHERE statements.
  • When the value of NLS_COMP is different from BINARY a full scan is used for VARCHAR2 fields instead of index range scan.

Resolution

Add “-Duser.country=en -Duser.language=en” to java runtime options in Kettle batch files.

Experiment

When NLS_SORT is BINARY, the index is used.

SELECT PARAMETER, VALUE FROM V$NLS_PARAMETERS
WHERE PARAMETER IN ('NLS_LANGUAGE', 'NLS_SORT', 'NLS_COMP');

UPDATE F_JOB_BUDGET_LINE SET ACTUAL_COST=0
WHERE INSTANCE_KEY='JobBudgetLine00000000000004835665';


When the  parameter NLS_LANGUAGE is changed, NLS_SORT is also changed. The index is not used when NLS_SORT=CZECH and NLS_COMP=ANSI.

ALTER SESSION SET NLS_LANGUAGE='CZECH';
SELECT PARAMETER, VALUE FROM V$NLS_PARAMETERS
WHERE PARAMETER IN ('NLS_LANGUAGE', 'NLS_SORT');

UPDATE F_JOB_BUDGET_LINE SET ACTUAL_COST=0
WHERE INSTANCE_KEY='JobBudgetLine00000000000004835665';


We can change NLS_SORT to BINARY to make the index used again. Alternatively we can set NLS_COMP to BINARY, this will have the same effect.

ALTER SESSION SET NLS_SORT='BINARY';
SELECT PARAMETER, VALUE FROM V$NLS_PARAMETERS
WHERE PARAMETER IN ('NLS_LANGUAGE', 'NLS_SORT');

UPDATE F_JOB_BUDGET_LINE SET ACTUAL_COST=0
WHERE INSTANCE_KEY='JobBudgetLine00000000000004835665';

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 sid, serial# from v$session where username = 'USER'

The syntax to kill session is

alter system kill session 'sid,serial#'

Disconnecting session:

alter system disconnect session 'sid,serial#' post_transaction;
alter system disconnect session 'sid,serial#' immediate;

More information

Killing Oracle Sessions on www.oracle-base.com

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);
create table USER1.T4 (C int, D int);
create table USER2.T4 (C int);
create table USER1.T5 (C int, E int);
create table USER2.T5 (C int, E varchar(20));

Result

Schema comparison

Continue reading

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).

D:\Oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxpls.sql

Hierarchical query

An example of use of hierarchical queries to print the last explain plan:

SELECT
  LPad(' ', level-1) || operation || ' (' || options || ')' AS operation,
  coalesce(object_name, ' ') object,
  cost,
  cardinality,
  bytes
FROM
  plan_table
START WITH
  id = 0
CONNECT BY PRIOR
  id=parent_id;

Example of the result:

OPERATION OBJECT COST CARDINALITY BYTES
SELECT STATEMENT () 873 1000 20000
  TABLE ACCESS (BY INDEX ROWID) T 873 1000 20000
    INDEX (RANGE SCAN) T_GROUP2 5 1000

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 queries with different selectivity of predicate. We will restrict on one field so the selectivity of the predicate will be equal to the density.

Data

Create a table with five indexed fields. All fields have different number of unique values.

DROP TABLE t;
CREATE TABLE t (id INT, 
                group1 INT, 
                group2 INT, 
                group3 INT, 
                group4 INT);

BEGIN
  FOR id IN 1..1000000 LOOP
    INSERT INTO t VALUES (id, 
                          dbms_random.Value(1,100000), 
                          dbms_random.Value(1,1000), 
                          dbms_random.Value(1,100), 
                          dbms_random.Value(1,10));
  END LOOP;
END;
/

commit;

CREATE INDEX t_id ON t (id);
CREATE INDEX t_group1 ON t (group1);
CREATE INDEX t_group2 ON t (group2);
CREATE INDEX t_group3 ON t (group3);
CREATE INDEX t_group4 ON t (group4);

Example 1

Query with a restriction on id with unique values. Selectivity=1/1M (high)

SELECT * FROM t WHERE id = 1; 

Execution plan. Index used.

TABLE ACCESS (BY INDEX ROWID) of T #1 TABLE (Cost=4 Card=1 Bytes=65)
  INDEX (RANGE SCAN) of T_ID INDEX Optimizer=ANALYZED (Cost=3 Card=1 Bytes=)

Example 2

Query with a restriction on group1. Selectivity=1/100K (still high)

SELECT * FROM t WHERE group1 = 1;

Execution plan. Index used.

TABLE ACCESS (BY INDEX ROWID) of T #1 TABLE (Cost=6 Card=3 Bytes=195)
  INDEX (RANGE SCAN) of T_GROUP1 INDEX Optimizer=ANALYZED (Cost=6 Card=3 Bytes=)

Example 3

Query with a restriction on group2. Selectivity=1/1K

SELECT * FROM t WHERE group2 = 1;

Execution plan. Index used.

TABLE ACCESS (BY INDEX ROWID) of T #1 TABLE (Cost=348 Card=468 Bytes=30,420)
  INDEX (RANGE SCAN) of T_GROUP2 INDEX Optimizer=ANALYZED (Cost=3 Card=468 Bytes=)

Example 4

Query with a restriction on group3. Selectivity=1/100 (low)

SELECT * FROM t WHERE group3 = 1;

Execution plan. Index has NOT been used.

TABLE ACCESS (FULL) of T #1 TABLE (Cost=795 Card=6,425 Bytes=417,625)

Example 5 (hint)

Let’s compare with the plan of the same query but using hint forcing use of the corresponding index.

                       
SELECT /*+ INDEX(t t_group3) */ * FROM t WHERE group3 = 1;

The cost is 1,774 vs 795 i.e. 2.23 times larger.

TABLE ACCESS (BY INDEX ROWID) of T #1 TABLE (Cost=1,774 Card=6,425 Bytes=417,625)
  INDEX (RANGE SCAN) of T_GROUP3 INDEX Optimizer=ANALYZED (Cost=13 Card=6,425 Bytes=)

Example 6

Just to complete picture. Here is query without restriction:

SELECT * FROM t;

Execution plan:

TABLE ACCESS (FULL) of T #1 TABLE (Cost=792 Card=1,179,200 Bytes=76,648,000)