Replace IN, NOT IN with Exists & Not Exists. Avoid using “Insert Into … Select …” statements as it can take lot time to execute if select query returns huge number of records. Instead user cursors. Avoid sub queries, instead use joins or subquery factoring (with clause) this will optimize the code. If insert &Continue reading “Performance Optimization Tips”
Category Archives: Advanced PL/SQL
Coding Standards
All variable names should start with “v”. e.g., vKount Number; All stored procedure names should start with “SP_”.e.g., Create or Replace Procedure SP_HPC_Cashback(OutMsg Out VarChar2) is All stored function names should start with “SF_”.e.g., Create or Replace Procedure SF_CalcInt(Prin Number, Yr Number) return number is All package names should start with “PKG_”.e.g., CREATE PACKAGE PKG_DMR_ProcessingContinue reading “Coding Standards”
Advanced SQL & PL/SQL
Q1. How to rebuild the index of a table? Answer. ALTER INDEX index_name REBUILD; Q2. What is a Dead Lock? Answer. When two or more users are waiting for access to data that has been locked by each other, it is known as deadlock. When a deadlock occurs, the transactions fail to go ahead –Continue reading “Advanced SQL & PL/SQL”
TechOraFAQ 