hash, nested and merge join

---nested loop  join ---
Nested Loops” operator basically does is: For each record from the outer input – find matching rows from the inner input.
Technically, this means that the clustered index scan you see as the outer input is executed once to get all the relevant records,
and the clustered index seek you see below it is executed for each record from the outer input.

SELECT
OC.first_name, OH.department_id
FROM
employees OC
JOIN
departments OH
ON
OH.department_id = OC.department_id;
--- merge join ----
SELECT
OC.first_name, OH.department_id
FROM
employees OC
JOIN
departments OH
ON
OH.department_id = OC.department_id
where OH.department_id  between 10 and 50;

--- Hash join ----
SELECT
OC.first_name, OH.department_id
FROM
employees OC
JOIN
departments OH
ON
OH.department_id = OC.department_id
where OC.first_name like '%S%';

Find the Number in var char column .

Find the Number in var char column .

select * from (select * from table@pwrdev where rtrim(column, '0123456789') is null);
select e1.eentry from table e1 where not regexp_like (e1.column, '^[A-Z]');

Column to Row Chnages in Oracle :

# How to Change the  Column to Row.
# Using PIVOT Operator :

 Pivot operator is display the data in column which all takes from row. this operator use for cross tabulation and give you the result in row to column format.

SELECT * FROM (SELECT first_name, last_name FROM employees) PIVOT (SUM(salary) AS sum_salary FOR (first_name) IN (1000,2400,17000));

SELECT REPLACE(Testbulk, ' ') FROM (SELECT SYS_CONNECT_BY_PATH(Testbulk,' ') Testbulk, level  FROM T1

START WITH Testbulk= (select min(Testbulk) from t1) CONNECT BY PRIOR Testbulk < Testbulk ORDER BY level DESC) WHERE rownum = 1;

DATABASE TUNNING- Using SQL Trace and TKPROF

                    DATABASE TUNING

Database Tuning : Database tuning is the process/steps which will help to improve the Database performance. If the database performed well then Application will worked  smoothly.

Types of Database Tuning: 

1.      Database Tuning

2.      SQL Tuning

Database Tuning :

              It’s performed by DBA. Database tuning need to performed in DB hardware and server side.

SQL Tuning: 

WHAT IS BULK COLLECT & FORALL

• BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval
  => Started using BULK COLLECT whenever I need to fetch large volumes of data.
  (Specifically, memory for collections is stored in the program global area (PGA), not the system global area (SGA). SGA memory is shared by all sessions connected to Oracle Database, but PGA memory is allocated for each session. Thus, if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, in addition to the memory allocated to the SGA.)
  
  Let it be thought I need to retrieve all the rows from the employees table and then perform some compensation analysis on each row. I can use BULK COLLECT as follows:
  
  

  PROCEDURE process_all_rows
  IS
     TYPE employees_aat 
     IS TABLE OF employees%ROWTYPE
        INDEX BY PLS_INTEGER;
     l_employees employees_aat;
  BEGIN
     SELECT *
     BULK COLLECT INTO l_employees
        FROM employees;
       
     FOR indx IN 1 .. l_employees.COUNT 
     LOOP
         analyze_compensation 
        (l_employees(indx));
     END LOOP;
  END process_all_rows; 

  
  
   => Fortunately, PL/SQL makes it easy for developers to control the amount of memory used in a BULK COLLECT operation by using the LIMIT clause.
  
  
   
• FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly

Cursor, ref cursor and SYS_Ref cursor.

CREATE OR REPLACE PROCEDURE get_emp_rs (p_deptno    IN  emp.DEPARTMENT_ID%TYPE, p_recordset OUT SYS_REFCURSOR)
AS
BEGIN
     OPEN p_recordset FOR
SELECT
     EMPLOYEE_ID,
     FIRST_NAME,
     LAST_NAME
     EMAIL,
     PHONE_NUMBER,
     HIRE_DATE,
     JOB_ID,
     SALARY,
     COMMISSION_PCT,
     MANAGER_ID,
     DEPARTMENT_ID
FROM   emp
     WHERE  DEPARTMENT_ID = p_deptno
     ORDER BY FIRST_NAME;
END get_emp_rs;
/

===============================================================================
print  out put :
SQL> exec get_emp_rs(p_deptno=>10,p_recordset=>:X);

PL/SQL procedure successfully completed.

SQL> set serveroutput on;SQL> exec get_emp_rs(p_deptno=>10,p_recordset=>:X);

PL/SQL procedure successfully completed.

SQL> print x;

EMPLOYEE_ID FIRST_NAME           EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID      SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        200 Jennifer             Whalen                    515.123.4444         17-SEP-87 AD_ASST          4400                       101            10
========================================================================



LOOP:

declare
n number (4):=1;
begin
loop
dbms_output.put_line(n);
exit when n>=11;
n:=n+1;
end loop;
end;
/




Cursor;

declare
cursor c1 is select first_name from employees;
X employees.first_name%type;
begin
open c1;
if c1%isopen then
dbms_output.put_line('XXXXcursor opend');
loop
fetch c1  into X ;
if C1%found then
dbms_output.put_line(X||', '||C1%rowcount);
dbms_output.put_line(C1%rowcount);
else
exit;
end if;
end loop;
end if;
close c1;
end;
/


======================

declare
cursor c1 (employee_no number ) is select * from employees where employee_id=employee_no;
X employees%ROWtype;
begin
open c1(&employee_no);
if c1%isopen then
dbms_output.put_line('XXXXcursor opend');
loop
fetch c1  into X ;
if C1%found then
dbms_output.put_line(X.employee_id||X.first_name);
dbms_output.put_line(C1%rowcount);
else
exit;
end if;
end loop;
end if;
close c1;
end;
/
======================================================