Shulou(Shulou.com)06/01 Report--

Performance optimization

When there is a large amount of data in full table scan (multi-block read size)

1, parallel query 2, multi-block read 3, index full scan

Row ID

An index is a database object that contains

The new table of k value and row id.

OLTP: online trading system

Common index: B-tree index (with the increase of the amount of data, the right node will also increase, resulting in imbalance, resulting in branch nodes, increasing Imax O)

Bitmap index (suitable for data warehouse, in tables with few additions, deletions and changes, the information of the whole table is needed. )

Benefit: get all the information about the table. Gets the start and end values of the row id. Disadvantages: index has an impact on additions, deletions and changes, locking two tables

Function-based index (use limitation is relatively large, utilization rate is low. Need to be evaluated,)

Cause function index to fail: 1, outer set of index 2, do operation 3, fuzzy query

Statistics on the time of collection of index information: during the week (2-4 am) weekend ()

Statistical table information, data dictionary

Reverse index:

Collect T-table statistics: exec dbms_stats.gather_table_stats ('SCOTT','T')

The statistics for the newly created table are empty

Index not visible: alter index cust_id_idx invisible

Alter index cust_id_idx visible

Reverse indexing: in order to avoid hot block effect (low performance), refer to reverse indexing and use the blocking method.

Disadvantages: range scanning is not supported and can only be used for equivalent queries

Unique scan of index scan type index (index value unique, where judgment =)

Range scan of the index (read single block)

Index jump scan (composite index)

Index fast full scan (multi-block scan)

Index full scan

The index is commonly used in 1d2.

Two optimized access paths

1, full table scan (simply scan all the tables, mainly when a query requests most of the blocks of the table)

2, index access path (based on row id)

If the value is less than 7%, the index will go.

Undo

ORA-01555 snapshot is too old (with large transactions) the updata transaction is too large to hold the undo tablespace, and the solution is to force a shutdown to terminate the transaction. Expand the undo tablespace.

There is a front image in undo. CTAS create table as select

Undo cannot be set to auto-extend

Index full scan

Conditions for generating a full scan of the index:

Order by corresponds to id

Group by corresponds to id

Fast full scan of index: (accessing multiple blocks at the same time)

When the index itself contains all the columns in the query, oracle performs a fast full scan of the index.

Index name: (how to determine if an index is used)

Alter index cust_id_idx monitoring usage

Select index_name,monitoring,used from v$object_usage

Avoid using indexes:

/ * + NO_INDEX (a + sign is a hint, no + sign is a comment)

View the execution plan:

Explain plan for select * from emp where empno='7765'

Select * from emp (dbms_xplain_display)

The situation of not taking the index.

There is no null (null value) when indexing columns

Do not walk the index when there is inequality, and do not walk the index when in or not in.

When using wildcards, fuzzy queries do not walk the index.

Do not walk the index when a null value is used in the predicate.

The query does not walk the index when it contains a function.

Statistical information query

Dbms stored procedure package

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.