USING clause in EXECUTE IMMEDIATE

BEGIN
   INSERT INTO MY_DATA  VALUES (1, 'STEVEN');
   INSERT INTO MY_DATA  VALUES (2, 'VEVA');
   INSERT INTO MY_DATA VALUES (3, 'ELI');
   COMMIT;
END;

(a)  
 DECLARE
   TYPE MY_DATA_T IS TABLE OF MY_DATA%ROWTYPE INDEX BY PLS_INTEGER;
   L_MY_DATA   MY_DATA_T;
BEGIN
   EXECUTE IMMEDIATE
      'DELETE FROM my_data
         WHERE nm <> :nmvalue
           AND pky <> :keyvalue
           AND SUBSTR (nm, 1, 3) = :nmvalue'
     USING 'abc' ,5  ,'def';
END;
 
(b)
DECLARE
   TYPE MY_DATA_T IS TABLE OF MY_DATA%ROWTYPE INDEX BY PLS_INTEGER;
   L_MY_DATA   MY_DATA_T;
BEGIN
   EXECUTE IMMEDIATE
      'begin
           DELETE FROM my_data
            WHERE nm <> :nmvalue
              AND pky <> :keyvalue
              AND SUBSTR (nm, 1, 3) = :nmvalue;
        end;'
      USING 'abc', 5 ;    
END;


What is the difference between the PLSQL (a) & (b) 




When executing dynamic SQL, you must provide an expression or variable for each placeholder.


When executing dynamic PL/SQL, you must provide an expression or variable for each uniquely-named placeholder.

Oracle in Big Data World

High Water Mark

High water mark is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations.


When an INSERT statement cannot find a free block to populate, it goes straight to the High Water Mark and allocates a new block.When rows are deleted, the High Water Mark does not come down.

Inorder to remove the empty blocks in the HWM you need to issue the following two commands


ALTER TABLE emp ENABLE ROW MOVEMENT;
ALTER TABLE emp SHRINK SPACE;

ROWIDs are normally assigned to a row for the life time of the row at insert time.  Oracle can move a table row using the first command .

It will read the table, it will delete/insert the rows at the bottom of the table to move them up, it will generate redo, it will generate undo thus can relocate and reorganize rows in a table including it will change rowids


The second step adjusts the high watermark. For this Oracle needs an exclusive table lock,but for a very short moment only.
 - can be done online
 - indexes will be maintained and remain usable

Monitoring Index

CREATE TABLE t1 (c1 NUMBER);
CREATE INDEX t1_idx ON t1(c1);
ALTER INDEX t1_idx MONITORING USAGE;
SELECT table_name, index_name, monitoring, used FROM v$object_usage;

SELECT * FROM t1 WHERE c1 = 1;


SELECT table_name, index_name, monitoring, used FROM v$object_usage;

ALTER INDEX indexname NOMONITORING USAGE;
ALTER INDEX indexname MONITORING USAGE;

Some points in Performance Tuning

Try to avoid using  following in triggers

1) UTL_ packages .  They do not roll back
2) Pragma autonomous_transaction only use if it is unavoidable


Wrong cardinality = Wrong Plan
Check Constraints can rewrite queries

If you want to limit the creation of index however want to increase performance degredated query by refering explain plan . One main point is that you want to check the the highest cardinality which is having FULL TABLE ACCESS in the explain plan , for that particular cardinality check the access predicates in the explain plan and try to create index for those columns in the join (access predicates)

Creating index will help in the performance of a query even though the join is right or left outer join .

Dimensions convey information to the optimizer.The presence of Dimensions open up access paths that would not be otherwise available.


The Forms' PL/SQL code runs in the Forms PL/SQL engine, which is different in PL/SQL engine in database , this is why you cannot access database package's variable, which result in the following error
Implementation Restriction: 'Cannot directly access remote package variable or cursor while compiling oracle forms