SQL Design Patterns

Same table refered twice in a multi level subquery.

SELECT calc_scheme.scheme_id
FROM calc_scheme
WHERE NOT EXISTS
  (SELECT 'PRODUCT'
   FROM calc_rule_product
   WHERE calc_rule_product.scheme_id = calc_scheme.scheme_id)
OR EXISTS
  (SELECT 'PRODUCT'
   FROM calc_rule_product
   WHERE calc_rule_product.scheme_id = calc_scheme.scheme_id
   AND 'FLT' = calc_rule_product.code
   AND 'FLT' IS NOT NULL)

Becomes

SELECT calc_scheme.scheme_id
FROM calc_scheme,
    (SELECT scheme_id,
     code
   FROM calc_rule_product)
x
WHERE(calc_scheme.scheme_id = x.scheme_id(+)
 AND x.rowid IS NULL) OR(x.scheme_id = calc_scheme.scheme_id
 AND 'FLT' = x.code
 AND 'FLT' IS NOT NULL) 

For WHERE EXISTS single table sub-queries, try a Semi-Join. If this doesn't work, try rewriting the query as a join

SELECT *
FROM   table_a
WHERE  EXISTS (
       SELECT 1
       FROM   table_b b, table_c c
       WHERE  b.key2 = c.key2
       AND    b.key1 = a.key1);

becomes

SELECT a.*
FROM   table_a a
, (    SELECT DISTINCT b.key1
       FROM   table_b b, table_c c
       WHERE  b.key2 = c.key2
  ) d
WHERE  a.key1 = d.key1

For WHERE NOT EXISTS sub-queries, try re-writing the SQL as an outer join with a b.ROWID IS NULL clause. eg.

SELECT a.*
FROM   table_a a
WHERE  NOT EXISTS (
       SELECT 1
       FROM   table_b
       WHERE  b.col1 = a.col1)

becomes

SELECT a.*
FROM   table_a a , table_b b
WHERE  a.col1 = b.col1 (+)
AND    b.rowid IS NULL

For IN and NOT IN sub-queries, eliminate correlated sub-queries, they must use a Nested Loops join. eg.

SELECT a.*
FROM   table_a a
WHERE  col2 IN (
       SELECT col2
       FROM   table_b
       WHERE  b.col1 = a.col1)

becomes

SELECT a.*
FROM   table_a a
WHERE  (col1, col2) IN (
       SELECT col2
       FROM   table_b)

For IN sub-queries that are not correlated, try an ORDERED hint and a USE_HASH or USE_MERGE hint. eg:

SELECT /*+ ORDERED USE_HASH(a)*/ *
FROM table_a a
WHERE col1 IN (
      SELECT col1
      FROM   table_b b)

In this way, Oracle can run the outer query and the sub-query independently and perform a hash join.


For correlated col = (sub-query) sub-queries, Oracle must use a Filter. Try to re-write as a table join. eg.

SELECT a.*
FROM   table_a a
WHERE  col2 = (
       SELECT max(col2)
       FROM   table_b
       WHERE  b.col1 = a.col1)

becomes

SELECT a.*
FROM   table_a a,
(      SELECT col1, max(col2)
       FROM   table_b
       GROUP  BY col1
) b
WHERE  a.col1 = b.col1

NOLOGGING

NOLOGGING tablespaces
For some database operations, you can control whether the database generates redo records. Without redo, no media recovery is possible. However, suppressing redo
generation can improve performance, and may be appropriate for easily recoverable operations.

An example of such an operation is a CREATE TABLE...AS SELECT statement, which can be repeated in case of database or instance failure. Specify the NOLOGGING clause in the CREATE TABLESPACE statement if you wish to suppress redo when these operations are performed for objects within the tablespace.

If you do not include this clause, or if you specify LOGGING instead, then the database generates redo when changes are made to objects in the tablespace. Redo is never generated for temporary segments or in temporary tablespaces, regardless of the logging attribute. The logging attribute specified at the tablespace level is the default attribute for objects created within the tablespace. You can override this default logging attribute by specifying LOGGING or NOLOGGING at the schema object level--for example, in a CREATE TABLE statement.

If you have a standby database, NOLOGGING mode causes problems with the availability and accuracy of the standby database. To overcome this problem, you can specify FORCE LOGGING mode. When you include the FORCE LOGGING clause in the CREATE TABLESPACE statement, you force the generation of redo records for all operations that make changes to objects in a tablespace. This overrides any specification made at the object level.

If you transport a tablespace that is in FORCE LOGGING mode to another database, the new tablespace will not maintain the FORCE LOGGING mode.

Consider Using NOLOGGING When Creating Tables
To create a table most efficiently use the NOLOGGING clause in the CREATE TABLE...AS SELECT statement. The NOLOGGING clause causes minimal redo information to be generated during the table creation. This has the following benefits:

* Space is saved in the redo log files.
* The time it takes to create the table is decreased.
* Performance improves for parallel creation of large tables.

The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader
and direct load INSERT operations are not logged. Subsequent DML statements
(UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo.

If you cannot afford to lose the table after you have created it (for example, you will no longer have access to the data used to create the table) you should take a backup immediately after the table is created. In some situations, such as for tables that are created for temporary use, this precaution may not be necessary.

In general, the relative performance improvement of specifying NOLOGGING is greater for larger tables than for smaller tables. For small tables, NOLOGGING has little effect on the time it takes to create a table. However, for larger tables the performance improvement can be significant, especially when you are also parallelizing the table creation.

Tablespace Resize

query to see if tablespaces can be reduced in size

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

Bind Variables in PL/SQL

Bind Variables in PL/SQL

variables in PL/SQL are implicitly bind variables.

 create or replace procedure dsal(p_empno in number)
    as
      begin
        update emp set sal=sal*2 where empno = p_empno;
        commit;
      end;
    /    

Now you might be thinking that you've got to replace the p_empno with a bind variable. However, the good news is that every reference to a PL/SQL variable is in fact a bind variable.

Dynamic SQL

In fact, the only time you need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL.

Dynamic SQL, allows you to execute a string containing SQL using the EXECUTE IMMEDIATE command. For next example would always require a hard parse when it is submitted:

 create or replace procedure dsal(p_empno in number)
    as
      begin
        execute immediate
         'update emp set sal = sal*2 where empno = '||p_empno;
      commit;
      end;
    /   

The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows:

    create or replace procedure dsal(p_empno in number)
    as
      begin
        execute immediate
         'update emp set
         sal = sal*2 where empno = :x' using p_empno;
      commit;
      end;
    /

And that's all there is to it. One thing to bear in mind, though, is that you can't substitute actual object names (tables, views, columns etc) with bind variables - you can only subsitute literals. If the object name is generated at runtime, you'll still need to string concatenate these parts, and the SQL will only match with those already in the shared pool when the same object name comes up. However, whenever you're using dynamic SQL to build up the predicate part of a statement, use bind variables instead and you'll reduce dramatically the amount of latch contention going on.

The Performance Killer

Just to give you a tiny idea of how huge of a difference this can make performance wise, you only need to run a very small test:

Here is the Performance Killer ....

SQL> alter system flush shared_pool;
SQL> set serveroutput on;

    declare
          type rc is ref cursor;
          l_rc rc;
          l_dummy all_objects.object_name%type;
          l_start number default dbms_utility.get_time;
      begin
          for i in 1 .. 1000
          loop
              open l_rc for
              'select object_name
                 from all_objects
                where object_id = ' || i;
              fetch l_rc into l_dummy;
              close l_rc;
              -- dbms_output.put_line(l_dummy);
          end loop;
          dbms_output.put_line
           (round((dbms_utility.get_time-l_start)/100, 2) ||
            ' Seconds...' );
      end;
    /

Modified code

    declare
          type rc is ref cursor;
          l_rc rc;
          l_dummy all_objects.object_name%type;
          l_start number default dbms_utility.get_time;
      begin
          for i in 1 .. 1000
          loop
              open l_rc for
              'select object_name
                 from all_objects
                where object_id = :x'
              using i;
              fetch l_rc into l_dummy;
              close l_rc;
              -- dbms_output.put_line(l_dummy);
          end loop;
          dbms_output.put_line
           (round((dbms_utility.get_time-l_start)/100, 2) ||
            ' Seconds...' );
    end;
    /

Modified code runs faster than the earlier version. This is because it reduces the number of parses required. This is good for scalability as less cpu and memory is consumed.

ORA-4031 Common Analysis Diagnostic Scripts

Metalink note :

430473.1
396940.1

Lbrary cache latch contention

Lbrary cache latch contention is typically caused by NOT using bind variables. It is due
to excessive parsing of statements.

One way to see if this might be the case in your situation is to run a script like:


create table t1 as select sql_text from v$sqlarea;

alter table t1 add sql_text_wo_constants varchar2(1000);

create or replace function
remove_constants( p_query in varchar2 ) return varchar2
as
l_query long;
l_char varchar2(1);
l_in_quotes boolean default FALSE;
begin
for i in 1 .. length( p_query )
loop
l_char := substr(p_query,i,1);
if ( l_char = '''' and l_in_quotes )
then
l_in_quotes := FALSE;
elsif ( l_char = '''' and NOT l_in_quotes )
then
l_in_quotes := TRUE;
l_query := l_query || '''#';
end if;
if ( NOT l_in_quotes ) then
l_query := l_query || l_char;
end if;
end loop;
l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
for i in 0 .. 8 loop
l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
end loop;
return upper(l_query);
end;
/
update t1 set sql_text_wo_constants = remove_constants(sql_text);

select sql_text_wo_constants, count(*)
from t1
group by sql_text_wo_constants
having count(*) > 100
order by 2
/



The output of that last query will show you statements that are identical in the shared
pool after all numbers and character string constants have been removed. These
statements -- and more importantly their counts -- are the potential bottlenecks. In
addition to causing the contention, they will be HUGE cpu consumers.

NETCA fails on 11g client installation

NETCA on Oracle 11g client installation fails on RHEL 4 with the following error

UnsatisfiedLinkError exception loading native library: njni11

java.lang.UnsatisfiedLinkError: jniGetOracleHome
        at oracle.net.common.NetGetEnv.jniGetOracleHome(Native Method)
        at oracle.net.common.NetGetEnv.getOracleHome(Unknown Source)
        at oracle.net.ca.NetCA.main(Unknown Source)
Oracle Net Services configuration failed.  The exit code is -1   

To rectify this install libaio-devel and libaio rpms on the machine.

11g Partitioning Enhancements

Interval Partitioning

Interval partitioning is an extension of range partitioning which instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the range partitions.

You must specify at least one range partition. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database creates interval partitions for data beyond that transition point.

Interval partitioning restrictions
Can only specify one partitioning key column, and it must be of NUMBER or DATE type.
Interval partitioning is not supported for index-organized tables.
Cannot create a domain index on an interval-partitioned table.

CREATE TABLE interval_sales
( prod_id        NUMBER(6)
, cust_id        NUMBER
, time_id        DATE
, channel_id     CHAR(1)
, promo_id       NUMBER(6)
, quantity_sold  NUMBER(3)
, amount_sold    NUMBER(10,2)
) 
PARTITION BY RANGE (time_id) 
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2005', 'DD-MM-YYYY')),      
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')),    
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2006', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')) );

above the transition point of January 1, 2007, partitions are created with a width of one month.
The high bound of partition p3 represents the transition point. p3 and all partitions below it (p0, p1, and p2 in this example) are in the range section while all partitions above it fall into the interval section.


System Partitioning
System partitioning does not have any partitioning keys so the mapping of the rows to a particular partition is not implicit. Instead, you specify the partition to which a row maps by using partition extended syntax when inserting a row.

Without a partitioning key the usual performance benefits of partitioned tables are not available for system partitioned tables. There is no support for traditional partition pruning or partition wise joins. Partition pruning is only achieved by accessing the same partitions in the system partitioned tables as those that were accessed in the base table.

System partitioned tables do provide the manageability advantages of equi-partitioning. For example, a nested table can be created as a system partitioned table that has the same number of partitions as the base table. A domain index can be backed up by a system partitioned table that has the same number of partitions as the base table.

System Partitioning Guidelines

The following operations are supported for system partitioned tables:

* Partition maintenance operations and other DDLs (See exceptions below)
* Creation of local indexes.
* Creation of local bitmapped indexes.
* Creation of global indexes.
* All DML operations.
* INSERT AS SELECT with partition extended syntax:
INSERT INTO PARTITION (partition-name|number|bind var) AS subqery

Following operations are not supported for system partitioning

* Unique local indexes are not supported because they require a partitioning key.
* CREATE TABLE AS SELECT
* Since there is no partitioning method, it is not possible to distribute rows to partitions. Instead the user should first create the table and then insert rows into each partition.
INSERT INTO tabname AS subquery
SPLIT PARTITION operations

CREATE TABLE systab (c1 integer, c2 integer) 
PARTITION BY SYSTEM
(
PARTITION p1 TABLESPACE tbs_1,
PARTITION p2 TABLESPACE tbs_2,
PARTITION p3 TABLESPACE tbs_3,
PARTITION p4 TABLESPACE tbs_4
); 

INSERT and MERGE statements must use partition extended syntax to identify a particular partition a row should go into.For example, tuple (4,5) can be inserted into any one of the above four partitions

INSERT INTO systab PARTITION (p1) VALUES (4,5); /* Partition p1 */ 
INSERT INTO systab PARTITION (1) VALUES (4,5); /* First partition */ 
INSERT INTO systab PARTITION (:pno) VALUES (4,5); /* pno bound to 1/p1 */ 

Or:

INSERT INTO systab PARTITION (p2) VALUES (4,5); /* partition p2 */ or
INSERT INTO systab PARTITION (2) VALUES (4,5) /* second partition */
INSERT INTO systab PARTITION (:pno) VALUES (4,5); /* pno bound to 1/p1 */ 

Composite Partitioning Enhancements

With Oracle Database 11g list partitioning can be a top level partitioning method for composite partitioned tables giving us List-List, List-Hash, List-Range and Range-Range composite methods.
With the Interval partitioning, the following composite partitioning methods are also supported: Interval-Range, Interval-List and Interval-Hash.

* Range-Range Partitioning :-Composite range-range partitioning enables logical range partitioning along two dimensions; for example, partition by order_date and range subpartition by shipping_date.

* List-Range Partitioning: Composite list-range partitioning enables logical range subpartitioning within a given list partitioning strategy; for example, list partition by country_id and range subpartition by order_date.

* List-Hash Partitioning :- Composite list-hash partitioning enables hash sub-partitioning of a list-partitioned object; for example, to enable partition-wise joins.

* List-List Partitioning :-Composite list-list partitioning enables logical list partitioning along two dimensions; for example, list partition by country_id and list subpartition by sales_channel.

eg: Composite Range-Range Partitioning

CREATE TABLE shipments(
order_date DATE NOT NULL, 
delivery_date DATE NOT NULL)
PARTITION BY RANGE (order_date)
SUBPARTITION BY RANGE (delivery_date)
(PARTITION p_2006_jul VALUES LESS THAN 
(TO_DATE('01-AUG-2006','dd-MON-yyyy'))
    ( SUBPARTITION p06_jul_e VALUES LESS THAN 
    (TO_DATE('15-AUG-2006','dd-MON-yyyy'))
   , SUBPARTITION p06_jul_a VALUES LESS THAN 
    (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
   , SUBPARTITION p06_jul_l VALUES LESS THAN (MAXVALUE))
,PARTITION p_2006_aug VALUES LESS THAN 
(TO_DATE('01-SEP-2006','dd-MON-yyyy'))
    ( SUBPARTITION p06_aug_e VALUES LESS THAN 
    (TO_DATE('15-SEP-2006','dd-MON-yyyy'))
    , SUBPARTITION p06_aug_a VALUES LESS THAN 
    (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
    , SUBPARTITION p06_aug_l VALUES LESS THAN (MAXVALUE)));

The above example illustrates how range-range partitioning might be used. The example tracks shipments.

A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within a specific partition range. The row is then mapped to a subpartition within that partition by identifying whether the value of the subpartitioning column falls within a specific range.

Virtual Column-Based Partitioning

The values for virtual columns are not physically stored in the table row on disk, rather they are evaluated on demand. The functions or expressions describing the virtual columns should be deterministic and pure, meaning the same set of input values should return the same output values.

Virtual columns can be used like any other table columns. They can be indexed, used in queries, DML and DDL statements. Tables and indexes can be partitioned on a virtual column and even statistics can be gathered upon them.

These columns can be specified during a CREATE, or ALTER table operation and can be defined to be either visible or hidden. Virtual columns share the same SQL namespace as other real table columns and conform to the data type of the underlying expression that describes it. These columns can be used in queries like any other table columns providing a simple, elegant and consistent mechanism of accessing expressions in a SQL statement.

Partition pruning takes place for virtual column partition keys when the predicates on the partitioning key are of the following types:

* Equality or Like
* List
* Range
* TBL$
* Partition extended names

Given a join operation between two tables, the optimizer recognizes when partition-wise join (full or partial) is applicable, decides whether to use it or not and annotate the join properly when it decides to use it. This applies to both serial and parallel cases.


To recognize full partition-wise join the optimizer relies on the definition of equi-partitioning of two objects, this definition includes the equivalence of the virtual expression on which the tables were partitioned.

CREATE TABLE employees 
  (employee_id  number(6) not null, first_name varchar2(30),  
   last_name varchar2(40) not null, email varchar2(25), 
   phone_number varchar2(20), hire_date  date not null, 
   job_id  varchar2(10) not null, salary number(8,2), 
   commission_pct number(2,2), manager_id  number(6), 
   department_id number(4),
   total_compensation as (salary *( 1+commission_pct))
   )
    PARTITION BY RANGE (total_compensation)
     ( 
       PARTITION p1 VALUES LESS THAN (50000),
       PARTITION p2 VALUES LESS THAN (100000),
       PARTITION p3 VALUES LESS THAN (150000),
       PARTITION p4 VALUES LESS THAN (MAXVALUE)
     );

Reference Partitioning

Reference partitioning allows the partitioning of a table based on the partitioning scheme of the table referenced in its referential constraint. The partitioning key is resolved through an existing parent-child relationship, enforced by active primary key or foreign key constraints. This means that tables with a parent-child relationship can be logically equi-partitioned by inheriting the partitioning key from the parent table without duplicating the key columns. The logical dependency also automatically cascades partition maintenance operations, making application development easier and less error-prone.

To create a reference-partitioned table, you specify a PARTITION BY REFERENCE clause in the CREATE TABLE statement. This clause specifies the name of a referential constraint and this constraint becomes the partitioning referential constraint that is used as the basis for reference partitioning in the table.

CREATE TABLE orders
  (order_id NUMBER(12), 
  order_date DATE, 
  order_mode VARCHAR2(8),    
  customer_id NUMBER(6), 
  order_status NUMBER(2),
  order_total NUMBER(8,2), 
  sales_rep_id NUMBER(6),
  promotion_id NUMBER(6),
  CONSTRAINT orders_pk PRIMARY KEY(order_id)
  )
 PARTITION BY RANGE(order_date)
   (PARTITION Q1_2005 VALUES LESS THAN
   (TO_DATE('01-APR-2005','DD-MON-YYYY')),
    PARTITION Q2_2005 VALUES LESS THAN
   (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
    PARTITION Q3_2005 VALUES LESS THAN   
   (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
    PARTITION Q4_2005 VALUES LESS THAN   
   (TO_DATE('01-JAN-2006','DD-MON-YYYY'))); 

CREATE TABLE order_items
 ( order_id NUMBER(12) NOT NULL,
   line_item_id NUMBER(3) NOT NULL,
   product_id NUMBER(6) NOT NULL,
   unit_price NUMBER(8,2),
   quantity NUMBER(8),
   CONSTRAINT order_items_fk
   FOREIGN KEY(order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE(order_items_fk); 

The reference-partitioned child table ORDER_ITEMS example above is created with four partitions, Q1_2005, Q2_2005, Q3_2005, and Q4_2005, where each partition contains the order_items rows corresponding to orders in the respective parent partition.

If partition descriptors are provided, then the number of partitions described must be exactly equal to the number of partitions or sub-partitions in the referenced table. If the parent table is a composite partitioned table, then the table will have one partition for each sub-partition of its parent; otherwise the table will have one partition for each partition of its parent.

Partition bounds cannot be specified for the partitions of a reference-partitioned table. The partitions of a reference-partitioned table can be named. If a partition is not explicitly named, then it will inherit its name from the corresponding partition in the parent table, unless this inherited name conflicts with one of the explicit names given. In this case, the partition will have a system-generated name.

Partitions of a reference-partitioned table will co-locate with the corresponding partition of the parent table, if no explicit tablespace is specified for the reference-partitioned table's partition.

Useful SQLs

Child tables not having index on foreign key columns

select table_name,column_name from user_cons_columns where constraint_name in (select constraint_name from user_constraints where r_constraint_name in (select constraint_name from user_constraints where table_name='table_name') and constraint_type='R') minus (select table_name,column_name from user_ind_columns);

child tables of a parent table

select table_name from user_constraints where r_constraint_name in (select constraint_name from user_constraints where table_name='table name');