有關oracle external table的一點測試。

10. Which two are true about Oracle's Optimizer statistics?

A) They are automatically updated when ddl statements execute.

B) They can be calculated exactly .

C) They can be gathered on external tables.

D) They are automatically updated when dml statements execute.

E) They can be estimated.

Answer: BE

--=======================

SQL> create table alert_log_external
  2        (line  varchar2(4000) )  --建立的外部表的列，可以根據需求寫
  3         organization external  --固定語法
  4        (type oracle_loader
  5         default directory DATA_PUMP_DIR
  6         access parameters (
  7           records delimited by newline
  8           nobadfile
  9           nologfile
 10           nodiscardfile
 11           --fields terminated by ;    --遇到；符號時終止
 12           missing field values are null  --缺失部分為空
 13           (line)
 14         )
 15         location (  'alert_test.log') )--name_alert為資料檔名
 16         reject limit unlimited;

表已建立。

SQL> exec dbms_stats.gather_table_stats('XYS','ALERT_LOG_EXTERNAL');
BEGIN dbms_stats.gather_table_stats('XYS','ALERT_LOG_EXTERNAL'); END;

*
第 1 行出現錯誤:
ORA-20000: Unable to analyze TABLE "XYS"."ALERT_LOG_EXTERNAL", sampling on
external table is not supported
ORA-06512: 在 "SYS.DBMS_STATS", line 20337
ORA-06512: 在 "SYS.DBMS_STATS", line 20360
ORA-06512: 在 line 1

SQL> create index idx1 on ALERT_LOG_EXTERNAL(line);
create index idx1 on ALERT_LOG_EXTERNAL(line)
                     *
第 1 行出現錯誤:
ORA-30657: 操作在外部組織表上不受支援

SQL>