About author I am Java/oracle professional.Working on Java/J2EE technologies and i.e Java,J2ee,Oracle ADF,hibernate,J2ee,PL/sql,Apps for 4+ years.I am passionate about learning new technologies.I am sharing my knowledge. Give your views and suggestion on vinay@techartifact.com
http://www.linkedin.com/in/vinaykumar2 Read more from this authorCases should be avoided -1. DO NOT use FUNCTIONS (,TO_NUMBER ,Decode,NVL, TO_CHAR) unnecessarily when a simple join would work
2. DO NOT use ‘SELECT *’ explicitly. Make sure that you select only required columns from the tables.
3. DO NOT use HAVING clause where a WHERE clause would do.
4. DO NOT use ORDER BY clause in a query unless necessarily by logic.
5. DO NOT use UNION and DISTINCT together in a single query
6. DO NOT use CURSOR LOOPS unnecessarily when a simple/single DML/SELECT can serve the purpose
7. DO NOT use DUAL table unnecessarily in programs and queries.
Cases should be followed -
1. Use UNION ALL as far as possible instead of UNION.
2. Use single/direct DML’s/SELECT’s instead of LOOP’s.
3. Use BULK COLLECT & FORALL for looping when the loop is going to get executed multiple time (take > 100 iterations as a base).
4. Use MATERIALIZED VIEWS or GLOBAL TEMPORARY TABLE for queries fetching data across dblinks
5. Use EXISTS in place of IN in the queries.
6. Care should be taken to use the LIMIT clause while using BULK operations in order to limit memory utilization. LIMIT should be set between 500-1000 and reduced or decreased as per performance behavior of the program
References – http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#i48876
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/design.htm#i29012
Vivek kompella
Hey Vinay,
Its good to list out these points, but it will cretainly be helpful if you can also list out the reasons behind do not use…
Marcin
Good summary … but it’s just induction – missing content with explenation and examples.
Sriram
Good cheat sheet!
BULK COLLECTs are somtimes not good when processing like Millions of records.Cursors are better when dealing tonnes of records. Do you agree with me friend ?
Doug
Cursors are definitely NOT better for “tonnes of records”. If you have a large result set and you can use BULK COLLECT, then do so. If you need to keep the bulk binds to reasonable sizes, you can use the LIMIT TO clause.
vinay
Bulk collect are good when to fetech the records at once .cursor are bad for large number of record processing.that is done sequently and it impact the perfomance issue.
Mandeep Grewal
Hello,
Good article.
Kamal
Nice ARTICLE