Index usage with like operator and Domain Index

 https://tipsfororacle.blogspot.com/2017/02/index-usage-with-like-operator-and.html?m=1

Dynamic sql

Retrieving DML Results into a Collection with the RETURNING INTO Clause

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm


sql_stmt := 'UPDATE Scott.emp
SET sal = (sal+ sal * :a)
where empno = :b
RETURNING sal
INTO :c';

/* Executing a Dynamic SQL for Oracle Version 8.1.7 onwards */

EXECUTE IMMEDIATE sql_stmt USING VarSalPercentage,VarEmpno RETURNING INTO UpdatedSalary;

/* You can use this way als0
EXECUTE IMMEDIATE sql_stmt USING VarSalPercentage,VarEmpno,OUT UpdatedSalary; */

------------------------------------------------------------------------------
COPY is Undead: copy data from one database to another.

Copy command is obsolete now, but still useful when you need to copy large amount of data (espcially LONG datatype)  without filling the undo/rollback segments.

http://docs.oracle.com/cd/B10500_01/server.920/a90842/apb.htm

--------------------------------------------------------
Dont Forget: http://www.oracle.com/technetwork/articles/sql/11g-misc-091388.html

Reference Cursor

 

Create procedure (dept_in  Number,  emp_ref_cur  SYS_REFCURSOR)

IS
Begin

Open emp_ref_cur  FOR   select * from employees where dept_id = dept_in;

If emp_cur%rowcount = 0 Then

....

End if;

End;
/

Bulk Exception with FORALL .. SAVE EXCEPTION

 The Bulk Exception are used to save the exception information and continue processing .

In order to  bulk collect exception information, we use FORALL clause with SAVE EXCEPTIONS keyword.

All exceptions raised during execution as saved in %BULK_Exception attribute.

SQL%BULK_EXCEPTION(i).ERROR_CODE  hold the corresponding Oracle error code.

SQL%BULK_EXCEPTION(i).ERROR_INDEX  holds the iteration number of the FORALL statement.

SQL%BUK_EXCEPTION.COUNT  holds the total number of exceptions encountered.

Select * BULK COLLECT INTO  v_collections
From TestTable;

FORALL  idx  IN  v_collections.FIRST .. v_collections.LAST  SAVE EXCEPTIONs
.....
.....

EXCEPTION

 WHEN OTHERS THEN

 FOR idx IN 1..  SQL%BULK_EXCEPTIONS.COUNT
 LOOP
   v_ind := SQL%BULK_EXCEPTIONS(idx).Error_index ;   

 Dbms_output.Put_Line('Error encountered at '||SQL%BULK_EXCEPTIONS(idx).Error_index);

 Dbms_output.Put_Line('Values '||v_collections(v_ind).Emp_id
                                || v_collections(v_ind).First_name
                                || v_collections(v_ind).Last_name );

 Dbms_output.Put_Line('Oracle Error Ora-Code '||SQL%BULK_EXCEPTIONS(idx).Error_code);

 End Loop;

End;

WHERE CURRENT OF Cursor Loop

WHERE CURRENT OF  clause is used in conjuction with  FOR UPDATE   to update / delete current working row in a cursor for loop.

The  WHERE CURRENT OF  clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted. We must declare the cursor with the  FOR UPDATE clause to use this feature

Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated

Declare
cursor c1 is
  Select    row_number()over(order by process_id)  lineno , process_id, line_item_no
  from wbbs_to_ar where notional_invoice_no='11011875' for update;
Begin
  For cx in c1
  Loop
   update wbbs_to_ar set line_item_no = cx.lineno where current of c1;
  End Loop;
End;

Diagnose Performance Issue Using Dynamic v$Performance Views

Diagnose Performance Issues Using DYNAMIC Performance Views:



v$session : Session Complete Details Type= USER/ BACKGROUND. sid, serial#, event, parameter, etc.. 

v$session_event -- summary of sessions wait-events  prior to Oracle 10g,

v$system_event -- wait experieced by Instace, from the time it started (but only Summary)

View for High Load Session:-  Consuming intensive Resource (CPU, PIO, LIO,No of commits/rollback)
v$sesstat, v$sysstat.   

Get complete details of wait events experienced, for eg. the time (how long), Sql Statment, Event  and corresponding parameter details so as  to drill down to the problem resolution.
v$active_session_history 

RANK & DENSE_RANK

RANK calculates the rank of a value in a group of values. The return type is NUMBER. The ranks may not be consecutive number.

DENSE_RANK computes the rank of a row in an ordered group of rows . The ranks are consecutive integers beginning with 1. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank. This function is useful for top-N and bottom-N reporting.

Q, Give me the set of sales people making the top 3 salaries in each department"? scott@TKYTE816> break on deptno skip 1 scott@TKYTE816> select * 2 from ( select deptno, ename, sal, 3 dense_rank() over ( partition by deptno 4 order by sal desc ) dr 5 from emp ) 6 where dr <= 3 7 order by deptno, sal desc 8 / DEPTNO ENAME SAL DR ---------- ---------- ---------- ---------- 10 KING 5000 1 CLARK 2450 2 MILLER 1300 3 20 SCOTT 3000 1 FORD 3000 1 JONES 2975 2 ADAMS 1100 3 30 BLAKE 2850 1 ALLEN 1600 2 TURNER 1500 3

Materialized View

 Materialized View

Query rewrite feature of Materialized view depends on 2 parameters:

1. QUERY_REWRITE_INTEGRITY = { enforced | trusted | stale_tolerated } , This  determines the degree to which Oracle must enforce query rewriting .

enforced =Oracle enforces and guarantees consistency and integrity.

stale_tolerated Materialized views are eligible for rewrite even if they are known to be inconsistent with the underlying detail data.

2. QUERY_REWRITE_ENABLED = {TRUE | False }

Realtime Materialized View can be created using  ENABLE QUERY COMPUTATION keyword, it nake use of mview logs and base table.

Tips for Materialized view

https://danischnider.wordpress.com/2019/02/18/materialized-view-refresh-for-dummies/

https://docs.oracle.com/en/database/oracle/oracle-database/18/dwhsg/refreshing-materialized-views.html#GUID-E4E896C7-8173-4A36-A43F-188158981EB7