Feeds:
Posts
Comments

Posts Tagged ‘Hash function’

•A sample table scan retrieves a random sample of data from a simple table or a complex SELECT statement, such as a statement involving joins and views.

•This access path is used when a statement’s FROM clause includes the SAMPLE clause or the SAMPLE BLOCK clause.

•To perform a sample table scan when sampling by rows with the SAMPLE clause, Oracle reads a specified percentage of rows in the table.

•To perform a sample table scan when sampling by blocks with the SAMPLE BLOCK clause, Oracle reads a specified percentage of table blocks.

Example:
——————-


SQL> select * from test_skip_scan sample(.2);

20 rows selected.

Execution Plan
———————————————————-
Plan hash value: 571935661

————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————–
| 0 | SELECT STATEMENT | | 20 | 180 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS SAMPLE| TEST_SKIP_SCAN | 20 | 180 | 6 (0)| 00:00:01 |

Expert are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2011/01/04/oversize-of-datatype-varchar2-causes-performance-issue/

https://samadhandba.wordpress.com/2011/02/16/what-and-when-index-scans-is-used/

https://samadhandba.wordpress.com/2011/02/16/index-skip-full-fast-full-index-index-joins-bitmap-indexes-scan/

https://samadhandba.wordpress.com/2011/02/14/sample-table-scans-in-oracle/

https://samadhandba.wordpress.com/2011/02/14/when-you-would-make-index-and-when-not/

https://samadhandba.wordpress.com/2011/02/14/optimize-data-access-path-in-oracle-2/

https://samadhandba.wordpress.com/2011/02/13/troubleshoot-unusable-index-in-oracle/

https://samadhandba.wordpress.com/2011/02/13/the-possible-causes-for-excessive-undo-generation-2/

https://samadhandba.wordpress.com/2011/02/13/three-basic-steps-of-sql-tuning/

https://samadhandba.wordpress.com/2011/02/13/goals-for-tuning-2/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cannot-set-the-on-commit-refresh-attribute-for-the-materialized-view/

Advertisements

Read Full Post »

Autotrace Option:
————————
SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY: Like SET AUTOTRACE ON, but suppresses the printing of the user’s query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.

Now look explain plan of a query which will use index.
SQL> SELECT COUNT(*) FROM TEST WHERE A=1;

Execution Plan
———————————————————-
Plan hash value: 2811351645

———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 3 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX UNIQUE SCAN| SYS_C005994 | 1 | 3 | 0 (0)| 00:00:01 |
———————————————————————————-

Now we can prevent oracle from using index in several methods.

Method 1:Adding an expression to the indexed column:
————————————————————————
SQL> SELECT COUNT(*) FROM TEST WHERE A+0=1;
Execution Plan
———————————————————-
Plan hash value: 160014765

——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FULL SCAN| SYS_C005994 | 1 | 3 | 1 (0)| 00:00:01 |
——————————————————————————–

Method 2:Specifying the FULL hint to force full table scan:
————————————————————————————–
SQL> select /*+ FULL(TEST)*/ * from TEST WHERE A=1;
Execution Plan
———————————————————-
Plan hash value: 1357081020

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 8 | 3 (0)| 00:00:01 |
————————————————————————–

Method 3:Specifying NO_INDEX hint
——————————————————————
SQL> select /*+ NO_INDEX(TEST) */ count(*) from test where A=1;
Execution Plan
———————————————————-
Plan hash value: 1950795681

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| TEST | 1 | 3 | 3 (0)| 00:00:01 |
—————————————————————————

Method 4:Using a function over the indexed column
——————————————————————-
SQL> select count(*) from test where to_number(A)=1;

Execution Plan
———————————————————-
Plan hash value: 160014765

——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FULL SCAN| SYS_C005994 | 1 | 3 | 1 (0)| 00:00:01 |
——————————————————————————–

Read Full Post »