Hi, I was testing out a new feature in 23ai and I stumbled upon something.
To summarize the issue: When inserting data into a staging table using append insert, statistics are not collected (which is expected behavior). However, when the staging table is created using CTAS, statistics are automatically collected(which is not expected behavior).
--By default, staging tables in Oracle 23ai should not have statistics collected.
SQL>
create table staging_tbl1 (col1 number) for staging;
insert into staging_tbl1 values (1);
commit;
--Manual collection of table statistics fails.
SQL> exec dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'STAGING_TBL1');
BEGIN dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'STAGING_TBL1'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 42592
ORA-06512: at "SYS.DBMS_STATS", line 41877
ORA-06512: at "SYS.DBMS_STATS", line 9201
ORA-06512: at "SYS.DBMS_STATS", line 10265
ORA-06512: at "SYS.DBMS_STATS", line 41025
ORA-06512: at "SYS.DBMS_STATS", line 42025
ORA-06512: at "SYS.DBMS_STATS", line 42573
ORA-06512: at line 1
--Additionally, due to the 12c feature, the `_optimizer_gather_stats_on_load` parameter prevents statistics from being collected during append inserts.
SQL>
create table staging_tbl2 (col1 number) for staging;
insert /*+ append */ into staging_tbl2 select * from staging_tbl1;
commit;
--Check the table statistics.
SQL>
set lines 200 pages 1000
col table_name for a15
select table_name, num_rows, blocks, empty_blocks, avg_row_len, last_analyzed
from dba_tables
where table_name = 'STAGING_TBL2';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANAL
--------------- ---------- ---------- ------------ ----------- ---------
STAGING_TBL2
--Statistics have not been collected.
--However, when creating a staging table using CTAS (Create Table As Select), statistics are generated.
SQL> create table staging_tbl3 for staging as select * from staging_tbl1;
Table created.
--Check the table statistics.
SQL>
set lines 200 pages 1000
col table_name for a15
select table_name, num_rows, blocks, empty_blocks, avg_row_len, last_analyzed
from dba_tables
where table_name = 'STAGING_TBL3';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANAL
--------------- ---------- ---------- ------------ ----------- ---------
STAGING_TBL3 1 4 0 3 20-OCT-24
--Statistics have been collected.
In my opinion, this appears to be a bug. Could someone from Oracle verify this?
And my database version is 23.5.0.24.07.
SQL> select * from v$version;
BANNER
---------------------------------------------------------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BANNER_LEGACY CON_ID
--------------------------------------------------------------------------------------------------------------------------------- ----------
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems 0