rvsunilkumar

1. Use count(Column_name) instead of count(*). 2. Use UNION ALL statement instead of UNION, whenver possible         The UNION ALL  statement is much faster than UNION, because UNION ALL statement does not          look for duplicate rows, and UNION statement does look for duplicate rows. 3. Avoid using the DISTINCT clause, whenever possible.          Using DISTINCT  clause wll result in some performance degrading, use this clause only when it is          necessary. 4. Avoid the HAVING clause, whenever possible           Having clause is used to restrict the results set returned by the GROUP BY clause. When you use           GROUP BY  with the HAVING clause divides the rows into sets of grouped rows and aggregates           their value, and then HAVING clause eliminates undesired aggregated groups.          Also do not place any ( where) conditions after the HAVING clause (first_name like '%thomos%') -           this would reduce the performance of the query. 5. Choose order o

Getting total count of rows for the query and required number of records for the page in single query using oracle analytic functions. to get all the employee records whose first name is "peter" and display total count and per page display only 10 records  Simple table Structure as follows CREATE TABLE Employee ( emp_id number not null, first_name varchar2 not null, last_name varchar2 not null, join_date Date, ); the query would look like the following. select * from (select max(ROWNUM) over() totalCount, row_number() over(order by join_date asc) rownumb,allrows.* from (select emp_id,first_name,last_name,join_date from Employee where first_name like '%peter%') allrows ) where rownumb between X and Y where x and y are starting and ending row numbers, it varies depending upon the page index. here totalCount is the total number of rows that exist in db for the query.