Category Archives: 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.

:evil:

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!

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

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

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

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.

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?

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

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?

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.

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!

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

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.

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

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

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:

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

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
     (
       '<?xml version="1.0"?><xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
          <xsl:template match="/">
            <xsl:for-each select="/ROWSET/ENAME">
              <xsl:value-of select="text()"/>;</xsl:for-each>
          </xsl:template>
        </xsl:stylesheet>'
     )
  ).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;

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

select in HTML format, XQUERY variant

Same as Select in html format, but with XMLTABLE instead of XMLTRANSFORM



select 
  xmlroot(
    xmlelement(
      "table",
      xmlconcat(
        xmltype(
'<tr><th>DEPTNO</th><th>DNAME</th><th>LOC</th></tr>'),
        xmlagg(column_value)
      )
    ),version '1.0'
  )
from 
  xmltable('
    for $f in ora:view("LSC_DEPT") 
    return 
      <tr>
        <td>{$f/ROW/DEPTNO/text()}</td> 
        <td>{$f/ROW/DNAME/text()}</td> 
        <td>{$f/ROW/LOC/text()}</td>
      </tr>');

XMLROOT(XMLELEMENT("TABLE",XMLCONCAT(XMLTYPE('<TR>
--------------------------------------------------
<?xml version="1.0"?>                             
<table>                                           
  <tr>                                            
    <th>DEPTNO</th>                               
    <th>DNAME</th>                                
    <th>LOC</th>                                  
  </tr>                                           
  <tr>                                            
    <td>10</td>                                   
    <td>ACCOUNTING</td>                           
    <td>NEW YORK</td>                             
  </tr>                                           
  <tr>                                            
    <td>20</td>                                   
    <td>RESEARCH</td>                             
    <td>DALLAS</td>                               
  </tr>                                           
  <tr>                                            
    <td>30</td>                                   
    <td>SALES</td>                                
    <td>CHICAGO</td>                              
  </tr>                                           
  <tr>                                            
    <td>40</td>                                   
    <td>OPERATIONS</td>                           
    <td>BOSTON</td>                               
  </tr>                                           
</table>                                          

select in HTML format

Last Wednesday I selected data from an HTML table : select from xml

Today, let’s try the opposite, generate an HTML table from a SQL query


select 
  XMLSERIALIZE(
    DOCUMENT
    XMLROOT(
      XMLTRANSFORM(
        XMLTYPE(
          CURSOR(
           SELECT * FROM DEPT
          )
        ),
        XMLTYPE.CREATEXML(
      '<?xml version="1.0"?>
<xsl:stylesheet version="1.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
<xsl:template match="/">
  <html>
    <head>
      <title>Table DEPT</title>
    </head>
    <body>
    <p>Table DEPT in HTML format</p>
    <table border="1">
      <tr>
        <th align="right">DEPTNO</th>
        <th align="left">DNAME</th>
        <th align="left">LOC</th>
      </tr>
<xsl:for-each select="/ROWSET/ROW">
      <tr>
        <td align="right"><xsl:value-of select="DEPTNO"/></td>
        <td align="left"><xsl:value-of select="DNAME"/></td>
        <td align="left"><xsl:value-of select="LOC"/></td>
      </tr>
</xsl:for-each>
    </table>
  </body>
  </html>
</xsl:template>
</xsl:stylesheet>'
        )
      )
    , VERSION '1.0')
  )
from DUAL;

Table DEPT in HTML format

DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Largely inspired from xmlfiles.com

select from xml

My wife is so happy that an African president is ruling the world for the next four years !

Ok, here is a simple exercice :

How do you select from an HTML file ?

Take an example

The table of departments

10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


create table T of XMLTYPE;

insert into T values(
  XMLTYPE(
'<?xml version="1.0" encoding="ISO-8859-1"?>
<html>
  <head>
    <title>Employee table</title>
  </head>
  <body>
    <p>The table of departments</p>
    <table border="1">
      <tr>
        <td>10</td>
        <td>ACCOUNTING</td>
        <td>NEW YORK</td>
      </tr>
      <tr>
        <td>20</td>
        <td>RESEARCH</td>
        <td>DALLAS</td>
      </tr>
      <tr>
        <td>30</td>
        <td>SALES</td>
        <td>CHICAGO</td>
      </tr>
      <tr>
        <td>40</td>
        <td>OPERATIONS</td>
        <td>BOSTON</td>
      </tr>
    </table>
  </body>
</html>'));

select deptno, dname, loc
from T t,
xmltable(‘/html/body/table/tr’
passing value(t) columns
deptno number path ‘/tr/td[1]‘,
dname varchar2(10) path ‘/tr/td[2]‘,
loc varchar2(10) path ‘/tr/td[3]‘
);


    DEPTNO DNAME      LOC
---------- ---------- ----------
        10 ACCOUNTING NEW YORK
        20 RESEARCH   DALLAS
        30 SALES      CHICAGO
        40 OPERATIONS BOSTON

xmltable is a 10gR2 features. For those with 9i, use TABLE(XMLSEQUENCE(EXTRACT(VALUE(t),’/html/body/table/tr’))) instead.

Note the html file must be in XHTML format, some rewritting may imply. SQLPLUS for example does not generate html files that are valid XHTML documents, for instance because of the <p> tags that to not have a </p> tag. Note that xml tags are case sensitive.

Flying toasters and dense_rank

Have fun with this caps-lock user question :

asktom:Logic behind the DENSE_RANK

This is one more statement on how to not use order by

like in

 
select ename, deptno, row_number() over (order by 1) 
from emp order by 2;
ENAME          DEPTNO ROW_NUMBER()OVER(ORDERBY1)
---------- ---------- --------------------------
CLARK              10                          1
KING               10                          2
MILLER             10                          3
JONES              20                          4
FORD               20                          5
ADAMS              20                          6
SMITH              20                          7
SCOTT              20                          8
WARD               30                          9
TURNER             30                         10
ALLEN              30                         11
JAMES              30                         12
BLAKE              30                         13
MARTIN             30                         14

According to the doc, order by position is invalid. Actually, order by 1 is treated as order by ‘bananas’.
When used in an analytic function, the order_by_clause must take an expression (expr). The SIBLINGS keyword is not valid (it is relevant only in hierarchical queries). Position (position) and column aliases (c_alias) are also invalid. Otherwise this order_by_clause is the same as that used to order the overall query or subquery.

Thanks Tom for being so funny :)

Stored outlines

Note:
Performance Tuning Guide
Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.


This said, let’s take a small example. If you have a query which is running fast most of the time and sometimes is running very slow due an unexpected plan change, you may want to considering enforcing plan stability with a Stored Outline.

To fake this example, let’s try to enforce a full table scan for select empno from emp where ename=’SCOTT’.

SQL> set autot on exp
SQL> select empno from emp where ename='SCOTT';
     EMPNO
----------
      7788

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 By

For the purpose of this exercice, I consider this to be the right plan and I want to enforce Oracle to use this plan for this query.


SQL> create or replace outline o for category emp_scott on 
  select empno from emp where ename='SCOTT';

Outline created.

SQL> create unique index i on emp(ename) 
  tablespace my_slow_tape_drive;

Index created.

SQL> set timi on
SQL> set autot on exp
SQL> select empno from emp where ename='SCOTT';
     EMPNO
----------
      7788

Elapsed: 01:45:59.95

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2
   2    1     INDEX (UNIQUE SCAN) OF 'I' (UNIQUE) (Cost=1 

Oracle uses an index scan, but the index is located on a tape (which is not possible on 11gR1 and older ;) ) and it takes ages to complete the query. Let’s try to use the good plan that was used at the time we created the stored outline

 
SQL> alter session set use_stored_outlines=emp_scott;

Session altered.

Elapsed: 00:00:00.00
SQL> select empno from emp where ename='SCOTT';
     EMPNO
----------
      7788

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 By

SQL> SELECT LAST_LOAD_TIME,OUTLINE_CATEGORY,sql_text 
  from v$sql 
  where sql_text like 'select%SCOTT%';
LAST_LOAD_TIME      OUTLINE_C SQL_TEXT
------------------- --------- ----------------------------
2008-10-16/09:42:30           select empno from emp where 
2008-10-16/09:46:50 EMP_SCOTT select empno from emp where 

The plan using the outline is now used

where is my database link listed?


$ sqlplus scott/tiger@DEVL
SQL> select * from all_db_links;
no rows selected
SQL> select * from dual@PROD;
D
-
X

Hey, why does this work???

Ok, after some research I found out that this seems to be an implicit loopback database link. The fact that the DEVL database has the global name set to PROD is just to annoy more ;)


SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
PROD.LAURENTSCHNEIDER.COM

Cycling

How to detect cycling records in 9i, remember CONNECT BY NOCYCLE does not exist in 9i


SQL> create table lsc_t as
  2  select 1 parent, 2 child from dual
  3  union all select 2,3 from dual
  4  union all select 4,5 from dual
  5  union all select 5,6 from dual
  6  union all select 6,4 from dual;

Table created.

SQL> select parent,child
  2  from lsc_t
  3  where level=50
  4  connect by parent=prior child
  5  and level<=50
  6  and prior dbms_random.value != dbms_random.value;

    PARENT      CHILD
---------- ----------
         5          6
         6          4
         4          5

Ok, it is a bit abusing the connect by operator, but it is for hierarchic purpose :mrgreen:

About case sensitivity

SQL is a case insensitive language. That means, you can write any SQL statement in uppercase or lowercase.

SQL> SELECT DUMMY FROM DUAL;
D
-
X

SQL> select dummy from dual;
D
-
X

Some formatters like Toad and some courseware like Skillsoft E-Learning advise to use UPPERCASE for commands and lowercase for tables and columns.


/* Formatted on 2008/09/19 10:00 
(Formatter Plus v4.8.8) */
SELECT dummy
  FROM DUAL;

Well, Toad decided to put DUAL in uppercase. Anyway, this is no authority, define your own guideline and stick to it. If you use TOAD a lot, it is maybe OK to use lowercase for non-keywords.

The column dummy is actually the uppercase “DUMMY” column of “DUAL”. Putting double quotes around a column of table makes it case sensitive, so “DUMMY” is not “dummy”.

SQL> select "dummy" from "dual";
select "dummy" from "dual"
                    *
Error at line 1
ORA-00942: table or view does not exist

SQL> SELECT "DUMMY" FROM "DUAL";
D
-
X

OK, something very bad about Toad formatter is (at least the version I am using) that it considers unquoted password to be case insensitive. Which is wrong. The password, in 11g, is case sensitive, even when not in double quotes. Therefore, be sure to put the right case when creating scripts and do not format your CREATE USER statements with Toad or use double quotes for disambiguation!


SQL> ALTER USER scott IDENTIFIED BY BIG;
User altered.
SQL> connect scott/BIG
Connected.
SQL> /* Formatted on 2008/09/19 10:17 (Formatter Plus v4.8.8) */
SQL> ALTER USER scott IDENTIFIED BY big;
SQL> connect scott/BIG
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

puzzled by Toad

one colleague just showed me how to insert duplicate rows in a table that has a primary key.


create table t(x number primary key);[F5]
Table created.
insert into t values (1);[F5]
1 row created.
insert into t values (1);[F5]
1 row created.

Hey, what happened? It took me quite a while to figure out which feature of Toad was “allowing” this.

Let’s look at Toad.ini (or View – ToadOptions – Transaction)


[SETTINGS]
ALWAYSROLLBACK=1
THREADQUERIES=1

Which means, Toad is starting a separate session each time a query is run (by F5 for instance) and at the end of this child session, it rollbacks. This is not the default, but this is pure magic!

Let’s prove it :


select count(*) from t where x=1;[F5]

  COUNT(*)
----------
         0

1 row selected.

:mrgreen:

what is faster, select count(*) or select count(pk) ?

Oh no, not another post about COUNT(*) and COUNT(1) :mrgreen:

Well, it is not exactly the case. I just had the issue this morning that count(*) was too slow.


SQL> create table t as select rownum id,
  2    lpad('a',4000,'a') a,
  3    lpad('b',4000,'b') b,
  4    lpad('c',4000,'c') c,
  5    lpad('d',4000,'d') d
  6  from dual connect by level<10000;

Table created.

SQL> create index i on t(id);

Index created.

SQL> alter table t add primary key (id) 
  2    using index i disable novalidate;

Table altered.

SQL> exec dbms_stats.gather_table_stats(user,'T')

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(user,'I')

PL/SQL procedure successfully completed.

SQL> set timi on
SQL> select count(id) from t;

 COUNT(ID)
----------
      9999

Elapsed: 00:00:00.01
SQL> select count(*) from t;

  COUNT(*)
----------
      9999

Elapsed: 00:00:01.43

My count(*) just takes too long… Why that?

I have no index on a not null column. The primary key is disabled.

Of course the count(pk) does not count the rows with pk=null, but it is faster :)