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: