Categories
11gR2 sql

number series

Patrick Wolf wrote about the newest Apex release, which contains a 11.2 db engine, so I had to play with recursive queries 😉


with t(x) as (select 1 from dual
union all
select x+1 from t where x<5 ) select x from t; X 1 2 3 4 5 with t(x,y) as (select 1 x, 1 y from dual union all select x+1,y*(x+1) from t where x<5 ) select x,y "X!" from t; X X! 1 1 2 2 3 6 4 24 5 120 with t(r,x,y) as (select 1,1,1 from dual union all select r+1,y,x+y from t where r<5) select x fib from t FIB 1 1 2 3 5 with t1(x) as (select 1 from dual union all select x+1 from t1 where x<4), t2(x,y,z) as (select x, 1,x from t1 union all select x,y+1,x*(y+1) from t2 where y<2) select listagg(z,';') within group (order by x) s from t2 group by y; S 1;2;3;4 2;4;6;8

Categories
dba sql

where is the TRIGGER ANY TABLE privilege?

You have your table data in one schema and your procedures in another one. But can you have triggers and tables in different schemas?


SYS@lsc01> create user u1 identified by u1;

User created.

SYS@lsc01> create user u2 identified by u2;

User created.

SYS@lsc01> grant create table, unlimited tablespace to u1;

Grant succeeded.

SYS@lsc01> grant create session, create trigger to u2;

Grant succeeded.

SYS@lsc01> create table u1.t(x number);

Table created.

SYS@lsc01> grant select on u1.t to u2;

Grant succeeded.

SYS@lsc01> connect u2/u2
Connected.
U2@lsc01> create trigger u2.tr after insert on u1.t for each row
2 begin
3 null;
4 end;
5 /
create trigger u2.tr after insert on u1.t for each row
*
ERROR at line 1:
ORA-01031: insufficient privileges

What’s the missing privilege? To create a trigger on another schema, you need the CREATE ANY TRIGGER privilege.


U2@lsc01> connect / as sysdba
Connected.
SYS@lsc01> grant CREATE ANY TRIGGER to u2;

Grant succeeded.

SYS@lsc01>
SYS@lsc01> connect u2/u2
Connected.
U2@lsc01> create trigger u2.tr after insert on u1.t for each row
2 begin
3 null;
4 end;
5 /

Trigger created.

Categories
sql

where c not in (:b1, :b2, … , :b9999)

I do not like this kind of dynamic NOT IN clauses. It is better to have a temporary table, a bit like in

create global temporary table t(x number not null);
insert into t(x) values (:b1);
insert into t(x) values (:b2);
...
insert into t(x) values (:b9999);
select foo from bar where c not in (select x from t);

If you want to however do this in one query you can still use AND


SQL> select foo from bar where c not in (
:b1,
:b2,
...
:b9999);
*
ERROR at line 1002:
ORA-01795: maximum number of expressions in a list is 1000

SQL> select foo from bar where
c!=:b1 and
c!=:b2 and
...
c!=:b9999;

FOO
---
foo

Translate c NOT IN (exprlist) into c!=expr1 and c!=expr2…
Translate c IN (exprlist) into c=expr1 or c=expr2…

If you use dynamic expression list, this will bypass the ORA-01795 error

Categories
sql

on analytics and superaggregation

When I wrote my book, I did not expect having the advanced Oracle sql features available on other dabatase engine.

This week-end I downloaded for fun a try of db2, v9.7.
1) download db2 linux64bit trial on ibm.com
2) install enterprise edition (next-next-install principle)
3) create the instance
4) create the sample database (where database and instance means something different than in Oracle)
$ db2 create db test pagesize 32 k
$ db2 connect to test
$ db2 create user temporary tablespace temp
$ db2 connect reset

5) connect with clpplus, which looks similar to sqlplus, even cooler at first look
$ clpplus db2inst1/***@vaio:50001/TEST

I tried a few examples from my book. Left, full and cross join works, but not partitioned outer join.

Scalar, Inline and nested query works too.

Surprisely many function that I thought Oracle specific work too, like sysdate and to_char.

In aggegation, the KEEP FIRST/LAST does not work,
In Super aggregations, all GROUPING SETS, CUBE, ROLLUP do work.
No Pivot or Unpivot.

No dual, but a powerful TABLE function

SQL> select * from table(values(1),(2));
1
-----------
1
2

Analytics works, With range, rows, order by partition, all the fancy stuff.

I have not checked at XML, but it sounds to be differently implemented.
Some things will work however
SQL> select xmlquery('1 to 10') from dual;

1
--------------------
1 2 3 4 5 6 7 8 9 10

No CONNECT BY, but as in Oracle 11gR2, recursive query factoring does build the hierarchy

No Model, obviously.

Categories
sql

what is the type of NULL

I was a bit surprised to see a VARCHAR2(0) column in my schema


select column_name, data_type, data_length
from user_tab_columns
where table_name='V';
COLUMN_NAME DATA_TYPE DATA_LENGTH
--------------- -------------------- -----------
X VARCHAR2 0

What’s this datatype? It is the datatype of NULL !!!

SQL> create or replace view v as select null x from dual
View created.
SQL> desc v
VIEW v
Name Null? Type
----------------- -------- ------------
X VARCHAR2(0)

Categories
sql

How many decimals do you need?

Do you user NUMBER or NUMBER(p,s) for your datatypes?

I posted last year about 1!=1

Today I realized this could be solved with the scale 🙂

SQL> drop table lsc_t
Table dropped.
SQL> create table lsc_t(x number, y number(*,6))
Table created.
SQL> insert into lsc_t values (1/3*3,1/3*3)
1 row created.
SQL> commit
Commit complete.
SQL> select * from lsc_t where x=1
no rows selected.
SQL> select * from lsc_t where y=1

X Y
---------- ----------
1.00000000 1

So if you have amounts in US$ or GB£ or CHF, do not use NUMBER, use NUMBER(*,6) or NUMBER(*,2) or whatever is relevant to your business!

Categories
dba sql

.plz dump file

What are those .plz dump files in my user dump directory ?

-rw-r--r-- 1 oracle dba 15168 Oct 6 14:34 _anon__3ca8c5e38__AB.plz
-rw-r----- 1 oracle dba 15883 Oct 6 14:45 db01_ora_10061.trc
-rw-r--r-- 1 oracle dba 15168 Oct 6 14:45 _anon__3c929b088__AB.plz
-rw-r----- 1 oracle dba 15895 Oct 6 14:47 db01_ora_10666.trc
-rw-r--r-- 1 oracle dba 15168 Oct 6 14:47 _anon__3c8651198__AB.plz

let’s check one, briefly :

*** ASSERT at file pdw4.c, line 2080; Type 0xffffffff7d79fb40 has no MAP method.
Source Location = _anon__3d2474b28__AB[1, 7]

== Dump of OPT Context Object ffffffff7c519ec8. ==
Tue Oct 6 16:31:11 2009
Event 10944 = 0
plsp = ffffffff7fff67a8
lu = 3c9c18210
Diana root = 0x20014 = 131092
Diana proc = 0x20012 = 131090
Graph = ffffffff7d774d70
Dump file = /app/oracle/admin/DB01/udump/_anon__3d2474b28__AB.plz
CG = 0
...

It is a kind of dump file, apparently. I could not find details on metalink. I generate the one above in 10.2.0.4 sparc with the following code

create or replace type t1 as object (x number)
/
create or replace type t2 as table of t1
/
exec if t1(1)member of t2()then null;end if

BEGIN if t1(1)member of t2()then null;end if; END;
Error at line 10
ORA-06550: line 1, column 7:
PLS-00801: internal error [*** ASSERT at file pdw4.c, line 2080; Type 0xffffffff7d7ba280 has no MAP method.; _anon__3c929b088__AB[1, 7]]

Categories
11gR2 sql

stragg in 11gR2

This will be a killer in the Oracle forums 😉
LISTAGG (measure_expr [, 'delimiter_expr'])
WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

for instance
SELECT LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename) FROM emp;
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD

Categories
fun game sql

le compte est bon

I am back from my vacations, I was at nice places in Switzerland like Rhone Glacier, underground lake of Saint-Leonard, Salt Mines of Bex, Rhine Waterfalls and more …

To keep up with the fun, here is a little quiz :

You have the numbers 1-3-4-6 and you need to achieve the number 24. The allowed operations are +, -, * and /

If I try to achieve 49 it is easy :
SQL> /
Enter value for n1: 1
old 14: (SELECT &n1 n
new 14: (SELECT 1 n
Enter value for n2: 3
old 17: SELECT &n2 n
new 17: SELECT 3 n
Enter value for n3: 4
old 20: SELECT &n3 n
new 20: SELECT 4 n
Enter value for n4: 6
old 23: SELECT &n4 n
new 23: SELECT 6 n
Enter value for result: 49
old 143: ) = &result
new 143: ) = 49

result
------------------------------------
(4+3)*(6+1)
(3+4)*(6+1)
(6+1)*(4+3)
(1+6)*(4+3)
(6+1)*(3+4)
(1+6)*(3+4)
(4+3)*(1+6)
(3+4)*(1+6)

8 rows selected.

Elapsed: 00:00:11.28

But for 24 it is not that simple 🙂 at least for human !

Ok, in SQL I am using a plsql function to evaluate expression

CREATE OR REPLACE FUNCTION lsc_eval (expr VARCHAR2)
RETURN NUMBER
IS
x NUMBER;
BEGIN
EXECUTE IMMEDIATE 'begin :x := ' || expr || ';end;'
USING OUT x;
RETURN x;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
/

I will post the rest of the code as a comment later 😉

Categories
sql

CTAS and NOT NULL

When you create a table as select (CTAS), you lose a lot of information like grants, partitioning, organization, referential integrity, check constraints. But the NOT NULL constraints remain. Sometimes

Let’s see when the not null constraints are not copied to the new table.

Here is the test case :
create table lsc_t1(
c0 number constraint C_PRIMARY_KEY primary key,
c1 number constraint C_DEFERRABLE not null deferrable,
c2 number constraint C_NOVALIDATE not null novalidate,
c3 number constraint C_DISABLE not null disable,
c4 number constraint C_DISABLE_VALIDATE not null disable validate,
c5 number constraint C_NOT_NULL not null
);
create table lsc_t2 as select * from lsc_t1;

let’s describe the tables :
SQL> desc lsc_t1
Name Null? Type
----------------------------- -------- --------------------
C0 NOT NULL NUMBER
C1 NUMBER
C2 NUMBER
C3 NUMBER
C4 NOT NULL NUMBER
C5 NOT NULL NUMBER

SQL> desc lsc_t2
Name Null? Type
----------------------------- -------- --------------------
C0 NUMBER
C1 NUMBER
C2 NUMBER
C3 NUMBER
C4 NOT NULL NUMBER
C5 NOT NULL NUMBER

The NOT NULL of c0 has been lost. C0 is the primary key, and the primary key is not transferred to the target table. We can see the non-deferrable validated not-null constraints c4 and c5.

Let’s compare all the constraints :
select * from user_constraints where table_name in ('LSC_T1','LSC_T2');

  LSC_T1 LSC_T2
C0 PRIMARY KEY

 
C1 DEFERRABLE

 
C2 ENABLED NOT VALIDATED

 
C3 DISABLED NOT VALIDATED

 
C4 DISABLED VALIDATED

ENABLED VALIDATED
C5 ENABLED VALIDATED

ENABLED VALIDATED

The deferrable and not-validated check constraints are lost. The regular ENABLE VALIDATE constraint is ok, but the DISABLE VALIDATE constraint is now enabled.

Categories
dba sql

How to tune WHERE NAME LIKE ‘%ABC%’

More than once customers wanted me to tune queries where the LIKE clause made the query very slow…

The easy answer is : you cannot. If you want to search for some characters in the middle of the string, Oracle will not use an index.

Is it a correct answer? Maybe not.

Let’s imagine my application allow you to search for a string in the middle of the name, but to avoid scanning too large amount of data, the application enforces the string to be at least 3 characters long. For example ABC.

In this case, instead of doing a full table scan of the table to retrieve only a few rows, we can use an hint to tell Oracle to use an index on the name:

CREATE TABLE lsc_t AS
SELECT
ROWNUM ID,
SUBSTR(DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
|| ' '
|| DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
,1,17) NAME,
TRUNC(SYSDATE-ABS(10000*DBMS_RANDOM.NORMAL)) birthdate,
LPAD('X',4000,'X') address
FROM DUAL
CONNECT BY LEVEL <= 1e5; ALTER TABLE lsc_t ADD PRIMARY KEY(ID); CREATE INDEX lsc_i ON lsc_t(NAME); EXEC dbms_stats.gather_table_stats(user,'LSC_T',cascade=>true)

let’s measure the time for a full table scan
SQL> set timi on
SQL> SELECT ID, NAME, birthdate
FROM lsc_t WHERE NAME LIKE '%ABC%';

ID NAME BIRTHDATE
--------- ----------------- ---------
60249 ABCBIFAB KRKBCRN 11-MAR-90
16714 AF YABCG 09-OCT-95
55571 BABCIQ GESGLW 27-MAR-50
77561 BP GABC 24-APR-90
80027 DALSABC TZLOAWDV 05-NOV-01
49817 EABCTFIY XWB 10-FEB-88
23283 EMMOGGBF DABCB 20-DEC-87
39530 FMABCKB AB 18-SEP-87
68605 FTPGOHE ABCC 28-SEP-28
74615 KIFDWABC CSSUQ 08-AUG-82
31772 KNOABCT BO 08-SEP-77
68730 KRYIEN LMABC 10-APR-07
43317 LUFJKZJT AUABCZR 19-DEC-88
76851 MZABC TEIFG 14-SEP-92
54589 NXE YABCDX 03-MAY-88
6940 OIWABCZ DLFFXY 29-MAR-88
59070 ONIB ADGABCI 29-JUL-07
27264 PGHOABC ZY 05-OCT-90
38157 QABC OPZHE 13-JUN-87
17511 QPDKD CIABCJ 08-AUG-69
25507 RX OWULOABC 24-FEB-92
62159 SEABC DAILK 25-JUN-02
3845 SK CCABCG 22-JAN-80
50059 SPABC BVHRHW 18-MAR-86
54700 UABCPC WUHAJS 28-OCT-71
70207 UKY OIDUABC 23-APR-88
39484 WABC TJLYHVJZ 14-MAR-78
14561 WDRWABC XZKDH 29-MAR-86
61501 YBYU RYABCGI 28-JUN-78
30578 YEWENGX ABCHARA 12-SEP-67
35397 YHBEABC HFKO 25-AUG-85
26450 YOABCVG HJT 23-DEC-98
87224 ZKNLNY YAABC 13-NOV-61

33 rows selected.

Elapsed: 00:00:02.56

about 3 seconds for retrieving 33 rows out of 100000

let’s try with an index

SQL> SELECT /*+INDEX(LSC_T,LSC_I)*/ ID, NAME, birthdate
FROM lsc_t WHERE NAME LIKE '%ABC%';

ID NAME BIRTHDATE
--------- ----------------- ---------
60249 ABCBIFAB KRKBCRN 11-MAR-90
16714 AF YABCG 09-OCT-95
55571 BABCIQ GESGLW 27-MAR-50
77561 BP GABC 24-APR-90
80027 DALSABC TZLOAWDV 05-NOV-01
49817 EABCTFIY XWB 10-FEB-88
23283 EMMOGGBF DABCB 20-DEC-87
39530 FMABCKB AB 18-SEP-87
68605 FTPGOHE ABCC 28-SEP-28
74615 KIFDWABC CSSUQ 08-AUG-82
31772 KNOABCT BO 08-SEP-77
68730 KRYIEN LMABC 10-APR-07
43317 LUFJKZJT AUABCZR 19-DEC-88
76851 MZABC TEIFG 14-SEP-92
54589 NXE YABCDX 03-MAY-88
6940 OIWABCZ DLFFXY 29-MAR-88
59070 ONIB ADGABCI 29-JUL-07
27264 PGHOABC ZY 05-OCT-90
38157 QABC OPZHE 13-JUN-87
17511 QPDKD CIABCJ 08-AUG-69
25507 RX OWULOABC 24-FEB-92
62159 SEABC DAILK 25-JUN-02
3845 SK CCABCG 22-JAN-80
50059 SPABC BVHRHW 18-MAR-86
54700 UABCPC WUHAJS 28-OCT-71
70207 UKY OIDUABC 23-APR-88
39484 WABC TJLYHVJZ 14-MAR-78
14561 WDRWABC XZKDH 29-MAR-86
61501 YBYU RYABCGI 28-JUN-78
30578 YEWENGX ABCHARA 12-SEP-67
35397 YHBEABC HFKO 25-AUG-85
26450 YOABCVG HJT 23-DEC-98
87224 ZKNLNY YAABC 13-NOV-61

33 rows selected.

Elapsed: 00:00:00.06

Much better 🙂

reposted due to % in url

Categories
dba sql

on recycle bin

more than one user may wondered who created those BIN$ when they first connected to a 10g database.


create table lsc_t(x number)
partition by range(x)
(partition LESS_THAN_ONE values less than (1));

drop table lsc_t;

select object_name, subobject_name, created
from user_objects
where object_name like 'BIN$%';
OBJECT_NAME SUBOBJECT_NAME CREATED
------------------------------ --------------- ---------
BIN$bh2VJ6FqFJ3gRAAUT+rFpg==$0 LESS_THAN_ONE 07-JUL-09

Ok, it is quite easy to get rid of it. Either at DROP time with a DROP TABLE LSC_T PURGE or later with PURGE RECYCLEBIN. Most of the objects disappear from USER_OBJECTS when dropped actually. The recyclebin view is called : RECYCLEBIN.


purge recyclebin;

select object_name, subobject_name, created
from user_objects
where object_name like 'BIN$%';

no rows selected.

select * from recyclebin;

no rows selected.

So far so good…

Let’s see what’s happening with my primary keys

purge recyclebin;
create table lsc_t(x number constraint lsc_t_pk primary key);
drop table lsc_t;
select object_name, original_name, type from recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ ------------- -----
BIN$bh23ggtBHALgRAAUT+rFpg==$0 LSC_T TABLE
BIN$bh23ggtAHALgRAAUT+rFpg==$0 LSC_T_PK INDEX

The primary key index is now in the recycle bin as well.

let’s recover the recycle bin version :

flashback table lsc_t to before drop;
select index_name from user_indexes where table_name='LSC_T';

INDEX_NAME
------------------------------
BIN$bh3GgNi1HR3gRAAUT+rFpg==$0

select object_name, original_name, type from recyclebin;

no rows selected.

👿

So if you cannot exclude BIN$ objects from your dba maintenance scripts, you will need to deal with thoses as they may be recovered indexes!

Categories
11g sql xml

select from comma-separated list

This is asked over and over in the forums, but why not proposing an 11g solution here 😉

create table t(description varchar2(12) primary key,
numbers varchar2(4000));
insert into t(description, numbers) values ('PRIME','2,3,5,7');
insert into t(description, numbers) values ('ODD','1,3,5,7,9');
commit;

DESCRIPTION NUMBERS
PRIME 2,3,5,7
ODD 1,3,5,7,9

Now I want to unpivot numbers in rows


select description,(column_value).getnumberval()
from t,xmltable(numbers)

DESCRIPTION (COLUMN_VALUE).GETNUMBERVAL()
PRIME 2
PRIME 3
PRIME 5
PRIME 7
ODD 1
ODD 3
ODD 5
ODD 7
ODD 9

It is that simple 🙂

Works also with strings :

select (column_value).getstringval()
from xmltable('"a","b","c"');

(COLUMN_VALUE).GETSTRINGVAL()
a
b
c
Categories
sql

to_number(to_char(:n))

Is it safe to do a TO_NUMBER(TO_CHAR(:n)) ?

var n number
exec :n := 9.9999999999999999999999999999999999e125
select to_number(to_char(:n)) from dual;
ORA-01722: invalid number

Why?
SQL Reference
If a positive NUMBER value is extremely large and cannot be represented in the specified format, then the infinity sign (~) replaces the value

select to_char(:n) from dual;
TO_CHAR(:N)
--------------
~

I wrote more about infinity here

Categories
sql

committing transaction

When and how do you commit a transaction?
SQL> insert into lsc_t(x) values(1); ... (1) not committed
SQL> commit; ... (1) committed

SQL> insert into lsc_t(x) values(2); ... (2) not committed
SQL> alter table lsc_t disable primary key; ... (2) committed

SQL> set autocommit ON
SQL> insert into lsc_t(x) values(3); ... (3) committed
SQL> set autocommit OFF

SQL> insert into lsc_t(x) values(4); ... (4) not committed
SQL> disc ... (4) committed

Row 1 is committed after the commit keyword.

Row 2 is implicitely committed after before the alter table ddl statement.

Row 3 is autocommitted. Autocommit exists in sqlplus, but is more often seen in stateless applications (for instance a web application).

Row 4 is committed after a successfull disconnection.

But what appends if you kill your current session?


SQL> insert into lsc_t(x) values (5);

1 row created.

SQL> !ps
PID TTY TIME CMD
13903 pts/33 0:00 ksh
22384 pts/33 0:00 sqlplus

SQL> !kill 22384
Terminated

The row 5 is not commited

Ref: Overview of Transaction Management

A transaction ends when any of the following occurs:
– A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
– A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER (*).
If the current transaction contains any DML statements, Oracle Database
first commits the transaction, and then runs and commits the DDL statement
as a new, single statement transaction.
– A user disconnects from Oracle Database. The current transaction is committed.
– A user process terminates abnormally. The current transaction is rolled back.

(*) but not ALTER SESSION

Please read On Implicit Commit

Categories
sql

CONNECT BY NOCYCLE

the idea of this post was largely inspired by nuke_y on developpez.net (in French)

I will try to rephrase it in a hierarchical way.

Let’s take all managers of Adams in EMP :
SELECT empno, ename, mgr
FROM emp
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
EMPNO ENAME MGR
---------- ---------- ----------
7876 ADAMS 7788
7788 SCOTT 7566
7566 JONES 7839
7839 KING

So far so good. Let’s imagine SCOTT has two managers, JONES and PAUL.

create table lsc_t AS
(SELECT ename, empno, mgr
FROM emp
UNION ALL
SELECT 'SCOTT', 7788, 9999
FROM DUAL
UNION ALL
SELECT 'PAUL', 9999, NULL
FROM DUAL);
SELECT empno, ename, mgr
FROM lsc_t
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
EMPNO ENAME MGR
---------- ---------- ----------
7876 ADAMS 7788
7788 SCOTT 7566
7566 JONES 7839
7839 KING
7788 SCOTT 9999
9999 PAUL

EMP is not supposed to allow this (primary key on empno), so I created a table LSC_T.

So far still fine, Paul is in my list of managers.

Let’s imagine Scott is the manager of Paul. In 9i and below, this would result in an ORA-0146 CONNECT BY loop in user data, but in 10g, and according to the doc The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY loop exists in the data.

Should I try ???

UPDATE lsc_t
SET mgr = 7788
WHERE ename = 'PAUL';
COMMIT ;
SELECT empno, ename, mgr
FROM lsc_t
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
EMPNO ENAME MGR
---------- ---------- ----------
7876 ADAMS 7788
7788 SCOTT 7566
7566 JONES 7839
7839 KING
7788 SCOTT 9999

Due to a loop in user data, PAUL is no longer returned. I have no explanation so far.

Categories
sql

Eastern challenge

I just wanted to give to my readers a bit fun for Eastern…

Using the well known EMP table, retrieve the name of the job that is the less common…

The solution must be a one-liner SELECT statement (max 80 chars) 🙂

Have fun!

Categories
sql

not a group by expression?

How do I count all objects in the current schema?


SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 1 09:58:46 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle 12g Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Expensive option

SQL> select
2 user,
3 count(*)
4 from
5 user_objects
6 where
7 status='INVALID';
user,
*
ERROR at line 2:
ORA-00937: not a single-group group function

Disconnected from Oracle 12g Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Expensive option

It does not seem to work … What’s wrong?

Categories
sql

locking and database link

Last week-end a new release went to production. This week we got plenty of ORA-2049. What in the hell went wrong? And why did not we find out this locking before?

We just did not have that concurrency effect in test.

How does locking work ?

With regular tables:
session 1
select * from lsc_t for update;
session 2
update lsc_t set id=3;

Session 2 waits until transaction in session 1 finishes.

But in case the update is using a subquery that access a remote table, the update will fail after 1 minute

session 1
select * from lsc_t for update;
session 2
update lsc_t set x=(select * from lsc_t@db02);
Session 2 will end with ORA-02049: timeout: distributed transaction waiting for lock

The timeout of one minute is defined by distributed_lock_timeout, and it is not modifiable in your session (chances are, you will not be able to modify this parameter).

Categories
sql

1!=1

In the process of validating some calculation, I noticed some strange differences from one run to another run, with exactly the same data and exactly the same function.

I suppose the only difference was the order in which the transactions were processed (and since I had no ORDER BY, the order was not deterministic).

To limit it to the minimum, I noticed that, in Oracle, 1/3*3 is not 1 !

SQL> select * from dual where 1/3*3=1
no rows selected.

Let me tell you, this was unexpected. Ok, on my first pocket calculator 30 years ago, I noticed than 1/3=.3333333 and .3333333*3=.9999999. But since then, I used to expect 1.

perl :

$ perl -e 'print 1 if 1/3*3==1'
1

OK, I tried in java

public class X {
public static void main(String[] args){
if (1/3*3<1) { System.out.println("<\n"); } } } $ javac X.java $ java X <

In java it seems different too.

So what is 1/3*3 in Oracle?

SQL> set numw 50
SQL> select 1/3*3 from dual;

1/3*3
--------------------------------------------------
.9999999999999999999999999999999999999999

Surprised?

Categories
dba sql

package version control

Oracle does not offer any kind of version control like CVS or subversion in the database. CVS and subversion could be used in the filesystem, then the code could be deployed with sqlplus.

To quickly compare packages in Test and Production I used :


select
env,name,type,line,text
from (
select
env,name,type,line,text,
count(distinct text) over(partition by name,type,line) c
from (
select
'TEST' env,name,type,line,text
from
user_source
union all
select
'PROD' env,name,type,line,text
from
user_source@prod
)
)
where c>1 ;

where “PROD” is a database link to production database. My security friends will scream to hear I have a database link to production in my test schema, but you are free to do it the other way round :mrgreen:

I also explained TOAD method in how to compare schema a few months ago.

Categories
dba sql

commit suicide;

Tanel Poder directed me to Miladin Modrakovic blog, I will definitely add this to my prefered feed.

You have to try this :

SQL> alter session set events 'immediate crash';
alter session set events 'immediate crash'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

A very decent method for killing yourself 😈

Categories
sql

ORA-2070

Today I received an error which was totally new to me. And totally unexpected…

For the purpose of this post, I reduced it to a minimum
CREATE TABLE LSC_T1
( x number);
CREATE TABLE LSC_T2
( x number);
with c as (select nls_charset_decl_len(1, x)
from lsc_t1 join lsc_t2
using (x))
select 1 from c
union
select 1 from c;

Error at line 1
ORA-02070: database does not support operator NLS_CHARSET_DECL_LEN in this context

My basic query was actually joining two dictionary views, and NLS_CHARSET_DECL_LEN is used in USER_TAB_COLUMNS.CHAR_COL_DECL_LENGTH.
Kind of
with c as (
select table_name, column_name, constraint_name
from user_tab_columns
join user_constraints
using (table_name)
where constraint_type='P'
)
select
constraint_name,column_name
from c
union all
select
column_name,constraint_name
from c;
Error at line 1
ORA-02070: database does not support operator NLS_CHARSET_DECL_LEN in this context

I spent some time to understand the reason for getting an ORA-2070 in a simple query that did seem to be totally unrelated to charset. The database used for this test is 9.2.0.8/Solaris.

It is quite easy to avoid the bug, for instance by not using the ANSI join 😉

When analyzing such a defect, my approach is to simplify the query as much as possible. Very often on forums you see users posting queries of more than 100 lines. Those users just expect the community to debug their code. Posting a tiny reproducible test case is key to receive a proper solution!

Categories
sql

dynamic database link

How do I select thru a database link, where the database link is not fixed?

SQL> var db_link varchar2(255);
SQL> exec :db_link := 'DB02';
SQL> select x from LSC_T@:db_link where x>0;
select x from LSC_T@:db_link where x>0;
*
ERROR at line 1:
ORA-01729: database link name expected

My solution using dynamic SQL
Remote databases DB02

create table lsc_t(x number);
insert into lsc_t(x) values (2);
commit;

Remote databases DB03

create table lsc_t(x number);
insert into lsc_t(x) values (3);
commit;

Local database DB01
create or replace type lsc_t_o as object(x number);
/
create or replace type lsc_t_c as table of lsc_t_o;
/
create or replace function lsc_f(str varchar2)
return lsc_t_c is
rc lsc_t_c;
begin
execute immediate
'select lsc_t_o(x) from '
|| dbms_assert.qualified_sql_name(str) bulk collect into rc;
return rc;
end;
/

Note the DBMS_ASSERT function. The DBMS_ASSERT just enhances the security by prevent SQL injection

Ok, let’s try
SQL> var db_link varchar2(255);
SQL> exec :db_link := 'DB02';
SQL> select x from table(lsc_f('LSC_T@'
||:db_link)) where x>0;
X
----------
2
SQL> exec :db_link := 'DB03';
SQL> select x from table(lsc_f('LSC_T@'||:db_link)) where x>0;
X
----------
3

Seems to work 🙂

Categories
sql

date or timestamp literals

Yesterday I commented on a post on the French developer forums http://www.developpez.net about using DATE and TIMESTAMP literals.

A date literal is DATE '2000-01-01'. It is not possible to have a date literal at 6am, like DATE '2000-01-01 06:00:00', the syntax simply does not exist. You can either use TO_DATE function, or if you are really hooked on litterals, maybe DATE '2000-01-01'+INTERVAL '+0 06:00:00' DAY TO SECOND.

One suggestion was to use TIMESTAMP, because TIMESTAMP literals do have time in it.

However, comparing a date with a timestamp will result in an implicit conversion to timestamp, and if the date colum is indexed, the index will not be used.

SQL> create table lsc_t(x number, d date)
Table created.
SQL> create index lsc_i on lsc_t(d)
Index created.
SQL> insert into lsc_t(d) values (to_date('2000-01-01 06:00:00',
'YYYY-MM-DD HH24:MI:SS'))
1 row created.
SQL> select * from lsc_t where d>=timestamp '2000-01-01 06:00:00'
X D
---------- -------------------
2000-01-01 06:00:00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=CHOOSE
1 0 TABLE ACCESS FULL TGPOWNER.LSC_T
SQL> select * from lsc_t where d>=to_date('2000-01-01 06:00:00',
'YYYY-MM-DD HH24:MI:SS')
X D
---------- -------------------
2000-01-01 06:00:00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=CHOOSE
1 0 TABLE ACCESS BY INDEX ROWID TGPOWNER.LSC_T
2 1 INDEX RANGE SCAN TGPOWNER.LSC_I

As shown above, the index is not used when comparing to timestamp.

Why does Oracle convert the date to timestamp ? Because timestamp may contain nanoseconds.
SQL> select * from lsc_t where
d>=timestamp '2000-01-01 06:00:00.000000001';

no rows selected

SQL> select * from lsc_t where
cast(d as timestamp)>=timestamp '2000-01-01 06:00:00.000000001';

no rows selected

SQL> select * from lsc_t where
d>=cast(timestamp '2000-01-01 06:00:00.000000001' as date);

X D
---------- -------------------
2000-01-01 06:00:00

Converting to date would deliver wrong result.

Categories
sql Uncategorized

Tuning query over database link

I just learnt from a colleague a very useful hint for remote databases. Over a db link, Oracle does not have the necessary statistics to take the right decision.

Here is the test case :
DB01

create database link lsc_link_2 using 'DB02';
create table lsc_small_1 as
select rownum x from dual connect by level<100; alter table lsc_small_1 add primary key(x); create table lsc_big_1 as select rownum x from dual connect by level<1000000; alter table lsc_big_1 add primary key(x);

DB02

create table lsc_small_2 as
select rownum x from dual connect by level<100; alter table lsc_small_2 add primary key(x); create table lsc_big_2 as select rownum x from dual connect by level<1000000; alter table lsc_big_2 add primary key(x);

and here are the two test queries (run on DB01)

select count(*) from lsc_big_1 b1
join lsc_small_2@lsc_link_2 s2 using (x);
COUNT(*)
----------
99
Elapsed: 00:00:00.10

select count(*) from lsc_big_2@lsc_link_2 b2
join lsc_small_1 s1 using (x);
COUNT(*)
----------
99
Elapsed: 00:00:10.31

As shown above, first one is 100x faster. Anything to tune? For sure!

The trick is to execute the join remotely when the remote table is much larger than the local one!

select /*+ DRIVING_SITE(b2) */ count(*)
from lsc_big_2@lsc_link_2 b2
join lsc_small_1 s1 using (x);
COUNT(*)
----------
99
Elapsed: 00:00:00.06

Ref: the DRIVING_SITE hint

Categories
dba security sql

track DDL changes

Why is my package being invalidated? This is the question I asked myself a few times those days. In order to find out what is happening in my schema, I created this simple DDL trigger which tracks all DDL changes in my schema.


CREATE TABLE AUDIT_DDL (
d date,
OSUSER varchar2(255),
CURRENT_USER varchar2(255),
HOST varchar2(255),
TERMINAL varchar2(255),
owner varchar2(30),
type varchar2(30),
name varchar2(30),
sysevent varchar2(30),
sql_txt varchar2(4000));

create or replace trigger audit_ddl_trg after ddl on schema
declare
sql_text ora_name_list_t;
stmt VARCHAR2(4000) := '';
n number;
begin
if (ora_sysevent='TRUNCATE')
then
null;
else
n:=ora_sql_txt(sql_text);
for i in 1..n
loop
stmt:=substr(stmt||sql_text(i),1,4000);
end loop;
insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent,sql_txt)
values(
sysdate,
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','CURRENT_USER') ,
sys_context('USERENV','HOST') ,
sys_context('USERENV','TERMINAL') ,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent,
stmt
);
end if;
end;
/

Thanks Yas for your comment, I added the SQL text

Categories
dba fun sql xml

high cost

What’s wrong with this query ?


select
(t6.column_value).getstringval() t6
from
table(xmlsequence(extract(xmltype(‘<x/>’),’/x’))) t1,
table(xmlsequence(t1.column_value))t2,
table(xmlsequence(t2.column_value))t3,
table(xmlsequence(t3.column_value))t4,
table(xmlsequence(t4.column_value))t5,
table(xmlsequence(t5.column_value))t6;
T6
————————
<x/>

Elapsed: 00:00:00.01

Well, let’s check the plan :

--------------------------------------------------------------------
| Id | Operation | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | 18E| 15E| 18E (0)|999:59:59 |
| 1 | NESTED LOOPS | 18E| 15E| 18E (0)|999:59:59 |
| 2 | NESTED LOOPS | 18E| 15E| 99P (3)|999:59:59 |
| 3 | NESTED LOOPS | 4451T| 31P| 12T (3)|999:59:59 |
| 4 | NESTED LOOPS | 544G| 3045G| 1490M (3)|999:59:59 |
| 5 | NESTED LOOPS | 66M| 254M| 182K (3)| 00:36:31 |
| 6 | COLLECTION I | | | | |
| 7 | COLLECTION I | | | | |
| 8 | COLLECTION IT | | | | |
| 9 | COLLECTION ITE | | | | |
| 10 | COLLECTION ITER | | | | |
| 11 | COLLECTION ITERA | | | | |
--------------------------------------------------------------------

It is returning 18 quadrillions of rows, 15 exabytes, the cost is 1.8E19 and the time is about one month :mrgreen:

Categories
sql

How to tune something doing nothing?

To end this year in glory, I tried to replace a row-level trigger (LSC_TR1) into a statement level trigger (LSC_TR2)


SQL> create global temporary table lsc_t1(x number primary key)
2 on commit delete rows;
Table created.
SQL> create table lsc_t2(x number primary key);
Table created.
SQL> create table lsc_t3(x number primary key);
Table created.
SQL> create or replace function f return number deterministic is
2 begin sys.dbms_lock.sleep(1); return 10; end;
3 /
Function created.
SQL> create or replace trigger lsc_tr1 after insert on lsc_t1 for each row
2 begin
3 if (:new.x > f)
4 then
5 insert into lsc_t2(x) values (:new.x);
6 end if;
7 end;
8 /
Trigger created.
SQL> create or replace trigger lsc_tr2 after insert on lsc_t1
2 begin
3 insert into lsc_t2(x) select x from lsc_t1 where x > f;
4 end;
5 /
Trigger created.
SQL> insert into lsc_t3 values(1);
1 row created.
SQL> insert into lsc_t3 values(5);
1 row created.
SQL> insert into lsc_t3 values(20);
1 row created.
SQL> insert into lsc_t3 values(50);
1 row created.
SQL> insert into lsc_t3 values(100);
1 row created.
SQL> insert into lsc_t3 values(200);
1 row created.
SQL> insert into lsc_t3 values(1000);
1 row created.
SQL> commit;
Commit complete.
SQL> alter trigger lsc_tr1 enable;
Trigger altered.
SQL> alter trigger lsc_tr2 disable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
7 rows created.
Elapsed: 00:00:07.05

The current trigger LSC_TR1 is very slow, it is doing some processing after each row. Since it is a temporary staging table, it makes no sense to process each row, I have written the trigger LSC_TR2 to boost the performance.

Have a look.


SQL> truncate table lsc_t2;
Table truncated.
SQL> alter trigger lsc_tr1 disable;
Trigger altered.
SQL> alter trigger lsc_tr2 enable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
7 rows created.
Elapsed: 00:00:01.03

Much faster ! LSC_TR1 just takes ages. What a glorious year 2008 🙂

Well, the table LSC_T3 is empty most of the time, and the insert is running again and again…


SQL> truncate table lsc_t3;
Table truncated.
SQL> alter trigger lsc_tr1 enable;
Trigger altered.
SQL> alter trigger lsc_tr2 disable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
0 rows created.
Elapsed: 00:00:00.01
SQL> alter trigger lsc_tr1 disable;
Trigger altered.
SQL> alter trigger lsc_tr2 enable;
Trigger altered.
SQL> insert into lsc_t1(x) select x from lsc_t3;
0 rows created.
Elapsed: 00:00:01.03

Suddenly my tuning exercice turned to be a disaster, in this example it is about 63 times slower 🙁

I could possibly count the rows in the LSC_TR2 trigger, but then it will still be a bit slower when processing very few rows (in this example with 1 row).

So I will keep this AFTER EACH ROW trigger until next year 😉

Categories
sql xml

old-fashion listagg

Yesterday I had my first session about XML, today I have one about SQL Model

Ok, it was the first time I spoke about XML so I did not really now where to focus. XML is so big, you have XQUERY, XPATH, dozens of XML functions in the database.

One of the XML function is called XMLTRANSFORM and transforms XML according to XSLT

I had a fun demo about XSLT to create a semi-column separated list :

select
deptno,
xmltransform
(
sys_xmlagg
(
sys_xmlgen(ename)
),
xmltype
(
'


;


'
)
).getstringval() listagg
from emp
group by deptno;


DEPTNO LISTAGG
------- --------------------------------------
10 CLARK;KING;MILLER;
20 SMITH;FORD;ADAMS;SCOTT;JONES;
30 ALLEN;BLAKE;MARTIN;TURNER;JAMES;WARD;

Categories
sql

HOT


CREATE TABLE T(X NUMBER PRIMARY KEY) ORGANIZATION HEAP;

I have read the SQL reference more than once -believe me- but I did not notice this syntax until today… an HOT table is a simple table and this clause is optional. An IOT, or INDEX ORGANIZED TABLE, is a table where you mostly select thru an index. Good candidates for IOT are tables where you mostly access rows thru a full index scan.

Compare :


SQL> CREATE TABLE LSC_SELECTED_EMPLOYEES (EMPNO NUMBER PRIMARY KEY)
2 ORGANIZATION HEAP;

Table created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES(EMPNO) VALUES (7839);

1 row created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES(EMPNO) VALUES (7788);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> set autot on exp
SQL> select empno, ename from LSC_EMP
2 join LSC_SELECTED_EMPLOYEES using (EMPNO);

EMPNO ENAME
---------- ----------
7839 KING
7788 SCOTT

Execution Plan
----------------------------------------------------------
Plan hash value: 609992009

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 46 | 3 (0)| 0
| 1 | NESTED LOOPS | | 2 | 46 | 3 (0)| 0
| 2 | TABLE ACCESS FULL| LSC_EMP | 14 | 140 | 3 (0)| 0
|* 3 | INDEX UNIQUE SCAN| SYS_C0010139 | 1 | 13 | 0 (0)| 0
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("LSC_EMP"."EMPNO"="LSC_SELECTED_EMPLOYEES"."EMPNO")

Note
-----
- dynamic sampling used for this statement

SQL> set autot off
SQL> select segment_name,segment_type from user_segments
2 where segment_name in (
3 select object_name from user_objects t where t.CREATED >
4 sysdate-1/24 and segment_name not like 'BIN$%');

SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------
LSC_SELECTED_EMPLOYEES TABLE
SYS_C0010139 INDEX

SQL> drop table LSC_SELECTED_EMPLOYEES;

Table dropped.

with

SQL> CREATE TABLE LSC_SELECTED_EMPLOYEES2 (EMPNO NUMBER PRIMARY KEY)
2 ORGANIZATION INDEX;

Table created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES2(EMPNO) VALUES (7839);

1 row created.

SQL> INSERT INTO LSC_SELECTED_EMPLOYEES2(EMPNO) VALUES (7788);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> set autot on exp
SQL> select empno, ename from LSC_EMP
2 join LSC_SELECTED_EMPLOYEES2 using (EMPNO);

EMPNO ENAME
---------- ----------
7839 KING
7788 SCOTT

Execution Plan
----------------------------------------------------------
Plan hash value: 3539129569

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 46 | 3 (
| 1 | NESTED LOOPS | | 2 | 46 | 3 (
| 2 | TABLE ACCESS FULL| LSC_EMP | 14 | 140 | 3 (
|* 3 | INDEX UNIQUE SCAN| SYS_IOT_TOP_23608 | 1 | 13 | 0 (
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("LSC_EMP"."EMPNO"="LSC_SELECTED_EMPLOYEES2"."EMPNO")

Note
-----
- dynamic sampling used for this statement

SQL> set autot off
SQL> select segment_name,segment_type from user_segments
2 where segment_name in (
3 select object_name from user_objects t where t.CREATED >
4 sysdate-1/24 and segment_name not like 'BIN$%');

SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------
SYS_IOT_TOP_23608 INDEX

SQL> drop table LSC_SELECTED_EMPLOYEES2;

Table dropped.

The plan is the same, in IOT, you have only one segment, the INDEX, and in HOT, you have two segments, the INDEX and the TABLE.

Surely a good candidate for IOT 🙂