create table calendar_dates as
select date'2016-01-01'+rownum-1 calendar_date ,
to_char( date'2016-01-01'+rownum-1, 'fmDay') day_of_week ,
to_char( date'2016-01-01'+rownum-1, 'fmMonth') month_of_year ,
to_number ( to_char( date'2016-01-01'+rownum-1, 'dd') ) day_of_month ,
to_number ( to_char( date'2016-01-01'+rownum-1, 'ddd') ) day_of_year
from dual connect by level <= 366
Table created.
exec dbms_stats.gather_table_stats(sys_context('userenv', 'current_user'), 'calendar_dates')
Statement processed.
alter table calendar_dates add constraint dt_u unique (
trunc ( calendar_date )
)
ORA-00904: : invalid identifierMore Details: https://docs.oracle.com/error-help/db/ora-00904
create unique index calendar_date_u on calendar_dates (
trunc ( calendar_date )
)
Index created.
create index month_of_year_upper on calendar_dates (
upper ( month_of_year )
)
Index created.
create index day_of_year_i on calendar_dates (
day_of_year
)
Index created.
begin
for rws in (
select /*+ gather_plan_statistics JUNE */* from calendar_dates
where month_of_year = 'JUNE') loop
null;
end loop;
end;
Statement processed.
select p.*
from v$sql s, table (
dbms_xplan.display_cursor (
s.sql_id, s.child_number, 'ALLSTATS LAST'
)
) p
where s.sql_text like '%JUNE%'
and s.sql_text not like '%not this%'
and s.sql_text not like 'begin%'
PLAN_TABLE_OUTPUT | SQL_ID d1p6x5qrwyhcz, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics JUNE */* FROM CALENDAR_DATES WHERE | MONTH_OF_YEAR = 'JUNE' | Plan hash value: 1916171033 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 10 | | |* 1 | TABLE ACCESS FULL| CALENDAR_DATES | 1 | 30 | 0 |00:00:00.01 | 10 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter("MONTH_OF_YEAR"='JUNE') |
---|
begin
for rws in (
select /*+ gather_plan_statistics UPPERJUNE */* from calendar_dates
where upper ( month_of_year ) = 'JUNE') loop
null;
end loop;
end;
Statement processed.
select p.*
from v$sql s, table (
dbms_xplan.display_cursor (
s.sql_id, s.child_number, 'ALLSTATS LAST'
)
) p
where s.sql_text like '%UPPERJUNE%'
and s.sql_text not like '%not this%'
and s.sql_text not like 'begin%'
PLAN_TABLE_OUTPUT | SQL_ID 55dtbncaasq2x, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics UPPERJUNE */* FROM CALENDAR_DATES | WHERE UPPER ( MONTH_OF_YEAR ) = 'JUNE' | Plan hash value: 1354611409 | --------------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | --------------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 30 |00:00:00.01 | 2 | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CALENDAR_DATES | 1 | 4 | 30 |00:00:00.01 | 2 | | |* 2 | INDEX RANGE SCAN | MONTH_OF_YEAR_UPPER | 1 | 1 | 30 |00:00:00.01 | 1 | | --------------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - access("CALENDAR_DATES"."SYS_NC00007$"='JUNE') |
---|
declare
day_diff pls_integer := 1;
begin
for rws in (
select /*+ gather_plan_statistics COLMATH */* from calendar_dates
where day_of_year + day_diff = 100 ) loop
null;
end loop;
end;
Statement processed.
select p.*
from v$sql s, table (
dbms_xplan.display_cursor (
s.sql_id, s.child_number, 'ALLSTATS LAST'
)
) p
where s.sql_text like '%COLMATH%'
and s.sql_text not like '%not this%'
and s.sql_text not like 'declare%'
PLAN_TABLE_OUTPUT | SQL_ID d8zvkcvfw43bk, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics COLMATH */* FROM CALENDAR_DATES WHERE | DAY_OF_YEAR + :B1 = 100 | Plan hash value: 1916171033 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 10 | | |* 1 | TABLE ACCESS FULL| CALENDAR_DATES | 1 | 4 | 1 |00:00:00.01 | 10 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter("DAY_OF_YEAR"+:B1=100) |
---|
declare
day_diff pls_integer := 1;
begin
for rws in (
select /*+ gather_plan_statistics VARMATH */* from calendar_dates
where day_of_year = 100 - day_diff ) loop
null;
end loop;
end;
Statement processed.
select p.*
from v$sql s, table (
dbms_xplan.display_cursor (
s.sql_id, s.child_number, 'ALLSTATS LAST'
)
) p
where s.sql_text like '%VARMATH%'
and s.sql_text not like '%not this%'
and s.sql_text not like 'declare%'
PLAN_TABLE_OUTPUT | SQL_ID 2vwv8rswg4sw1, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics VARMATH */* FROM CALENDAR_DATES WHERE | DAY_OF_YEAR = 100 - :B1 | Plan hash value: 507463100 | ---------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | ---------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CALENDAR_DATES | 1 | 1 | 1 |00:00:00.01 | 2 | | |* 2 | INDEX RANGE SCAN | DAY_OF_YEAR_I | 1 | 1 | 1 |00:00:00.01 | 1 | | ---------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - access("DAY_OF_YEAR"=100-:B1) |
---|
begin
for rws in (
select /*+ gather_plan_statistics JANSTART */* from calendar_dates
where calendar_date >= date'2016-01-01'
and calendar_date < date'2016-01-15') loop
null;
end loop;
end;
Statement processed.
select p.*
from v$sql s, table (
dbms_xplan.display_cursor (
s.sql_id, s.child_number, 'ALLSTATS LAST'
)
) p
where s.sql_text like '%JANSTART%'
and s.sql_text not like '%not this%'
and s.sql_text not like 'begin%'
PLAN_TABLE_OUTPUT | SQL_ID 19dvdcnamd53b, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics JANSTART */* FROM CALENDAR_DATES | WHERE CALENDAR_DATE >= DATE'2016-01-01' AND CALENDAR_DATE < | DATE'2016-01-15' | Plan hash value: 2307430397 | ----------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | ----------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 2 | | |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CALENDAR_DATES | 1 | 14 | 14 |00:00:00.01 | 2 | | |* 2 | INDEX RANGE SCAN | CALENDAR_DATE_U | 1 | 2 | 15 |00:00:00.01 | 1 | | ----------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter(("CALENDAR_DATE"<TO_DATE(' 2016-01-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND | "CALENDAR_DATE">=TO_DATE(' 2016-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) | 2 - access("CALENDAR_DATES"."SYS_NC00006$">=TRUNC(TO_DATE(' 2016-01-01 00:00:00', 'syyyy-mm-dd | hh24:mi:ss')) AND "CALENDAR_DATES"."SYS_NC00006$"<=TRUNC(TO_DATE(' 2016-01-15 00:00:00', 'syyyy-mm-dd | hh24:mi:ss'))) |
---|