CREATE TABLE test_table (id number,year number(4),period number(2),charcol varchar(256))Table created.
insert into test_table (id, year, period, charcol) SELECT level, DBMS_RANDOM.VALUE(2014, 2016), DBMS_RANDOM.VALUE(1,12), 'X' FROM dual CONNECT BY LEVEL <= 150000150000 row(s) inserted.
commitStatement processed.
create unique index test_index on test_table (year,period,id)Index created.
exec dbms_stats.gather_table_stats(NULL,'TEST_TABLE')Statement processed.
exec dbms_stats.gather_index_stats(NULL,'TEST_INDEX')Statement processed.
explain plan for select /*+ FIRST_ROWS */ * from test_table where (year=:1 and period>=:2) or (year>:1 and year<:3) or (year=:3 and period<=:4) order by year, period, idStatement processed.
select * from table(dbms_xplan.display)| PLAN_TABLE_OUTPUT | Plan hash value: 3979868219 | -------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | -------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 55478 | 758K| 138 (2)| 00:00:01 | | |* 1 | TABLE ACCESS FULL| TEST_TABLE | 55478 | 758K| 138 (2)| 00:00:01 | | -------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter("YEAR"<2015 AND "YEAR">2014 OR "PERIOD">=12 AND | "YEAR"=2014 OR "PERIOD"<=1 AND "YEAR"=2015) |
|---|
explain plan for select /*+ FIRST_ROWS INDEX_RS_ASC("TEST_TABLE" "TEST_INDEX") USE_CONCAT */ * from test_table where (year=:1 and period>=:2) or (year>:1 and year<:3) or (year=:3 and period<=:4) order by year, period, idselect * from table(dbms_xplan.display)