Help! I got a request for ANSI SQL What is it and what do you do with it?

1. Help! I got a request for ANSI SQLWhat is it and what do you do with it? Presenter: Walter Guerrero Computer Associates Email: walter.guerrero@ca.com

2. Abstract As companies deploy more XML and exchange information across many different database types, they will need to implement ISO standards within their business models. SQL statements must then move away from proprietary format and fit within the ISO/ANSI standards. These ISO/ANSI standards provide a more readable and portable format that can be used by different database engines. Oracle is making the move for Oracle developers to start using ISO/ANSI SQL constructs in the creation of SQL statements to exchange data between XML style sheets. Even after changing the SQL statements into the ISO/ANSI format, developers still need to be concern with the performance needs of the SQL statements running in an Oracle database server.

3. What is ANSI/ISO SQL • ANSI and ISO SQL is the standard definition of SQL as agreed by these standards bodies • ANSI and ISO SQL standards are the same • If more information is desired, visit • http://www.cssinfo.com/ncitsgate.html • http://www.iso.ch/

4. Old Syntax Issues • Non-standard implementations • Lack of data modeling • Lack of data structure processing • Outer join implementations not standardized

5. Benefits of ANSI/ISO SQL • Portability of SQL code across different database engines • Easier to read SQL statements • National language support included

6. Benefits Of ANSI/ISO SQL – Cont’d • Standardize outer join implementations • No need to use a UNION to complete an outer join process

7. Outer Join Advanced Capabilities • Database navigation • Access Optimizations • Data warehouse interaction • Flexible aggregate operations usage • Object relational interaction • View update capability • Multimedia directory support

8. Why ANSI/ISO SQL • Allows the Oracle SQL statements to become more portable and readable • Information exchange between companies requires portable code • This is very important for companies using XML, which uses SQLX or XSQL

9. Oracle Supported ANSI/ISO Datatypes • Character, char, nchar • Varchar • National • Decimal, dec • Integer, int • Float • Double, precision • Real

10. Basic ANSI/ISO SQL Keywords • Inner • Using • Natural • Left Outer – outer is optional • Right Outer – outer is optional • Full Outer – outer is optional • Case

11. Advanced ANSI/ISO SQL Keywords • Siblings • Cross • When/then/else • Extract • Analytic functions • IS OF • Cast • First • Last

12. Standard Oracle SQL Join Keywords • = • =(+) • (+)= • Full outer is made up of either =(+) or (+)= and an UNION to an equi-join

13. SELECT * FROM employees e, assignments a WHERE e.emp_seq = a.emp_seq Typical Oracle Statement This is a typical inner join using Oracle syntax

14. Basic ANSI/ISO SQL Statement SELECT * FROM employees e JOIN assignments a ON e.emp_seq = a.emp_seq; This is a typical ANSI SQL inner join

15. ANSI/ISO SQL “USING” Statement SELECT * FROM employees e JOIN assignments a USING (emp_seq);

16. ANSI/ISO SQL “Natural” Statement SELECT * FROM employees e NATURAL JOIN assignments a;

17. Oracle Syntax Outer Join SELECT * FROM employees e, assignments a WHERE e.emp_seq = a.emp_seq(+)

18. ANSI Syntax Left Outer Join SELECT * FROM employees e LEFT OUTER JOIN assignments a ON e.emp_seq = a.emp_seq

19. ANSI Syntax Right Outer Join SELECT * FROM employees e RIGHT OUTER JOIN assignments a ON e.emp_seq = a.emp_seq

20. Oracle Syntax Full Outer Join SELECT e.emp_seq, p.proj_seq FROM employees e, assignments a, projects p WHERE e.emp_seq = a.emp_seq(+) AND a.proj_seq = p.proj_seq(+) UNION ALL SELECT null, p.proj_seq FROM projects p, assignments a WHERE p.proj_seq = a.proj_seq(+) AND a.proj_seq IS NULL

21. ANSI/ISO Syntax Full Outer Join SELECT e.emp_seq, p.proj_seq FROM (employees e LEFT JOIN assignments a ON e.emp_seq = a.emp_seq) FULL JOIN projects p ON a.proj_seq = p.proj_seq

22. Oracle Decode Syntax SELECT d.dept_seq, d.description, decode(d.active, 'Y','Active', 'N','Not Active', 'Not Applicable') FROM departments d

23. ANSI/ISO CASE Syntax SELECT d.dept_seq, d.description, CASE active WHEN 'Y' THEN 'Active' WHEN 'N' THEN 'Not Active' ELSE 'Not applicable' END FROM departments d

24. Oracle Inner Syntax Performance Example SELECT * FROM employees e, assignments a WHERE e.emp_seq = a.emp_seq ______________________________________ SELECT STATEMENT Cost = 17 HASH JOIN TABLE ACCESS FULL ASSIGNMENTS TABLE ACCESS FULL EMPLOYEES

25. ANSI/ISO Inner Syntax Performance Example SELECT * FROM employees e JOIN assignments a ON e.emp_seq = a.emp_seq; _________________________ SELECT STATEMENT Cost = 17 HASH JOIN TABLE ACCESS FULL ASSIGNMENTS TABLE ACCESS FULL EMPLOYEES

26. Oracle Syntax Outer Join Performance Example SELECT e.emp_seq, p.proj_seq FROM employees e, assignments a, projects p WHERE e.emp_seq = a.emp_seq(+) AND a.proj_seq = p.proj_seq(+) UNION ALL SELECT null, p.proj_seq FROM projects p, assignments a WHERE p.proj_seq = a.proj_seq(+) AND a.proj_seq IS NULL; _________________________________________________________ SELECT STATEMENT Cost = 6 UNION-ALL NESTED LOOPS OUTER HASH JOIN OUTER INDEX FULL SCAN PK_EMP INDEX FULL SCAN PK_ASSIGN INDEX UNIQUE SCAN PK_PROJECTS FILTER HASH JOIN OUTER INDEX FULL SCAN PK_PROJECTS INDEX FULL SCAN PK_ASSIGN

27. ANSI/ISO Syntax Outer Join Performance Example SELECT e.emp_seq, p.proj_seq FROM (employees e LEFT JOIN assignments a ON e.emp_seq = a.emp_seq) FULL JOIN projects p ON a.proj_seq = p.proj_seq ___________________________________________________ SELECT STATEMENT Cost = 4 VIEW UNION-ALL NESTED LOOPS OUTER HASH JOIN OUTER INDEX FULL SCAN PK_EMP INDEX FULL SCAN PK_ASSIGN INDEX UNIQUE SCAN PK_PROJECTS INDEX FULL SCAN PK_PROJECTS FILTER NESTED LOOPS OUTER INDEX FULL SCAN PK_EMP INDEX FULL SCAN PK_ASSIGN

28. Performance Differences • There are slight performance differences between Oracle and ANSI SQL syntaxes • ANSI SQL provides you with improved readability • ANSI SQL provides you with code portability across different database systems

29. Summary • Now that Oracle supports ANSI SQL 92 and parts of ANSI SQL 99, it is important to keep performance targets in mind • Do performance analysis of your ANSI/ISO SQL during your development phase • Use the CBO (Cost Based Optimizer) to your advantage

30. Questions?