24
2014
Basit SQL Tuning/SQL Optimization
Yazar 
1) Sql sorgusunda ‘*’ yerine gerçek kolon adları kullanmak daha hızlı sonuçlar vericektir.
SELECT id, first_name, last_name, age, subject FROM student_details;
——-
SELECT * FROM student_details;
2) HAVING tüm satırlar seçildikten sonra filtreleme yapar. Gereksiz kullanımdan kaçınmalıyız.
SELECT subject, count(subject)
FROM student_details
WHERE subject != ‘Science’
AND subject != ‘Maths’
GROUP BY subject;
————
SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= ‘Vancouver’ AND subject!= ‘Toronto’;
3)Bazen iç içe sorgular yazmamız gerekebilir.Mümkün olduğunca az iç sorgu kullanmak performans açısından önemlidir.
SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = ‘Electronics’;
——————–
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) IN Yerine EXISTS kullanmaya çalışmalıyız.
a) Genellikle IN performansı yavaşlatır.
b) Filtre kriterlerin en alt sorguda olduğunda IN etkilidir.
c) Filtre kriterlerin çoğu ana sorguda olduğunda EXISTS etkilidir.
Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)
——————————
Select * from product p
where product_id IN
(select product_id from order_items)
5) DISTINC yerine EXISTS kullanmaya çalışmalıyız.
SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT ‘X’ FROM employee e WHERE e.dept = d.dept);
——————————
SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;
6) UNION Yerine UNION ALL kullanımına önem göstermeliyiz.
SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;
——————–
SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;
7) Where kuşulundaki durumlara dikkat etmeliyiz..
For Example: Write the query as
SELECT id, first_name, age FROM student_details WHERE age > 10;
———————–
SELECT id, first_name, age FROM student_details WHERE age != 10;
****************************************
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE ‘Chan%’;
————————-
SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = ‘Cha’;
****************************************
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, ‘%’);
————————-
SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);
****************************************
SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)
————————-
SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)
****************************************
SELECT id, name, salary
FROM employee
WHERE dept = ‘Electronics’
AND location = ‘Bangalore’;
————————-
SELECT id, name, salary
FROM employee
WHERE dept || location= ‘ElectronicsBangalore’;
****************************************
SELECT id, name, salary
FROM employee
WHERE salary < 25000;
————————-
SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;
****************************************
SELECT id, first_name, age
FROM student_details
WHERE age > 10;
————————-
SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;
8) Gereksiz DECODE kullanımından kaçınmalıyız.
SELECT id FROM employee
WHERE name LIKE ‘Ramesh%’
and location = ‘Bangalore’;
————————-
SELECT DECODE(location,’Bangalore’,id,NULL) id FROM employee
WHERE name LIKE ‘Ramesh%’;
9) Büyük ikili nesneleri depolamak için ilk dosya sistemine koyun ve veritabanı dosya yolunu ekleyin.
10) Sorguları yazarken genel SQL standart kurallarına uygun yazmaya çalışmalıyız.
a-)SQL terimlerine yeni satırda başlayın.
b-)Tüm kelimeleri tek boşlukta ayırın.
c-)İlk sql kelimesindeki hizaya göre sağa ve sola hizalamaya özen gösterin.
Mustafa Yılmaz
Oldu.
4 yıldan fazla bir zamandır Yazılım,3 yıldır Oracle E-Business Suite Uygulama Yazılımı
üzerinde Teknik Danışman Olarak profesyonel kariyerine devam
etmektedir.
Mail: mustafayilmaz56@gmail.com
Latest posts by Mustafa Yılmaz (see all)
- Trigger’ların Çalışma Sırası - 06 Kasım 2015
- AR-GL interface alanlarının ilişkisi - 05 Mayıs 2015
- FND_PROFILE Kullanımı - 05 Mayıs 2015
