Tham khảo tài liệu 'oracle sql internals handbook phần 5', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | 6 SQL Tuning CHAPTER Improvements SQL Tuning Improvements in Oracle Tuning a single SQL query is an enormously important topic. Before going into production virtually every system would expose some statements that require tuning. In this article we ll explore several Oracle improvements that make life of performance analyst easier. Access and Filter Predicates Syntactically SQL query consists of three fundamental parts a list of columns a list of tables and a where clause. The where clause is a logical formula that can be further decomposed into predicates connected by Boolean connectives. For example the where clause of select empno sal from emp e dept d where and dname ACCOUNTING Is a conjunction of dname ACCOUNTING single table predicate and join predicate. Arguably predicate handling is the heart of SQL optimization predicates could be transitively added rewritten using Boolean Algebra laws moved around at SQL Execution Plan and so on. In our simplistic example the single table predicate is 64 Oracle SQL Internals Handbook applied either to index or table scan plan nodes while join predicate could also be applied to the join node. Unfortunately despite their significance Oracle Execution Plan facility didn t show predicates until version . although experts had an option of running 10060 event trace . In the latest release plan_table together with its runtime sibling v sql_plan acquired two new columns ACCESS_PREDICATES VARCHAR 4000 FILTER_PREDICATES VARCHAR 4000 In our example the plan OPERATION OPTIONS OBJECT NAME FILTER PREDICATES SELECT STATEMENT NESTED LOOPS TABLE ACCESS FULL DEPT ACCOUNTING TABLE ACCESS FULL EMP .DEPTNO now explicitly shows us that dname ACCOUNTING single table predicate is filtering rows during the outer table scan while join predicate is applied during each inner table scan. I found it convenient to apply a little bit of GUI sugarcoating and display the .
