Software Applications Programming Errors and solutions.

I want to share some tips through image sharing in  SQL Server Database Engine Tuning Advisor 2012.Database Engine Tuning Advisor  is used to recommend indexes and other actions which is required to improve performance of your database.  If there is a big size data then you should use index technique to improve database performance.

Following are the  difference between the index by table and nested tables. Index by table - 1)Not store in database  2)Automatically initialize when declared . 3)Index By Table is something like a hash-map or map in another languages where you can index a value with either String or Integer. Nested table  1)Store in database  2)Initialize using contractor . 3)Where are Nested Table is something like Array in another languages but the difference is it is not fixed in size

Bulk collect fetch all rows from table in single shot so if table have so many rows then it can cause consume too much session memory and can raise an error .for this developer should set limit parameter carefully by keeping performance or memory overhead in mind. Suppose I have 10000 rows in my emp table but my session doesn't have enough memory to hold 10000 row on that case we can use limit parameter. declare c_limit PLS_INTEGER=100; cursor emp_cur is select empno from emp where deptno=&depnto; type l_emp_id_t is table of emp.empno%type; l_emp_ids l_emp_id_t ; begin open emp_cur; loop fetch emp_cur bulk collect into l_emp_ids limit c_limit; exit when l_emp_ids.COUNT=0; end loop; end;

 Ref cursor is called dynamic cursor and it is a datatype and it is used to you can open the multiple select statement at run time and it is dynamic change the query Ref cursor is used to write more then one select statement in cursor. Ref cursor is 'pointer' datatype that allow you to quickly reference cursor result , using ref cursor we use the re-usability of cursor ,ref cursor is variable defined as a cursor type , which will point to, or reference a cursor result , ref cursor can be assigned to other ref cursor variable  Ref cursor is one data type and can be defined into one variable,this variable can be called as ref_cursor variable. We can use this variable for different SQL statements at run time. For more details you can visit this site mentioned in image