Index Fast Full Scan and SQL Efficiency

SQL Response time varied anywhere from < 1 second to 120 seconds.  Why?

A look at the execution plan showed that a FTS was the chosen access path for Table 1.  No suitable indices existed.

"PLAN_TABLE_OUTPUT"

"SQL_ID 3qbq31dpd8a9a"

"--------------------"

"SELECT T.ID FROM TABLE1 T, TABLE2 ee, TABLE3 er WHERE "

"T.COLUMN1 = :1 and t.COLUMN2 = 'Employee'                                                           and "

"t.COLUMN3 = ee.COL1 and ee.COL2= er.COL1 and er.COL2 = :2"

" "

"Plan hash value: 531088180"

" "

"-------------------------------------------------------------------------------------------"

"| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |"

"-------------------------------------------------------------------------------------------"

"|   0 | SELECT STATEMENT               |          |       |       | 37807 (100)|          |"

"|   1 |  NESTED LOOPS                  |          |       |       |            |          |"

"|   2 |   NESTED LOOPS                 |          |     1 |    48 | 37807   (1)| 00:07:34 |"

"|   3 |    NESTED LOOPS                |          |     1 |    36 | 37806   (1)| 00:07:34 |"

"|   4 |     TABLE ACCESS FULL          | TABLE1   |     1 |    24 | 37804   (1)| 00:07:34 |"

"|   5 |     TABLE ACCESS BY INDEX ROWID| TABLE2 |     1 |    12 |     2   (0)| 00:00:01 |"

"|   6 |      INDEX UNIQUE SCAN         | TABLE2_PK    |     1 |       |     1   (0)| 00:00:01 |"

"|   7 |    INDEX UNIQUE SCAN           |TABLE3_PK    |     1 |       |     0   (0)|          |"

"|   8 |   TABLE ACCESS BY INDEX ROWID  | TABLE3 |     1 |    12 |     1   (0)| 00:00:01 |"

"-------------------------------------------------------------------------------------------"

" "

How to achieve more efficient query?  Add an index so that the access path is an Index Fast Full Scan.

With a Index Fast Full Scan, the optimizer only touches the index and not the table since everything exist

that is required to answer the query.

What would be the columns?  Since this is a simple query.  Meaning no complex joins, NVL, Between and all

those other combinations that can cause the optimizer to loose its mind in my opinion.

An index on TABLE1 to avoid a FTS, would include all the columns in the WHERE clause and the one column in

in the SELECT.  Adding the column from the SELECT is how we achieve an index only access path.

CREATE INDEX TEST_IFFS on TABLE1 (COLUMN1, COLUMN2, COLUMN3);

Rerun the query.

SQL RESPONSE TIME:  .005 Seconds!

"PLAN_TABLE_OUTPUT"
"SQL_ID  fbdrmctw66g9n, child number 0"
"-------------------------------------"
"SELECT T.ID FROM TABLE1 T, Table2 ee, Table3 er WHERE "
"T.COLUMN1 =1468392  and t.COLUMN2 = 'Employee'  and "
"t.COLUMN3 = ee.COL1 and ee.COL2 = er.COL1"
" "
"Plan hash value: 5312928"
" "
"------------------------------------------------------------------------------------------------------"
"| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |"
"------------------------------------------------------------------------------------------------------"
"|   0 | SELECT STATEMENT              |                      |       |       |  1414 (100)|          |"
"|   1 |  NESTED LOOPS                 |                      |    81 |  3402 |  1414   (1)| 00:00:03 |"
"|   2 |   NESTED LOOPS                |                      |    99 |  3564 |  1414   (1)| 00:00:03 |"
"|*  3 |    INDEX FAST FULL SCAN       | TABLE1_INDX |    99 |  2376 |  1216   (1)| 00:00:03 |"
"|   4 |    TABLE ACCESS BY INDEX ROWID| TABLE2             |     1 |    12 |     2   (0)| 00:00:01 |"
"|*  5 |     INDEX UNIQUE SCAN         | TABLE1_PK                |     1 |       |     1   (0)| 00:00:01 |"
"|*  6 |   INDEX UNIQUE SCAN           | TABLE2_PK                |     1 |     6 |     0   (0)|          |"

"|* 7 |    TABLE ACCESS BY INDEX ROWID  | TABLE3 |     1 |    12 |     1   (0)| 00:00:01 |"
"------------------------------------------------------------------------------------------------------"
" "
"Predicate Information (identified by operation id):"
"---------------------------------------------------"
" "
"   3 - filter(("T"."COLUMN1=1468392"
"              "T"."COLUMN2"='Employee'))"
"   5 - access("T"."COLUMN3"="TABLLE1"."COL1")"
"   6 - access("EE"."COL2"="TABLE3"."COL1")"
" "
"Note"
"-----"
"   - automatic DOP: skipped because of IO calibrate statistics are missing"
" "

Advertisements

Tagged: , ,

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: