Row Chaining, Migrating, PCTFREE, PCTUSE

In this post I like to discuss several aspects of oracle data block. We all know that data block is the smallest unit of storage space managed by oracle database. Oracle requests data in multiples of Oracle data blocks.

Row Chaining
---------------------------
There may be the scenario that a row to be inserted is too large that can not fit into a single data block. Suppose standard data block is 8k and row size is larger that it. In this case, Oracle stores the data for the row in a chain of data blocks -one or more data blocks that are reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases can't be avoided.

Row Migrating
-----------------------------
The second case in which rows can not fit into a single case is the row is updated, the overall free space of the data block is fully filled up. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.

Whenever row chaining or row migrating happens the I/O performance associated with the row is decreased as to retrieve the information of one row multiple data blocks need to be scanned.

PCTFREE parameter
-------------------------------------
The PCTFREE parameter specifies the minimum percentage of data block to be reserved as free space. If you used PCTFREE 20 then at least 20% size of data block will be reserved as free space. For example if you use data block size 8K (DB_BLOCK_SIZE=8192) then PCTFREE 20 will reserved 1638 bytes as free space in a data block.

This parameter is used to update to the existing rows already within each block.

You can specify this parameter which issuing CREATE TABLE statement.
PCTUSED Parameter
----------------------------------
This parameter specifies the minimum percentage of a block that can be used for row data plus overhead(data block header, table directory, and row directory) before new rows are added to the block.

After a data block is filled to the limit determined by PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls below the parameter PCTUSED.

If we set PCTUSED 40 in the CREATE TABLE statement then data block used for this table's data segment is considered unavailable for the insertion of any new rows until the amount of used space in the block falls to 39% or less (Here the block's used space has previously reached PCTFREE).

Thus with the help of PCTFREE and PCTUSED oracle optimize the use of space in the data blocks of the extents within a data segment. For example, whether oracle will try to use the data block or not in order to insert a new row.

Data Block Format in Oracle

You already might know that oracle manages the storage space in the datafiles of the database in units called data blocks. You might be curious about what the data block actually holds, in other words what data block format is.

The data block holds the following format.
1)Header Information (Common and Variable Header)
2)Table Directory
3)Row Directory
4)Row Data
5)Free Space

1)Header Information (Common and Variable Header)
---------------------------------------------------------------------
• The header contains general information about data block. It contains information like data block address, type of segment.
2)Table Directory
----------------------------
• This part of data block contains information about the table having rows in this block.
3)Row Directory
--------------------------------
• This part of data block contains information about the actual rows in the block.
• After the space has been allocated in the row directory of a data block's overhead, this space is not reclaimed when the row is deleted.

The data block header, table directory, and row directory are collectively known as overhead.

4)Row Data
---------------------
• This part of data block contains row data. A row can span between multiple blocks.

5)Free Space
-----------------------
• The free space part is for insertion of new rows and for updates to rows that require additional space.

• The free space can also hold transaction entries like INSERT, UPDATE, DELETE, or SELECT...FOR UPDATE statement accessing one or more rows in the block.

• The space taken by the transaction entry is approximately 23 bytes.

Task Manager has been disabled by your administrator

Symptoms of The Problem:
-----------------------------------------
Whenever you try to open the task manager using CTRL+ALT_DEL or right click on the startup bar and task manager button is disable of if enabled clicking it shows the following message,
"Task Manager has been disabled by your administrator".

Cause of The Problem:
--------------------------------------------
There may be several causes behind the problem.
1)The account your are using is blocked via the "Local Group Policy" or "Domain Group Policy".
2)Registry settings block to use "Task Manager".
3)The virus attack.

Solution of The Problem:
------------------------------------------------------
Method 1: With Console Registry Tool
--------------------------------------------------
Go to "Start" -> "Run" -> Write the following command,
REG add HKCU\Software\Microsoft\Windows\CurrentVersion\Policies\System /v DisableTaskMgr /t REG_DWORD /d 0 /f
and press enter. Better you can copy the command and paste on to your run window.

Method 2: Edit Registry Settings
----------------------------------------------------------
1.Open notepad as an administrator.

[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\System]
"DisableTaskMgr"=dword:00000000

[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Group Policy Objects\LocalUser\Software\Microsoft\Windows\CurrentVersion\Policies\System]
"DisableTaskMgr"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\policies\system\]
"DisableTaskMgr"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon]
"DisableCAD"=dword:00000000

2. Copy the above contents and paste it into notepad.
3. Save the file as fix.reg
4.Double click on the file and reboot your computer.

Method 3:
----------------------
1. Go to "Start" -> "Run" -> Write "Gpedit.msc" and press enter.
2. Go to following branch
User Configuration / Administrative Templates / System / Ctrl+Alt+Delete Options / Remove Task Manager
3.Select Remove Task Manager and set the policy to Not Configured.
4.Close "Gpedit.msc" memory management console.

5.Go to "Start" -> "Run" -> Write "gpupdate /force" and press enter.

Method 4: With regedit.exe with explorer.
---------------------------------------------------------
1. Go to "Start" -> "Run" -> Write "regedit.exe" and press enter.
2. Go to following branch HKEY_CURRENT_USER \ Software \ Microsoft \ Windows \ CurrentVersion \ Policies\ System
3.In the right-pane, delete the value named DisableTaskMgr
4.Exit registry editor.
Related Documents

How to check or identify which process is listening to port 80 in windows

Task Manager has been disabled by your administrator

Resolve of You may be a victim of software counterfeiting Windows is not genuine

Advantages and Disadvantages of using smaller and bigger data blocks

Whether I will use bigger or smaller data blocks in my database it can be specified by parameter DB_BLOCK_SIZE or DB_nK_CACHE_SIZE. With the settings I can I can differentiate three types of data blocks in oracle.

1)Small Block(2KB-4KB)
2)Medium Block(8KB)
3)Large Block(16KB-32KB)

Advantages of Bigger Blocks
•Using bigger blocks means more data transfer per I/O call. So faster data transfer from disk to memory.

•Using bigger blocks means more space for key storage in the branch nodes of B*-tree indexes, which reduces index height, which improves the performance of indexed queries.

•When using large block there are less probability of chained and migrated rows, which in turn reduced the number of reads required to get the information.

Disadvantages of bigger Blocks
•If the rows are predominated random then you are increasing the possibility of contention in the buffer cache. Because now with same same amount of memory in buffer cache as it was in small blocks, we need more memory in the buffer cache to keep the same amount of buffers in memory in the buffer cache.

•If you have high transactional concurrency to a segment, using bigger blocks is just going to make the concurrency even higher.


Advantages and disadvantages of these blocks
1)Small Block(2KB-4KB): The advantage of small blocks are they reduce block contention and they are really good where there is small rows or the selectivity of rows are highly random.

The disadvantages of small blocks are they have relatively larger overhead.

2)Medium Block(8KB): The advantage of medium blocks are if the rows are of medium size then you can bring a number of rows in a single I/O.

The disadvantage of it is space in the buffer cache will be wasted if you are doing random access to small rows and have a large block size. For example, with an 8KB block size and 60 byte row size, you are wasting 8000-60=7940 bytes in the buffer cache when doing random access.

3)Large Block(16KB-32KB): If you use larger block then relatively less overhead. Per I/O you can fetch more data. This is very good for sequential access, or very large rows.

Large block size is not good for index blocks used in an OLTP(Online Transaction Processing) type environment, because they increase block contention on the index leaf blocks.

Related Documents:
General Idea of Database Block Size and BLOCKSIZE
Choose an optimal Data block size in Oracle

DB_BLOCK_SIZE, DB_nK_CACHE_SIZE and BLOCKSIZE

First I want to say about BLOCKSIZE cluase in oracle. The BLOCKSIZE cluase is used to specify the block size for the tablespace. If you don't specify this clause while tablespace creation then standard that is default blocksize is used which is specified by parameter DB_BLOCK_SIZE. For example, You have DB_BLOCK_SIZE set to 8K=8192 and you specified CREATE TABLESPACE command without any BLOCKSIZE clause then database use 8k blocksize for the specified tablespace.

The parameter DB_BLOCK_SIZE specifies the size of Oracle database blocks in bytes. The default value of this parameter is 8192 and value ranges between 2048 to 32768. But it must be multiple of physical block size at device level.

Now lets have an attention of the DB_nK_CACHE_SIZE parameters. Here n is variable and can be 2, 4, 8, 16, 32. That is DB_2K_CACHE_SIZE, DB_4K_CACHE_SIZE, DB_16K_CACHE_SIZE etc are available. Now question may come why we will set DB_nK_CACHE_SIZE? What advantage we will get. Before look for advantage (which will be discussed in another topic) let me why we need to set this parameter? This parameter will need to be set whenever I wish to make or want to make a tablespace with non-standard data block size.

Suppose your standard block size that block size set by DB_BLOCK_SIZE is 8K and you want to make a tablespace with block size 16K then at first you need to set DB_16K_CACHE_SIZE and then you need to create tablespace with BLOCKSIZE clause specifying 16K.

Here is an example which shows database standard block size is 8k and you have made an tablespace with 16k blocksize.

Example:
------------------------------
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

ALTER SYSTEM SET DB_16K_CACHE_SIZE=200M SCOPE=BOTH;
ALTER SYSTEM SET DB_CREATE_FILE_DEST='/oradata2';
CREATE TABLESPACE TEST BLOCKSIZE 16K;

Remember You can set this parameter only when DB_BLOCK_SIZE has a value other than nK. For example, if DB_BLOCK_SIZE=8192, then it is illegal to specify the parameter DB_8K_CACHE_SIZE Because the size for the 8 KB block cache is already specified by DB_CACHE_SIZE.
Related Documents

Choose an Optimal Block Size in Oracle

Choose an optimal Data block size in Oracle

Before going to proceed it will be better if you have an idea about DB_BLOCK_SIZE, BLOCKSIZE and DB_nK_CACHE_SIZE. In my post B_BLOCK_SIZE, BLOCKSIZE and DB_nK_CACHE_SIZE I tried to give an idea.

The default database data block buffer size is specified by the parameter DB_BLOCK_SIZE. It is common to ask what will be the data block size of my oracle database? Should I increase or decrease data block size whenever there is performance issue of my database. There is common scenario that with one blocksize a query takes 30 minutes and with another query of a tablespace a query takes 2 or 3 minutes. So choosing an optimal data block size is very necessary task.

The general rule while choosing an optimal data block size is,

•For OLTP (Online Transaction Processing) systems use smaller block sizes. I saw Default or 8K block size does fit well. However if you have enough memory for SGA you might think about 16K blocksize.

•For DSS (Decision Support Systems) systems use larger block sizes.

This is because a larger block size in a heavy duty OLTP system with lots and lots of scattered reads all over the places which might be bad as no one is going after the same blocks i.e I fetched a block in the memory and no one else needs that block- the data is cached but no one using that data.

A larger block size in a query intensive system that is in DSS system it may be very good as I was going to read the blocks right around my data anyway. And - people are going to be needing that data again and again.

Whatever the size of the data, the goal is to minimize the number of reads required to retrieve the desired data.

•If the rows are small and access to the rows are random , then choose a smaller block size.
•If the rows are small and access is sequential, then choose a larger block size.
•If the rows are small and access is both random and sequential, then it might be effective to choose a larger block size.
•If the rows are large, such as rows containing large object (LOB) data, then choose a larger block size.


In my next topics I demonstrated on advantage and disadvantage of settings different types of blocks.

Related Documents

Maximum Oracle Database Size.

How to determine OS block size for windows or unix

Estimate the dumpfile size before taking dump using data pump export

Settting maximum Size of Backup Set or Backup Piece

Optimizer choise of Outer Join in execution plan

Nested Loop Outer Joins
--------------------------------------------
This operation is used when an outer join is used between two tables. The outer join returns the outer table rows, even when there are no corresponding rows in the inner table.

In a regular outer join, the optimizer chooses the order of tables based on the cost. However, in a nested loop outer join, there is not alternative. The order of tables is determined by the join condition. The outer table, with rows that are being preserved, is used to drive to the inner table.

To use nested loop outer join explicitly USE_NL hint can be used.

Here is an example to use nested loop outer joins in oracle.

Based on the data in Example of Outer Join

SQL> select CustName, OrderDate, Address
from Customers c Right outer join Orders o
on c.CustNo = o.CustNo ;


Execution Plan
----------------------------------------------------------
Plan hash value: 1206303405

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 380 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 5 | 380 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | ORDERS | 5 | 55 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 65 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C006142 | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Hash Join Outer Joins
--------------------------------------------
The optimizer uses hash joins for processing an outer join if the data volume is high enough to make the hash join method efficient or if it is not possible to drive from the outer table to inner table.

Based on the data in Examples of All outer joins in Oracle

SQL> select a.n, b.k from table_a a left outer join table_b b on a.k=b.k;

10000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2129125445

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 166K| 603 (3)| 00:00:08 |
|* 1 | HASH JOIN OUTER | | 10000 | 166K| 603 (3)| 00:00:08 |
| 2 | TABLE ACCESS FULL| TABLE_A | 10000 | 90000 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TABLE_B | 1007K| 7873K| 589 (2)| 00:00:08 |
------------------------------------------------------------------------------

Sort Merge Outer Joins
--------------------------------------------
When an outer join cannot drive from the outer table to the inner table, it cannot use a hash join or nested loop joins. Then it uses the sort merge outer join for performing the join operation.

SQL> select CustName, OrderDate, Address
from Customers c left outer join Orders o
on c.CustNo = o.CustNo ;

Based on the data in Example of Outer Join


Execution Plan
----------------------------------------------------------
Plan hash value: 1595815448

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 380 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 5 | 380 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 4 | 260 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | SYS_C006142 | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 5 | 55 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | ORDERS | 5 | 55 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Full Outer Joins
---------------------------------------------
A full outer join acts like a combination of the left and right outer joins. you can specify FULL OUTER JOIN.

Curtesian Join in Execution Plan

Cartesian Join:
--------------------------
•A Cartesian join is used when one or more of the tables does not have any join conditions to any other tables in the statement. The optimizer joins every row from one data source with every row from the other data source, creating the Cartesian product of the two sets.

•The optimizer uses Cartesian joins when it is asked to join two tables with no join conditions. In some cases, a common filter condition between the two tables could be picked up by the optimizer as a possible join condition.

•In other cases, the optimizer may decide to generate a Cartesian product of two very small tables that are both joined to the same large table.

•Applying the ORDERED hint, instructs the optimizer to use a Cartesian join. By specifying a table before its join table is specified, the optimizer does a Cartesian join.

With the example of creation script of table_a in example Example of Using Hints in Index,
SQL> create table table_e as select level a from dual connect by level<=100;
Table created.

SQL> select a.n, b.a from table_a a , table_e b;

1000000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1945448542

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 15M| 627 (2)| 00:00:08 |
| 1 | MERGE JOIN CARTESIAN| | 1000K| 15M| 627 (2)| 00:00:08 |
| 2 | TABLE ACCESS FULL | TABLE_E | 100 | 1300 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 10000 | 30000 | 624 (2)| 00:00:08 |
| 4 | TABLE ACCESS FULL | TABLE_A | 10000 | 30000 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Sort Merge Joins

Overview of Sort Merge Joins
------------------------------------------------
Sort merge joins can be used to join rows from two independent sources. Hash joins generally perform better than sort merge joins. On the other hand, sort merge joins can perform better than hash joins if both of the following conditions exist:

•The row sources are sorted already.
•A sort operation does not have to be done.

Sort merge joins are useful when the join condition between two tables is an inequality condition (but not a nonequality) like <, <=, >, or >=. Sort merge joins perform better than nested loop joins for large data sets. You cannot use hash joins unless there is an equality condition.
In a merge join, there is no concept of a driving table. The join consists of two steps:

1.Sort join operation: Both the inputs are sorted on the join key.
2.Merge join operation: The sorted lists are merged together.

If the input is already sorted by the join column, then a sort join operation is not performed for that row source.

When the Optimizer Uses Sort Merge Joins
-----------------------------------------------------
The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:

•The join condition between two tables is not an equi-join.
•Because of sorts already required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join.

Sort Merge Join Hints
--------------------------------------------
To instruct the optimizer to use a sort merge join, apply the USE_MERGE hint. You might also need to give hints to force an access path.

There are situations where it is better to override the optimize with the USE_MERGE hint.

Example of Sort Merge Joins
----------------------------------


Based on the data in Example of using Index by Hints
SQL> select a.n, b.b from table_a a , table_b b where a.k>b.k;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3680082791

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | | 5265 (2)| 00:01:04 |
| 1 | MERGE JOIN | | 1 | 21 | | 5265 (2)| 00:01:04 |
| 2 | SORT JOIN | | 10000 | 90000 | 408K| 50 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TABLE_A | 10000 | 90000 | | 8 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 1007K| 11M| 38M| 5214 (2)| 00:01:03 |
| 5 | TABLE ACCESS FULL| TABLE_B | 1007K| 11M| | 590 (2)| 00:00:08 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access(INTERNAL_FUNCTION("A"."K")>INTERNAL_FUNCTION("B"."K"))
filter(INTERNAL_FUNCTION("A"."K")>INTERNAL_FUNCTION("B"."K"))

Jokes between Bill Gates and God

Former President of America Bill Clinton, Al Gore, and Bill Gates all died in a plane crash and when they meet to their lord, the supreme deity looked to Al Gore and asked, tell what is important about yourself?

Al Gore responded that he felt that the earth was the ultimate importance and that protecting the earth's ecological system was most important.

Lord then looked to Al Gore and asked, " I like the way you think, come and sit at my left hand". God then asked Bill Clinton what he think?.

Bill Clinton responded that he felt about the people and their personal choices were most important.

Lord responded, " I also like the way you think, come and sit at my right hand". Lord then turned to Bill Gates "What is your problem Bill Gates?"

Bill responded " The chair you are now on that is owner by me".

Hash Joins

Overview of Hash Joins
------------------------------------------
•Hash joins are used for joining large data sets.

•The optimizer uses the smaller of two tables or data sources to build a hash table on the join key in memory.

•It then scans the larger table, probing the hash table to find the joined rows.

•This method is best used when the smaller table fits in available memory.

•The cost is then limited to a single read pass over the data for the two tables.

When the Optimizer Uses Hash Joins
----------------------------------------------------
The optimizer uses a hash join to join two tables if they are joined using an equijoin and if either of the following conditions are true:

•A large amount of data needs to be joined.
•A large fraction of a small table needs to be joined.

Hash Join Hints
--------------------------------------------
Apply the USE_HASH hint to instruct the optimizer to use a hash join when joining two tables together.

Hash Join Example:
--------------------------------
You can see the hash join example in my post Use of Hint to use Index

Nested Loop Joins

Overview Nested Loop Joins
---------------------------------------
•Oracle always join two of the tables. In join one row set is called inner, and the other is called outer. If the inner table row set is dependent or derived from outer table then nested loop join performs better.

•But if the inner table's access path is independent of the outer table, then the same rows are retrieved for every iteration of the outer loop, degrading performance considerably. In such cases, hash joins joining the two independent row sources perform better.

•Nested loop joins are useful when small subsets of data are being joined and if the join condition is an efficient way of accessing the second table.

How Nested Loop Join Works
---------------------------------------------------
In case of nested loop join the following steps is involved.
1)The optimizer first determine the driving table and designates it as the outer table.

2)The optimizer designate other table (driven/dependent) as inner table.

3)For every row in the outer table, Oracle accesses all the rows in the inner table. The outer loop is for every row in outer table and the inner loop is for every row in the inner table. The outer loop appears before the inner loop in the execution plan, as follows:

NESTED LOOPS
outer_loop
inner_loop

When the Optimizer Uses Nested Loop Joins
-----------------------------------------------------------
•The optimizer uses nested loop joins when joining small number of rows, with a good driving condition between the two tables. You drive from the outer loop to the inner loop, so the order of tables in the execution plan is important.

•The outer loop is the driving row source. It produces a set of rows for driving the join condition. The row source can be a table accessed using an index scan or a full table scan. Also, the rows can be produced from any other operation. For example, the output from a nested loop join can be used as a row source for another nested loop join.

•The inner loop is iterated for every row returned from the outer loop, ideally by an index scan. If the access path for the inner loop is not dependent on the outer loop, then you can end up with a Cartesian product; for every iteration of the outer loop, the inner loop produces the same set of rows. Therefore, you should use other join methods when two independent row sources are joined together.

Nested Loop Join Hints
------------------------------------------
If the optimizer is choosing to use some other join method, you can use the USE_NL(table1 table2) hint, where table1 and table2 are the aliases of the tables being joined.

Nested Loop Join Example
------------------------------------------
Here I used the script and data that is listed in post Examples of Outer Joins
SQL> select address, orderdate from customers c, orders o where c.custno=o.custno;


Execution Plan
----------------------------------------------------------
Plan hash value: 3442778016

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 275 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 5 | 275 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | ORDERS | 5 | 55 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 44 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C006142 | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("C"."CUSTNO"="O"."CUSTNO")

Here the outer loop is | 2 | TABLE ACCESS FULL | ORDERS |
And inner loop is | 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS |
|* 4 | INDEX UNIQUE SCAN |

How the Query Optimizer Chooses Execution Plans for Joins

In order to know various types of joins search inside my blog.
In a join, one row set is called inner, and the other is called outer.
To choose an execution plan for a join statement, the optimizer must make these interrelated decisions.

1)Access Paths
2)Join Method: To join each pair of row sources, Oracle must perform a join operation. Join methods include nested loop, sort merge, cartesian, and hash joins.
3)Join Order: To execute a statement that joins more than two tables, Oracle joins two of the tables and then joins the resulting row source to the next table. This process is continued until all tables are joined into the result.

During choosing an execution plan the query optimizer considers the following:

The optimizer first determines whether joining two or more tables definitely results in a row source containing at most one row. If such a situation exists, then the optimizer places these tables first in the join order. The optimizer then optimizes the join of the remaining set of tables.

With the query optimizer, the optimizer generates a set of execution plans, according to possible join orders, join methods, and available access paths. The optimizer then estimates the cost of each plan and chooses the one with the lowest cost. The optimizer estimates costs in the following ways:


•The cost of a nested loops operation is based on the cost of reading each selected row of the outer table and each of its matching rows of the inner table into memory. The optimizer estimates these costs using the statistics in the data dictionary.

•The cost of a sort merge join is based largely on the cost of reading all the sources into memory and sorting them.

•The cost of a hash join is based largely on the cost of building a hash table on one of the input sides to the join and using the rows from the other of the join to probe it.

•A smaller sort area size(That is settings of PGA_AGGREGATE_TARGET) is likely to increase the cost for a sort merge join because sorting takes more CPU time and I/O in a smaller sort area.

•A larger multiblock read count is likely to decrease the cost for a sort merge join in relation to a nested loop join.

Sample Table Scans in Oracle

•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 |

Index Skip, Full, Fast Full Index, Index Joins Bitmap Indexes Scan

A)Index Skip Scan
----------------------------------------
As the name suggest index skip scan does not scan complete index. But it scan of the subindexes.

Index skip scan lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.

The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

Suppose if I make a make a composite index with two columns sex and id. The leading column sex contains only two distinct columns. Now if I query with non-leading column that is with id column then index skip scan will be used.

Example:
------------------

SQL> create table test_skip_scan (sex varchar2(1), id number, address varchar2(20));
Table created.

SQL> create index test_skip_scan_I on test_skip_scan(sex,id);
Index created.

SQL> begin
for i in 1 .. 10000
loop
insert into test_skip_scan values(decode(remainder(abs(round(dbms_random.value(2,20),0)),2),0,'M','F'),i,null);
end loop;
end;
/
PL/SQL procedure successfully completed.

SQL> analyze table test_skip_scan estimate statistics;
Table analyzed.

SQL> select * from test_skip_scan where id=1;


Execution Plan
----------------------------------------------------------
Plan hash value: 2410156502

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_SKIP_SCAN | 1 | 9 | 4 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TEST_SKIP_SCAN_I | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)
filter("ID"=1)

B)Index Fast Full Scan
---------------------------------------
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint.

A fast full scan accesses the data in the index itself, without accessing the table.

It cannot be used to eliminate a sort operation, because the data is not ordered by the index key.

It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.

Fast full index scans cannot be performed against bitmap indexes.

You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint.

Example:
-----------------
SQL> select /*+INDEX_FFS(test_skip_scan)*/ sex,id from test_skip_scan;

10000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4280781105

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| TEST_SKIP_SCAN_I | 10000 | 40000 | 7 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------


C)Index Joins
-------------------------
An index join is a hash join of several indexes that together contain all the table columns that are referenced in the query. If an index join is used, then no table access is needed, because all the relevant column values can be retrieved from the indexes. An index join cannot be used to eliminate a sort operation.

You can specify an index join with the INDEX_JOIN hint. For more information on the INDEX_JOIN hint.

SQL> select sex,id from test_skip_scan where id in (select col1 from test_tab);

1000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1059662925

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 6993 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN RIGHT SEMI | | 999 | 6993 | 9 (12)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| TEST_TAB_I | 1000 | 3000 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST_SKIP_SCAN | 10000 | 40000 | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("ID"="COL1")


D)Bitmap Indexes
-----------------------------------
A bitmap join uses a bitmap for key values and a mapping function that converts each bit position to a rowid. Bitmaps can efficiently merge indexes that correspond to several conditions in a WHERE clause, using Boolean operations to resolve AND and OR conditions.

Bitmap indexes and bitmap join indexes are available only if you have purchased the Oracle Enterprise Edition.

What and when Index Scans is used

In case of index scan a row is retrieved by traversing the index, using the indexed column values specified by the statement. An index scan retrieves data from an index based on the value of one or more columns in the index. To perform an index scan, Oracle searches the index for the indexed column values accessed by the statement. If the statement accesses only columns of the index, then Oracle reads the indexed column values directly from the index, rather than from the table.

The index contains not only the indexed value, but also the rowids of rows in the table having that value. Therefore, if the statement accesses other columns in addition to the indexed columns, then Oracle can find the rows in the table by using either a table access by rowid or a cluster scan.

An index scan can be various types like

1)Index Unique Scans
2)Index Range Scans
3)Index Range Scans Descending
4)Index Skip Scans
5)Full Scans
6)Fast Full Index Scans
7)Index Joins
8)Bitmap Indexes


1)Index Unique Scans
----------------------------------------------
This scan returns, at most, a single rowid. Oracle performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed.

This access path is used when all columns of a unique (B-tree) index or an index created as a result of a primary key constraint are specified with equality conditions. There is an example in later of this section.

2)Index Range Scans
----------------------------------------------------
•An index range scan is a common operation for accessing selective data.

•Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.

•If data must be sorted by order, then use the ORDER BY clause, and do not rely on an index. If an index can be used to satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort.

•The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions like col1=1 or col1<1 or col1>1 or (col1=1 AND col1=99 AND ..)

•Range scans can use unique or non-unique indexes. Range scans avoid sorting when index columns constitute the ORDER BY/GROUP BY clause.

•The hint INDEX(table_alias index_name) instructs the optimizer to use a specific index.

•Note that leading wildcards like %text does not result range scan but text% might result range scan.
Look at following examples, 88% used range scans but %88 did not used range scans.

SQL> create table table_a(n number ,k varchar2(15));
Table created.

SQL> begin
for i in 1 .. 10000
loop
insert into table_a values(i,'pc-'||round(dbms_random.value(1,20000),0));
end loop;
end;
/
2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> create index table_a_I_K on table_a(k);
Index created.

SQL> select * from table_a where k like '88%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1124802227

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLE_A | 1 | 22 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TABLE_A_I_K | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("K" LIKE '88%')
filter("K" LIKE '88%')

Note
-----
- dynamic sampling used for this statement


SQL> select * from table_a where k like '%88';
102 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1923776651

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102 | 2244 | 8 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TABLE_A | 102 | 2244 | 8 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("K" LIKE '%88')

Note
-----
- dynamic sampling used for this statement

3)Index Range Scans Descending
------------------------------------------------------
•An index range scan descending is identical to an index range scan, except that the data is returned in descending order.

•Indexes, by default, are stored in ascending order.

•The optimizer uses index range scan descending when an order by descending clause can be satisfied by an index.

•The hint INDEX_DESC(table_alias index_name) is used for index range scan descending.

Example:
-------------------------------------
SQL> select * from table_a where k like '8888%';

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1124802227

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLE_A | 1 | 9 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TABLE_A_I_K | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("K" LIKE '8888%')
filter("K" LIKE '8888%')
SQL> select /*+index_desc(table_a)*/ * from table_a where k like '8888%';

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3364135956

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TABLE_A | 1 | 9 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| TABLE_A_I_K | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("K" LIKE '8888%')
filter("K" LIKE '8888%' AND "K" LIKE '8888%')

4)Index Skip Scans:
-----------------------------
Discussed in
5)Full Scans
------------------------------
Discussed in
6)Fast Full Index Scans
-----------------------------------------
Discussed in
7)Index Joins
------------------------------------------
Discussed in
8)Bitmap Indexes
---------------------------------------------
Discussed in
To illustrate an example create a table and make it's column primary key. Now put the indexed column in the where clause with an equality operator. Note that index unique scan will be used.


SQL> create table test_tab2 as select level col1, level col2 from dual connect by level<=100;
Table created.
Case 1: No index, so full table scan will performed.
--------------------------------------------------------------------------------
SQL> select * from test_tab2 where col1=99;

Execution Plan
----------------------------------------------------------
Plan hash value: 700767796

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_TAB2 | 1 | 26 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("COL1"=99)

Note
-----
- dynamic sampling used for this statement

Create non-unique index on the table.

SQL> create index test_tab2_I on test_tab2(col1);
Index created.

Case 2: As on col1 there is non-unique index so range scan will be performed.
-------------------------------------------------------------------------------------------------
SQL> select * from test_tab2 where col1=99;


Execution Plan
----------------------------------------------------------
Plan hash value: 465564947

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB2 | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_TAB2_I | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COL1"=99)

Note
-----
- dynamic sampling used for this statement


Now drop the index and add primary key on the table.
SQL> drop index test_tab2_I;
Index dropped.

SQL> alter table
2 test_tab2 add primary key(col1);
Table altered.

Case 3: Adding primary key with equality operation on column causes to use index unique scan.
------------------------------------------------------------------------------
SQL> select * from test_tab2 where col1=99;


Execution Plan
----------------------------------------------------------
Plan hash value: 1384425796

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB2 | 1 | 26 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C006487 | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COL1"=99)

What and when oracle uses Rowid Scans in execution plans

What is Rowid Scans?
-------------------------------------------
•The rowid of a row specifies the datafile and data block containing the row and the location of the row in that block. Locating a row by specifying its rowid is the fastest way to retrieve a single row, because the exact location of the row in the database is specified.

•To access a table by rowid, Oracle first obtains the rowids of the selected rows, either from the statement's WHERE clause or through an index scan of one or more of the table's indexes. Oracle then locates each selected row in the table based on its rowid.

When the Optimizer Uses Rowids?
------------------------------------------------------------
Suppose I made an index on table test_tab column col1. The table has two columns col1 and col2. Now if in my query I use to select both columns like I use SELECT * FROM TEST_TAB WHERE COL1=1; then optimizer at first step retrieve the rowid from index on column col1 and then in the second step optimizer look for row using that rowid.

Access by rowid does not need to follow every index scan. If the index contains all the columns needed for the statement, then table access by rowid might not occur.

An example will make you clear.
SQL> create table test_tab as select level col1, level col2 from dual connect by level<=1000;
Table created.

SQL> set autot trace
SQL> create index test_tab_I on test_tab(col1);
Index created.

Here, I select col1 , the only column by which I created index test_tab_I. So, to select only col1 no need to rowid scan.

SQL> select col1 from test_tab where col1=99;


Execution Plan
----------------------------------------------------------
Plan hash value: 933728095

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_TAB_I | 1 | 13 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("COL1"=99)

Note
-----
- dynamic sampling used for this statement

Here I select both col1 and col2. col2 is not inside test_tab_I index. So it used index rowid scan to find the col2 field.
SQL> select * from test_tab where col1=99;


Execution Plan
----------------------------------------------------------
Plan hash value: 3125486718

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_TAB_I | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COL1"=99)

Note
-----
- dynamic sampling used for this statement


Note that in both cases dynamic sampling are used. Because I have not gather statistics. If I generate statistics then note will disappear.


SQL> analyze table test_tab estimate statistics;
Table analyzed.

SQL> select * from test_tab where col1=99;


Execution Plan
----------------------------------------------------------
Plan hash value: 3125486718

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 6 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_TAB_I | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COL1"=99)

What and when oracle use Full Table Scans in explain plans

Full Table Scans
-------------------------------
•Full table scan reads all rows from a table.

•After scanning all rows then it filters out those that do not meet the selection criteria.

•Actually full table scan scans all blocks in the table that are under the high water mark. The high water mark indicates the amount of used space, or space that had been formatted to receive data.

•During full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process. The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads means a full table scan can be performed very efficiently.

•Full table scans are cheaper than index range scans when accessing a large fraction of the blocks in a table. This is because full table scans can use larger I/O calls, and making fewer large I/O calls is cheaper than making many smaller calls.

In the following section it is said when optimizer decides full table scans.

When the Optimizer Uses Full Table Scans
-----------------------------------------------------
1)Lack of Index: If the query is unable to use any existing indexes, then it uses a full table scan. For example, if there is a function used on the indexed column in the query, the optimizer is unable to use the index and instead uses a full table scan.

2)Large Amount of Data: If the optimizer thinks that the query will access most of the blocks in the table, then it uses a full table scan, even though indexes might be available.

3)Small Table: If a table contains less than DB_FILE_MULTIBLOCK_READ_COUNT blocks under the high water mark, which can be read in a single I/O call, then a full table scan might be cheaper than an index range scan, regardless of the fraction of tables being accessed or indexes present.

4)High Degree of Parallelism:A high degree of parallelism for a table skews the optimizer toward full table scans over range scans. Examine the DEGREE column in ALL_TABLES for the table to determine the degree of parallelism.

To perform full table scan use, FULL hints, like
SQL> select /*+ FULL(TEST_xplan)*/ * from test_xplan where a=1;


Related Documents:
------------------------------
How does one prevent Oracle from using index
Use of Hint to use index

Understanding Access Paths for the Query Optimizer

Access paths are the ways in which data is retrieved from the database. For example whether data will be retrieved through full table scans or index scans. In general, index access paths should be used for statements that retrieve a small subset of table rows, while full scans are more efficient when accessing a large portion of the table. The optimizer take decision which access path it will be used based on the cost of the access path if you don't provide hints.

In the following section I ran 4 query and we see
first one used index unique scan(select a from test_xplan where a=1)
second one used index fast full scan(select a from test_xplan where a>1)
third one used full table scan(select * from test_xplan where a>1) and
fourth one (select * from test_xplan where a=1) used rowid scans.

I used following examples.

SQL> create table test_xplan(a number primary key, b number);
Table created.

SQL> insert into test_xplan select level a , level b from dual connect by level<=100;
100 rows created.
1)SQL> select a from test_xplan where a=1;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2336596025

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| SYS_C006438 | 1 | 13 | 1 (0)| 00:00:01 |

2)SQL> select a from test_xplan where a>1;

99 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 623884626

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 1287 | 2 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| SYS_C006438 | 99 | 1287 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
3)SQL> select * from test_xplan where a>1;

99 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3901816595

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 2574 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_XPLAN | 99 | 2574 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------

4)SQL> select * from test_xplan where a=1;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1650693642

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_XPLAN | 1 | 26 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C006438 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

In all cases optimizer decides base on the access path costs which one it will use.

The data access paths that can be used to locate and retrieve any row in any table are,

1)Full Table Scans-Described in
http://arjudba.blogspot.com/2008/06/what-and-when-oracle-use-full-table.html
2)Rowid Scans-Described in http://arjudba.blogspot.com/2008/06/what-and-when-oracle-uses-rowid-scans.html
3)Index Scans-Described in http://arjudba.blogspot.com/2008/06/what-and-when-index-scans-is-used.html
4)Cluster Access-Described in
5)Hash Access-Described in
6)Sample Table Scans-Described in

Now question is how query optimizer chooses an access path?

The query optimizer chooses an access path based on the following factors:

-The available access paths for the statement
-The estimated cost of executing the statement, using each access path or combination of paths

To choose an access path, the optimizer first determines which access paths are available by examining the conditions in the statement's WHERE clause and its FROM clause. The optimizer then generates a set of possible execution plans using available access paths and estimates the cost of each plan, using the statistics for the index, columns, and tables accessible to the statement. Finally, the optimizer chooses the execution plan with the lowest estimated cost.

When choosing an access path, the query optimizer is influenced by Optimizer Hints and Old Statistics. For example, if a table has not been analyzed since it was created, and if it has less than DB_FILE_MULTIBLOCK_READ_COUNT blocks under the high water mark, then the optimizer thinks that the table is small and uses a full table scan. Review the LAST_ANALYZED and BLOCKS columns in the ALL_TABLES table to examine the statistics.