Hi all,
today I stumbled upon something strange
I wanted to analyze the effects of using hints in a piece of code, so I
used the _optimizer_ignore_hints hidden parameter to disable hints used in
my code.
After running the pl/sql code I got this error:
ORA-00904: invalid identifier
ORA-06512 at "SYS.DBMS_STATS", line 35016
ORA-06512: at line 2
After debugging, I was able to reproduce the problem as follows:
CREATE TABLE t_test (ID NUMBER NOT NULL, year_to DATE DEFAULT
to_date('01012021','ddmmyyyy') NOT NULL)
PARTITION BY RANGE (year_to) (PARTITION MAXVALUE VALUES LESS THAN
(MAXVALUE) TABLESPACE USERS);
ALTER TABLE t_test ADD CONSTRAINT pk_t_test PRIMARY KEY(ID) USING INDEX
TABLESPACE USERS;
INSERT INTO t_test VALUES(1,to_date('01012021','ddmmyyyy'));
COMMIT;
ALTER SESSION SET "_optimizer_ignore_hints"=TRUE;
BEGIN
dbms_stats.gather_table_stats(ownname => USER,tabname =>
'T_TEST',estimate_percent => 100,degree => 4,cascade => TRUE,method_opt =>
'FOR ALL COLUMNS SIZE 1');
END;
The problem only occurs when the table is partitioned and contains data
setting cascade parameter to false it works.
I observed the issue on 12.1.0.2. On 11.2.0.4 is not working fine
It is true that I used hidden parameter, but I find that interesting.
Best regards
Ahmed
