(Installed) oracle execution plan

2011-07-20  来源:本站原创  分类:Database  人气:106 

One. Related Concepts
Rowid concept
Recursive Sql concept
Predicate (verb)
DRiving Table (table-driven)
Probed Table (by probe table)
Combination index (concatenated index)
Selectivity (selectivity)

II. oracle access to the data access methods
1) Full table scan (Full Table Scans, FTS)
2) a table by ROWID access (Table Access by ROWID or rowid lookup)
3) Scan the index (Index Scan or index lookup) There are four types of index scan:
(1) index unique scan (index unique scan)
(2) an index range scan (index range scan)
In the non-unique index on the use index range scan. Use index rang scan of three situations:
(A) in unique index column on the use of range operators (> <<>> = <= between)
(B) in the composite index, the query using only part of the column, causing the query to a multi-line (c) the non-unique index column on any inquiry.
(3) index full scan (index full scan)
(4) index fast scan (index fast full scan)

Third, the connection between the table

1, sorting - - merge join (Sort Merge Join, SMJ)
2, nested loops (Nested Loops, NL)
3, the hash join (Hash Join, HJ)
In addition, Cartesian product (Cartesian Product)

Oracle Connection Method Summary

Summary overview of Oracle implementation plan

+ + +

One. Related Concepts

Rowid concept: rowid is a pseudo-column, since it is pseudo-column, then this column is not user-defined, but the system of their own to add.

For each table has a rowid pseudo-column, but the table does not physically store the ROWID column value. But you can use other columns so as to use it

, But can not be deleted, and listed, it can not modify the column value, insert. Once the line data into a database, the rowid in the bank's health

Life cycle is unique, that even if the line produce the migration, the line will not change the rowid.
Recursive SQL concept: Sometimes in order to execute a sql statement issued by the user, Oracle must perform some additional statements, we will

These additional statements called the''recursive calls''or''recursive SQL statements''. Such as when a DDL statement is issued,

ORACLE always implied a number of recursive SQL statements issued to modify the data dictionary information, so that users can successfully execute the DDL statement

. When the data dictionary information is not in the shared memory, often in Recursive calls, the number of these Recursive calls will

According to the dictionary information from the hard disk into memory. Users concerned about these than the implementation of recursive SQL statements, when required, ORACLE

Automatically in the internal implementation of these statements. SELECT DML statement and of course, may cause recursive SQL. Simply put, we can touch

Hair as recursive SQL.
Row Source (line source): used in the query from the previous operation to return the set of qualifying rows, which can be a table of all rows

Set; can also be part of the table row of data collection; can also row source for the two to connect operations (such as the join connections) after that

To the rows of data collection.
Predicate (verb): a query WHERE restrictions Driving Table (driving table): This table is also called the outer table (OUTER TABLE). This concept is used in a nested connection with HASH. As

If the row source returns more rows, then for all subsequent operations have a negative impact. Note here that although the translation table-driven, but the actual

Translation on the drive line source (driving row source) are more precise. In general, the application of query constraints, the return less the line source

Table as the driving table, so if a large table in the WHERE conditions are restrictions (such as the equivalent limit), the large table as the driving table is

Right, so not only the smaller table can be used as the driving table, the correct statement should check the restrictions for the application, return fewer lines of source

Table as the driving table. In the implementation plan, should rely on that row source, the back will give specific instructions. Described in our back

Above, the general operation of the connection table as row source 1.
Probed Table (by probe table): This table is also called the inner table (INNER TABLE). In the table we get the specific driver line

Data in the table to find the rows meet the join condition. So the table should be large table (actually should return large row source table)

And the corresponding columns should be indexed. In our description below, the general operation of the connection table as row source 2.
Combination index (concatenated index): index constituted by multiple columns, such as create index idx_emp on emp (col1,

col2, col3, ... ...), then we say idx_emp index for the index portfolio. In the composite index has an important concept: guide column

(Leading column), in the above example, col1 as a guide line. When we can use the query "where col1 =?

"You can also use" where col1 =? and col2 =? "Such restrictions will use the index, but" where col2

=? "Query will not use the index, so restrictions contained in the pilot column, the restrictions will use the index portfolio.
Selectivity (selectivity): more of the following only the number of keys and the number of rows in the table, we can determine the selectivity of the column.

If the column "the number of unique key / number of rows in the table," the ratio closer to 1, the higher the selectivity of the column, the column is more suitable for creating an index

, The same index of selectivity is also higher. In the optional column on the high query to return data on fewer, more suitable index

Query.

II. oracle access to the data access methods

1) Full table scan (Full Table Scans, FTS)
To achieve full table scan, Oracle reads all rows in the table, and check whether each line to meet the statement's WHERE constraints a multi-block read

Operation allows an I / O to read multiple data blocks (db_block_multiblock_read_count parameter setting), rather than just reading a

Block, which greatly reduces I / O total number, increased system throughput, so the use of multi-block reading method can achieve very efficient full-

Table scan, but only in the case of full table scan in order to use multi-block read operation. In this access mode, each data block is read only once

.
Prerequisite for using the FTS: In a large table full table scan is not recommended, unless the data are more removed, more than 5% of the total -

10%, or you want to use the parallel query feature.
Examples of using the full table scan:
SQL> explain plan for select * from dual;
Query Plan
-----------------------------------------
SELECT STATEMENT [CHOOSE] Cost =
TABLE ACCESS FULL DUAL

2) a table by ROWID access (Table Access by ROWID or rowid lookup)
Line of the ROWID that the line where the data file, block and row position of the block, so data can be accessed by ROWID

Quickly navigate to the target data, is Oracle's fastest way to access a single row of data.
This access method does not use multi-block read operation, an I / O can only read a data block. We will often see the stored execution plan

Access methods, such as through the indexing query data.
Use the ROWID access methods:
SQL> explain plan for select * from dept where rowid =''AAAAyGAADAAAAATAAF'';

Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
TABLE ACCESS BY ROWID DEPT [ANALYZED]

3) Scan the index (Index Scan or index lookup)
Let's look through the index to the data corresponding rowid values ​​(for non-unique index may return multiple rowid value), then rowid

Directly from the table to get specific data, this look is called an index scan or index lookup (index lookup). Only a rowid

Said row of data, the line corresponding to the data block is an i / o to be, in this case the second i / o will read a database block.
In the index, in addition to storing the value of each index, the index value is also stored with the row corresponding ROWID value.
Index scan can be composed by two steps:
(1) Scan the index to get the corresponding rowid values.
(2) by finding the rowid from table to read out specific data.
Each step is a separate I / O, but for the index, due to frequent use, most have been CACHE into memory, so Step 1

The I / O is often the logic I / O, that data can be obtained from memory. But for Step 2, if the table is relatively large, then the data can not be full

In memory, so its I / O is likely that the physical I / O, which is a mechanical operation, the relative logical I / O, it is extremely time-consuming. So

If the little table index scan, remove the data if the total is greater than 5% - 10%, using an index scan will be much reduced efficiency. As

Listed below:
SQL> explain plan for select empno, ename from emp where empno = 10;
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX UNIQUE SCAN EMP_I1

However, if the query data can all be found in the index, you can avoid the step 2, to avoid unnecessary I / O, this time even through

Index scan data out over more, or high efficiency
SQL> explain plan for select empno from emp where empno = 10; - just check out the value of empno Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
INDEX UNIQUE SCAN EMP_I1

Further, if the sql statement in the column to sort the index because the index has been sorted well in advance, so do not need in the implementation plan

Re-sort the columns of the index SQL> explain plan for select empno, ename from emp
where empno> 7876 order by empno;
Query Plan
-------------------------------------------------- ------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]

You can see from this example: Because index is already sorted, so will check out the order of the index rows that qualify, so

Order to avoid further action.
According to the index type and where the different constraints, there are four types of index scan:
Index unique scan (index unique scan)
Index range scan (index range scan)
Index full scan (index full scan)
Index fast scan (index fast full scan)

(1) index unique scan (index unique scan)
Find a value through a unique index always returns a single ROWID. If there is a UNIQUE or PRIMARY KEY constraint (which guarantees a statement

Access only a single line), then, Oracle often achieve unique scanning.
A unique constraint example:
SQL> explain plan for
select empno, ename from emp where empno = 10;
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX UNIQUE SCAN EMP_I1

(2) an index range scan (index range scan)
Use an index to access multiple rows of data in a unique index on the index range scan using the typical case is the predicate (where restrictions

) Using a range operator (such as >,<,<>,>=,<=, between)
Use an index range scan example:
SQL> explain plan for select empno, ename from emp
where empno> 7876 order by empno;
Query Plan
-------------------------------------------------- ------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]

In the non-unique index on the predicate col = 5 may return multiple rows of data, so use non-unique index on an index range scan.
Use index rang scan of three situations:
(A) in unique index column on the use of range operators (> <<>> = <= between)
(B) in the composite index, the query using only part of the column, causing the query to a multi-line (c) the non-unique index column on any inquiry.

(3) index full scan (index full scan)
Correspond with the full table scan, but also the corresponding full index scan. And then check out the data must be obtained directly from the index.
Full index scan example:
An Index full scan will not perform. Single block i / o''s and so it may prove to be

inefficient.
eg
Index BE_IX is a concatenated index on big_emp (empno, ename)
SQL> explain plan for select empno, ename from big_emp order by empno, ename;
Query Plan
-------------------------------------------------- ------------------------------
SELECT STATEMENT [CHOOSE] Cost = 26
INDEX FULL SCAN BE_IX [ANALYZED]

(4) index fast scan (index fast full scan)
Scanning index of all the data blocks, and index full scan is very similar, but a significant difference is that it does not check the number of

It sort, that data is not the sort order is returned. In this access method, you can use the multi-block read function, you can also use the parallel read

Into, in order to maximize throughput and reduce the execution time.
Index fast scan example:
BE_IX index is a multi-column index: big_emp (empno, ename)
SQL> explain plan for select empno, ename from big_emp;
Query Plan
------------------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
INDEX FAST FULL SCAN BE_IX [ANALYZED]

Select only the first multi-column index 2:
SQL> explain plan for select ename from big_emp;
Query Plan
------------------------------------------
SELECT STATEMENT [CHOOSE] Cost = 1
INDEX FAST FULL SCAN BE_IX [ANALYZED]

Third, the connection between the table

Join an attempt to combine the two predicate table, one can only connect two tables, table join is also called table association. In the latter

Surface of the narrative, we will use the "row source" instead of "table", because a number of more rigorous with row source, and will participate in

2 row source connected are called row source1 and row source 2.Join process steps are often serial operation, even if the phase

Related row source can be accessed in parallel, that can read in parallel to connect the two do join row source of the data, but in the character table

Combined constraints of the form data is read into memory after the row source, join the other steps are generally sequential. There are many ways to two tables

Connected, of course, each method has its own advantages and disadvantages of each type of connection only in the specific conditions under which it will play its greatest advantage.
row source (table) in order to check the connection between the efficiency of a very big impact. By first accessing a particular table, the table is about

As a driver table, which can be applied with certain restrictions, resulting in a smaller row source, so that connection more efficient, which

We often say that the first reason for the implementation of restrictions. Generally in the table is read into memory, the application of the table where clause restrictions

.
According to two row source of the connection conditions in the different operators can be connected into the equivalent connection (such as WHERE A.COL3 =

B.COL4), non-equivalent connections (WHERE A.COL3> B.COL4), outer join (WHERE A.COL3 = B.COL4 (+))。 Above all

Connections of the connection principle is basically the same, so for simplicity period, the following example to introduce the equivalent connection.
In the following presentation, are an example to illustrate the following Sql:
SELECT A.COL1, B.COL2
FROM A, B
WHERE A.COL3 = B.COL4;
Suppose A table Row Soruce1, then the operation associated with the corresponding connection as COL 3;
B table Row Soruce2, then the operation associated with the corresponding connection as COL 4;

Connection Type:
So far, no matter how the connection operator, typically there are three connection types:
Sort - - merge join (Sort Merge Join (SMJ))
Nested loops (Nested Loops (NL))
Hash join (Hash Join)
In addition, there is a Cartesian product (Cartesian product), under normal circumstances, try to avoid using.

1, sorting - - merge join (Sort Merge Join, SMJ)
Internal connection process:
1) First generate the row source1 data needed, and then join operations associated with these data in accordance with column (such as A.col3) sort.
2) Then generate row source2 data needed, and then the data in accordance with the sort source1 join operations associated with the corresponding column (

If B.col4) sort.
3) Finally, both sides of the line to be sorted together to perform the merge operation, about two row source connected by connection conditions

The following is a graphical representation of connection steps:
MERGE
/ \
SORTSORT
| |
Row Source 1Row Source 2

If the row source is already associated with the connection to be sorted on the column, the connection does not require further action on the sort operation, which can significantly increase

This connection to connect high-speed operation, because the sort is an extremely cost resources operations, especially for large tables. Pre-sorted row

source, including the columns have been indexed (such as a.col3 or b.col4 on the index) or row source in the previous steps have been sorted. Do

Control the process of merging the two row source is a serial, but parallel access to both row source (such as parallel read data, parallel sorting

).
SMJ connection examples:
SQL> explain plan for
select / * + ordered * / e.deptno, d.deptno
from emp e, dept d
where e.deptno = d.deptno
order by e.deptno, d.deptno;
Query Plan
-------------------------------------
SELECT STATEMENT [CHOOSE] Cost = 17
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL EMP [ANALYZED]
SORT JOIN
TABLE ACCESS FULL DEPT [ANALYZED]

Sorting is a time-consuming, cost resources operations, particularly for large tables. For this reason, SMJ is often not a particularly effective connection side

Law, but if two row source have been pre-sorted, then the efficiency of this connection method is quite high.

2, nested loops (Nested Loops, NL)
The connection methods are the driving table (external table) concept. In fact, the connection process is a two-layer nested loop, so the outer loop

Times as possible, which is why we return to the small table or small table as the driving row source table (for the outer loop) reasons

On the basis. But this theory is only a general guideline, because, following this theory does not always ensure that the statement produced by I / O times at least. Have

Does not comply with this theoretical basis, but will get better efficiency. If you use this method to determine which table to use as a driving table is very important

. Sometimes if the driver chose not to correct the table, the statement will result in poor performance, poor.
Internal connection process:
Row source1 of Row 1 - Probe -> Row source 2
Row source1's Row 2 - Probe -> Row source 2
Row source1 of Row 3 - Probe -> Row source 2
... ....
Row source1 of Row n - Probe -> Row source 2

The connection process from the inside point of view, need to use row source1 each row to match the row source2 all the lines, so at this time to keep

row source1 as small and efficient access row source2 (usually achieved by the index) that affect the efficiency of the key issues in this connection.

This is only theoretical guiding principle, the purpose is to make the connection operation produces minimal physical I / O times, and if compliance with this principle, the general will

Bringing the total physical I / O count at least. But if you do not comply with the guidelines, but can use fewer physical I / O to connect operations, despite the violation of that

Guidelines it! Because at least the physical I / O times is true that we should follow the guiding principles behind the analysis of specific cases gave

Of such examples.
In the above connection, we called Row source1-driven table or external table. Row Source2 is known as being the internal probe table or tables.
In NESTED LOOPS connection, Oracle reads the row source1 in each row, then row sourc2 check for a matching

Line, all matching rows are placed in the result set and then the row source1 in the next line. This process continues until the row

source1 all the rows are processed. This is from the connection operations can be the first line the fastest way to match one of this type of connection

Then can be used in rapid response to the statement in the need to respond to the speed as the main target.
If the driving row source (external table) is relatively small, and in the inner row source (internal table) has a unique index, or

Highly selective non-unique index, this method can get better efficiency. NESTED LOOPS other connection methods are not a priority

Point is: can the line is connected to return without having to wait for all operations processed before the connection returns data, which can achieve fast response

Room.
If you do not use the parallel operation, the best driving table is applied where those restrictions, you can return fewer rows of the table, the

A large table may also be known as the driving table, the key constraints. For parallel query, we often choose a large table as the driving table, because the big table can be

Take advantage of parallelism. Of course, sometimes the query is not bound to use the parallel query operations do not use parallel operation, high efficiency, because the last

Each table may be only a few lines meet the constraints, and depends on your hardware configuration can support parallel (such as whether more than one CPU,

More than one hard disk controller), so specific issues and problems.
NL connection examples:
SQL> explain plan for
select a.dname, b.sql
from dept a, emp b
where a.deptno = b.deptno;
Query Plan
-------------------------
SELECT STATEMENT [CHOOSE] Cost = 5
NESTED LOOPS
TABLE ACCESS FULL DEPT [ANALYZED]
TABLE ACCESS FULL EMP [ANALYZED]

3, the hash join (Hash Join, HJ)
This connection is introduced later in oracle 7.3, in theory more efficient than the NL and SMJ, but only in the CBO optimizer.
Smaller row source to be used to build hash table and bitmap, 2nd row source to be used by hansed, and the first row

source to match the generated hash table for further connections. Bitmap is used as a faster way to find, to

Check the hash table if there are matching rows. In particular, when the hash table is relatively large and not all fit in memory, this search

Method is more useful. This connection is also connected in the NL so-called table-driven concept is built for the hash table and the bitmap table-driven

Table, when the hash table is built up with the bitmap can be in memory, this connection of the high efficiency.

HASH connection examples:
SQL> explain plan for
select / * + use_hash (emp) * / empno
from emp, dept
where emp.deptno = dept.deptno;
Query Plan
----------------------------
SELECT STATEMENT [CHOOSE] Cost = 3
HASH JOIN
TABLE ACCESS FULL DEPT
TABLE ACCESS FULL EMP

For hash join effective, it needs to set HASH_JOIN_ENABLED = TRUE, by default this parameter is TRUE, the other, do not forget to also

To set hash_area_size parameters, so that the hash join and efficient operation, because the hash join in this parameter specifies the size of memory to run,

Parameter is too small will make hash join performance is even lower than other connections.

In addition, the Cartesian product (Cartesian Product)
When the two row source to do to connect, but no correlation between the conditions they will do in the two row source in the Cartesian product, which

Usually caused by a coding oversight (that programmers forget to write the associated condition). Cartesian product is a table for each row in turn with another table

All the lines match. In exceptional cases we can use Cartesian product, such as the star connection, in addition, we have to try not to use the Cartesian

Children product, otherwise, the result is what they want it!
Note the following statement, in no connection between the two tables.
SQL> explain plan for
select emp.deptno, dept, deptno
from emp, dept
Query Plan
------------------------
SLECT STATEMENT [CHOOSE] Cost = 5
MERGE JOIN CARTESIAN
TABLE ACCESS FULL DEPT
SORT JOIN
TABLE ACCESS FULL EMP

CARTESIAN keyword pointed out between the two tables do Cartesian product. If the table has n rows emp, dept table has m rows, the Cartesian product of the results

If the line is to get n * m results.

Finally, sum up, under what circumstances to use which method of connection is better:

Sort - - merge join (Sort Merge Join, SMJ):
a) For non-equivalent connection, this connection efficiency is relatively high.
b) If the association has an index on the column, the better.
c) For the two large row source to do to connect, the connection method to connect is better than the NL.
d) However, if sort merge the returned row source is too large, it will again lead to excessive use of rowid in the table in the query data, the number of

Database performance, because too much I / O.

Nested loops (Nested Loops, NL):
a) If the driving row source (external table) is relatively small, and in the inner row source (internal table) has a unique index,

Or highly selective non-unique index, this method can get better efficiency.
b) NESTED LOOPS with other connection methods do not have an advantage: can be connected to return line, without waiting for all

Connect operation to return until they have finished processing the data, which can achieve fast response time.

Hash join (Hash Join, HJ):
a) This method is introduced in oracle7 later, using more advanced connection theory, in general, and its efficiency should be better than the other 2

Kinds of connections, but this connection can only be used in the CBO optimizer, but also need to set appropriate hash_area_size parameters in order to achieve better

Performance.
b) In two large row source when a connection is made between the relatively good efficiency in a small row source is able to achieve more

Good efficiency.
c) can only be used for equivalent connections

+ + +
--- Overview of Oracle implementation plan

Oracle implementation of the plan related concepts:

Rowid: the system for each row of data to the oracle of a pseudo column attached, contains the data table name, database id, database id and a storage

A serial number and other information, rowid unique life cycle of the line.
Recursive sql: statement to execute the user, the system perform the additional operation of additional statements, such as data dictionary maintenance.
Row source (line source): oracle step process, from the last operation to return the qualifying rows collection.
Predicate (verb): where, after the restrictions.
Driving table (table-driven): also known as connecting the outer table, with the hash key for the nested connection. Generally limited the application

System conditions, to return fewer rows of the table as the driving source table. In the following description, the driving table as the row source connected operation

1.
Probed table (by probe table): connect the inner table, from the driving table in our line to get specific data, in

probed table to find qualifying rows, so the table should be large row source, and the corresponding join condition columns should have a claim on

Cited. In the following description, the general operation of the connection table as row source 2.
Concatenated index (combination index): a multi-column index if the composition, then known as the composite index, a composite index of the first

As a guide column guiding column contains only the predicate, the index is available.
Optional: a column in the table the number of different values ​​/ total number of rows if the table is close to 1, the column selectivity is high.

Oracle Access data access methods:

Full table scans, FTS (full table scan): one can be set by setting db_block_multiblock_read_count IO can read

Take the number of data blocks, thereby effectively reducing the full-table scan when the total number of IO, which is the mechanism through read-ahead data blocks will be accessed by pre-reading

Memory. Only in the case of full table scan in order to use multi-block read operation.
Table Access by rowed (Table access by rowid, rowid lookup): As the rowid stored in the location of the line record, the

Oracle access to a single row of data is the fastest way.
Index scan (index scan index lookup): in the index, in addition to storing the value of each index, the index is also stored with this value

Line corresponds to the rowid value, index scan 1 in two steps, scanning the index to get rowid; 2, read by rowid specific data. Each step is a separate

An IO, so if the data filtered by the restrictions of the total number of lines is greater than the original form of 5% -10%, with the index down a lot of scanning efficiency

. If all the resulting data can be found in the index, you can avoid the second step, thus speeding up the retrieval speed.
Where the index type and the different constraints, there are four types of index scan:
Index unique scan (index unique scan): the existence of unique or primary key in the case, return the data within a single rowid

Capacity.
Index range scan (index range scan): 1, using the unique index on a range operator (>,<,<>,>=,<=, between)

; 2, in the composite index, the query using only part of the column; 3, the non-unique index on the columns of the query.
Index full scan (index scan): the need to query data from the index can all get.
Index fast full scan (index fast scan): the index full scan is similar, but not this way the results row

Sequence.

So far, the typical type of connection there are three kinds:

Sort merge join (SMJ sort - merge join): First production driving table data needed, and then the data in accordance with

Connected operation is associated with the column to sort; and production probed table data needed, and then the data in accordance with the driving table corresponding to the

To sort out the connection operation; last line on both sides has been put together sort of merge operations. Sorting is a time-consuming, resource-intensive operations costs

, Particularly for large tables. So smj usually not a particularly effective method of connection, but if the driving table and probed table are

Pre-ordering, then the connection method of the efficiency is relatively high.
Nested loops (NL nested loops): the connection process is the driving table and probed table for a nested loop over

Process. Is to use the driving table for each row to match probed table of all rows. Nested loops can be connected to return line

Without waiting for completion of all connections before returning the data processing operation, which can achieve fast response time.
Hash join (hash join): the smaller row source to be used to build the hash table and bitmap, for the second row source

Is hashed, and the first row source with the production of hash table to match. For further connections. When building the hash

table and the bitmap can be hold in memory, this connection of the high efficiency. But need to set the appropriate parameters and hash_area_size

Only be used for equivalent connections.
In addition, there is a connection type: Cartesian product (Cartesian product): table for each row in turn with another table of all rows match

With, under normal circumstances, try to avoid using.

相关文章
  • (Installed) oracle execution plan 2011-07-20

    One. Related Concepts Rowid concept Recursive Sql concept Predicate (verb) DRiving Table (table-driven) Probed Table (by probe table) Combination index (concatenated index) Selectivity (selectivity) II. oracle access to the data access methods 1) Ful

  • Oracle execution plan in order to see how the 2011-01-13

    Oracle execution plan ways to read: Look to the right start at the very beginning has been, until you see the place where the far right side by side, for non-parallel, right, the first run: For side by side, relying on the first run. Namely, in the i

  • Oracle execution plan to see how the order is 2011-01-13

    Reading oracle execution plan approach: Start at the very beginning has been to right, side by side until you see the most right place for non-parallel, right, the first execution: For side by side, relying on the first run. That is parallel to inden

  • oracle execution plan 3 2010-03-02

    Environment: oracle 817 + linux + array cabinet swd_billdetail Table 50 million data SUPER_USER Table 2800 data Connect columns are indexed, and super_user in a table corresponds to swd_billdetail many records Table and Index are also analyzed. Pract

  • oracle execution plan 4 2010-03-02

    The statement by adding hints, so that the optimizer oracle nested loop, and a large table as the driving table, generate a new execution plan: select / * + ORDERED USE_NL (A) * / count (a.CHANNEL | | B.user_class) from swd_billdetail B, SUPER_USER A

  • Oracle execution plan from the start view 2010-08-29

    Consider the following situation (1) If your data table to manage 40 million video information; (2) If you manage 10 million kinds of product data sheets; A simple paging query, you truly feel its efficiency over it? If users complain about slow resp

  • Oracle execution plan from the beginning to view 2010-08-29

    Consider the following scenario, (1) If your data tables to manage 30 million line of card user information; (2) If you manage 10 million kinds of product data sheets; A simple paging query, you truly felt it efficient? If users complain about slow r

  • oracle execution plan cost, card meaning 2011-03-19

    This card is a step in the planned number of rows processed. cost refers to the cbo in the resources spent on this step, this value is a relative value. This step refers to the cbo bytes to process all records of the number of bytes, is estimated fro

  • oracle execution plan 1 2010-03-02

    1. Related concepts Rowid concept: rowid is a pseudo-column, since it is pseudo-column, then this column is not user-defined, but the system itself to add the. Each table has a rowid pseudo-column, but the table does not physically store the value of

  • oracle execution plan 2 2010-03-02

    3. Form the connection between Join an attempt to combine the two tables predicate, one can only connect two tables, table join can also be referred to as worksheets. In the following description, we will use the "row source" instead of "ta

  • oracle execution plan 2010-07-19

    Set the implementation of the plan: set autotrace on; set timing on; Execution time format: Elapsed: 00:00:00.30 Hours : Minutes : Seconds : Seconds /100 Switch User: SQL> connect username/[email protected]

  • oracle execution plan explained 2011-03-31

    [Transfer] http://www.iteye.com/topic/586256 I. Concepts 1 · rowid, pseudo-column: is the system to add their own, and each table has a pseudo-column, not the physical presence. It can not be modified, deleted, and added, rowid in the life cycle of t

  • oracle view the SQL execution plan parameters analysis 2010-04-14

    sql_trace been tracking the implementation plan: Call Count CPU Time Elapsed Time Disk Query Current Rows ------- ------ -------- ------------ ---------- ------- --- ---------- ---------- Parse 1 0.000 0.000 0 0 0 0 Execute 1 0.000 0.000 0 0 0 0 Fetc

  • oracle - how to analyze the execution plan 2011-08-15

    Example 1: Suppose LARGE_TABLE is a large table, and there is no index on the username column, then run the following statement: SQL> SELECT * FROM LARGE_TABLE where USERNAME = 'TEST'; Query Plan ----------------------------------------- SELECT STATE

  • oracle sql statement execution plan in the analysis method 2011-01-14

    How to generate explain plan? Answer: Run utlxplan.sql. The establishment plan table Specific SQL statement, using the explain plan set statement_id = 'tst1' into plan_table for sql statement Run explain plan look utlxplp.sql or utlxpls.sql (Select *

  • Oracle how to analyze the execution plan (reproduced) 2011-03-31

    Example 1: Suppose LARGE_TABLE is a large table, and there is no index on the username column, then run the following statement: SQL> SELECT * FROM LARGE_TABLE where USERNAME = 'TEST'; Query Plan ----------------------------------------- SELECT STATE

  • How do I view the execution plan oracle sql 2011-04-09

    Method One The first step: login sql / plus the command (no order) set time on; (Note: Open time display) set autotrace on; (Note: Turn on Automatic statistical analysis, and display the results of an SQL statement) set autotrace traceonly; (Note: Op

  • Oracle using sql plus view the execution plan (reprint) 2011-04-26

    In sql * plus, enter set autotrace on, and then directly execute sql statements; will automatically display the execution plan and statistics. Disadvantages: This method is used to view the sql statement execution time is longer, you need to wait for

  • Oracle: hint to manually change the sql execution plan 2011-07-06

    Tips (hint) from Oracle7 introduced, aimed at cost-based optimizer to make up for deficiencies. Tips are usually used to manually change the SQL execution plan to improve the efficiency of enforcement. hints is the oracle to provide a mechanism to te

  • How to view the execution plan oracle 2011-07-07

    See individual SQL method I. using explain plan If PLAN_TABLE table does not exist, execute $ ORACLE_HOME / rdbms / admin / utlxplan.sql create plan_table table. The first step: explain plan for select * from ...... Step two: select * from table (DBM