The approach of Oracle Foundation-sql Statement Optimization

One: An optimization approach to SQL statements

1. Select the appropriate Oracle Optimizer

(1). RBO optimization based on rules --oracle 10G was thrown away

(2). CBO optimized optimizer based on cost (CPU and memory occupancy) - The model used by the optimizer depends on the target

The CBO optimization mode:

Rule: Rules-based

Choose: Default, table or index has statistics, walk CBO mode, otherwise go Rbo mode

First row: When there is statistics in the table, the top rows of the query are returned in the quickest way, reducing the overall response time

All row: When there is statistics in the table, all rows of the table are returned in the quickest way, improving the overall query throughput

2. Select the appropriate scan mode

Full table Scan: Oracle reads all rows in the table and checks if each row satisfies the limit of the where statement

Use ROWID: Quickly navigate to target data

Index Scan: Valid only in CBO mode

Fast Index Scan: Scanned data is not sorted

3. Be good at using shared SQL statements--shared SQL statements are placed in the system global Region (SGA) of an Oracle instance,

The approach of Oracle Foundation-sql Statement Optimization