I don't think you can follow those guidelines at all.
For example:
One table. Two queries.
One fetches < 1% of the rows from the table. But FTS is the chosen approach.
The over > 20% of the rows. Yet an index range scan is faster:
create table m_and_ms (
m_and_m_id not null,
insert_datetime not null,
expiry_datetime not null,
colour not null,
weight not null,
type not null,
junk,
constraint m_and_ms_pk primary key (m_and_m_id)
)
as
select rownum m_and_m_id,
date'2018-01-01'+(rownum/550) insert_datetime,
sysdate + 100 + floor ( rownum / 100 ) expiry_datetime,
colour,
weight,
type,
junk
from (
select case
when rownum = 5500 then 'silver'
else
case mod(rownum, 6)
when 0 then 'blue'
when 1 then 'brown'
when 2 then 'green'
when 3 then 'orange'
when 4 then 'red'
when 5 then 'yellow'
end
end colour,
round(dbms_random.value(0.7, 1.1), 3) weight,
case
when rownum<(5500/2) then 'chocolate'
else 'peanut'
end type,
rpad('x', 10, 'x') junk
from dual
connect by level <= 5500
order by dbms_random.value
);
create index mm_colour_i on m_and_ms (colour);
create index mm_weight_i on m_and_ms (weight);
exec dbms_stats.gather_table_stats ( user, 'm_and_ms' ) ;
alter session set statistics_level = all;
set serveroutput off
select count ( junk ) from m_and_ms m
where weight between 0.7 and 0.703;
select *
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));
select count ( junk ) from m_and_ms m where weight between 0.7 and
0.702
SQL_ID 0z55624qq8fz0, child number 0
-------------------------------------
select count ( junk ) from m_and_ms m where weight between 0.7 and
0.703
Plan hash value: 3457968548
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 44 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 44 |
|* 2 | TABLE ACCESS FULL| M_AND_MS | 1 | 55 | 51 |00:00:00.01 | 44 |
-----------------------------------------------------------------------------------------
select count ( junk ) from m_and_ms m
where m_and_m_id between 1 and 1250;
select *
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));
SQL_ID ab9rcyx39pz2n, child number 0
-------------------------------------
select count ( junk ) from m_and_ms m where m_and_m_id between 1 and
1250
Plan hash value: 1872607669
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 14 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 14 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| M_AND_MS | 1 | 1250 | 1250 |00:00:00.01 | 14 |
|* 3 | INDEX RANGE SCAN | M_AND_MS_PK | 1 | 1250 | 1250 |00:00:00.01 | 4 |
--------------------------------------------------------------------------------------------------------------