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 🙂
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.
DROP TABLE t; CREATE TABLE t ( x VARCHAR(20), y NUMERIC(20,2), z INT ); BEGIN FOR i IN 1..20000 LOOP INSERT INTO t VALUES ( dbms_random.string('L', 20), dbms_random.Value, dbms_random.value(1,1000) ); END LOOP; COMMIT; END; /
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';
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).
Add “-Duser.country=en -Duser.language=en” to java runtime options in Kettle batch files.
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 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;
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;
Suppose we need to compare two database schemas to find out changes such as new tables, columns, and change of data type.
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));
DELETE PLAN_TABLE; EXPLAIN PLAN FOR SELECT * FROM t WHERE group2=1; SELECT * FROM TABLE(dbms_xplan.display());
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
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 |
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.
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);
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=)
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=)
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=)
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)
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=)
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)
bukhantsov.org 