Unnecessary Locking on Parent Table due to missing index on foreign key column in Child Table

If index not exists on foreign key column of a child table then it will create locks on the parent table when DML operation performed on child table.

Demonstration:

Create following sample tables. order_header is parent and order_detail is child table.


create table order_header (header_id number, customer varchar2(20))

alter table order_header add constraint pk_order_header  primary key (header_id)

create table order_detail (header_id number references order_header (header_id) on delete cascade , line_id number);

insert into order_header values (1,'a1');
insert into order_header values  (2,'a2');
insert into order_header values  (3,'a3');

insert into order_detail values  (1,1);
insert into order_detail values  (2,1);

commit;


Now, open two sessions to the database,

From session-1, delete a record from child table.
From session-2, delete a record from parent table. This transaction is going to be locked until the transaction in session-1 is either committed or rolled back.

To avoid this lock it is recommend to create a index on child table column.

create index order_detail_idx on order_detail (header_id);

Now, if we try the same delete statements, we wont see locking any more....

PIPELINED Function vs Normal Function Use Case in Oracle

Suppose we wanted to return values from Function in a nested table type format then pipelined table functions will give us better performance when compared with normal functions.

PGA memory consumption will be less in case of pipelined functions when compared it with normal functions


Example:
Create Object Type

create or replace type test_obj as object (id number, dsc varchar2(100));

Create Table Type

create or replace type test_obj_tab as table of test_obj;

Create Normal Function

CREATE OR REPLACE FUNCTION test_normal (p_val IN NUMBER)
   RETURN test_obj_tab
AS
   v_test_obj_tab   test_obj_tab := test_obj_tab ();
BEGIN
   FOR i IN 1 .. p_val
   LOOP
      v_test_obj_tab.EXTEND;
      v_test_obj_tab (v_test_obj_tab.LAST) := test_obj (i, 'Number-' || i);
   END LOOP;

   RETURN v_test_obj_tab;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (' error-  ' || SQLERRM);

END;

Create PIPELINED Function

CREATE OR REPLACE FUNCTION test_pipe (p_val IN NUMBER)

   RETURN test_obj_tab pipelined 

AS

   v_test_obj_tab   test_obj_tab := test_obj_tab ();

BEGIN

   FOR i IN 1 .. p_val

   LOOP

      pipe row (test_obj (i, 'Number-' || i));

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (' error-  ' || SQLERRM);

END;

Testing

select * from
table(test_normal (100));

select * from
table (test_pipe(100));

PIPELINED Table Functions To load Data Files into Oracle Table

To load the data files (.csv) into Oracle Tables we have following options

1) Use SQLLOADER
2) Use External Table
3) Use UTL_FILE

The traditional or old approach is using SQLLOADER to perform the file loads.

External Table is the new concept and using pipelined table functions in combination with external tables will give huge performance benefit while loading huge data files.

Code is created to load the file using either of these methods and posted in here.

DOWNLOAD

Useful Oracle SQL Info

     1.Trim Example

SELECT TRIM (' 7'),

       TRIM (' 777 77  '),

       TRIM (' ' FROM '  7 7 '),

       TRIM (LEADING '0' FROM '000123'),

       TRIM (TRAILING '1' FROM 'Tech1'),

       TRIM (BOTH '1' FROM '123Tech111')

  FROM DUAL

        2. Sql to add seconds, minutes, hours to current date

SELECT SYSDATE,

       SYSDATE + (1 / (24 * 60 * 60)) "1Sec Addition",

       sysdate + interval '1' second,

       SYSDATE + (1 / (24 * 60)) "1Min Addition",

       sysdate + interval '1' minute,

       SYSDATE + (1 / 24) "1HR Addition",

       sysdate + interval '1' hour

  FROM DUAL

1 3.Converting string into rows

with t as (select 'abcd,123,defoifcd,87765' as str from dual)

    select level as n, regexp_substr(str,'[^,]+',1,level) as val

    from   t

   connect by regexp_substr(str,'[^,]+',1,level) is not null

    4.Converting string into columns

with t as (select 'abcd,123,defoifcd,87765' as str from dual)

    select max(decode(level,1,regexp_substr(str,'[^,]+',1,level))) as val1

          ,max(decode(level,2,regexp_substr(str,'[^,]+',1,level))) as val2

          ,max(decode(level,3,regexp_substr(str,'[^,]+',1,level))) as val3

          ,max(decode(level,4,regexp_substr(str,'[^,]+',1,level))) as val4

          ,max(decode(level,5,regexp_substr(str,'[^,]+',1,level))) as val5

   from   t

  connect by regexp_substr(str,'[^,]+',1,level) is not null

    5.Listagg Test

create table dept_test

(

  dno       number,

  dname     varchar2(100 byte),

  location  varchar2(100 byte)

)

set define off;

insert into dept_test   (dno, dname, location) values   (1, 'Accounts', 'NY');

insert into dept_test   (dno, dname, location) values   (2, 'Delivery', 'NJ');

insert into dept_test   (dno, dname, location) values   (3, 'Customer Support', 'NJ');

insert into dept_test   (dno, dname, location) values   (4, 'Billing', 'NY');

insert into dept_test   (dno, dname, location) values   (5, 'Payments', 'NY');

commit;

select * from  dept_test

select location, listagg (dname,',') within group (order by dname) dept_list

from  dept_test

group by location

    6. Notepad++ add quotes at begin & end of each line in a file

7.Convert String with comma separated values into Columns Using SQL

1.       select * from  test

select  c1, trim(regexp_substr(c2,'[^,]+',1,level)) c2

from   test

connect by regexp_substr(c2,'[^,]+',1,level) is not null

and c2 = prior c2

and c1 = prior c1

and prior sys_guid() is not null

order by c1

Useful Unix Commands

1.Unix command to display column -3 info from file

Suppose the sample file (test4.txt) is as follows

col1|gcol2|col3|col4|col5

r1c1|gr1c2|r1c3|r1c4|r1c5

r2c1|gr2c2|r2c3|r2c4|r2c5

r3c1|gr3c2|r3c3|r3c4|r3c5

r4c1|gr4c2|r4c3|r4c4|r4c5

To display column-3 use following command

awk -F '|' '{print $3}' test4.txt

-F -> Field separator

$3 -> column 3

Or use cut command

cut -d'|' -f3 test4.txt

-d -> delimitor

-f3 -> column 3 values

2.Sed command to find and replace a character

Suppose file (test_1.txt) exists with below text and we want to replace pipe | with space

col1|col2|col3|col4|col5

r1c1|r1c2|r1c3|r1c4|r1c5

r2c1|r2c2|r2c3|r2c4|r2c5

r3c1|r3c2|r3c3|r3c4|r3c5

r4c1|r4c2|r4c3|r4c4|r4c5

sed command as follows

sed 's/\|/ /g' test_1.txt > t5.txt

Following is the command if we open the .txt file in vi editor

:%s/\|/ /g

3. Case Example in Unix

while getopts d:s:m: arg

do

case $arg in

dl)

date_list="$OPTARG"

;;

sl)

stage_list="$OPTARG"

;;

rm)

run_mode="$OPTARG"

;;

\?)

echo "Usage : $0 [-dl from_to_dates] [-sl from_to_stage] [-rm run_mode]"

echo "Ex: $0 -dl 01-jan-2001,01-dec-2001 -sl 1,2 -rm NORMAL"

exit 2

;;

esac

done

echo date_list $date_list

echo stage_list $stage_list

echo run_mode $run_mode

4. Unix IF ELSE Test

UNIX script to test the variable string is null or not

#! /usr/bin/ksh

run_mode="RERUN"

if [ "${run_mode}" = "" ]; then

echo With inif 

else

echo "${run_mode}"

fi

5. UNIX command to find files owned by user

find / -user rama 2>/dev/null | ls –ltr

6. Unix command to remove blank lines

sed '/^\s*$/d' 1.txt > 2.txt

7. IF ELSE Logic in shell script … search for a string in a filename

#!/bin/ksh

for filename in *file*; do

   #echo $filename

   fn=$filename

   if [ `ls $fn | grep -E file1` ]

   then

   echo "in if filename1 is -> $fn"

   elif [ `ls $fn | grep -E file2` ]

   then

   echo "in if filename2 is -> $fn"

   elif [ `ls $fn | grep -E file3` ]

   then

   echo "in if filename3 is -> $fn"     

   fi

done

8. Find command in Unix

find / -type f -exec grep -l "a_nwm_utt_data_mth" {} \;  2>/dev/null

find / -name hostway* 2>/dev/null

windows/dos search folder for  a text

findstr /n /i /c:"stg_ool_outage_utt_dly_chc" *

9.AWK in UNIX examples

GET_FROM_DATE="#01-jun-2015 05:50:31#1433152231"

echo $GET_FROM_DATE

#01-jun-2015 05:50:31#1433152231

FROM_DATE=`echo $GET_FROM_DATE | awk -F"#" '{ print $2 }'`

echo $FROM_DATE

01-jun-2015 05:50:31

FROM_DATE_EPOCH=`echo $GET_FROM_DATE | awk -F"#" '{ print $3 }'`

echo $FROM_DATE_EPOCH

1433152231

-F is a field separator

10.CUT In Unix 

Below displays the column-2 value considering # as a field separator

a='#2#'

echo $a

#2#

echo $a | cut -f2 -d '#'

2

I hope this info helps you :). Good Luck & Happy coding :) :)