Summary (details follow below) - To improve performance of a query involving several tables I created an index on one table that included all of the columns referenced in the query. With the new index in place the optimizer is still choosing a Full Table Scan over an Index Fast Full Scan. However, by removing tables from the query one by one I reach the point where the optimizer suddenly does use the Index Fast Full Scan on this table. And "Yes", it is a much lower cost than the Full Table Scan it used before. In getting a test case I was able to get the query down to 4 tables with the optimizer still ignoring the index, and at 3 tables it will use the index.
So why is the Optimizer not choosing the Index Fast Full Scan, when it is obvious that it is so much cheaper than a Full Table Scan? And why does removing a table change the way the Optimizer works - I don't think it is an issue with the number of join permutations (see below). The query is as simple as I can make it, while still being true to the original application SQL, and it still shows this flipping in access path choice. I can run the queries one after the other and it always uses a Full Table Scan for the original query, and an Index Fast Full Scan for the modified query with one less table.
Looking at 10053 trace output for both queries I can see that for the original 4 table query the SINGLE TABLE ACCESS PATH section only costs a Full Table Scan. But for the modified query with one less table, the same table now has a cost for an Index Fast Full Scan too. And the end of the 10053 join costing does not finish with a message about exceeding the maximum number of permutations. So why is the Optimizer not costing the IFFS for the first query, when it does so for the second, near identical query?
Potentially this is an issue to do with OUTER JOINs, but why? The joins between the tables do not change when the one extra table is removed.
This is on 10.2.0.5 on Linux (Oracle Enterprise Linux). I've not set any special parameters that I am aware of. I am seeing the same behaviour on 10.2.0.4 32 bit on Windows (XP).
Thanks
John
Database Performance Blog
DETAILS
I've replicated the whole scenario via SQL scripts to create and populate the tables against which I can then run the queries. I've deliberately padded the table's so that the average row length from the generated data is similar to that of the real data. That way the statistics should be similar regarding number of blocks and so forth.
System - uname -a
Linux mysystem.localdomain 2.6.32-300.25.1.el5uek #1 SMP Tue May 15 19:55:52 EDT 2012 i686 i686 i386 GNU/Linux
Database - v$version
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
Original Query (full table details below):
SELECT
episode.episode_id , episode.cross_ref_id , episode.date_required ,
product.number_required ,
request.site_id
FROM episode
LEFT JOIN REQUEST on episode.cross_ref_id = request.cross_ref_id
JOIN product ON episode.episode_id = product.episode_id
LEFT JOIN product_sub_type ON product.prod_sub_type_id = product_sub_type.prod_sub_type_id
WHERE (
episode.department_id = 2
and product.status = 'I'
)
ORDER BY episode.date_required
;
Execution Plan from display_cursor after execution:
SQL_ID 5ckbvabcmqzw7, child number 0
-------------------------------------
SELECT episode.episode_id , episode.cross_ref_id , episode.date_required ,
product.number_required , request.site_id FROM episode LEFT JOIN REQUEST on
episode.cross_ref_id = request.cross_ref_id JOIN product ON episode.episode_id =
product.episode_id LEFT JOIN product_sub_type ON product.prod_sub_type_id =
product_sub_type.prod_sub_type_id WHERE ( episode.department_id = 2 and
product.status = 'I' ) ORDER BY episode.date_required
Plan hash value: 3976293091
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 35357 (100)| |
| 1 | SORT ORDER BY | | 33333 | 1920K| 2232K| 35357 (1)| 00:07:05 |
| 2 | NESTED LOOPS OUTER | | 33333 | 1920K| | 34879 (1)| 00:06:59 |
|* 3 | HASH JOIN OUTER | | 33333 | 1822K| 1728K| 34878 (1)| 00:06:59 |
|* 4 | HASH JOIN | | 33333 | 1334K| | 894 (1)| 00:00:11 |
|* 5 | TABLE ACCESS FULL| PRODUCT | 33333 | 423K| | 103 (1)| 00:00:02 |
|* 6 | TABLE ACCESS FULL| EPISODE | 299K| 8198K| | 788 (1)| 00:00:10 |
| 7 | TABLE ACCESS FULL | REQUEST | 3989K| 57M| | 28772 (1)| 00:05:46 |
|* 8 | INDEX UNIQUE SCAN | PK_PRODUCT_SUB_TYPE | 1 | 3 | | 0 (0)| |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EPISODE"."CROSS_REF_ID"="REQUEST"."CROSS_REF_ID")
4 - access("EPISODE"."EPISODE_ID"="PRODUCT"."EPISODE_ID")
5 - filter("PRODUCT"."STATUS"='I')
6 - filter("EPISODE"."DEPARTMENT_ID"=2)
8 - access("PRODUCT"."PROD_SUB_TYPE_ID"="PRODUCT_SUB_TYPE"."PROD_SUB_TYPE_ID")
Modified Query:
SELECT
episode.episode_id , episode.cross_ref_id , episode.date_required ,
product.number_required ,
request.site_id
FROM episode
LEFT JOIN REQUEST on episode.cross_ref_id = request.cross_ref_id
JOIN product ON episode.episode_id = product.episode_id
WHERE (
episode.department_id = 2
and product.status = 'I'
)
ORDER BY episode.date_required
;
Execution Plan from display_cursor after execution:
SQL_ID gbs74rgupupxz, child number 0
-------------------------------------
SELECT episode.episode_id , episode.cross_ref_id , episode.date_required ,
product.number_required , request.site_id FROM episode LEFT JOIN REQUEST on
episode.cross_ref_id = request.cross_ref_id JOIN product ON episode.episode_id =
product.episode_id WHERE ( episode.department_id = 2 and product.status =
'I' ) ORDER BY episode.date_required
Plan hash value: 4250628916
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 10515 (100)| |
| 1 | SORT ORDER BY | | 33333 | 1725K| 2112K| 10515 (1)| 00:02:07 |
|* 2 | HASH JOIN OUTER | | 33333 | 1725K| 1632K| 10077 (1)| 00:02:01 |
|* 3 | HASH JOIN | | 33333 | 1236K| | 894 (1)| 00:00:11 |
|* 4 | TABLE ACCESS FULL | PRODUCT | 33333 | 325K| | 103 (1)| 00:00:02 |
|* 5 | TABLE ACCESS FULL | EPISODE | 299K| 8198K| | 788 (1)| 00:00:10 |
| 6 | INDEX FAST FULL SCAN| IX4_REQUEST | 3989K| 57M| | 3976 (1)| 00:00:48 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EPISODE"."CROSS_REF_ID"="REQUEST"."CROSS_REF_ID")
3 - access("EPISODE"."EPISODE_ID"="PRODUCT"."EPISODE_ID")
4 - filter("PRODUCT"."STATUS"='I')
5 - filter("EPISODE"."DEPARTMENT_ID"=2)
Table Creation and Population:
1. Create Tables
2. Load data
3. Create Indexes
4. Gather statistics
--
-- Main table
--
create table episode (
episode_id number (*,0),
department_id number (*,0),
date_required date,
cross_ref_id varchar2 (11),
padding varchar2 (80),
constraint pk_episode primary key (episode_id)
) ;
--
-- Product tables
--
create table product_type (
prod_type_id number (*,0),
code varchar2 (10),
binary_field number (*,0),
padding varchar2 (80),
constraint pk_product_type primary key (prod_type_id)
) ;
--
create table product_sub_type (
prod_sub_type_id number (*,0),
sub_type_name varchar2 (20),
units varchar2 (20),
padding varchar2 (80),
constraint pk_product_sub_type primary key (prod_sub_type_id)
) ;
--
create table product (
product_id number (*,0),
prod_type_id number (*,0),
prod_sub_type_id number (*,0),
episode_id number (*,0),
status varchar2 (1),
number_required number (*,0),
padding varchar2 (80),
constraint pk_product primary key (product_id),
constraint nn_product_episode check (episode_id is not null)
) ;
alter table product add constraint fk_product
foreign key (episode_id) references episode (episode_id) ;
alter table product add constraint fk_product_type
foreign key (prod_type_id) references product_type (prod_type_id) ;
alter table product add constraint fk_prod_sub_type
foreign key (prod_sub_type_id) references product_sub_type (prod_sub_type_id) ;
--
-- Requests
--
create table request (
request_id number (*,0),
department_id number (*,0),
site_id number (*,0),
cross_ref_id varchar2 (11),
padding varchar2 (80),
padding2 varchar2 (80),
constraint pk_request primary key (request_id),
constraint nn_request_department check (department_id is not null),
constraint nn_request_site_id check (site_id is not null)
) ;
--
-- Activity & Users
--
create table activity (
activity_id number (*,0),
user_id number (*,0),
episode_id number (*,0),
request_id number (*,0), -- always NULL!
padding varchar2 (80),
constraint pk_activity primary key (activity_id)
) ;
alter table activity add constraint fk_activity_episode
foreign key (episode_id) references episode (episode_id) ;
alter table activity add constraint fk_activity_request
foreign key (request_id) references request (request_id) ;
--
create table app_users (
user_id number (*,0),
user_name varchar2 (20),
start_date date,
padding varchar2 (80),
constraint pk_users primary key (user_id)
) ;
prompt Loading episode ...
--
insert into episode
with generator as
(select rownum r
from (select rownum r from dual connect by rownum <= 1000) a,
(select rownum r from dual connect by rownum <= 1000) b,
(select rownum r from dual connect by rownum <= 1000) c
where rownum <= 1000000
)
select r, 2,
sysdate + mod (r, 14),
to_char (r, '0000000000'),
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || to_char (r, '000000')
from generator g
where g.r <= 300000
/
commit ;
--
prompt Loading product_type ...
--
insert into product_type
with generator as
(select rownum r
from (select rownum r from dual connect by rownum <= 1000) a,
(select rownum r from dual connect by rownum <= 1000) b,
(select rownum r from dual connect by rownum <= 1000) c
where rownum <= 1000000
)
select r,
to_char (r, '000000000'),
mod (r, 2),
'ABCDEFGHIJKLMNOPQRST' || to_char (r, '000000')
from generator g
where g.r <= 12
/
commit ;
--
prompt Loading product_sub_type ...
--
insert into product_sub_type
with generator as
(select rownum r
from (select rownum r from dual connect by rownum <= 1000) a,
(select rownum r from dual connect by rownum <= 1000) b,
(select rownum r from dual connect by rownum <= 1000) c
where rownum <= 1000000
)
select r,
to_char (r, '000000'),
to_char (mod (r, 3), '000000'),
'ABCDE' || to_char (r, '000000')
from generator g
where g.r <= 15
/
commit ;
--
prompt Loading product ...
--
-- product_id prod_type_id prod_sub_type_id episode_id padding
insert into product
with generator as
(select rownum r
from (select rownum r from dual connect by rownum <= 1000) a,
(select rownum r from dual connect by rownum <= 1000) b,
(select rownum r from dual connect by rownum <= 1000) c
where rownum <= 1000000
)
select r, mod (r, 12) + 1, mod (r, 15) + 1, mod (r, 300000) + 1,
decode (mod (r, 3), 0, 'I', 1, 'C', 2, 'X', 'U'),
dbms_random.value (1, 100), NULL
from generator g
where g.r <= 100000
/
commit ;
--
prompt Loading request ...
--
-- request_id department_id site_id cross_ref_id varchar2 (11) padding
insert into request
with generator as
(select rownum r
from (select rownum r from dual connect by rownum <= 1000) a,
(select rownum r from dual connect by rownum <= 1000) b,
(select rownum r from dual connect by rownum <= 1000) c
where rownum <= 10000000
)
select r, mod (r, 4) + 1, 1, to_char (r, '0000000000'),
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz01234567890123456789' || to_char (r, '000000'),
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789012345678' || to_char (r, '000000')
from generator g
where g.r <= 4000000
/
commit ;
--
prompt Loading activity ...
--
-- activity activity_id user_id episode_id request_id (NULL) padding
insert into activity
with generator as
(select rownum r
from (select rownum r from dual connect by rownum <= 1000) a,
(select rownum r from dual connect by rownum <= 1000) b,
(select rownum r from dual connect by rownum <= 1000) c
where rownum <= 10000000
)
select r, mod (r, 50) + 1, mod (r, 300000) + 1, NULL, NULL
from generator g
where g.r <= 100000
/
commit ;
--
prompt Loading app_users ...
--
-- app_users user_id user_name start_date padding
insert into app_users
with generator as
(select rownum r
from (select rownum r from dual connect by rownum <= 1000) a,
(select rownum r from dual connect by rownum <= 1000) b,
(select rownum r from dual connect by rownum <= 1000) c
where rownum <= 10000000
)
select r,
'User_' || to_char (r, '000000'),
sysdate - mod (r, 30),
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || to_char (r, '000000')
from generator g
where g.r <= 1000
/
commit ;
--
prompt Episode (1)
create index ix1_episode_cross_ref on episode (cross_ref_id) ;
--
prompt Product (2)
create index ix1_product_episode on product (episode_id) ;
create index ix2_product_type on product (prod_type_id) ;
--
prompt Request (4)
create index ix1_request_site on request (site_id) ;
create index ix2_request_dept on request (department_id) ;
create index ix3_request_cross_ref on request (cross_ref_id) ;
-- The extra index on the referenced columns!!
create index ix4_request on request (cross_ref_id, site_id) ;
--
prompt Activity (2)
create index ix1_activity_episode on activity (episode_id) ;
create index ix2_activity_request on activity (request_id) ;
--
prompt Users (1)
create unique index ix1_users_name on app_users (user_name) ;
--
prompt Gather statistics on schema ...
--
exec dbms_stats.gather_schema_stats ('JB')
10053 Sections - Original query
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Table: REQUEST Alias: REQUEST
Card: Original: 3994236 Rounded: 3994236 Computed: 3994236.00 Non Adjusted: 3994236.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 28806.24 Resp: 28806.24 Degree: 0
Cost_io: 28738.00 Cost_cpu: 1594402830
Resp_io: 28738.00 Resp_cpu: 1594402830
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: PK_REQUEST
resc_io: 7865.00 resc_cpu: 855378926
ix_sel: 1 ix_sel_with_filters: 1
Cost: 7901.61 Resp: 7901.61 Degree: 0
Access Path: index (FullScan)
Index: PK_REQUEST
resc_io: 7865.00 resc_cpu: 855378926
ix_sel: 1 ix_sel_with_filters: 1
Cost: 7901.61 Resp: 7901.61 Degree: 0
****** finished trying bitmap/domain indexes ******
******** End index join costing ********
Best:: AccessPath: TableScan
Cost: 28806.24 Degree: 1 Resp: 28806.24 Card: 3994236.00 Bytes: 0
***************************************
10053 - Modified Query
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Table: REQUEST Alias: REQUEST
Card: Original: 3994236 Rounded: 3994236 Computed: 3994236.00 Non Adjusted: 3994236.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 28806.24 Resp: 28806.24 Degree: 0
Cost_io: 28738.00 Cost_cpu: 1594402830
Resp_io: 28738.00 Resp_cpu: 1594402830
Access Path: index (index (FFS))
Index: IX4_REQUEST
resc_io: 3927.00 resc_cpu: 583211030
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 3951.96 Resp: 3951.96 Degree: 1
Cost_io: 3927.00 Cost_cpu: 583211030
Resp_io: 3927.00 Resp_cpu: 583211030
Access Path: index (FullScan)
Index: IX4_REQUEST
resc_io: 14495.00 resc_cpu: 903225273
ix_sel: 1 ix_sel_with_filters: 1
Cost: 14533.66 Resp: 14533.66 Degree: 1
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: IX4_REQUEST
resc_io: 14495.00 resc_cpu: 903225273
ix_sel: 1 ix_sel_with_filters: 1
Cost: 14533.66 Resp: 14533.66 Degree: 0
Access Path: index (FullScan)
Index: IX4_REQUEST
resc_io: 14495.00 resc_cpu: 903225273
ix_sel: 1 ix_sel_with_filters: 1
Cost: 14533.66 Resp: 14533.66 Degree: 0
****** finished trying bitmap/domain indexes ******
******** End index join costing ********
Best:: AccessPath: IndexFFS Index: IX4_REQUEST
Cost: 3951.96 Degree: 1 Resp: 3951.96 Card: 3994236.00 Bytes: 0
***************************************