Oracle Query Optimization - Single table If we are using a query on single table with where clause on a column, it can be simply optimized by creating an Index. If the query is fetching near 10% data, index would be used by query. Example: SQL> set autot trace SQL> SELECT * FROM MYEMP WHERE EMPNO=7839; Elapsed: 00:00:00.02 Execution Plan ---------------------------------------------------------- Plan hash value: 2418123361 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| MYEMP | 1 | 87 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- SQL> CREATE INDEX MYEMP_EMPNO_INDX ON MYEMP(EMPNO); Index created. Elapsed: 00:00:00.07 SQL> SELECT * FROM MYEMP WHERE EMPNO=7839; Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 254813117 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| MYEMP | 1 | 87 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | MYEMP_EMPNO_INDX | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Get More Query Optimization Tips: http://nimishgarg.blogspot.in/2012/10/query-optimization-tips-for-oracle.html
Query Optimization - Single table
Subscribe to:
Post Comments (Atom)
hm,mmm
ReplyDeleteIt’s nearly impossible to find well-informed people in this particular topic,
ReplyDeletebut you sound like you know what you’re talking about!
경마사이트
경마