Sometimes you may find your sequences jump by a constant value(e.g. 20) and leaving a gap in-between. This is due to the losing of the cached sequence in the shared pool. There are several common reasons for losing the cached sequences,e.g. issuing alter system flush shared_pool and shutdown database with abort options. This often requires DBA to reset the sequence back to the correct value or ,as suggested by Thomas Kyte, reengineer the application ( or even business logic) to handle the problem.

On the other hand, we can create sequence with nocache option(by default sequences are created with caching option on). Note that, for busy OLTP database, setting nocache on may lead to performance problem, particularly in RAC database.

You can check the following links to find out more about this issue.

http://www.jlcomp.demon.co.uk/faq/seq_loss.html
http://dbaforums.org/oracle/index.php?s=1677c76331bcd690ae3ae62a1a3b33d0&showtopic=5156&st=0&p=19575&#entry19575
http://www.orafaq.com/forum/t/126004/0/

Oracle does not provide builtin function to check if a string is a number.
However, there is a function named to_number which can convert a string into a number. If the string is not a number, to_number would return, otherwise it would return the numerical value of the string.

Here is a function that makes use of to_number() to check if a string is a number.

CREATE OR REPLACE FUNCTION Is_Number
(num_In_str  IN VARCHAR2)
RETURN BOOLEAN
IS
num  NUMBER;
BEGIN
IF num_In_str IS NULL  THEN
RETURN NULL;
END IF;

num := To_Number(num_In_str);

RETURN True;
EXCEPTION
WHEN OTHERS THEN
RETURN False;
END Is_Number;

Oracle does not support BOOLEAN datatype in the table as shown in link
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330
If you create table with boolean dataype, you will get error.


SQL> CREATE TABLE t (Bool BOOLEAN);
CREATE TABLE t (Bool BOOLEAN)
*
ERROR at line 1:
ORA-00902: invalid datatype


But you can declare boolean variables in PL/SQL


DECLARE
bool BOOLEAN;
BEGIN
bool := TRUE;
IF x THEN
dbms_output.put_line('TRUE');
ELSIF NOT x THEN
dbms_output.put_line('FALSE');
END IF;
END;
/

If you really want to use boolean dataype in columns, there are 2 solutions.

The first one is use CHAR(1) with constraint/
CREATE TABLE t1 (Bool CHAR(1) CHECK (Bool IN ( 'Y', 'N' )));

Another common solution is to use number(1).
CREATE TABLE t2 (Bool number(1) CHECK (BOOL in (0,1)));

4) the has bitmap indexes (only available in Oracle Ent Edition).


Update on 8-Sep-2010
Jonathan talked about how not null constraint and how it affect the the performance on query. It is surprised to hear this little feature would embarrassed in front of the client. Anyway, he had an update on his blog(http://jonathanlewis.wordpress.com/2010/09/05/not-null/) about this not null issue in with examples. It is worth to read!

There has been long debates about the performance between Implicit cursors and Explicit cursors. In the past, people(probably influenced by the book Oracle PL/SQL Programming ) believed that Explicit cursor is faster and efficient. However, there are oracle professionals, e.g. Tom Kyte, Tim Hall..etc, now suggest that implicit cursors are more performant than explicit ones. Personally, I opt for Implicit cursors because of its simplicity(remember KISS?) and less error-prone.

Below is some reference links about Implicit and Explicit cursors
http://www.bhatipoglu.com/entry/27/implicit-vs-explicit-cursors-performance-analysis
http://www.dba-oracle.com/plsql/t_plsql_cursors.htm
http://oracle-gurus.co.uk/ExplicitVsImplicitCursors.aspx
http://sql-plsql.blogspot.com/2007/05/oracle-plsql-implicit-cursors.html
http://radiofreetooting.blogspot.com/2007/01/explicit-cursors-rip-not-quite.html
http://awads.net/wp/2007/06/21/cursors-again/

Tom Kyte's explain on why implicit cursor is faster than explicit one.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1205168148688
"PLSQL, like java, is interpreted. We have some C code that is a PLSQL VM (virtual
machine) that runs PLSQL. The more PLSQL code you write, the more we have to intrepret.
Intrepeted code runs slower then native code (object/binary code) does."

This is a procedure to create a huge table for testing quickly. I use it quite often when I need to build a large for testing. This stored proc takes two parameters, table_name for the name of the table to be created and table_size for the number of rows to be loaded to the newly created table .

Some of the advantages of this procedure are that this stored proc create table with nologging and load the data to the table with APPEND hint.
Therefore this stored proc can create a huge table quickly with minminal redo log generation.


CREATE OR REPLACE PROCEDURE Build_Table
(Table_Name IN VARCHAR,
Table_Size NUMBER)
IS
sql_stmt VARCHAR(2000);
l_cnt NUMBER := 0;
l_Rows NUMBER := Table_Size;
BEGIN
sql_stmt := 'create table ' --create table structure

||Table_Name
||' nologging
as select rownum id,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from all_objects a where 1=0';

EXECUTE IMMEDIATE sql_stmt;

COMMIT;

sql_stmt := 'insert /*+ APPEND */ into ' --load the data

||Table_Name
||' select rownum,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from all_objects
where rownum<=:1';

EXECUTE IMMEDIATE sql_stmt
USING l_Rows;

l_cnt := l_cnt + SQL%ROWCOUNT;

COMMIT;

WHILE (l_cnt < l_Rows) LOOP
sql_stmt := 'insert /*+ APPEND */ into '
||Table_Name
||' select rownum+:1,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from '
||Table_Name
||' where rownum <= :2-:3';

EXECUTE IMMEDIATE sql_stmt
USING l_cnt,l_Rows,l_cnt;

l_cnt := l_cnt + SQL%ROWCOUNT;

COMMIT;
END LOOP;

sql_stmt := 'alter table ' --create the primary key after loading the data

||Table_Name
||' add constraint '
||Table_Name
||'_pk primary key(id)';

EXECUTE IMMEDIATE sql_stmt;

sql_stmt := ' BEGIN dbms_stats.gather_table_stats --update the table statistics
( ownname => SYS_CONTEXT (''USERENV'', ''CURRENT_USER''),
tabname =>'''
||Upper(Table_Name)
||''',
cascade => TRUE ); end;';

EXECUTE IMMEDIATE sql_stmt;
END;

References
http://www.dbspecialists.com/files/presentations/load_faster.html

Below is an query taken from Metalink which can used to compare the differences(column Names, datatype, datalength) between two tables. However, in Oracle 11g, you may no longer need this query as there is a new package named DBMS_COMPARE which provides more power funtion to facilitate the comparison between tables or schemas.


create table t1 as select * from emp where 1=0;

create table t2 as select * from t1;

alter table t2 drop column ename;

alter table t2 modify job varchar2(10);

column data_type format a10

(select 'IN T1, NOT T2', column_name,data_type,data_length
from user_tab_columns
where table_name = 'T1'
MINUS
select 'IN T1, NOT T2', column_name,data_type,data_length
from user_tab_columns
where table_name = 'T2'
)
UNION ALL
(
select 'IN T2, NOT T1', column_name,data_type,data_length
from user_tab_columns
where table_name = 'T2'
MINUS
select 'IN T2, NOT T1', column_name,data_type,data_length
from user_tab_columns
where table_name = 'T1'
)
/

which gives the following Output

'INT1,NOTT2' COLUMN_NAME DATA_TYPE DATA_LENGTH
------------- ------------------------------ ---------- -----------
IN T1, NOT T2 ENAME VARCHAR2 10
IN T1, NOT T2 JOB VARCHAR2 9
IN T2, NOT T1 JOB VARCHAR2 10

References
Metalink Doc ID: :443557.1

variable start_date varchar2(25)
variable end_date varchar2(25)

exec :start_date := '01-Jan-2009'; :end_date := '20-Jun-2009';

select to_date(:start_date,'dd-mon-yyyy') + rownum -1
from all_objects
where rownum <= to_date(:end_date,'dd-Mon-yyyy')-to_date(:start_date,'dd-Mon-yyyy')+1;

Sometimes we may find that Oracle chooses to use FTS(Full table scan) instead of using index when LIKE operator is used in where clause. Below example illustrates this problem and fixes.

SQL> create table t as select * from dba_objects;
SQL> create index t_idx on t(object_id);
SQL> exec dbms_stats.gather_table_stats('USER','T',cascade=>TRUE);
SQL> SELECT * FROM t WHERE object_id like '111%';
111 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2723 |   244K|   166   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |  2723 |   244K|   166   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter(TO_CHAR("OBJECT_ID") LIKE '111%')

As you can see from the above query, when using like operator, index t_idx is not used in the explain plan because there is an implicit conversion when like operator is used , this conversion is implemented on the ID column as to_char(ID). As the function wrapped ID column then any index on ID column won't be used.

We can improve the performance of this query by two means, either create function based index on to_char(object_id) or change the object_id to text colum,e.g. varchar2.




Method 1(creating function-based index):

SQL> create index t_idx2 on test(to_char(object_id));
Index created.
SQL> select * from t where to_char(object_id) like '111%';
111 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1998783028

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |  2723 |   244K|   104   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T   |  2723 |   244K|   104   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | T_IDX2 |   490 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access(TO_CHAR("OBJECT_ID") LIKE '111%')
filter(TO_CHAR("OBJECT_ID") LIKE '111%')

Method 2(changing the object_id to type varchar2)
Use the following SQL statements to create a table with object_id column of type varchar2.

SQL> create table t2 as select * from dba_objects where 1=0;
SQL> alter table t2  modify object_id varchar(30);
--SQL> alter table t2 add constraint test2_pk primary key (object_id);
SQL> create index t2_idx on t2(object_id);
SQL> exec dbms_stats.gather_table_stats('USER','T2',cascade=>TRUE);
SQL> insert /*+ append*/ into t2 select * from dba_objects;

SQL> select * from t2 where object_id like '111%';

Execution Plan
----------------------------------------------------------
Plan hash value: 4244861920

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   214 | 19902 |   217   (0)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |   214 | 19902 |   217   (0)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN          | T2_IDX |   214 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access("OBJECT_ID" LIKE '111%')
filter("OBJECT_ID" LIKE '111%')

By either creating FBI on the column or changing the datatype to varchar, indexes are used during queries and thus the performance is improved alot.


Note that when the LIKE operator is implemented with wildcard card in the begining of the string, e.g. ... where object_id like '%111%', there is noway to use the index since it begins with wildcard % which is indeterministic.

This sql script list the SQL statements from the shared cursor in the SGA and the sessions who have executed them


set linesize 120
col c1 format a9 heading "OS User"
col c2 format a15 heading "Oracle User"
col c3 format 9999 justify left heading "SID"
col c4 format 99999 justify left heading "SERIAL#"
col c5 format a80 heading "SQL"
select  s.osuser c1, s.username c2, s.sid c3, s.serial# c4,st.sql_text c5
  from v$sqltext st, v$session s
   where st.address    = s.sql_address
--   and b.status     = 'ACTIVE' /* uncommet this line if you only want to see currently active session */
   and st.hash_value = s.sql_hash_value
 order by st.hash_value,st.piece
/         


OS User   Oracle User     SID   SERIAL# SQL
--------- --------------- ----- ------- --------------------------------------------------------------------------------
oracle    SYS               684     351 select  s.osuser c1, s.username c2, s.sid c3, s.serial# c4,st.sq
oracle    SYS               684     351 l_text c5   from v$sqltext st, v$session s    where st.address
oracle    SYS               684     351   = s.sql_address --   and b.status     = 'ACTIVE' /* YOU CAN CH
oracle    SYS               684     351 OOSE THIS OPTION ONLY TO SEE ACTVE TRANSACTION ON THAT MOMENT */
oracle    SYS               684     351     and st.hash_value = s.sql_hash_value  order by st.hash_value
oracle    SYS               684     351 ,st.piece