Oracle Optimizer's Join Method

A join method is the mechanism for joining two row sources. Depending on the statistics, the optimizer chooses the method with the lowest estimated cost.

This section contains the following topics:

• Nested Loops Joins
• Hash Joins
• Sort Merge Joins
• Cluster Joins

Nested Loop Join

Suppose somebody gave you a telephone book and a list of 20 names to look up, and then asked you to write down each person’s name and corresponding telephone number. You would probably go down the list of names, looking up each one in the telephone book one at a time. This task would be pretty easy because the telephone book is alphabetized by name. Moreover, somebody looking over your shoulder could begin calling the first few numbers you write down while you are still looking up the rest. This scene describes a NESTED LOOPS join.

In a NESTED LOOPS join, Oracle reads the first row from the first row source and then checks the second row source for matches. All matches are then placed in the result set and Oracle goes on to the next row from the first row source. This continues until all rows in the first row source have been processed. The first row source is often called the outer or driving table, whereas the second row source is called the inner table. Using a NESTED LOOPS join is one of the fastest methods of receiving the first records back from a join.

NESTED LOOPS joins are ideal when the driving row source (the records you are looking for) is small and the joined columns of the inner row source are uniquely indexed or have a highly selective nonunique index. NESTED LOOPS joins have an advantage over other join methods in that they can quickly retrieve the first few rows of the result set without having to wait for the entire result set to be determined. This situation is ideal for query screens where an end user can read the first few records retrieved while the rest are being fetched. NESTED LOOPS joins are also flexible in that any two-row sources can always be joined by NESTED LOOPS—regardless of join condition and schema definition.

However, NESTED LOOPS joins can be very inefficient if the inner row source (second table accessed) does not have an index on the joined columns or if the index is not highly selective. If the driving row source (the records retrieved from the driving table) is quite large, other join methods may be more efficient.

Figure 1 below illustrates the method of executing the query shown next where the DEPT table is accessed first and the result is then looped through the EMP table with a NESTED LOOPS join. The type of join performed can be forced with a hint and will vary due to different variables on your system.

Oracle SORT-MERGE Joins

Suppose two salespeople attend a conference and each collect over 100 business cards from potential new customers. They now each have a pile of cards in random order, and they want to see how many cards are duplicated in both piles. The salespeople alphabetize their piles, and then they call off names one at a time. Because both piles of cards have been sorted, it becomes much easier to find the names that appear in both piles. This example describes a SORT-MERGE join.

In a SORT-MERGE join, Oracle sorts the first row source by its join columns, sorts the second row source by its join columns, and then merges the sorted row sources together. As matches are found, they are put into the result set. SORT-MERGE joins can be effective when lack of data selectivity or useful indexes render a NESTED LOOPS join inefficient, or when both of the row sources are quite large (greater than 5 percent of the blocks accessed).

However, SORT-MERGE joins can be used only for equijoins (WHERE D.deptno = E.deptno, as opposed to WHERE D.deptno >= E.deptno). SORT-MERGE joins require temporary segments for sorting (if SORT_AREA_SIZE or the automatic memory parameters like MEMORY_TARGET are set too small). This can lead to extra memory utilization and/or extra disk I/O in the temporary tablespace. Table 1 below illustrates the method of executing the query shown next when a SORT-MERGE join is performed.

 

Table 1. SORT-MERGE join

Oracle HASH Joins

HASH joins are the usual choice of the Oracle optimizer when the memory is set up to accommodate them. In a HASH join, Oracle accesses one table (usually the smaller of the joined results) and builds a hash table on the join key in memory. It then scans the other table in the join (usually the larger one) and probes the hash table for matches to it. Oracle uses a HASH join efficiently only if the parameter PGA_AGGREGATE_TARGET is set to a large enough value. If MEMORY_TARGET is used, the PGA_AGGREGATE_TARGET is included in the MEMORY_TARGET, but you may still want to set a minimum.

If you set the SGA_TARGET, you must set the PGA_AGGREGATE_TARGET as the SGA_TARGET does not include the PGA (unless you use MEMORY_TARGET as just described). The HASH join is similar to a NESTED LOOPS join in the sense that there is a nested loop that occurs—Oracle first builds a hash table to facilitate the operation and then loops through the hash table. When using an ORDERED hint, the first table in the FROM clause is the table used to build the hash table.

HASH joins can be effective when the lack of a useful index renders NESTED LOOPS joins inefficient. The HASH join might be faster than a SORT-MERGE join, in this case, because only one row source needs to be sorted, and it could possibly be faster than a NESTED LOOPS join because probing a hash table in memory can be faster than traversing a b-tree index.

As with SORT-MERGE joins and CLUSTER joins, HASH joins work only on equijoins. As with SORT-MERGE joins, HASH joins use memory resources and can drive up I/O in the temporary tablespace if the sort memory is not sufficient (which can cause this join method to be extremely slow).

Finally, HASH joins are available only when cost-based optimization is used (which should be 100 percent of the time for your application running on Oracle 11g).

Table 1 illustrates the method of executing the query shown in the listing that follows when a HASH join is used.

Table 1. HASH join

 

Oracle CLUSTER Joins

A CLUSTER join in Oracle is really just a special case of the NESTED LOOPS join that is not used very often. If the two row sources being joined are actually tables that are part of a cluster, and if the join is an equijoin between the cluster keys of the two tables, then Oracle can use a CLUSTER join. In this case, Oracle reads each row from the first row source and finds all matches in the second row source by using the CLUSTER index.

CLUSTER joins are extremely efficient because the joining rows in the two row sources will actually be located in the same physical data block. However, clusters carry certain caveats of their own, and you cannot have a CLUSTER join without a cluster. Therefore, CLUSTER joins are not very commonly used.

SQL Processing Flow in Oracle

About SQL Processing

SQL processing is the parsing, optimization, row source generation, and execution of a SQL statement. Depending on the statement, the database may omit some of these stages. Figure 1 depicts the general stages of SQL processing

Figure 1 Stages of SQL Processing

Description of "Figure 1 Stages of SQL Processing"

1.1 SQL Parsing

As shown in Figure 1, the first stage of SQL processing is parsing. This stage involves separating the pieces of a SQL statement into a data structure that other routines can process. The database parses a statement when instructed by the application, which means that only the application­, and not the database itself, can reduce the number of parses.

When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution. The parse call opens or creates a cursor, which is a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information. The cursor and private SQL area are in the program global area (PGA).

During the parse call, the database performs the following checks:

• Syntax Check
• Semantic Check
• Shared Pool Check

The preceding checks identify the errors that can be found before statement execution. Some errors cannot be caught by parsing. For example, the database can encounter deadlocks or errors in data conversion only during statement execution.

1.1.1 Syntax Check

Oracle Database must check each SQL statement for syntactic validity. A statement that breaks a rule for well-formed SQL syntax fails the check. For example, the following statement fails because the keyword FROM is misspelled as FORM:

SQL> SELECT * FORM employees;
SELECT * FORM employees
         *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

1.1.2 Semantic Check

The semantics of a statement are its meaning. Thus, a semantic check determines whether a statement is meaningful, for example, whether the objects and columns in the statement exist. A syntactically correct statement can fail a semantic check, as shown in the following example of a query of a nonexistent table:

SQL> SELECT * FROM nonexistent_table;
SELECT * FROM nonexistent_table
              *
ERROR at line 1:
ORA-00942: table or view does not exist

1.1.3 Shared Pool Check

During the parse, the database performs a shared pool check to determine whether it can skip resource-intensive steps of statement processing. To this end, the database uses a hashing algorithm to generate a hash value for every SQL statement. The statement hash value is the SQL ID shown inV$SQL.SQL_ID. This hash value is deterministic within a version of Oracle Database, so the same statement in a single instance or in different instances has the same SQL ID.

When a user submits a SQL statement, the database searches the shared SQL area to see if an existing parsed statement has the same hash value. The hash value of a SQL statement is distinct from the following values:

• Memory address for the statementOracle Database uses the SQL ID to perform a keyed read in a lookup table. In this way, the database obtains possible memory addresses of the statement.
• Hash value of an execution plan for the statementA SQL statement can have multiple plans in the shared pool. Typically, each plan has a different hash value. If the same SQL ID has multiple plan hash values, then the database knows that multiple plans exist for this SQL ID.

Parse operations fall into the following categories, depending on the type of statement submitted and the result of the hash check:

• Hard parse

If Oracle Database cannot reuse existing code, then it must build a new executable version of the application code. This operation is known as a hard parse, or a library cache miss.

Note:

The database always perform a hard parse of DDL.

During the hard parse, the database accesses the library cache and data dictionary cache numerous times to check the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on required objects so that their definition does not change. Latch contention increases statement execution time and decreases concurrency.

• Soft parse

A soft parse is any parse that is not a hard parse. If the submitted statement is the same as a reusable SQL statement in the shared pool, then Oracle Database reuses the existing code. This reuse of code is also called a library cache hit.

Soft parses can vary in how much work they perform. For example, configuring the session shared SQL area can sometimes reduce the amount of latching in the soft parses, making them "softer."

In general, a soft parse is preferable to a hard parse because the database skips the optimization and row source generation steps, proceeding straight to execution.

Figure 2 is a simplified representation of a shared pool check of an UPDATE statement in a dedicated server architecture.

Figure 2 Shared Pool Check

Description of "Figure 2 Shared Pool Check"

If a check determines that a statement in the shared pool has the same hash value, then the database performs semantic and environment checks to determine whether the statements have the same meaning. Identical syntax is not sufficient. For example, suppose two different users log in to the database and issue the following SQL statements:

CREATE TABLE my_table ( some_col INTEGER );
SELECT * FROM my_table;

The SELECT statements for the two users are syntactically identical, but two separate schema objects are named my_table. This semantic difference means that the second statement cannot reuse the code for the first statement.

Even if two statements are semantically identical, an environmental difference can force a hard parse. In this context, the optimizer environment is the totality of session settings that can affect execution plan generation, such as the work area size or optimizer settings (for example, the optimizer mode). Consider the following series of SQL statements executed by a single user:

ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS;
ALTER SYSTEM FLUSH SHARED_POOL;               # optimizer environment 1
SELECT * FROM sh.sales;

ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS;  # optimizer environment 2
SELECT * FROM sh.sales;

ALTER SESSION SET SQL_TRACE=true;             # optimizer enviornment 3
SELECT * FROM sh.sales;

In the preceding example, the same SELECT statement is executed in three different optimizer environments. Consequently, the database creates three separate shared SQL areas for these statements and forces a hard parse of each statement.

2 How Oracle Database Processes DDL

Oracle Database processes DDL differently from DML. For example, when you create a table, the database does not optimize the CREATE TABLEstatement. Instead, Oracle Database parses the DDL statement and carries out the command.

The database processes DDL differently because it is a means of defining an object in the data dictionary. Typically, Oracle Database must parse and execute many recursive SQL statements to execute a DDL statement. Suppose you create a table as follows:

CREATE TABLE mytable (mycolumn INTEGER);

Typically, the database would run dozens of recursive statements to execute the preceding statement. The recursive SQL would perform actions such as the following:

• Issue a COMMIT before executing the CREATE TABLE statement
• Verify that user privileges are sufficient to create the table
• Determine which tablespace the table should reside in
• Ensure that the tablespace quota has not been exceeded
• Ensure that no object in the schema has the same name
• Insert rows that define the table into the data dictionary
• Issue a COMMIT if the DDL statement succeeded or a ROLLBACK if it did not

3 Shared Pool  Memory Allocation and Reuse

In general, any item (shared SQL area or dictionary row) in the shared pool remains until it is flushed according to a modified LRU algorithm. The memory for items that are not being used regularly is freed if space is required for new items that must be allocated some space in the shared pool. A modified LRU algorithm allows shared pool items that are used by many sessions to remain in memory as long as they are useful, even if the process that originally created the item terminates. As a result, the overhead and processing of SQL statements associated with a multiuser Oracle system is minimized.

When a SQL statement is submitted to Oracle for execution, Oracle automatically performs the following memory allocation steps:

1. Oracle checks the shared pool to see if a shared SQL area already exists for an identical statement. If so, that shared SQL area is used for the execution of the subsequent new instances of the statement.Alternatively, if there is no shared SQL area for a statement Oracle allocates a new shared SQL area in the shared pool. In either case, the users private SQL area is associated with the shared SQL area that contains the statement.

2. Oracle allocates a private SQL area on behalf of the session. Thelocation of the private SQL area depends on the type of connection established for the session.

Oracle also flushes a shared SQL area from the shared pool in these circumstances:

• When the DBMS_STATS statement is used to update or delete the statistics of a table, cluster, or index, all shared SQL areas that contain statements referencing the analyzed schema object are flushed from the shared pool. The next time a flushed statement is run, the statement is parsed in a new shared SQL area to reflect the new statistics for the schema object.

• If a schema object is referenced in a SQL statement and that object is later modified in any way, the shared SQL area is invalidated (marked invalid), and the statement must be reparsed the next time it is run.

• If you change a databases global database name, all information is flushed from the shared pool.

• The administrator can manually flush all information in the shared pool to assess the performance (with respect to the shared pool, not the data buffer cache) that can be expected after instance startup without shutting down the current instance. The statement ALTER SYSTEM FLUSH SHARED_POOL is used to do this.

Note: A shared SQL area can be flushed from the shared pool, even if the shared SQL area corresponds to an open cursor that has not been used for some time. If the open cursor is subsequently used to run its statement, Oracle reparses the statement, and a new shared SQL area is allocated in the shared pool.

Using B-Tree Indexes - Indexes Fundamental

Content:

•  When B Tree Index should be used. We will try to understand some of the situations when the optimizer will use B Tree Index.
•  Index Clustering Factor.
•  Influence of parameter OPTIMIZER_MODE and OPTIMIZER_INDEX_COST_ADJ on using B Tree Index.

When to use B-Tree Index:

Although it can differ from situation to situation that when Oracle Optimizer will decide to use index or not, based on table size, available statistics, index clustering factor, several db parameters like OPTIMIZER_MODE and OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_INDEX_CACHING etc and other things.

But mainly B*Tree index are used, when:

1. You are fetching very small fraction of the rows from the table using index. B*Tree index should be created on the columns that are frequently used in predicates, join condition of query.

2. You are fetching many/all rows of a table and the index can be used instead of the table (you are only selecting the columns that you have indexed).

When we access the table using index, Oracle will scan the index and from the index leaf nodes it will get the ROWID, then using the rowid it will read the data block from the table segment. This is typically known as "TABLE ACCES BY ROWID". This is very efficient method when you are accessing a small percentage of rows but however its not that efficient while you are reading a large amount of rows. Now the statement "small percentage" is very much relative. In a thin table it might be 2-3% or 5% but in a fat table it may be upto 20-25%. And Index Clustering Factor has significant impact on this small percentage value.

Table Access by Index Rowid

 An index is stored sorted by index key. The index might be ascending order or it might be descending order in case of Descending index. The index will be accessed in sorted order by key, it will be sequential access and from the leaf block it will get the rowid to access the data blocks of the table segment and data block are stored randomly, scattered in heap. Therefore when Oracle does a table access by index rowid, it will not access the table in sorted order, it will not go to block 1, block 2, block 3, rather it will search for blocks that are scattered in the heap. For example it might go to block 10, then block 533, then block 777, then again block 10. It will do n number of "table access by index Rowid" to get each and every row and to do it will have to read and reread blocks.

There will be lots of scattered, single block read from here and there in table segment. Typically in the thin table a single block will hold more number of rows, while in a fat table a block will hold less number of rows.

Suppose DB_BLOCK_SIZE = 8k. A thin table has 1 Million rows. And if rows are almost about 80 bytes in size, so logically there will be about 8k/80b = 100 rows per block. That means the table has approzimately 100*100 = 10,000 blocks.

If read 2,00,000 rows via the index; there will be 2,00,000 "TABLE ACCESS BY ROWID" operations. Oracle will read the blocks 2,00,000 times to execute this query but there are only about 10,000 block in the entire table. If the index column data that you are fetching is scattered in all the 10000 blocks then it will read and reread 1 single block in the table on average 20 times. So in this case optimizer will prefer Full Table Scan (FTS) than using index.

Now if a fat table has 1 million rows, and if avg size of row is about 1600k then per block there will have about 5 rows, so there will be about 2,00,000 blocks (20 times more than thin table), so if you are accessing 2,00,000 "TABLE ACCESS BY ROWID" then in avg you will read the block once, so there will be lesser number of rereading blocks. So optimizer might think about using the index. But this is just a rough measure, but this is actually how it works. Although index clustering factor have a great impact on this.

CREATE TABLE FAT_EMP(

EMPLOYEE_ID NUMBER,

FIRST_NAME VARCHAR2(20),

LAST_NAME VARCHAR2(25) NOT NULL,

EMAIL VARCHAR2(232) NOT NULL,

PHONE_NUMBER VARCHAR2(20),

HIRE_DATE DATE NOT NULL,

JOB_ID VARCHAR2(10) NOT NULL,

SALARY NUMBER(8,2)

COMMISSION_PCT NUMBER(2,2)

MANAGER_ID NUMBER(6)

DEPARTMENT_ID NUMBER(4),

EMP_STS_FLAG    VARCHAR2(10),

CTC    NUMBER,

MANAGER_NAME VARCHAR2(60),

FIRST_NAME2 VARCHAR2(20),

LAST_NAME2 VARCHAR2(25) NOT NULL,

EMAIL2 VARCHAR2(232) NOT NULL,

PHONE_NUMBER2 VARCHAR2(20),

HIRE_DATE2 DATE NOT NULL,

JOB_ID2 VARCHAR2(10) NOT NULL,

SALARY2 NUMBER(8,2)

COMMISSION_PCT2 NUMBER(2,2)

MANAGER_ID2 NUMBER(6)

DEPARTMENT_ID2 NUMBER(4),

EMP_STS_FLAG2    VARCHAR2(10),

CTC2    NUMBER,

MANAGER_NAME2 VARCHAR2(60),

FIRST_NAME3 VARCHAR2(20),

LAST_NAME3 VARCHAR2(25) NOT NULL,

EMAIL3 VARCHAR2(232) NOT NULL,

PHONE_NUMBER3 VARCHAR2(20),

HIRE_DATE3 DATE NOT NULL,

JOB_ID3 VARCHAR2(10) NOT NULL,

SALARY3 NUMBER(8,2)

.

.

.

.

MANAGER_ID7 NUMBER(6)

DEPARTMENT_ID7 NUMBER(4),

EMP_STS_FLAG7    VARCHAR2(10),

CTC7    NUMBER,

MANAGER_NAME7 VARCHAR2(60));

CREATE TABLE THIN_EMP

AS 

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, JOB_ID, SALARY, COMMISSION_PCT, CTC

FROM FAT_EMP;

ALTER TABLE FAT_EMP ADD CONSTRAINT FAT_EMP_PK PRIMARY_KEY (EMPLOYEE_ID);

ALTER TABLE THIN_EMP ADD CONSTRAINT THIN_EMP_PK PRIMARY KEY (EMPLOYEE_ID);

BEGIN

  SYS.DBMS_STATS.GATHER_TABLE_STATS (

OwnName => 'SYS',

TabName  => 'FAT_EMP',

Method_Opt  => 'FOR ALL COLUMNS SIZE AUTO',

Cascade  => 'TRUE');

END;

/

BEGIN

  SYS.DBMS_STATS.GATHER_TABLE_STATS (

OwnName => 'SYS',

TabName  => 'THIN_EMP',

Method_Opt  => 'FOR ALL COLUMNS SIZE AUTO',

Cascade  => 'TRUE');

END;

/

Both tables will now have around 1 million records. We have created primary key on these tables and by default indexes have been created on these tables.

SELECT TABLE_NAME, BLOCKS, NUM_ROWS, NUM_ROWS/BLOCKS "Rows Per Block"

FROM DBA_TABLES

WHERE TABLE_NAME IN ('FAT_EMP', 'THIN_EMP');

TABLE_NAME BLOCKS NUM_ROWS         "Rows Per Block"

THIN_EMP 9197 1000000 108.731107966999

FAT_EMP 203536 1000000 4.91313575976731

Now selecting around 2% from each table:

SELECT * FROM THIN_EMP WHERE EMPLOYEE_ID < 20000;

SELECT * FROM FAT_EMP WHERE EMPLOYEE_ID < 20000;

In both the cased there is TABLE ACCESS BY ROWID and index is being used for two percent.

Now Let's check what happen when we fetch 5% of the record:

SELECT * FROM THIN_EMP WHERE EMPLOYEE_ID < 50000;

SELECT * FROM FAT_EMP WHERE EMPLOYEE_ID < 50000;

In case of THIN_EMP table, it is not using the Index anymore. It is doing TABLE ACCESS FULL. But in case of FAT_EMP table it is still using INDEX RANGE SCAN and then TABLE ACCESS BY ROWID

Now fetching 20% records from FAT_EMP table:

SELECT * FROM FAT_EMP WHERE EMPLOYEE_ID < 200000;

We were fetching around 20% records and it is still using the index for the fact table but for THIN_EMP table it was not using index even when we were fetching 5% of rows. 

With this example we can clear our concept that FTS will be performed if oracle optimizer sees huge reread of blocks to fetch data from table ( as in case of THIN_EMP). In FAT_EMP table data was less in each block so number of reread of block was less.

Index Clustering Factor is important factor on which Optimizer will decide whether to use index or not when we select 5% rows.

Index Clustering Factor

The clustering factor is a measure that indicates how many adjacent index key do not refer to the same data block in the table. It compares the order of the index with the degree of disorder in the table. It is typically the number of Block changes while ypu are reading the table using the index.

If you look conceptually, we have one index leaf block and four data block, each block contaiing five rows. Suppose scan begin from 100 key value. This Rowid pointing to BLOCK 1 (2), counter will become 1. Now 101 it is going to Block 2 (1). There is a block change, it is reading from a different block so the counter will become 2. Now for Employee_ID 102, it is again referring to Block 1, there is again a block change, reading from Block1 (5). Counter value become 3. For Employee_ID 103, it again reading from different block (BLOCK 2), counter is then set to 4.  For 104 it is reading from same block (BLOCK 2) so counter will remain same 4. 105 referring to different block - BLOCK 1, counter changes to 5. 106 pointing to BLOCK 3 counter will change to 6. 107 and 108 are also pointing to BLOCK 3, counter will remain 6. Read of Employee_ID 109 and 110 mapping to BLOCK 1, again a block change and counter will become 7 and so on.

Finally for Key Values 115 to 119, all data will be in BLOCK 4 and Clustering Factor will be 10.

This is how Index Clustering Factor can be defined.

•  If the clustering factor his high, then Oracle Database performs a relatively high number of I/O during index range scan. The index entries points to random table blocks, so the database may have to read and reread the same blocks over and over again to retrieve the data pointed to by the index.
•  If the clustering factor is low, then Oracle Database performs a relatively low number of I/O during a large index range scan. The index keys in a range tend to point to the same data block, so the database does not have to read and reread the same block over and over. For example the read of Key Value 115 to 119 in the diagram above, all these values referring to block 4. So Low clustering factor is a good indicator of good index.
•  The smallest possible value of clustering factor will be same as the number of table blocks, and the largest possible value will be the same as the number of rows in the table.

We will create two table. One table will have Organized data in particular order of Primary Key 

CREATE TABLE ORGANIZED (COL1 INT, COL2 VARCHAR2(200), COL3 VARCHAR2(200));

BEGIN 

 FOR I IN 1..100000

 LOOP 

 INSERT INTO ORGANIZED VALUES(I, DBMS_RANDOM.STRING('x', 10),  DBMS_RANDOM.STRING('Y', 10));

 END LOOP; 

COMMIT;

END;

/

ALTER TABLE ORGANIZED ADD CONSTRAINT ORGANIZED_PK PRIMARY KEY(COL1);

In case of sequence populated values using such FOR LOOP, the particular index will hold index key in the same sequence. Typically clustering factor will be low.

Now Creating table DISORGANIZED.

CREATE TABLE DISORGANIZED 

AS SELECT COL1, COL2, COL3 FROM ORGANIZED ORDER BY COL2;

By making it Order By COL2, values in DISORGANIZED table will not be stored in in order by COL1. It will be disorganized.

ALTER TABLE DISORGANIZED ADD CONSTRAINT DISORGANIZED_PK PRIMARY KEY(COL1);

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SCOTT', 'ORGANIZED');

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SCOTT', 'DISORGANIZED');

SELECT IND.INDEX_NAME, IND.TABLE_NAME, IND.CLUSTERING_FACTOR, TAB.NUM_ROWS, TAB.BLOCKS

FROM ALL_INDEXES IND, ALL_TABLES TAB

WHERE IND.TABLE_NAME = TAB.TABLE_NAME

AND IND.TABLE_NAME IN ('ORGANIZED','DISORGANIZED');

SQL> SELECT IND.INDEX_NAME, IND.TABLE_NAME, IND.CLUSTERING_FACTOR, TAB.NUM_ROWS, TAB.BLOCKS

  2  FROM ALL_INDEXES IND, ALL_TABLES TAB

  3  WHERE IND.TABLE_NAME = TAB.TABLE_NAME

  4  AND IND.TABLE_NAME IN ('ORGANIZED','DISORGANIZED');

INDEX_NAME                     TABLE_NAME                     CLUSTERING_FACTOR   NUM_ROWS     BLOCKS

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

ORGANIZED_PK                   ORGANIZED                                    440     100000        496

DISORGANIZED_PK                DISORGANIZED                               99766     100000        458

In Organized table Clustering Factor is close to number of Blocks. This is a very good indicator. In the Disorganized table data is not organized by particular key value, clustering factor is very high, it is close to number of rows 100000. 

SELECT * /* 0.1 percent */  FROM DISORGANIZED WHERE COL1 < 100;

SELECT * /* 0.1 percent */  FROM ORGANIZED WHERE COL1 < 100;

We are fetching 0.1 percent of records and in both cases it is using the index to access the table in both cases.

Now fetching one percent records:

SELECT * /* 1 percent */  FROM DISORGANIZED WHERE COL1 < 1000;

SELECT * /* 1 percent */  FROM ORGANIZED WHERE COL1 < 1000;

In DISORGANIZED table we are accessing just one percent rows but it is not using the Index any more. It is doing TABLE ACCESS FULL. In case of ORGANIZED table it is using Index.

SELECT * /* 10 percent */  FROM ORGANIZED WHERE COL1 < 10000;

It is still using index for 10,000 rows.

SELECT * /* 15 percent */  FROM ORGANIZED WHERE COL1 < 15000;

Even with 15 percent data fetch it is using index because the clustering factor is too low.

Optimizer_Mode Effect

Apart from clusting factor there are other various thing that has crucial affect on whether Optimizer will use the index or not. Among them most crucial is Optimizer_Mode parameter.

ALL_ROWS:

  ALL_ROWS is the default mode. If the value of the Optimizer_Mode parameter is set to "ALL_ROWS" then the optimizer will attempt to find an execution plan that completes the statement (typically meaning "returning all rows") in the shortest possible time. ALL_ROWS mode is desinged to minimize computing resource & provide best throughput. The Default value of optimizer_mode in oracle 11g is set to ALL_ROWS. 

  If the Optimizer_Mode is set to ALL_ROWS then the CBO will favor FULL scan compared to index scan as the index scan is an additional IO.

  

FIRST_ROWS:

 If the value of the Optimizer_Mode parameter is set to "FIRST_ROWS" the optimizer will attempt to find an execution plan to return the first row of a result set as fast as possible. This mode always prefer Index scan over FTS even if FTS is better option.

It blindly rely on INDEX SCAN.   

It was deprecated in Oracle 9i.

FIRST_ROWS_N:

It was introduced in Oracle 9i. The number N can be 1,10,100 or 1000 (using first_rows(n) hint, the number n can be any positive whole number). The FIRST_ROWS_N mode instructs the optimizer to choose a query execution plan that minimizes the response time to produce the first N rows of query result & it also favors index scan to access table row. It is useful in case of interactive front end /web app where you want to view first N number of rows as soon as possible. You don't care about the whole result set. At a time you want to see first 100 rows.

Checking default Optimizer Mode:

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE

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

optimizer_mode                       string      ALL_ROWS

Creating table EMP which has around 0.2 million records, we have also created Primary Key on Employee_ID, be dafault an Index is created and I have gathered the statistics.

ALTER TABLE HR.T_EMP 

ADD CONSTRAINT T_EMP_PK PRIMARY KEY (EMPLOYEE_ID);

BEGIN

   SYS.DBMS_STATS.GATHER_TABLE_STATS(

OwnName => 'HR',

TabNmae =>  'T_EMP',

Cascade =>  'TRUE');

END;

/

SELECT * FROM HR.T_EMP

WHERE EMPLOYEE_ID < 100000;

It is doing TABLE ACCESS FULL. Now we will change the parameter to FIRST_ROWS.

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

It is now using Index to access the 50% of the rows. Here now even if we fetch 90% rows, it will still use index no matter there are more IOs. FIRST_ROWS mode will always try to use an index if present on table.

This is the problem with FIRST_ROWS mode. It blindly uses the Index.

Now setting OPTIMIZER_MODE to FIRST_ROWS_100, the oracle will try to return first 100 rows.

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_100;

SELECT * FROM HR.T_EMP WHERE EMPLOYEE_ID < 100000;

We are trying to access 50% of the data and using FIRST_ROWS_100 as optimizer mode. In execution plan it is still using Index in execution plan to give the data. It uses the index to give you the first 100 rows as fast as possible. As can be seen from execution plan ROWS is 100. All the information of ROWS, Bytes, Cost and Time is of first 100 rows. 

So even if you are fetching large number of rows, if your optimizer mode is FIRST_ROWS or FIRST_ROWS_100 oracle might use the index. 

Impact of OPTIMZER_INDEX_COST_ADJ:

•  Using OPTIMZER_INDEX_COST_ADJ parameter you can change the cost of the table access through index scans.
•  Valid Values goes from 1 to 10,000. The default is 100.
•  Values greater than 100 make index scans more expensive and favor full table scans. Values less than 100 make index scans less expensive & make index scan more favourable.
•  By default it is 100.

Setting Optimizer_Mode agian to ALL_ROWS.

ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;

SQL> show parameter OPTIMIZER_INDEX_COST_ADJ

NAME                                 TYPE        VALUE

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

optimizer_index_cost_adj             integer     100

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=20;

SELECT * FROM HR.T_EMP WHERE EMPLOYEE_ID < 70000;

We tried to fetch thirty percent data and it is using the Index.

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 100;

SELECT * FROM HR.T_EMP WHERE EMPLOYEE_ID < 70000;

By setting OPTIMIZER_INDEX_COST_ADJ to 100 ( which is default), it is using INDEX as we are fetching 30% of the data. However it was using index when we set it to 20.

Now fetching EMPLOYEE_ID less than 15,000.

SELECT * FROM HR.T_EMP WHERE EMPLOYEE_ID < 15000;

We are fetching around 7.5 percent data and it is using index, with  OPTIMIZER_INDEX_COST_ADJ parameter set to 100.

Setting the OPTIMIZER_INDEX_COST_ADJ parameter to 400. According to theory by setting parameter to higher value, it will prefer FULL TABLE SCAN . 

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 400;

SELECT * FROM HR.T_EMP WHERE EMPLOYEE_ID < 15000;

As we can see it is doing TABLE ACCESS FULL, it is not using the index any more.

This is how different values of different parameter OPTIMIZER_MODE and INDEX_OPTIMIZER_MODE_ADJ can affect optimizer to use the index or not.

Source: https://www.youtube.com/user/Anindya007das