1 / 41

Writing advance SQL Queries

Writing advance SQL Queries. 1. Hierarchical Retrieval. Tree Structure of EMPLOYEE data. Sample Data from EMPLOYEE table. EMP_ID MGR_ID DEPT_ID ESAL ENAME 1 6 1 2000 ROGER 2 6 1 3000 DAYN 3 6 1 8000 STEVE 4 6 1 200 MICK

niyati
Download Presentation

Writing advance SQL Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Writing advance SQL Queries

  2. 1. Hierarchical Retrieval

  3. Tree Structure of EMPLOYEE data

  4. Sample Data from EMPLOYEE table EMP_ID MGR_ID DEPT_ID ESAL ENAME 1 6 1 2000 ROGER 2 6 1 3000 DAYN 3 6 1 8000 STEVE 4 6 1 200 MICK 6 8 1 10000 AMIT 7 9 4 2000 VIKAS 8 14 4 3000 RAVI 10 9 2 8000 VIVEK 9 8 3 14000 STEVE 11 9 3 90000 PRABHU 13 100000 ASHISH

  5. Hierarchical Queries SELECT [LEVEL], column, expr... FROM table [WHERE condition(s)] [START WITH condition(s)] [CONNECT BY PRIOR condition(s)] ; WHERE condition: expr comparison_operator expr

  6. Keywords and Clauses In the syntax: SELECT Is the standard SELECT clause. LEVEL For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. FROM table Specifies the table, view, or snapshot containing the columns. You can select from only one table. WHERE Restricts the rows returned by the query without affecting other rows of the hierarchy. condition Is a comparison with expressions. START WITH Specifies the root rows of the hierarchy (where to start). This clause is required for a true hierarchical query. CONNECT BY PRIOR Specifies the columns in which the relationship between parent and child rows exist. This clause is required for a hierarchical query.

  7. Walking the Tree: From the Bottom Up SELECT emp_id, mgr_id, dept_id, LEVEL FROM employee WHERE mgr_id IS NOT NULL START WITH emp_id =1 CONNECT BY PRIOR mgr_id = emp_id; Output EMP_ID MGR_ID DEPT_ID LEVEL 1 6 1 1 6 8 1 2 8 14 4 3

  8. Walking the Tree: From the Top Down SELECT emp_id, mgr_id, dept_id, LEVEL FROM employee WHERE mgr_id IS NOT NULL START WITH emp_id =8 CONNECT BY PRIOR emp_id = mgr_id; Output EMP_ID MGR_ID DEPT_ID LEVEL 8 14 4 1 6 8 1 2 1 6 1 3 2 6 1 3 3 6 1 3 4 6 1 3 9 8 3 2 7 9 4 3 10 9 2 3 11 9 3 3

  9. Formatting using LEVEL and LPAD SELECT LPAD(emp_id, LENGTH(emp_id)+(LEVEL*2)-2,'_') org_chart ,mgr_id, ename, LEVEL FROM employee WHERE mgr_id IS NOT NULL START WITH emp_id =8 CONNECT BY PRIOR emp_id = mgr_id; Output ORG_CHART MGR_ID ENAME LEVEL 8 14 RAVI 1 __6 8 AMIT 2 ____1 6 ROGER 3 ____2 6 DAYN 3 ____3 6 STEVE 3 ____4 6 MICK 3 __9 8 STEVE 2 ____7 9 VIKAS 3 ____10 9 VIVEK 3 ____11 9 PRABHU 3

  10. Using SYS_CONNECT_BY_PATH SELECT LPAD(emp_id, LENGTH(emp_id)+(LEVEL*2)-2,'_') org_chart ,mgr_id, ename, LEVEL, sys_connect_by_path(ename, '/') cbp FROM employee WHERE mgr_id IS NOT NULL START WITH emp_id =8 CONNECT BY PRIOR emp_id = mgr_id; Output ORG_CHART MGR_ID ENAME LEVEL CBP 8 14 RAVI 1 /RAVI __6 8 AMIT 2 /RAVI/AMIT ____1 6 ROGER 3 /RAVI/AMIT/ROGER ____2 6 DAYN 3 /RAVI/AMIT/DAYN ____3 6 STEVE 3 /RAVI/AMIT/STEVE ____4 6 MICK 3 /RAVI/AMIT/MICK __9 8 STEVE 2 /RAVI/STEVE ____7 9 VIKAS 3 /RAVI/STEVE/VIKAS ____10 9 VIVEK 3 /RAVI/STEVE/VIVEK ____11 9 PRABHU 3 /RAVI/STEVE/PRABHU

  11. 2. BULK BINDING

  12. Overview PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine, which executes the SQL statements and, in some cases, returns data to the PL/SQL engine

  13. Overview …. • Too many context switches between the PL/SQL and SQL engines can harm performance. • That can happen when a loop executes a separate SQL statement for each element of a collection, specifying the collection element as a bind variable. Ex DECLARE TYPE NumList IS VARRAY(20) OF NUMBER; depts NumList := NumList(10, 30, 70); -- department numbers BEGIN ... FOR i IN depts.FIRST..depts.LAST LOOP DELETE FROM emp WHERE deptno = depts(i); END LOOP; END;

  14. How Do Bulk Binds Improve Performance? • The assigning of values to PL/SQL variables in SQL statements is called binding. • A DML statement can transfer all the elements of a collection in a single operation, a process known as bulk binding. • This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. • With bulk binds, entire collections, not just individual elements, are passed back and forth. • To do bulk binds with INSERT, UPDATE, and DELETE statements, you enclose the SQL statement within a PL/SQL FORALL statement. • To do bulk binds with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.

  15. Example: Performing a Bulk Bind with DELETE The following DELETE statement is sent to the SQL engine just once, even though it performs three DELETE operations: DECLARE TYPE NumList IS VARRAY(20) OF NUMBER; depts NumList := NumList(10, 30, 70); -- department numbers BEGIN FORALL i IN depts.FIRST..depts.LAST DELETE FROM emp WHERE deptno = depts(i); END;

  16. Example: Performing a Bulk Bind with INSERT DECLARE TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER; TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER; pnums NumTab; pnames NameTab; t1 NUMBER(15); t2 NUMBER(15); t3 NUMBER(15); BEGIN FOR j IN 1..5000 LOOP -- load index-by tables pnums(j) := j; pnames(j) := 'Part No. ' || TO_CHAR(j); END LOOP; t1 := dbms_utility.get_time; FOR i IN 1..5000 LOOP -- use FOR loop INSERT INTO parts VALUES (pnums(i), pnames(i)); END LOOP; t2 := dbms_utility.get_time; FORALL i IN 1..5000 -- use FORALL statement INSERT INTO parts VALUES (pnums(i), pnames(i)); t3 := dbms_utility.get_time; dbms_output.put_line('Execution Time (secs)'); dbms_output.put_line('---------------------'); dbms_output.put_line('FOR loop: ' || TO_CHAR(t2 - t1)); -- 32 dbms_output.put_line('FORALL: ' || TO_CHAR(t3 - t2)); -- 3 END;

  17. Retrieving Query Results into Collections with the BULK COLLECT Clause • The keywords BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. BULK COLLECT INTO collection_name[, collection_name] • The SQL engine bulk-binds all collections referenced in the INTO list. The corresponding columns can store scalar or composite values including objects. Ex: In the following example, the SQL engine loads the entire empno and ename database columns into nested tables before returning the tables to the PL/SQL engine: DECLARE TYPE NumTab IS TABLE OF emp.empno%TYPE; TYPE NameTab IS TABLE OF emp.ename%TYPE; enums NumTab; -- no need to initialize names NameTab; BEGIN SELECT empno, ename BULK COLLECT INTO enums, names FROM emp; ... END;

  18. Ex In this example, the SQL engine loads all the values in an object column into a nested table before returning the table to the PL/SQL engine: CREATE TYPE Coords AS OBJECT (x NUMBER, y NUMBER); CREATE TABLE grid (num NUMBER, loc Coords); INSERT INTO grid VALUES(10, Coords(1,2)); INSERT INTO grid VALUES(20, Coords(3,4)); DECLARE TYPE CoordsTab IS TABLE OF Coords; pairs CoordsTab; BEGIN SELECT loc BULK COLLECT INTO pairs FROM grid; -- now pairs contains (1,2) and (3,4) END;

  19. Examples of Bulk Fetching from a Cursor You can bulk-fetch from a cursor into one or more collections: DECLARE TYPE NameList IS TABLE OF emp.ename%TYPE; TYPE SalList IS TABLE OF emp.sal%TYPE; CURSOR c1 IS SELECT ename, sal FROM emp WHERE sal > 1000; names NameList; sals SalList; BEGIN OPEN c1; FETCH c1 BULK COLLECT INTO names, sals; END; You can bulk-fetch from a cursor into a collection of records: DECLARE TYPE DeptRecTab IS TABLE OF dept%ROWTYPE; dept_recs DeptRecTab; CURSOR c1 IS SELECT deptno, dname, loc FROM dept WHERE deptno > 10; BEGIN OPEN c1; FETCH c1 BULK COLLECT INTO dept_recs; END;

  20. Limiting the Rows for a Bulk FETCH Operation with the LIMIT Clause • The optional LIMIT clause, allowed only in bulk (not scalar) FETCH statements, lets you limit the number of rows fetched from the database. The syntax is: FETCH ... BULK COLLECT INTO ... [LIMIT rows]; DECLARE TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; CURSOR c1 IS SELECT empno FROM emp; empnos NumTab; rows NATURAL := 10; BEGIN OPEN c1; LOOP /* The following statement fetches 10 rows (or less). */ FETCH c1 BULK COLLECT INTO empnos LIMIT rows; EXIT WHEN c1%NOTFOUND; ... END LOOP; CLOSE c1; END;

  21. Restrictions on BULK COLLECT • You cannot bulk collect into an associative array that has a string type for the key. • You can use the BULK COLLECT clause only in server-side programs (not in client-side programs). Otherwise, you get the error this feature is not supported in client-side programs. • All targets in a BULK COLLECT INTO clause must be collections, as the following example shows: DECLARE TYPE NameList IS TABLE OF emp.ename%TYPE; names NameList; salary emp.sal%TYPE; BEGIN SELECT ename, sal BULK COLLECT INTO names, salary -- illegal target FROM emp WHERE ROWNUM < 50; ... END;

  22. Using BULK COLLECT and FOR ALL together CREATE OR REPLACE PROCEDURE fast_proc (p_array_size IN PLS_INTEGER DEFAULT 100) IS TYPE ARRAY IS TABLE OF all_objects%ROWTYPE; l_data ARRAY; CURSOR c IS SELECT * FROM all_objects; BEGIN OPEN c; LOOP FETCH c BULK COLLECT INTO l_data LIMIT p_array_size; FORALL i IN 1..l_data.COUNT INSERT INTO t2 VALUES l_data(i); EXIT WHEN c%NOTFOUND; END LOOP; CLOSE c; END fast_proc;

  23. DEMO – Fast Processing using BULK BINDING Create demo Table: CREATE TABLE parent ( part_num NUMBER, part_name VARCHAR2(15)); CREATE TABLE child AS SELECT * FROM parent;

  24. DEMO – Fast Processing using BULK BINDING……… Create and load demo data: DECLARE j PLS_INTEGER := 1; k parent.part_name%TYPE := 'Transducer'; BEGIN FOR i IN 1 .. 200000 LOOP SELECT DECODE(k, 'Transducer', 'Rectifier', 'Rectifier', 'Capacitor', 'Capacitor', 'Knob', 'Knob', 'Chassis', 'Chassis', 'Transducer') INTO k FROM DUAL; INSERT INTO parent VALUES (j+i, k); END LOOP; COMMIT; END;

  25. DEMO – Fast Processing using BULK BINDING……… Slow way of processing without BULK BINDING: CREATE OR REPLACE PROCEDURE slow_way IS BEGIN FOR r IN (SELECT * FROM parent) LOOP -- modify record values r.part_num := r.part_num * 10; -- store results INSERT INTO child VALUES (r.part_num, r.part_name); END LOOP; COMMIT; END slow_way; Time taken -- 07.71

  26. DEMO – Fast Processing using BULK BINDING……… Fast Way 1 - Fetch into user defined array : CREATE OR REPLACE PROCEDURE fast_way IS TYPE myarray IS TABLE OF parent%ROWTYPE; l_data myarray; CURSOR r IS SELECT part_num, part_name FROM parent; BEGIN OPEN r; LOOP FETCH r BULK COLLECT INTO l_data LIMIT 1000; FOR j IN 1 .. l_data.COUNT LOOP l_data(j).part_num := l_data(j).part_num * 10; END LOOP; FORALL i IN 1..l_data.COUNT INSERT INTO child VALUES l_data(i); EXIT WHEN r%NOTFOUND; END LOOP; COMMIT; CLOSE r; END fast_way; Time taken – 0.50

  27. DEMO – Fast Processing using BULK BINDING……… Fast Way 2 - Fetch into user defined PL/SQL table : CREATE OR REPLACE PROCEDURE fast_way IS TYPE PartNum IS TABLE OF parent.part_num%TYPE INDEX BY BINARY_INTEGER; pnum_t PartNum; TYPE PartName IS TABLE OF parent.part_name%TYPE INDEX BY BINARY_INTEGER; pnam_t PartName; BEGIN SELECT part_num, part_name BULK COLLECT INTO pnum_t, pnam_t FROM parent; FOR i IN pnum_t.FIRST .. pnum_t.LAST LOOP pnum_t(i) := pnum_t(i) * 10; END LOOP; FORALL i IN pnum_t.FIRST .. pnum_t.LAST INSERT INTO child (part_num, part_name) VALUES (pnum_t(i), pnam_t(i)); COMMIT; END fast_way; --- Time taken – 0.62

  28. Partial collection using BULK BINDING CREATE OR REPLACE PROCEDURE collection_part IS TYPE NumList IS VARRAY(10) OF NUMBER; depts NumList := NumList(100,200,101,102,202,201,103,104,105,106); BEGIN FORALL j IN 4..7 -- use only part of varray DELETE FROM test WHERE deptno = depts(j); COMMIT; END collection_part;

  29. 3. Query Execution Plan

  30. Overview When a PL/SQL-based application performs poorly, it is often due to badly written SQL statements, poor programming practices, inattention to PL/SQL basics, or misuse of shared memory. If badly written SQL statements are slowing down your program, analyze their execution plans and performance using the methods listed below: • EXPLAIN PLAN statement • SQL Trace facility with TKPROF utility • Oracle Trace facility

  31. Explain Plan • The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement. • Explain plan is stored in PLAN_TABLE. To explain a SQL statement, use the following: EXPLAIN PLAN FOR SQL_Statement For example: EXPLAIN PLAN FOR SELECT last_name FROM employees;

  32. Identifying Statements for EXPLAIN PLAN • With multiple statements, you can specify a statement identifier and use that to identify your specific execution plan. Before using SET STATEMENT ID, remove any existing rows for that statement ID For Example EXPLAIN PLAN SET STATEMENT_ID = 'bad1' FOR SELECT last_name FROM employees;

  33. Displaying PLAN_TABLE Output • dbms_xplan.display can be used to see the output for last explain plan query. select * from table(dbms_xplan.display) ; • Below query can also be used to see the operations performed. SELECT lpad(' ',level-1)||operation||' '||options||' '|| object_name "Plan" FROM plan_table CONNECT BY prior id = parent_id AND prior statement_id = statement_id START WITH id = 0 AND statement_id = 'bad4' ORDER BY id;

  34. Explain Plan example EXPLAIN PLAN SET STATEMENT_ID = 'bad6' FOR SELECT * FROM employee WHERE emp_id = 8; 1. select * from table(dbms_xplan.display) ; Output: ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | | | | |* 2 | INDEX UNIQUE SCAN | EMP_PK | | | | -------------------------------------------------------------------------

  35. Explain Plan example........ 2. SELECT lpad(' ',level-1)||operation||' '||options||' '|| object_name "Plan" FROM plan_table CONNECT BY prior id = parent_id AND prior statement_id = statement_id START WITH id = 0 AND statement_id = 'bad6' ORDER BY id; Output: PLAN ------------------------------ SELECT STATEMENT TABLE ACCESS BY INDEX ROWID EMPLOYEE INDEX UNIQUE SCAN EMP_PK

  36. 4. Writing efficient queries

  37. 1) Use EXISTS clause instead of IN clause as it is more efficient than IN and performs faster. Ex. --- Replace SELECT * FROM employee WHERE DEPT_id IN (SELECT DEPT_id FROM department); --With SELECT * FROM employee e WHERE EXISTS (SELECT 1 FROM department d WHERE e.DEPT_id = d.DEPT_id);

  38. 2) There must be a character-by-character match between the statement being examined and one already in the shared pool. Ex. SELECT * FROM employee; is not the same as any of these Select * from employee; select * FROM employee; SELECT * FROM employee;

  39. 3.When you have a choice of using the IN or the BETWEEN clauses in your SQL, use the BETWEEN clause as it is much more efficient than IN. Depending on the range of numbers in a BETWEEN, the optimizer will choose to do a full table scan or use the index. 4.Indexes can't be used when Oracle is forced to perform implicit datatype conversion. 5.Do not use SQL functions in predicate clauses or WHERE clauses or on indexed columns, (e.g. concatenation, substr, decode, rtrim, ltrim etc.) as this prevents the use of the index. Use function based indexes where possible SELECT * FROM EMP WHERE SUBSTR (ENAME, 1, 3) = ‘KES’ Use the LIKE function instead of SUBSTR () SELECT * FROM EMP WHERE ENAME LIKE ‘KES%’;

  40. 6. If you want the index used, don’t perform an operation on the field. Replace SELECT * FROM EMPLOYEE WHERE SALARY +1000 = v_NEWSALARY With SELECT * FROM EMPLOYEE WHERE SALARY = v_NEWSALARY –1000 7.Try joins rather than sub-queries which result in implicit joins Replace SELECT * FROM A WHERE A.CITY IN (SELECT B.CITY FROM B) With SELECT A.* FROM A, B WHERE A.CITY = B.CITY 8.Indexes can't be used when Oracle is forced to perform implicit datatype conversion

  41. 9. The following operations always require a sort. Avoid them whenever you can: SELECT DISTINCT SELECT UNIQUE SELECT ....ORDER BY... SELECT....GROUP BY... CREATE INDEX CREATE TABLE.... AS SELECT with primary key specification Use of INTERSECT, MINUS, and UNION set operators 10.Table JOINS should be written first before any condition of WHERE clause. And the conditions which filter out the maximum records should be placed at the end after joins as the parsing is done from BOTTOM to TOP. SELECT ename FROM employee e WHERE sal>5000 AND job='MANAGER' AND 25<( SELECT count(*) FROM employee WHERE mgr_id = e.emp_id); -- LEAST EFFICIENT SELECT ename FROM employee e WHERE 25<( SELECT count(*) FROM employee WHERE mgr_id = e.emp_id) AND sal>5000 AND job='MANAGER'; -- MOST EFFICIENT

More Related