SQL and PL/SQL

1

How can you create a function that will return the median of a group of numbers? 

Here are 2 examples:

Note: The first query works in all releases (7.1 and up) The second query is a little more efficient but works in releases 8.1.5 and up only

Example 1:

CREATE TABLE t AS SELECT user_id FROM all_users
WHERE ROWNUM < 11;
CREATE OR REPLACE VIEW number_data AS SELECT user_id datum from t
 /

SELECT AVG(DISTINCT datum)

FROM (SELECT cp1.datum

FROM number_data cp1, number_data CP2

GROUP BY cp1.datum

HAVING SUM(decode(cp1.datum, cp2.datum, 1, 0)) >=

abs(SUM(sign(cp1.datum - cp2.datum))))

/

Example 2:

SELECT AVG( user_id )

FROM ( SELECT user_id, ROWNUM r

FROM ( SELECT user_id

FROM t

ORDER BY user_id )

 ),

( SELECT count(*) cnt

FROM t

)

WHERE ( cnt/2 = trunc(cnt/2) AND ( r = cnt/2 OR r = cnt/2+1 ) )

OR ( cnt/2<> trunc(cnt/2) AND ( r = ceil(cnt/2) ) )

 /

2

select * from emp where hiredate = to_date('30/01/2001','dd/mm/rrrr');

The dates are probably getting stored with the time.

If you place a function on the date you will get a full table scan.

If your database is large enough you don't like that you have a few

alternatives:

1) Place a redundant column in the table that is a trunc of the hiredate column. (This is what most of us used to do for this kind of problem)

2) If you are using Oracle 8.1.5(?? I think) or later you can make an index on trunc(hiredate) so you can compare using it and use that index.

3) If you can't change the database, you can use

select * from emp where hiredate between to_date('30/01/2001','dd/mm/rrrr')

and to_date('31/01/2001','dd/mm/rrrr')

This will use the index. You probably want to take a second off the second date.

  3

The query has to return the rows from a master table which are not present in the detail table. It is not like an outer join; the IN operator is selecting the compliment of what is needed. The NOT IN operator should work for my condition but it is retrieving all the rows and it takes a lot of time.

The query is as follows:

select code from code_master where code not in (select code from code_detail

where codedate between to_date('14-JUN-2001 03:04:05','dd-MON-yyyy

hh24:mi:ss') and to_date('14-JUN-2001 04:04:05','dd-MON-yyyy hh24:mi:ss'));

The codedate datatype is date and it has data for every second.

select m.code from code_master m,

(select code from code_detail where codedate between

to_date('14-JUN-2001 03:04:05','dd-MON-yyyy hh24:mi:ss') and

to_date('14-JUN-2001 04:04:05','dd-MON-yyyy hh24:mi:ss')) d

where m.code = d.code(+) and d.code is null

4

This is not a trivial question.  You can't assume that dropping indexes will solve the problem.  At 2 seconds per entry, this is probably not an index problem.  It does not take 2 seconds to update an index.  It might take 2 seconds to update 50 of them, but this would mean that the system is too slow even in an OLTP mode and you have more serious problems.

I would look closely at the migration script and put timing points into it to find out where the bottleneck is.  It is just as likely that the problem is due to a poorly tuned query looking up a reference value as it is due to anything regarding indexes.

There are many tuning documents available to look at for help as well as any book on tuning in general.

For migration tuning you have a few more tools available to you.

1) Selective shutting off of indexes, triggers, constraints.

2) Use direct load SQL loader for 1,000,000 records

The problem can even be due to something pretty silly.  Going down memory lane, in my first migration project 5 years ago, I was building a history table.  I had a call to SYSDATE in a loop that was executing thousands of times.  I pulled the call to SYSDATE out and calculated SYSDATE once at the beginning of the loop and the performance problem was solved.

Before you start shutting off indexes at random, try to figure out exactly where in your code the delay is.

I tried to invoke a Procedure (with parameters, by using clause USING ...) and it worked well but did not work with a function.

declare

t varchar2(30) := 'FLACK';

x varchar2(30);

begin

execute immediate 'begin :a := LOWER(:b); end;' USING OUT x, IN t;

DBMS_OUTPUT.PUT_LINE('x is '||x);

end;

6

How can you use 'dynamic' ref cursors in Oracle Reports 3.0 / 6i?

Thanks to  Zlatko Sirotic in Croatia for this tip:

From Oracle 8.1.5 database, we can use 'dynamic' ref cursors. With 'dynamic' ref cursor we can avoid the use of lexical parameters in Reports 3.0 / 6i. With 'static' ref cursor this is not possible in all cases.

For example, if we need dynamic WHERE, we practically can't use 'static' ref cursor.

1. Example for 'static' ref cursor (dynamic ORDER BY)

1.1 Stored package

CREATE OR REPLACE PACKAGE report_static IS

TYPE type_ref_cur_sta IS REF CURSOR RETURN dept%ROWTYPE;

FUNCTION func_sta (p_order_by VARCHAR2) RETURN type_ref_cur_sta;

END;

/

CREATE OR REPLACE PACKAGE BODY report_static IS

FUNCTION func_sta (p_order_by VARCHAR2) RETURN type_ref_cur_sta IS

   l_ref_cur_sta type_ref_cur_sta;

BEGIN

   IF p_order_by = 'dname' THEN

      OPEN l_ref_cur_sta FOR

         SELECT * FROM dept ORDER BY dname;

   ELSE

     OPEN l_ref_cur_sta FOR

        SELECT * FROM dept ORDER BY deptno;

   END IF;

   RETURN l_ref_cur_sta;

END;

END;

/

1.2 Query PL/SQL in Reports

function QR_1RefCurQuery return report_static.type_ref_cur_sta is

begin

return report_static.func_sta (:p_order_by);

end;

2. Example for 'dynamic' ref cursor (dynamic WHERE)

2.1 Stored package

CREATE OR REPLACE PACKAGE report_dynamic IS

TYPE type_ref_cur_sta IS REF CURSOR RETURN dept%ROWTYPE; -- for Report Layout only

TYPE type_ref_cur_dyn IS REF CURSOR;

FUNCTION func_dyn (p_where VARCHAR2) RETURN type_ref_cur_dyn;

END;

/

CREATE OR REPLACE PACKAGE BODY report_dynamic IS

FUNCTION func_dyn (p_where VARCHAR2) RETURN type_ref_cur_dyn IS

   l_ref_cur_dyn type_ref_cur_dyn;

BEGIN

   OPEN l_ref_cur_dyn FOR

      'SELECT * FROM dept WHERE ' || NVL (p_where, '1 = 1');

   RETURN l_ref_cur_dyn;

END;

END;

/

2.2 Query PL/SQL in Reports

function QR_1RefCurQuery return report_dynamic.type_ref_cur_sta is

begin

return report_dynamic.func_dyn (:p_where);

end;

Note that Oracle Reports 3.0 / 6i needs 'static' ref cursor type for building Report Layout.

So, in the package specification we must have both ref cursor types, static for Report Layout and dynamic for ref cursor query.

7

 Also thanks to Zlatko Sirotic for this tip:

--------------------------------------------------------------

We often want to create and execute 'generic' dynamic statements in database triggers

containing :NEW, referring to new values of current records, or :OLD, referring to old values

of current records. In execution time, however, :OLD and :NEW are flagged as bad bind variables.

One workaround is to use AFTER STATEMENT triggers in the following manner:

1. Create a database package with a PL/SQL table for storing ROWID values

2. Clear the PL/SQL table in the BEFORE STATEMENT trigger

3. Populate the PL/SQL table with ROWID values in the BEFORE (or AFTER) ROW trigger

4. Read the rows in the AFTER STATEMENT trigger with dynamic PL/SQL, using ROWIDs

   from the PL/SQL table and using dynamically selected column names with this statement:

     SELECT column_name

       FROM user_tab_columns

      WHERE UPPER (table_name) = UPPER (p_table);

In this way, we can read :NEW values of columns.

Using AUTONOMOUS_TRANSACTION, we can read :OLD values as well, with one (big) restriction!

Because an autonomous transaction does not see any changes made by the main transaction,

this method does not work very well if we have 2 (or more) updates on the same row

in the same transaction.

For example, if we have DEPT row with DEPTNO = 10 and LOC = 'AAA',

then after first update (in a transaction):

  UPDATE dept SET loc = 'BBB' WHERE deptno = '10';

we have:

  OLD VALUES: 10--DEPT10--AAA

  NEW VALUES: 10--DEPT10--BBB

but after second update (in the same transaction):

  UPDATE dept SET loc = 'CCC' WHERE deptno = '10'

we have:

  OLD VALUES: 10--DEPT10--AAA (!!! not BBB)

  NEW VALUES: 10--DEPT10--CCC ..."

Example (scott.dept table):

1. DATABASE TRIGGERS (FOR UPDATE)

CREATE OR REPLACE TRIGGER bus_dept

BEFORE UPDATE ON dept

BEGIN

   plsql_table.clear;

END;

/

CREATE OR REPLACE TRIGGER bur_dept

BEFORE UPDATE ON dept

FOR EACH ROW

BEGIN

   plsql_table.populate_with_rowid (:OLD.ROWID);

END;

/

CREATE OR REPLACE TRIGGER aus_dept

AFTER UPDATE ON dept

DECLARE

   v_current_rowid ROWID;

BEGIN

   dynamic_new_old.set_table_name ('dept');

   dynamic_new_old.create_column_names;

   WHILE plsql_table.rowid_exists LOOP

      v_current_rowid := plsql_table.current_rowid;

      DBMS_OUTPUT.PUT_LINE ('OLD VALUES:');

      dynamic_new_old.display_old_values (v_current_rowid);

      DBMS_OUTPUT.PUT_LINE ('NEW VALUES:');

      dynamic_new_old.display_new_values (v_current_rowid);

      DBMS_OUTPUT.PUT_LINE ('*****');

   END LOOP;

END;

/

2. PACKAGES

CREATE OR REPLACE PACKAGE plsql_table IS

   PROCEDURE clear;

   PROCEDURE populate_with_rowid (p_rowid ROWID);

   FUNCTION rowid_exists RETURN BOOLEAN;

   FUNCTION current_rowid RETURN ROWID;

END;

/

CREATE OR REPLACE PACKAGE BODY plsql_table IS

   TYPE type_plsql_table IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

   m_plsql_table type_plsql_table;

   m_rec_number BINARY_INTEGER;

   PROCEDURE clear IS

   BEGIN

      m_rec_number := 0;

   END;

   PROCEDURE populate_with_rowid (p_rowid ROWID) IS

   BEGIN

      m_rec_number := m_rec_number + 1;

      m_plsql_table (m_rec_number) := p_rowid;

   END;

   FUNCTION rowid_exists RETURN BOOLEAN IS

   BEGIN

      RETURN (m_rec_number > 0);

   END;

   FUNCTION current_rowid RETURN ROWID IS

      v_rowid ROWID;

   BEGIN

      v_rowid := m_plsql_table (m_rec_number);

      m_rec_number := m_rec_number - 1;

      RETURN v_rowid;

   END;

END;

/

CREATE OR REPLACE PACKAGE dynamic_new_old IS

   PROCEDURE set_table_name (p_table VARCHAR2);

   PROCEDURE create_column_names;

   PROCEDURE display_old_values (p_rowid ROWID);

   PROCEDURE display_new_values (p_rowid ROWID);

END;

/

CREATE OR REPLACE PACKAGE BODY dynamic_new_old IS

   m_table VARCHAR2 (30);

   m_columns VARCHAR2 (32000);

   PROCEDURE set_table_name (p_table VARCHAR2) IS

   BEGIN

      m_table := UPPER (p_table);

   END;

   PROCEDURE create_column_names IS

      v_first_column BOOLEAN;

   BEGIN

      v_first_column := TRUE;

      FOR rec IN

         (SELECT column_name

            FROM user_tab_columns

           WHERE table_name = m_table)

      LOOP

         IF v_first_column THEN

            v_first_column := FALSE;

            m_columns := 'v_record.' || rec.column_name;

         ELSE

            m_columns := m_columns ||

               '||' || '''--''' || '|| v_record.' || rec.column_name;

         END IF;

      END LOOP;

   END;

   PROCEDURE display_values (p_rowid ROWID) IS

      v_statement VARCHAR2 (32000);

   BEGIN

      v_statement :=

      ' DECLARE '  ||

      '    v_record ' || m_table || '%ROWTYPE;' ||

      ' BEGIN' ||

      '    SELECT * INTO v_record' ||

      '      FROM ' || m_table ||

      '     WHERE ROWID = :p_rowid;' ||

      '    DBMS_OUTPUT.PUT_LINE (' || m_columns || ');' ||

      ' END;';

      EXECUTE IMMEDIATE v_statement USING p_rowid;

   END;

   PROCEDURE display_old_values (p_rowid ROWID) IS

      PRAGMA AUTONOMOUS_TRANSACTION;

   BEGIN

      display_values (p_rowid);

   END;

   PROCEDURE display_new_values (p_rowid ROWID) IS

   BEGIN

      display_values (p_rowid);

   END;

END;

/

If you need :OLD values only, you can write a simpler solution (without statement triggers and 'plsql_table' package), using 'dynamic_new_old' package and AUTONOMOUS_TRANSACTION

in the BEFORE (or AFTER) ROW trigger (but, don't forget previously noted restriction).

8

This can be done using a Java stored script as follows:

// CMLob_Java_Proc_Src.txt

// Copyright (c) 2002 Dulcian, Inc
package CMLob;

import java.io.*;
import java.lang.*;
import java.util.*;

/**
* A Class class.
* <P>
* @author Robert F. Edwards
*/
public class LobOut {

// Method ExecLobOut
public static int ExecLobOut (String in_path,
String in_hexdata,
String in_mode,
int in_len) throws Exception {
// Declare vars
String path;
String hexdata;
String filemode;
int hexlen;
boolean appendtf;
FileOutputStream f_out; // Output file
int i = 0;
int str[] = new int[3];
int value = 0;
//String retString = new String("");
// Instantiate vars
path = in_path;
hexdata = in_hexdata;
filemode = in_mode;
hexlen = in_len;
if (filemode == "W")
appendtf = false; // open new file for writing
else
appendtf = true; // open existing file for append
f_out = new FileOutputStream (path, appendtf); // Output file -- append = true

//System.out.println("Starting LobOut.ExecLobOut");
// Check parameters for null values; return if any found
if ((( path == null || hexdata == null) || filemode == null) || hexlen < 1 )
return -20000; //"Parameters are null, or are negative or zero!";
//if (path == null)
// return "Parameter path has a null value!";
//if (hexdata == null)
// return "Parameter hexdata has a null value!";
//if (filemode == null)
// return "Parameter filemode has a null value!";
//if (hexlen < 1)
// return "Parameter hexlen is negative or zero!";

// Read hex characters from data (hexdata)
while (i < hexlen) {
// Read hex value(1)
str[0] = hexdata.charAt(i);
i++;

// Read hex value(2)
str[1] = hexdata.charAt(i);
i++;

// Convert the first byte to the binary value.
if (str[0] > 64 && str[0] < 71)
str[0] = str[0] - 55;
else
str[0] = str[0] - 48;

// Convert the second byte to the binary value.
if (str[1] > 64 && str[1] < 71)
str[1] = str[1] - 55;
else
str[1] = str[1] - 48;

// Convert the hex value to binary (first & second byte).
value = str[0] * 16 + str[1];
//System.out.print(value + " "); Display binary value of each byte written

// Write the binary data to the binary file, one byte at a time.
try {
f_out.write(value);
}
catch (IOException io) {
return -20001; //"IO Exception on write: " + path;
//System.out.println("IO Exception on file write");
//io.printStackTrace();
}
} // end while

// Close the file.
try {
f_out.close(); //fclose(file_handle);
}
catch (IOException io) {
return -20002; //"IO Exception on close: " + path;
//System.out.println("IO Exception on file close");
//io.printStackTrace();
}

// Return with successful message
//System.out.println(""); // Insert linefeed after display of values
//retString = "Successful";
return 0; //retString;
} // end ExecLobOut

/* main -- @param args */
public static void main(String[] args) {
LobOut lobOut = new LobOut();
}
} // end class

-----------------------------------------------------

The PL/SQL file is shown here:

-- cm_lob_sql_faq.sql


--DROP package body cm_lob;
--DROP package cm_lob;

CREATE OR REPLACE
PACKAGE cm_lob is
-- Check in binary data
function f_checkin (in_fk_oid number, in_file_tx varchar2, in_path_tx varchar2)
return number;

-- Call spec to Java
FUNCTION f_lob_write ("in_path" IN VARCHAR2, "in_hexdata" IN VARCHAR2, "in_mode" IN VARCHAR2, "in_len" IN NUMBER)
RETURN NUMBER
AS LANGUAGE JAVA
NAME 'CMLob.LobOut.ExecLobOut(java.lang.String, java.lang.String, java.lang.String, int) return int';

-- Check out binary data - prepare data to pass to Java
function f_checkout (in_fk_oid number, in_path_tx varchar2, in_file_tx varchar2)
return number;
END; -- cm_lob
/


CREATE OR REPLACE
PACKAGE BODY cm_lob is
--
function f_checkin (in_fk_oid number, in_file_tx varchar2, in_path_tx varchar2 := null)
return number is
v_break number(2);
v_file_tx varchar2(200) := in_file_tx;
v_path_tx varchar2(200) := in_path_tx;
v_lob_id orcllob.orcllob_oid%TYPE;
file_loc bfile;
loc_exists number;
image_loc blob;
file_size number;
ver_nr number;
e_no_file_found exception;
--e_no_path exception;
BEGIN
-- Directory path is optional, but not used; path is fixed
/*-- add logic to break for last '\' - NT platform
v_break := instr( FULL_FILE_NAME_TX, '\', -1 );
IF v_break <= 0 THEN -- No path is included in file name
raise e_no_path;
END IF;
-- parse path and file names
v_path_tx := substr( FULL_FILE_NAME_TX, 1, v_break-1 );
v_file_tx := substr( FULL_FILE_NAME_TX, v_break+1 );
--
IF v_path_tx is null THEN
raise e_no_path;
END IF;
*/
-- DBMS_LOB code
-- CHECKIN is a required directory object name
-- DDL: CREATE DIRECTORY CHECKIN AS 'E:\ORA_IO\CHECKIN';
file_loc := bfilename( 'CHECKIN', v_file_tx );
loc_exists := DBMS_LOB.fileexists( file_loc );
IF loc_exists = 0 THEN -- passed image file name does not exist
RAISE e_no_file_found;
END IF;

DBMS_LOB.fileopen( file_loc );
file_size := DBMS_LOB.getlength( file_loc );
-- Get ID# for new lob
SELECT orcllob_seq.nextval INTO v_lob_id FROM dual;
-- Get current orclform version nr - 3/25/02 RE
SELECT version_nr INTO ver_nr
FROM orclform
WHERE orclform_oid = in_fk_oid;
-- Increment version number - 3/25/02 RE
ver_nr := ver_nr + 1;
-- Insert new lob into orcllob
INSERT INTO orcllob (orcllob_oid, class_cd, fk_oid, version_nr, form_lob)
VALUES (v_lob_id, 'ORCLForm' , in_fk_oid, ver_nr, EMPTY_BLOB)
RETURNING form_lob INTO image_loc;
DBMS_LOB.loadfromfile( image_loc, file_loc, file_size );
-- Update version nr for orclform - 3/25/02 RE
UPDATE orclform
SET version_nr = ver_nr
WHERE orclform_oid = in_fk_oid;
--
DBMS_LOB.fileclose( file_loc );
RETURN v_lob_id;

EXCEPTION
--WHEN e_no_path THEN
-- dbms_output.put_line('Passed file path does not exist');
-- RETURN -99999;
WHEN e_no_file_found THEN
dbms_output.put_line('Passed filename does not exist');
RETURN -99901;
WHEN others THEN
dbms_output.put_line(substr('OTHER ERROR: '||sqlerrm,1,255));
RETURN -99999;
END; -- f_checkin

-- Private function to format path for Java stored procedure
function f_java_path (in_path_tx varchar2)
return varchar2 is
len number := length(in_path_tx);
ptr number := 1;
hold_ch varchar2(1) := null;
out_tx varchar2(200);
begin
dbms_output.put_line(substr('in_path_tx before = '||in_path_tx,1,255));
while ptr <= len loop
hold_ch := substr(in_path_tx, ptr ,1);
if hold_ch != '\' then
out_tx := out_tx || hold_ch;
else
out_tx := out_tx || '\\';
end if;
ptr := ptr + 1;
end loop;
dbms_output.put_line(substr('in_path_tx after = '||out_tx,1,255));
return out_tx;
end;

--
function f_checkout (in_fk_oid number, in_path_tx varchar2, in_file_tx varchar2)
return number is
full_file_tx varchar2(200);
i1 blob;
len number;
max_len number := 16000; -- length of data to pass to Java procedure (*2=32K)
my_vr raw(16000); -- must = max_len
i2 number;
i3 number := max_len;
ret number;
ver_nr number;
e_lob exception;
begin
-- Assemble path and file components and format for Java
if substr(in_path_tx,-1) != '\' then
full_file_tx := in_path_tx ||'\'|| in_file_tx; -- insert final '\'
else
full_file_tx := in_path_tx || in_file_tx;
end if;
full_file_tx := f_java_path(full_file_tx);
-- Get orclform version nr - 3/25/02 RE
SELECT version_nr INTO ver_nr
FROM orclform
WHERE orclform_oid = in_fk_oid;
-- Get the blob locator
SELECT form_lob INTO i1
FROM orcllob
WHERE fk_oid = in_fk_oid -- 3/20/02 - was: orclform_oid = in_fk_oid
AND version_nr = ver_nr; -- 3/25/02 - added version_nr
-- find the length of the blob column
len := DBMS_LOB.GETLENGTH(i1);
--dbms_output.put_line('Length of the Column : ' || to_char(len));
i2 := 1; -- init offset
if len < max_len then
DBMS_LOB.READ(i1,len,i2,my_vr); -- (blob var, length, offset, raw var)
-- call to java routine here
ret := f_lob_write(full_file_tx, rawtohex(my_vr), 'W', 2*len); -- Open new file
dbms_output.put_line('Initial Read - len < max_len; length='||to_char(len));
if ret < 0 then
raise e_lob;
end if;
-- You have to convert the data to rawtohex format.
-- Directly sending the buffer data will not work.
-- That is the reason why we are sending the length as the double the size of the data read.
else -- If the col length is >= max_len
DBMS_LOB.READ(i1,i3,i2,my_vr); -- (blob var, length, offset, raw var)
ret := f_lob_write(full_file_tx, rawtohex(my_vr), 'W', 2*i3); -- Open new file
dbms_output.put_line('Initial Read - len >= max_len; length='||to_char(len));
if ret < 0 then
raise e_lob;
end if;
end if;
i2 := i2 + max_len; -- increment offset
while (i2 < len) loop -- loop till entire binary object is fetched
DBMS_LOB.READ(i1,i3,i2,my_vr); -- (blob var, length, offset, raw var)
ret := f_lob_write(full_file_tx, rawtohex(my_vr), 'A', 2*i3); -- Apend to file
dbms_output.put_line('While loop - start of block='||to_char(i2));
if ret < 0 then
raise e_lob;
end if;
i2 := i2 + max_len; -- increment for next loop
end loop;
return 0;
exception
when e_lob then
return ret;
when others then
return -999;
end; -- f_checkout

end; -- cm_lob
/

 

9

M. K. Shrivastava asked:

I want to optimize some of our long running queries. Even after going through the documentation, it is not clear to me that which query hint (FULL, INDEX, STAR, RULE etc.) should be used in a particular query. Could you please write me some Hints/Thumb rules on this topic?

The basics don't even involve hints.  As the Cost Based Optimizer (CBO) has become smarter over time, the need for hints continues to decrease.  We use them rarely now.

Here is my top 10 list of tuning tips:

1. Query tuning is a dark art. Sometimes no matter what you do, it just doesn't make any sense.  I was on a project where the following happened:  I had a query that had run well everywhere just fine (sub second), at a client site it took almost a minute. 

Select 'None', null from dual

Union all

Select ...

was the structure of the query.

When I removed the

"Select 'None', null from dual

Union all" 

and ran only "Select ...", it ran fine.

I have always believed that UNION ALL simply ran the 2 queries in sequence with no additional overhead.  So much for conventional tuning wisdom.

2. Learn to read an ExplainPlan, TK Prof output.  All nice tools (SQL Navigator, Toad) include ExplainPlan as part of the product so it is less of a hassle.  This will tell you where full table scans are causing problems. Many Oracle tuning books cover this topic and you can also read the Help files.

3. The main trivial thing to do is make sure you have indexes on all FK columns in the joins.

4. Beware of WHERE clauses with functions.  If you put an index on a date and then saye  'where trunk(mydate) = trunk(sysdate)' you will not use the index,.

Instead you can write mydate between trunk(sysdate) and trunk(sysdate) + .9999999 and it will use the index.  Alternatively, you can put an index directly on the function trunk(mydate).

5. One of the things we have started using often is replacing queries with multi-table joins with nested SELECT from SELECT commands.  It makes the queries look a little odd, but for things that really needed to be fast, this tends to give the best performance.

6. For learning to use hints, I would suggest Rich Niemiec's book Oracle Performance Tuning Tips and Techniques (Oracle Press, 1999) .  It includes useful discussions about many hints. 

7. Don't believe the conventional wisdom.  When the query still doesn't run fast, try everything.  You may find something that works that doesn't even make sense.

8. Before you start, analyze the schema; otherwise the CBO will make stupid decisions.

9. Don't use multi-table views in joins.  The CBO tends to say "What the heck, I am confused.  I will just full table scan EVERYTHING!"

10. You don't have to get really complicated.  Hints that force different join methods never really matter.  99% of all queries can be made to work acceptably with relatively simple stuff.  It is only when you are trying to eek out every last millisecond that you need to resort to the subtle stuff.

10

Thanks to Zlatko Sirotic from Croatia for this tip:

How can you avoid a "vicious circle" (a closed loop) in a multi-tree recursive

structure (any object may have any number of children and, at most, one parent) or in a

single-tree recursive structure (exactly one object would have no parent, and all other

objects would have exactly one parent)?

For example - To forbid that in an EMP table one worker is a supervisor of another and that the other is subordinate to the first one. It is better to validate this in the database rather than a client application. Unfortunately, this is not so easy to do.

To illustrate: Add some records in a DEPT table:

DELETE emp

/

INSERT INTO emp (empno, ename, mgr) VALUES (1, 'EMP 1', NULL)

/

INSERT INTO emp (empno, ename, mgr) VALUES (2, 'EMP 2', 1)

/

INSERT INTO emp (empno, ename, mgr) VALUES (3, 'EMP 3', 1)

/

INSERT INTO emp (empno, ename, mgr) VALUES (4, 'EMP 4', 2)

/

INSERT INTO emp (empno, ename, mgr) VALUES (5, 'EMP 5', 2)

/

INSERT INTO emp (empno, ename, mgr) VALUES (6, 'EMP 6', 3)

/

INSERT INTO emp (empno, ename, mgr) VALUES (7, 'EMP 7', 3)

/

Then write the first version of the package:

CREATE OR REPLACE PACKAGE emp_closed_loop IS

   PROCEDURE clear_plsql_tab;

   PROCEDURE write_plsql_tab (

      p_empno emp.empno%TYPE,

      p_mgr   emp.mgr%TYPE);

   PROCEDURE test;

END emp_closed_loop;

/

CREATE OR REPLACE PACKAGE BODY emp_closed_loop IS

   TYPE rec_t IS RECORD (

      empno emp.empno%TYPE,

      mgr   emp.mgr%TYPE);

   TYPE plsql_tab_t IS TABLE OF rec_t INDEX BY BINARY_INTEGER;

   m_plsql_tab plsql_tab_t;

   m_rows      BINARY_INTEGER;

   PROCEDURE clear_plsql_tab IS

   BEGIN

      m_rows := 0;

   END;

   PROCEDURE write_plsql_tab (

      p_empno emp.empno%TYPE,

      p_mgr   emp.mgr%TYPE)

   IS

   BEGIN

      m_rows := m_rows + 1;

      m_plsql_tab (m_rows).empno := p_empno;

      m_plsql_tab (m_rows).mgr   := p_mgr;

   END;

   PROCEDURE test IS

      l_mgr   emp.mgr%TYPE;

      l_empno emp.empno%TYPE;

   BEGIN

      FOR i IN 1..m_rows LOOP

         l_empno := m_plsql_tab (i).empno;

         l_mgr   := m_plsql_tab (i).mgr;

         WHILE l_mgr IS NOT NULL LOOP

            SELECT mgr INTO l_mgr

              FROM emp

             WHERE empno = l_mgr;

            IF l_mgr = l_empno THEN

               RAISE_APPLICATION_ERROR (-20003, 'Closed loop!');

            END IF;

         END LOOP;

      END LOOP;

   END;

END emp_closed_loop;

/

We can write the package which uses CONNECT BY clause

(but to continue we would use the first version that would be upgraded):

CREATE OR REPLACE PACKAGE BODY emp_closed_loop IS

   TYPE rec_t IS RECORD (

      empno emp.empno%TYPE,

      mgr   emp.mgr%TYPE);

   TYPE plsql_tab_t IS TABLE OF rec_t INDEX BY BINARY_INTEGER;

   m_plsql_tab plsql_tab_t;

   m_rows      BINARY_INTEGER;

   PROCEDURE clear_plsql_tab IS

   BEGIN

      m_rows := 0;

   END;

   PROCEDURE write_plsql_tab (

      p_empno emp.empno%TYPE,

      p_mgr   emp.mgr%TYPE)

   IS

   BEGIN

      m_rows := m_rows + 1;

      m_plsql_tab (m_rows).empno := p_empno;

      m_plsql_tab (m_rows).mgr   := p_mgr;

   END;

   PROCEDURE test IS

      closed_loop EXCEPTION;

      -- ORA-01436: CONNECT BY loop in user data

      PRAGMA EXCEPTION_INIT (closed_loop, -1436);

      l_mgr   emp.mgr%TYPE;

      l_empno emp.empno%TYPE;

      l_dummy NUMBER;

   BEGIN

      FOR i IN 1..m_rows LOOP

         l_empno := m_plsql_tab (i).empno;

         l_mgr   := m_plsql_tab (i).mgr;

         SELECT COUNT (*) INTO l_dummy

           FROM emp

          WHERE empno = l_empno

          START WITH empno = l_mgr CONNECT BY PRIOR mgr = empno;

      END LOOP;

   EXCEPTION

      WHEN closed_loop THEN

         RAISE_APPLICATION_ERROR (-20003, 'Closed loop!');

   END;

END emp_closed_loop;

/

Database triggers:

CREATE OR REPLACE TRIGGER bir_emp

   BEFORE INSERT ON emp

   FOR EACH ROW

BEGIN

   IF :NEW.empno = :NEW.mgr THEN

      RAISE_APPLICATION_ERROR (-20002, 'Employee can''t be managed by itself!');

   END IF;

END;

/

CREATE OR REPLACE TRIGGER bus_emp

   BEFORE UPDATE ON emp

BEGIN

   emp_closed_loop.clear_plsql_tab;

END;

/

CREATE OR REPLACE TRIGGER bur_emp

   BEFORE UPDATE ON emp

   FOR EACH ROW

BEGIN

   IF :NEW.empno <> :OLD.empno THEN

      RAISE_APPLICATION_ERROR (-20001, 'NEW EMPNO <> OLD EMPNO');

   END IF;

   IF :NEW.empno = :NEW.mgr THEN

      RAISE_APPLICATION_ERROR (-20002, 'Employee can''t be managed by itself!');

   END IF;

   IF :NEW.mgr IS NOT NULL AND :NEW.mgr <> NVL (:OLD.mgr, 0) THEN

      emp_closed_loop.write_plsql_tab (

         p_empno => :OLD.empno,

         p_mgr   => :NEW.mgr);

   END IF;

END;

/

CREATE OR REPLACE TRIGGER aus_emp

   AFTER UPDATE ON emp

BEGIN

   emp_closed_loop.test;

END;

/

If we try to make a next UPDATE we get an error (and this is OK):

UPDATE emp

   SET mgr = 4

 WHERE empno = 1

/

ERROR at line 1:

ORA-20003: Closed loop!

ORA-06512: at "SCOTT.EMP_CLOSED_LOOP", line 40

ORA-06512: at "SCOTT.AUS_EMP", line 2

ORA-04088: error during execution of trigger 'SCOTT.AUS_EMP'

It seems that this solution works well. Unfortunately, it works only in a single user mode.

In a multi-user mode an error is possible as followed:

-- SESSION 1

UPDATE emp

   SET mgr = 3

 WHERE empno = 2

/

-- SESSION 2

UPDATE emp

   SET mgr = 2

 WHERE empno = 3

/

Consequently, the loop occurrence was permitted by the database.

We must not forget to execute a ROLLBACK:

-- SESSION 1

ROLLBACK

/

-- SESSION 2

ROLLBACK

/

It is obvious that we have to find another solution for the multi-user mode.

The main idea is, while checking the circularity, also check to see if the current record is locked. If it is locked, we might assume that the rules are violated. But how to check to see whether or not the record is locked? If we use SELECT FOR UPDATE, the record locked by us will be locked until the end of the transaction and others will be unable to work. This solution is completely impractical. Therefore we might try to make the lock action an autonomous transaction in order to unlock the record after checking:

CREATE OR REPLACE PACKAGE BODY emp_closed_loop IS

   TYPE rec_t IS RECORD (

      empno emp.empno%TYPE,

      mgr   emp.mgr%TYPE);

   TYPE plsql_tab_t IS TABLE OF rec_t INDEX BY BINARY_INTEGER;

   m_plsql_tab plsql_tab_t;

   m_rows      BINARY_INTEGER;

   PROCEDURE clear_plsql_tab IS

   BEGIN

      m_rows := 0;

   END;

   PROCEDURE write_plsql_tab (

      p_empno emp.empno%TYPE,

      p_mgr   emp.mgr%TYPE)

   IS

   BEGIN

      m_rows := m_rows + 1;

      m_plsql_tab (m_rows).empno := p_empno;

      m_plsql_tab (m_rows).mgr   := p_mgr;

   END;

   PROCEDURE test_lock (p_mgr emp.mgr%TYPE);

   PROCEDURE test IS

      l_mgr   emp.mgr%TYPE;

      l_empno emp.empno%TYPE;

   BEGIN

      FOR i IN 1..m_rows LOOP

         l_empno := m_plsql_tab (i).empno;

         l_mgr   := m_plsql_tab (i).mgr;

         WHILE l_mgr IS NOT NULL LOOP

            test_lock (l_mgr);

            SELECT mgr INTO l_mgr

              FROM emp

             WHERE empno = l_mgr;

            IF l_mgr = l_empno THEN

               RAISE_APPLICATION_ERROR (-20003, 'Closed loop!');

            END IF;

         END LOOP;

      END LOOP;

   END;

   PROCEDURE test_lock (p_mgr emp.mgr%TYPE) IS

      PRAGMA AUTONOMOUS_TRANSACTION;

      l_dummy NUMBER;

   BEGIN

      SELECT 1 INTO l_dummy

        FROM emp

       WHERE empno = p_mgr

         FOR UPDATE NOWAIT;

      ROLLBACK;

   EXCEPTION

      WHEN OTHERS THEN

         -- ORA-00054: resource busy and acquire with NOWAIT specified

         IF SQLCODE = -54 THEN

            RAISE_APPLICATION_ERROR (-20004, 'Maybe closed loop!');

         ELSE

            RAISE;

         END IF;

   END;

END emp_closed_loop;

/

Now it works well:

-- SESSION 1

UPDATE emp

   SET mgr = 3

 WHERE empno = 2

/

-- SESSION 2

UPDATE emp

   SET mgr = 2

 WHERE empno = 3

/

ERROR at line 1:

ORA-20004: Maybe closed loop!

ORA-06512: at "SCOTT.EMP_CLOSED_LOOP", line 64

ORA-06512: at "SCOTT.EMP_CLOSED_LOOP", line 37

ORA-06512: at "SCOTT.AUS_EMP", line 2

ORA-04088: error during execution of trigger 'SCOTT.AUS_EMP'

-- SESSION 1

ROLLBACK

/

Unfortunately this doesn't work well, because the autonomous transaction (because of its autonomous character) finds the locked record (locked by the main transaction) and "thinks" that the rule is violated (and it isn't):

-- two UPDATEs in the same session

UPDATE emp

   SET mgr = 2

 WHERE empno = 6

/

UPDATE emp

   SET mgr = 6

 WHERE empno = 7

/

ERROR at line 1:

ORA-20004: Maybe closed loop!

ORA-06512: at "SCOTT.EMP_CLOSED_LOOP", line 64

ORA-06512: at "SCOTT.EMP_CLOSED_LOOP", line 37

ORA-06512: at "SCOTT.AUS_EMP", line 2

ORA-04088: error during execution of trigger 'SCOTT.AUS_EMP'

ROLLBACK

/

The solution is to use the PL/SQL tip "Simulating ROLLBACK TO SAVEPOINT Behavior in a Database Trigger" (www.dulcian.com/ Conference Papers and Slide Presentations/PL/SQL):

CREATE DATABASE LINK local_db_link

   CONNECT TO scott IDENTIFIED BY tiger using 'local_alias' -- alias for your local database

/

CREATE OR REPLACE PACKAGE emp_closed_loop IS

   PROCEDURE clear_plsql_tab;

   PROCEDURE write_plsql_tab (

      p_empno emp.empno%TYPE,

      p_mgr   emp.mgr%TYPE);

   PROCEDURE test;

   PROCEDURE test_lock (p_mgr emp.mgr%TYPE);

END emp_closed_loop;

/

CREATE OR REPLACE PACKAGE BODY emp_closed_loop IS

   TYPE rec_t IS RECORD (

      empno emp.empno%TYPE,

      mgr   emp.mgr%TYPE);

   TYPE plsql_tab_t IS TABLE OF rec_t INDEX BY BINARY_INTEGER;

   m_plsql_tab plsql_tab_t;

   m_rows      BINARY_INTEGER;

   PROCEDURE clear_plsql_tab IS

   BEGIN

      m_rows := 0;

   END;

   PROCEDURE write_plsql_tab (

      p_empno emp.empno%TYPE,

      p_mgr   emp.mgr%TYPE)

   IS

   BEGIN

      m_rows := m_rows + 1;

      m_plsql_tab (m_rows).empno := p_empno;

      m_plsql_tab (m_rows).mgr   := p_mgr;

   END;

   PROCEDURE test IS

      l_mgr   emp.mgr%TYPE;

      l_empno emp.empno%TYPE;

   BEGIN

      FOR i IN 1..m_rows LOOP

         l_empno := m_plsql_tab (i).empno;

         l_mgr   := m_plsql_tab (i).mgr;

         WHILE l_mgr IS NOT NULL LOOP

            BEGIN

               emp_closed_loop.test_lock@local_db_link (l_mgr);

            EXCEPTION

               WHEN OTHERS THEN

                  IF SQLCODE = -20999 THEN

                     NULL;

                  ELSE

                     RAISE;

                  END IF;

            END;

            SELECT mgr INTO l_mgr

              FROM emp

             WHERE empno = l_mgr;

            IF l_mgr = l_empno THEN

               RAISE_APPLICATION_ERROR (-20003, 'Closed loop!');

            END IF;

         END LOOP;

      END LOOP;

   END;

   PROCEDURE test_lock (p_mgr emp.mgr%TYPE) IS

      l_dummy NUMBER;

   BEGIN

      SELECT 1 INTO l_dummy

        FROM emp

       WHERE empno = p_mgr

         FOR UPDATE NOWAIT;

      RAISE_APPLICATION_ERROR (-20999, 'This is for unlocking record');

   EXCEPTION

      WHEN OTHERS THEN

         -- ORA-00054: resource busy and acquire with NOWAIT specified

         IF SQLCODE = -54 THEN

            RAISE_APPLICATION_ERROR (-20004, 'Maybe closed loop!');

         ELSE

            RAISE;

         END IF;

   END;

END emp_closed_loop;

/

This solution could notify a rule violation even when this rule is not really violated. But that is inevitable since one session doesn't know precisely what the other does:

-- SESSION 1

UPDATE emp

   SET mgr = 6

 WHERE empno = 2

/

-- SESSION 2

UPDATE emp

   SET mgr = 5

 WHERE empno = 7

/

ERROR at line 1:

ORA-20004: Maybe closed loop!

ORA-06512: at "SCOTT.EMP_CLOSED_LOOP", line 70

ORA-06512: at "SCOTT.EMP_CLOSED_LOOP", line 42

ORA-06512: at "SCOTT.AUS_EMP", line 2

ORA-04088: error during execution of trigger 'SCOTT.AUS_EMP'

11

Tom Berthoff asked: Why is "select *" a bad thing?

Depends on where it is.

 If you are doing INSERT INTO AS SELECT * FROM,  if the table changes you are in big trouble.

However, if you are using it in a cursor and you are using all the columns in the table, then it certainly can be used  to make cleaner code that is easier to maintain when table  structures change.

I also read Steven Feuerstein’s paper of top 10 stupid things, and maybe I am really stupid but I am not opposed to some of them.

For example, I will use an exception to bypass a routine.  It is not really an exception so I am using an exception to handle code logic but it is sort of an exception (I am checking initial conditions on a routine). 

 I just wrote a paper on this for a presentation and I found that a number of the "rules" were breakable.

Kurt asked:

Isn't an exception to bypass a routine as problematic as using GOTO - a bit more restricted (can jump only to the Exception code section, though anonymous blocks can modify that to an extent) but still not as structured as it could be?

Consider the following example:

Function f_getsomething (i_id number) return varchar2 is

  e_noId exception;

  v_out_tx varchar2(200);

Begin

  if i_id is null then

    raise e_noId;

  end if;

   ...

   Big complex routine to figure out what to return that should be bypassed if the ID is null.

   ...

   Return v_out_tx;

Exception

  when others then

    return  'no id';

End;

--

I can put a big IF THEN around the whole routine. I can make a bypass Boolean that my exception populates, but that is just as bad. I could break it into 2 routines. All of those would be less clear than using the above technique.

We need to be careful about setting best practice rules that then become set in stone when there may be very valid times to ignore them.  Others, like the ever popular "WHEN OTHERS THEN NULL" seem to be a valid acid test for incompetence.

The goal is to create bug free code that performs adequately.  Once we have that, we should try to maximize:

1) readability

2) maintainability

3) stability    

The "rules" we come up with are supposed to reflect the best practices we have discovered in order to achieve the above goals. If we can write code that performs better on the criteria listed above, then the rules are wrong (or at least have exceptions).

In the example above, I came up with this technique in response to building some functions that had several input parameters and the code was a few hundred lines long. Raising exceptions to support the initial condition checks made for a very clean routine.