Thursday, January 21, 2016

Oracle Points -- Part2



Q1) Inner join vs equi join
Equi join is a traditional Oracle join. We use = operator in it
select * from emp , dept where emp.deptno = dept.deptno
Inner join is a ANSI standard.
select * from emp inner join dept  using (deptno)
Q2) primary key vs foreign key vs unique key
PK
FK
Unique
More than one in Table
No
Yes
Yes
Can have NULLs
No
Yes
Yes

Q3) where vs having clauses
Where clause doesn’t work with aggregate functions like SUM, MAX etc… but having clause can be used with aggregate functions
Where clause is a row filter and having clause can work as a additional filter when use in combination with where
Q4) Why Index? How Many Types of Indexes?
Most commonly used index is B-tree index. B-tree index stores column values.
Hash table indexes works efficiently in case of string comparison using equality operator. Hash indexes are not stored values in sorted order. We can’t use Hash indexes with other than = operator
Bitmap index can be used for small tables
Q5) self join vs inner join vs left outer join
Self join
select *
from emp_s e, emp_s m
where e.mgr = m.empno   à doesn’t display employees with their manager as null
inner join
select *
from emp_s e inner join emp_s m
on( e.mgr = m.empno)   à doesn’t display employees with their manager as null
outer join
select *
from emp_s e left join emp_s m
on (e.mgr = m.empno)   à display employees with their manager as null + not null

Q6) How to get distinct records
            Using DISTINCT
                        select distinct customer_no, count(distinct customer_no) over ()
from oeorder_header

            Using UNIQUE
                        select unique customer_no, count(unique customer_no) over ()
from oeorder_header

            Using Group By
                        select customer_no
from oeorder_header
group by customer_no


Posted by at No comments:

FORALL BULK COLLECT Performance Test in Oracle



Examples to Test the BULK Collect:
Example-1:
Test to compare the performance of populating plsql table type variable using for loop cursor and bulk collect.
declare
    type bulk_test_mah_tab is table of bulk_test_mah%rowtype;
    v_bulk_test_mah_tab bulk_test_mah_tab := bulk_test_mah_tab();
    v_start_time  pls_integer;
begin
    v_start_time :=dbms_utility.get_time;

    for i in (select * from bulk_test_mah) loop
        v_bulk_test_mah_tab.extend; -- to increment one cell to hold new values       
        v_bulk_test_mah_tab (v_bulk_test_mah_tab.last) := i;
    end loop;
    dbms_output.put_line ('Time taken by for loop -> ' || (dbms_utility.get_time - v_start_time ) ||' for rows -'|| v_bulk_test_mah_tab.count);
    rollback;

    v_start_time :=dbms_utility.get_time;
    select * bulk collect into v_bulk_test_mah_tab from bulk_test_mah;
    dbms_output.put_line ('Time taken by bulk collect ->' || (dbms_utility.get_time - v_start_time) || ' for rows -' || v_bulk_test_mah_tab.count);

exception
    when others then
        dbms_output.put_line ('error -' || sqlerrm );
end;

output1:
Time taken by for loop -> 30 for rows -57958
Time taken by bulk collect ->10 for rows -57958

Conclusion -1: It is proven with above example-1 that populating plsql table type with bulk collect is much more efficient than populating it using for loop.
------------------------------------------------------------------------------------------------
Example-2:
Test to compare the time taken by select bulk collect into and for all statement with DML associated with it.
declare
    st_time pls_integer ;
    type ord_mah_tab is table of ord_mah.order_no%type;
    v_ord_mah_tab ord_mah_tab;
begin
    st_time := dbms_utility.get_time;
    dbms_output.put_line ('st_time 1 is -> ' || st_time );

select order_no bulk collect into v_ord_mah_tab from ord_mah;
dbms_output.put_line ('Time taken for select with bulk collect ->' || (dbms_utility.get_time - st_time ) );

    st_time := dbms_utility.get_time;
    dbms_output.put_line ('st_time 2 is -> ' || st_time );

    forall i in v_ord_mah_tab.first..v_ord_mah_tab.count
    update ord_mah set customer_po_no = substr(customer_po_no,1,7) where order_no = v_ord_mah_tab(i);
    dbms_output.put_line ('Time taken for exeuction using forall  ->' || (dbms_utility.get_time - st_time ) );
    rollback;

exception
    when others then
        dbms_output.put_line ( dbms_utility.format_error_stack || '-' || dbms_utility.format_error_backtrace );
end;

output2:
st_time 1 is -> 431409877
Time taken for select with bulk collect ->4
st_time 2 is -> 431409881
Time taken for exeuction using forall  ->25009
Conclusion -2:
From the above test, it is proven that select bulk collect into is executed in no time and the over head is associated with update statement in forall.
------------------------------------------------------------------------------------------------
Example-3:
Test to compare DML using direct update, using for loop and using FORALL statements
declare
    st_time pls_integer ;
    type ord_mah_tab is table of ord_mah.order_no%type;
    v_ord_mah_tab ord_mah_tab;
begin

    st_time := dbms_utility.get_time;
    dbms_output.put_line ('st_time 1 is -> ' || st_time );

    update ord_mah set customer_po_no = substr(customer_po_no,1,7);
    dbms_output.put_line ('Time taken for exeuction using direct update ->' || (dbms_utility.get_time - st_time ) );
    rollback;

    st_time := dbms_utility.get_time;
    dbms_output.put_line ('st_time 2 is -> ' || st_time );

    for i in (select order_no from ord_mah) loop
        update ord_mah set customer_po_no = substr(customer_po_no,1,7) where order_no = i.order_no;
    end loop;
    dbms_output.put_line ('Time taken for exeuction using for loop ->' || (dbms_utility.get_time - st_time ) );
    rollback;

    st_time := dbms_utility.get_time;
    dbms_output.put_line ('st_time 3 is -> ' || st_time );

    select order_no bulk collect into v_ord_mah_tab from ord_mah;

    forall i in v_ord_mah_tab.first..v_ord_mah_tab.count
    update ord_mah set customer_po_no = substr(customer_po_no,1,7) where order_no = v_ord_mah_tab(i);
    dbms_output.put_line ('Time taken for exeuction using forall  ->' || (dbms_utility.get_time - st_time ) );
    rollback;

exception
    when others then
        dbms_output.put_line ( dbms_utility.format_error_stack || '-' || dbms_utility.format_error_backtrace );
end;
output3:
st_time 1 is -> 431311406
Time taken for exeuction using direct update ->137
st_time 2 is -> 431311654
Time taken for exeuction using for loop ->22853
st_time 3 is -> 431334572
Time taken for exeuction using forall  ->23074


------------------------------------------------------------------------------------------------
LIMIT Clause:
Remember that collections are held in memory, so doing a bulk collect from a large query could cause a considerable performance problem. In actual fact you would rarely do a straight bulk collect in this manner. Instead you would limit the rows returned using the LIMIT clause and move through the data processing smaller chunks. This gives you the benefits of bulk binds, without hogging all the server memory.
Example-4:
Test to limit the bulk collect into given number of rows
declare
    type bulk_test_mah_tab is table of bulk_test_mah%rowtype;
    v_bulk_test_mah_tab bulk_test_mah_tab := bulk_test_mah_tab();
    v_start_time  pls_integer;

    cursor c1 is select * from bulk_test_mah;
begin
    v_start_time :=dbms_utility.get_time;

    open c1;
    loop
    fetch c1 bulk collect into v_bulk_test_mah_tab limit 10000;
    --exit when c1%notfound;
    exit when v_bulk_test_mah_tab.count = 0;
   
    dbms_output.put_line ('No of rows in table type ->' || v_bulk_test_mah_tab.count );
    end loop;
    close c1;

exception
    when others then
        dbms_output.put_line ('error -' || sqlerrm );
end;

Output-4:
No of rows in table type ->10000
No of rows in table type ->10000
No of rows in table type ->10000
No of rows in table type ->10000
No of rows in table type ->10000
No of rows in table type ->7958
-----------------------------------------------------------------------------------------------
Example-5:
Test to track the errors using SAVE EXCEPTIONS while  performing DML using FORALL
declare
    type ord_mah_tab is table of ord_mah.order_no%type;
    v_ord_mah_tab ord_mah_tab;
begin
    select order_no bulk collect into v_ord_mah_tab from ord_mah;

    forall i in v_ord_mah_tab.first..v_ord_mah_tab.count save exceptions
        update ord_mah set customer_po_no = customer_po_no ||'X' where order_no = v_ord_mah_tab(i);

exception
    when others then
        if sqlcode= -24381 then
            for indx in 1..SQL%BULK_EXCEPTIONS.COUNT loop
               dbms_output.put_line ( SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX || '- ORA-' || SQL%BULK_EXCEPTIONS(indx).ERROR_CODE );
            end loop;       
        else
            dbms_output.put_line ( dbms_utility.format_error_stack || '- ORA-' || dbms_utility.format_error_backtrace );
        end if;
end;
Output-5: The below are the failed rows while performing update
1- ORA-12899
2- ORA-12899
3- ORA-12899
9199- ORA-12899
9313- ORA-12899
14968- ORA-12899
ORA-12899: value too large for column

-----------------------------------------------------------------------------------------------
Example-5:
Test FORALL with sparse collections by using VALUES OF and/or INDICES OF clauses
Use INDICES OF for Nested table type collection and VALUES OF for associative array type collection
In a FORALL statement, the VALUES OF clause allows the values of one collection to be used as a index pointers to another collection.
DECLARE
  TYPE t_tab1 IS TABLE OF tab1%ROWTYPE;
  TYPE t_tab2 IS TABLE OF BINARY_INTEGER;

  l_tab1  t_tab1 := t_tab1();
  l_tab2  t_tab2 := t_tab2();
BEGIN
  FOR i IN 1 .. 1000 LOOP
    l_tab1.extend;
    l_tab1(l_tab1.last).id := i;
    IF MOD(i, 100) = 0 THEN
      l_tab2.extend;
      l_tab2(l_tab2.last) := i;
    END IF;
  END LOOP;

  l_tab1.delete(301);
  l_tab1.delete(601);
  l_tab1.delete(901);

  -- This would fail due to sparse collection.   --FORALL i IN l_tab.first .. l_tab.last
  --  INSERT INTO tab1 VALUES l_tab(i);    -- This works fine with sparse collections.
  FORALL i IN INDICES OF l_tab1
    INSERT INTO tab1 VALUES l_tab1(i);

  -- This works fine for collections of indexes   -- pointing to elements of another collection.
  FORALL i IN VALUES OF l_tab2
    INSERT INTO tab1 VALUES l_tab1(i);
END;


PLS-00436 Restriction in FORALL Statements Removed in 11G
The PLS-00436 restriction has been removed, which means you can now reference the individual elements of a collection within the SET and WHERE clauses of a DML statement in a FORALL construct.
If we execute the following test in ORACLE 10G, we will get the following error
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

Example-6:
CREATE TABLE forall_test (
  id          NUMBER,
  description VARCHAR2(50)
);

INSERT INTO forall_test VALUES (1, 'ONE');
INSERT INTO forall_test VALUES (2, 'TWO');
INSERT INTO forall_test VALUES (3, 'THREE');
INSERT INTO forall_test VALUES (4, 'FOUR');
INSERT INTO forall_test VALUES (5, 'FIVE');
COMMIT;
DECLARE
  TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;
  l_tab t_forall_test_tab;
BEGIN
  -- Retrieve the existing data into a collection.
  SELECT *
  BULK COLLECT INTO l_tab
  FROM   forall_test;

  -- Alter the data in the collection.
  FOR i IN l_tab.first .. l_tab.last LOOP
    l_tab(i).description := 'Description for ' || i;
  END LOOP;

  -- Update the table using the collection.
  FORALL i IN l_tab.first .. l_tab.last
    UPDATE forall_test
    SET    description = l_tab(i).description
    WHERE  id          = l_tab(i).id;

  COMMIT;
END;

Output:


-----------------------------------------------------------------------------------------------
Example-7: Testing BULK COLLECT with RETURNING clause
declare
    type ord_mah_tab is table of ord_mah.order_no%type;
    v_ord_mah_tab ord_mah_tab;
begin
    delete from ord_mah  returning order_no bulk collect into v_ord_mah_tab;
    rollback;
    dbms_output.put_line ('No of Rows going to be deleted -> '  || v_ord_mah_tab.count );
end;

Output-7:
No of Rows going to be deleted -> 17960
Example-8: Testing BULK COLLECT with FORALL, RETURNING
declare
    type ord_mah_ord is table of ord_mah.order_no%type;
    v_ord_mah_ord ord_mah_ord;
    v_ord_mah_tab ord_mah_ord;
begin
    select order_no bulk collect into v_ord_mah_ord from ord_mah where rownum < 101;

    forall i in 1..v_ord_mah_ord.count
    delete from ord_mah  where order_no = v_ord_mah_ord(i) returning order_no bulk collect into v_ord_mah_tab;
    rollback;
    dbms_output.put_line ('No of Rows in v_ord_mah_ord -> '  || v_ord_mah_ord.count );
    dbms_output.put_line ('No of Rows in v_ord_mah_tab -> '  || v_ord_mah_tab.count );
end;

output-8:
No of Rows in v_ord_mah_ord -> 100
No of Rows in v_ord_mah_tab -> 100

Example-9: Testing SQL%BULK_ROWCOUNT
declare
    type ord_mah_tab is table of ord_mah.customer_po_no%type;
    v_ord_mah_tab ord_mah_tab ;
begin
    select distinct customer_po_no bulk collect into v_ord_mah_tab from  ord_mah
        where customer_po_no in  ('621000441XX','621000442XX','253P14XX','M7312210BXX','FG04/TOOLCRIBXX','PLANNEDXX','AC449AXX','5500452851-0001188335XX','5500452851-0001180273XX');
        dbms_output.put_line ('no of rows ->' || v_ord_mah_tab.count );

    forall idx in v_ord_mah_tab.first..v_ord_mah_tab.last
        delete from ord_mah where customer_po_no = v_ord_mah_tab(idx);

    for idx in  1..v_ord_mah_tab.count loop
        dbms_output.put_line ('No of orders assosciated with customer po no ->' || v_ord_mah_tab(idx) ||' --- '|| SQL%BULK_ROWCOUNT(idx) );
    end loop;
    rollback;
exception
    when others then
        dbms_output.put_line ('Error-' || sqlerrm );
end;
Output-9:
no of rows ->9
No of orders associated with customer po no ->621000441XX --- 13
No of orders associated with customer po no ->621000442XX --- 11
No of orders associated with customer po no ->253P14XX --- 2
No of orders associated with customer po no ->M7312210BXX --- 2
No of orders associated with customer po no ->FG04/TOOLCRIBXX --- 2
No of orders associated with customer po no ->PLANNEDXX --- 2
No of orders associated with customer po no ->AC449AXX --- 13
No of orders associated with customer po no ->5500452851-0001188335XX --- 3
No of orders associated with customer po no ->5500452851-0001180273XX --- 5











Note:
·         In Oracle 10g and above, the optimizing PL/SQL compiler rewrites conventional cursor for loops to use a BULK COLLECT with a LIMIT 100, so code that previously didn't take advantage of bulk binds may now run faster.
·         Oracle 10g introduced support for handling sparse collections in FORALL statements (here).
·         The restriction on accessing individual columns of the collection with a FORALL has been removed in Oracle 11g (here).
·         For bulk updates and deletes the timing points remain unchanged. Each row in the collection triggers a before statement, before row, after row and after statement timing point. For bulk inserts, the statement level triggers only fire at the start and the end of the the whole bulk operation, rather than for each row of the collection


Posted by at 1 comment:
Labels: , , , , ,
Subscribe to: Posts (Atom)