Oracle Partitioning and indexing

Oracle partitioning and indexing can be said to be its own bright spot, and you might say there are other databases, yes, but Oracle's kind of performance convenience can be said to be more humane, the following we through the experiment to illustrate their characteristics and functions.

1. Give an example of the high and low performance of a B-tree index for full table scanning respectively.

Index definition: An index in an Oracle database is a directory structure to speed up data access

B-tree Index Features:

(1) Two-fork tree structure

(2) Find the index block in a comparative size way

(3) Suitable for creating fields with low repetition rate of key values

For example, primary key fields: Emphasizing table reference relations, that is, can be referenced by foreign key

Uniqueness constraint field: Highlighting uniqueness of field key values

(4) The first scan, from the root node into the back is not back into the

(5) The leaf and leaves have a pointer chain, do not return to the previous layer, you can directly navigate to the next leaf node

(6) Primary key fields do search efficiency and the amount of data independent, such as 10,000 records 100 million records retrieval efficiency is almost

(7) The index blocks are stored sequentially, and the data blocks are scattered and stored.

(8) The smaller the result set, the better the result set and the worse the performance

(9) compared with bitmap index, occupy more space

Experiment

leo1@leo1> drop table leo1;

Table dropped.

leo1@leo1> drop table Leo2;

Table dropped.

To clean up the environment first, we recreate the tables and indexes to see what performance is under the different execution plans.

Leo1@leo1> CREATE TABLE Leo1 as SELECT * from Dba_objects;

Table created.

We create leo1 tables for full table scans

Leo1@leo1> CREATE TABLE Leo2 as SELECT * from Dba_objects;

Table created.

We create Leo2 tables for walking b-tree indexes

Leo1@leo1> CREATE index Idx_leo2 on Leo2 (object_id);

Index created.

Leo1@leo1> Execute dbms_stats.gather_table_stats (' LEO1 ', ' LEO1 ', cascade=>true);

Pl/sql procedure successfully completed.

Leo1@leo1> Execute dbms_stats.gather_table_stats (' LEO1 ', ' LEO2 ', cascade=>true);

Pl/sql procedure successfully completed.

Statistical analysis of indexes on LEO1 and LEO2 tables and tables to allow Oracle to understand their data distribution

Leo1@leo1> Select table_name, num_rows, last_analyzed, object_type from dba_tab_statistics where Wner = ' LEO1 ';

TABLE_NAME Num_rows last_anal object_type

------------------------------ ---------- --------- -------------------------------- ---------- --------- -------

LEO1 71961 09-jan-13 TABLE

LEO2 71962 09-jan-13 TABLE

Well, you've shown a statistical analysis of 2 tables, and you know that there are 71961 rows and 71962 rows of data on the table.

Leo1@leo1> set Autotrace traceonly launch execution plan

Leo1@leo1> SELECT * from Leo1 where object_id=10000;

Execution Plan

----------------------------------------------------------

Plan Hash value:2716644435

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

--------------------------------------------------------------------------

| 0 |      SELECT STATEMENT |     |    1 |   97 | 287 (1) | 00:00:04 |

|* 1 | TABLE ACCESS full|     LEO1 |    1 |   97 | 287 (1) | 00:00:04 |

--------------------------------------------------------------------------

Go full table scan, cost cost=287

predicate information (identified by Operation ID):

---------------------------------------------------------------------------

1-filter ("object_id" =10000) predicate condition

Statistics