Oracle 10g New features

 

1.        COLLECT  function

            SELECT   deptno, COLLECT(ename) AS emps
                FROM   emp
              GROUP BY deptno;

2.        Regular Expressions

         ·        REGEXP_LIKE

         ·        REGEXP_INSTR

         ·        REGEXP_SUBSTR

         ·        REGEXP_REPLACE

3.    Aggregating data with RETURNING clause 

     DELETE FROM emp      WHERE deptno =(SELECT deptno                                      FROM dept WHERE dname ='RESEARCH'                     RETURNINGCOUNT(sal),SUM(sal)       INTO r_savings;

4. DBMS_SCHEDULER : place and extend the functionality provided by the DBMS_JOBpackage. 

           Provide privileges and roles specifically for the scheduler to increase control over the scheduling of job

 

5. Enhancements in  DBMS_OUTPUT package
   

Line length : In versions prior to 10.2, the PUT and PUT_LINE procedures have an imposed limit of 255 bytes. This means that to display anything longer than this requires a "string chopping" utility (almost every Oracle developer must have written or borrowed one of these at some time). This is less than ideal, especially when using this package for screen-based debugging. In 10.2, this restriction has been relaxed to the more sensible 32,767 bytes (the same upper line-length as the equivalently-named UTL_FILE procedures).

Eg : BEGIN  
    DBMS_OUTPUT.put_line ( RPAD('x',32767,'x'));
    END;

Buffer Limits:  SET SERVEROUTPUT ON SIZE UNLIMITED

            SQL> SHOW SERVEROUTPUT

            SERVEROUTPUT ON SIZE UNLIMITED FORMAT WORD_WRAPPED

6. Flashback Restore Points

         This technology provides a means of restoring data as it existed at a specific point in time (defined by either a timestamp or System Change Number)

SQL> CREATE RESTORE POINT before_we_do_anything;

Restore point created.

SQL> DELETE FROM t WHERE ROWNUM <= 2;

2 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> FLASHBACK TABLE t TO RESTORE POINT before_we_do_anything;

Flashback complete.

 

7.  MERGE Enhancements

·         MERGE was introduced in Oracle 9i. Since then it has gone through a revamp to accommodate new features, most importantly:MERGE in 10G supports insert-only and update-only operations. Its 9i predecessor required both WHEN MATCHED and WHEN NOT MATCHED to be present in the statement, this is no longer necessary in 10G.

o   You may ask why one would use MERGE for only a single operation – when there is no all-in-one "upsert" happening, what is the point? What benefit does it give us over good old INSERT or UPDATE?

o   Well, the big advantage is of ease of coding and understanding. MERGE is a lot more intuitive to read – and so a lot less error-prone – than the equivalent UPDATE with correlated subqueries.

o   MERGE might also have a performance advantage over INSERT or UPDATE – don’t assume that though, do benchmark and test.

·         MERGE in 10G supports the delete operation as part of the update clause.

·         Merge in 10G has improved exception reporting.
MERGE INTO student_n a
   USING (SELECT SID, sname, tot_m

            FROM student) b

   ON (a.SID = b.SID)

   WHEN MATCHED THEN

      UPDATE

         SET a.sname = b.sname

      DELETE

         WHERE a.SID > 7

   WHEN NOT MATCHED THEN

      INSERT (a.SID, a.sname, a.tot_m, a.nn)
      VALUES (b.SID, b.sname, b.tot_m, 'Good');

SQL Tuning or SQL Optimization

SQL Tuning or SQL Optimization

Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.

SQL Tuning/SQL Optimization Techniques:

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.

For Example: Write the query as

SELECT id, first_name, last_name, age, subject FROM student_details;
              Instead of:
        SELECT * FROM student_details;

2)  HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
For Example: Write the query as

       SELECT subject, count(subject)
          FROM student_details
        WHERE subject != 'Science'
            AND subject != 'Maths'
    GROUP BY subject;
  
 Instead of:
     SELECT subject, count(subject)
        FROM student_details
       GROUP BY subject  HAVING subject!= 'Vancouver' AND subject!= 'Toronto';

3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as
SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = 'Electronics';

Instead of:
SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = 'Electronics';

4) Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.

For Example: Write the query as
Select * from product p
where EXISTS (select * from order_items o where o.product_id = p.product_id)
Instead of:
Select * from product p
where product_id IN (select product_id from order_items )

Src: http://beginner-sql-tutorial.com/sql-query-tuning.htm