версия оракла: 11.2. далее в примере кардинальность запроса = 1000 вместо 10 на 10000 строк в таблице.
create table tt as
select level n1, level + 1 n2, level + 2 n3
from dual connect by level <= 10000;
/
begin
dbms_stats.gather_table_stats(ownname => user, tabname => 'TT', method_opt => 'FOR ALL COLUMNS SIZE 1', estimate_percent => 100);
end;
/
explain plan for select * from TT where (N1 between 1 and 1000) AND (N2 BETWEEN 2 AND 1001) AND (N3 BETWEEN 3 AND 1002);
SELECT * FROM table(DBMS_XPLAN.DISPLAY(null,null,'ALL'));
/
| + |
DROP TABLE TT succeeded.
create table succeeded.
anonymous block completed
explain plan succeeded.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 264906180
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 120 | 8 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TT | 10 | 120 | 8 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1"<=1000 AND "N2"<=1001 AND "N3"<=1002 AND "N1">=1 AND
"N2">=2 AND "N3">=3)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N1"[NUMBER,22], "N2"[NUMBER,22], "N3"[NUMBER,22]
24 rows selected |