Click the following link of Sachin for Join Types Nested Loops, Hash and Sort Merge Join Types by Sachin Previously, i copied most of the important points from his post for my own request but now deleting as the author does not like it. Related articles by Zemanta Optimization of Joins
Archive for the ‘SQL Optimization’ Category
25 Jun
Optimize Joins and Query Plan Changes
Joins and Query Plans Details of the article can be viewed at ask Tom Below are two queries posted by a user on askTom site. </pre>1) SELECT distinct D.deptno, D.dname FROM dept D, emp E WHERE E.deptno = D.deptno ORDER BY D.deptno; 2) SELECT D.deptno, D.dname FROM dept D WHERE EXISTS ( SELECT 1 FROM [...]
29 May
Basic SQL Performance Diagnosis
Article is based on Randolf’s Post on Basic SQL statement performance diagnosis-HOW TO The following steps should be taken to gather detailed information about the SQL statement: 1. Gather information about parameters relevant to the optimizer 2. Gather the output of the SQL*Plus AUTOTRACE and TIMING feature when executing your statement 3. Get the [...]
19 May
Creating SQL Tuning Task
DECLARE my_task_name VARCHAR2(30); my_sqltext CLOB; BEGIN my_sqltext := ‘SELECT a16.mkt_oid mkt_oid,’|| ‘a16.mkt_nm mkt_nm,’|| ‘ a16.mkt_cd mkt_cd,’||’a15.consolidated_brand_nm consolidated_brand_nm,’|| ‘SUM (a11.xp_trx) wjxbfs3′ || ‘FROM prescrip_sales a11 JOIN month a12 ON (a11.mo_id = a12.mo_id)’|| ‘JOIN zzmq00 pa13 ON (a12.mo_oid = pa13.mo_oid) JOIN d_product a14 ON (a11.src_prod_oid = a14.src_prod_oid) ‘GROUP BY a16.mkt_oid, a16.mkt_nm, a16.mkt_cd, a15.consolidated_brand_nm, a18.spec_group_oid, a18.spec_group_nm, pa13.mo_oid, pa13.mo_disp’; [...]
19 May
SQL optimization using Oracle Hints
ORACLE HINT REFERENCE PARALLEL Scanning full table with parallel degree of 8. Parallel always assumes full table scan. SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 8 ) */ last_name FROM employees hr_emp; PARALLEL_INDEX The PARALLEL_INDEX hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans for partitioned [...]
