It's been a while since I've written a post, but twice this week, the same issue came up..
The story goes like this.. "I have a query that is using indexes not FTS, but it is much slower then expected". It seems most folks have it drummed into their heads that Indexes are fastest.
This is the statement I've made twice....
"The only thing worse than a FTS is an index lookup of the whole table." I figured I would show you what I mean.
First I create 2 tables..
TEST_TABLE with 76,989 rows of data. There is a primary key.
DRIVER with the same rows of test_table.
This is the 2 test I did.
1) I created DRIVER with 1 row, and analyzed it , then I deleted the row, and inserted all the rows from TEST_TABLE.
2) I ran the following query which should return every row.
select /*+ GATHER_PLAN_STATISTICS */ count(distinct a.capture_time)
from test_table a,
driver b
where a.pval=b.pval
and b.instance_number < 20;
So what happens ? since the statistics on DRIVER say there is only row you can see the plan and actual vs estimated below.
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
----------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.86 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.86 |
| 2 | VIEW | VW_DAG_0 | 1 | 1 | 77961 |00:00:01.28 |
| 3 | HASH GROUP BY | | 1 | 1 | 77961 |00:00:01.01 |
| 4 | NESTED LOOPS | | 1 | | 77989 |00:00:01.00 |
| 5 | NESTED LOOPS | | 1 | 1 | 77989 |00:00:00.58 |
|* 6 | TABLE ACCESS FULL | DRIVER | 1 | 1 | 77989 |00:00:00.07 |
|* 7 | INDEX UNIQUE SCAN | PVAL_KEY | 77989 | 1 | 77989 |00:00:00.21 |
| 8 | TABLE ACCESS BY INDEX ROWID| TEST_TABLE | 77989 | 1 | 77989 |00:00:00.22 |
----------------------------------------------------------------------------------------------
Since the Optimizer is only expecting 1 row to come back from the table DRIVER, it does an index lookup on TEST_TABLE, for every row. Notice the actual number of rows is the full table.
Now lets look at the cost of this index lookup.
select /*+ GATHER_PLAN_STATISTICS */ count(distinct a.capture_time)
from test_table a,
driver b
where a.pval=b.pval
and b.instance_number < 20
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.84 0.85 0 83932 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.84 0.85 0 83932 0 1
OK. so the cost of the index lookup row-by-row is .85 seconds elapsed time.
Now after analyzing the DRIVER table, you can see the plan changed to a FTS.
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.16 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.16 |
| 2 | VIEW | VW_DAG_0 | 1 | 76723 | 77961 |00:00:00.40 |
| 3 | HASH GROUP BY | | 1 | 76723 | 77961 |00:00:00.23 |
|* 4 | HASH JOIN | | 1 | 76723 | 77989 |00:00:01.23 |
|* 5 | TABLE ACCESS FULL| DRIVER | 1 | 76723 | 77989 |00:00:00.22 |
| 6 | TABLE ACCESS FULL| TEST_TABLE | 1 | 79110 | 77989 |00:00:00.22 |
------------------------------------------------------------------------------------
Notice the actual rows, and estimates match. You can also see it is a FTS.
Now for the run time stats with the FTS.
select /*+ GATHER_PLAN_STATISTICS */ count(distinct a.capture_time)
from test_table a,
driver b
where a.pval=b.pval
and b.instance_number < 20
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.15 0.15 0 1390 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.15 0.15 0 1390 0 1
WOW.. look at that the FTS took .15 seconds compared to .85 seconds.
The bottom line is.. The next time you talk to developers, and they think their query should be fast because they are using an index, look deeper. The biggest clue is to look at the Estimate vs Actual for the plan. The index might not be what you want. FTS's can be good.