oracle 如何使用PLSQL更快地执行代码

-----我的代码执行时间太长了。执行时间为1074秒。谁能告诉我有什么方法可以让我执行得更快-------

set serveroutput on;
declare 
table_or_view_does_not_exist exception;
pragma exception_init(table_or_view_does_not_exist,-00942);
d_table varchar2(200);
q_table varchar2(200);
r_emp   SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST();
type t_list is table of all_tab_columns%rowtype index by PLS_INTEGER;
v_array t_list;
begin
begin
d_table:='drop table subs_profile_spcl_char PURGE';
execute immediate d_table;
exception
when table_or_view_does_not_exist then
null;
end;
dbms_output.put_line('Table has been dropped');
q_table:='create table subs_profile_spcl_char
         (column_name varchar2(50),
         spcl_char_count Number)';
         execute immediate q_table;
dbms_output.put_line('Table has been created');
dbms_output.enable;
select /*parallel(14)*/ * bulk collect into v_array from all_tab_columns where table_name='SUBSCRIBER_PROFILE' and OWNER='MIG';
for i in 1..v_array.count() loop
r_emp.extend;
EXECUTE IMMEDIATE
      'select /*parallel(16)*/ count(*) from '||v_array(i).table_name||' where not regexp_like ('||v_array(i).column_name||',''[A-za-z0-9.]'')'
      into r_emp(i);
      if r_emp(i)<>0 then
dbms_output.put_line(v_array(i).column_name||'------------>>>>'||r_emp(i));
execute immediate 'insert into subs_profile_spcl_char values (:param1,:param2)' using v_array(i).column_name,r_emp(i);
end if;
end loop; 
end;

在我看来，有一些反对意见。

不应该在PL/SQL中删除/创建表;在SQL级别创建表，然后在一个过程中删除它的内容（如果有必要的话）（truncate比delete快，但是是不可逆的。因为您实际上删除了表，所以我认为您可以使用它）
目标表创建错误;只知道列名是远远不够的-还应该存储所有者和表名
看起来您正在查找包含"特殊字符"的列（即不是字母数字或点）;在这种情况下，请修改正则表达式
不需要扫描所有所有者-SYS，SYSTEM，CTXSYS，可能的APEX_用户很可能对你正在做的事情不感兴趣-删除他们。如果你愿意，也可以删除其他所有者。
不需要扫描所有列-数字和日期不能包含 * 特殊字符 *-仅过滤CHAR数据类型系列列
不需要使用r_emp集合。将计数取到标量变量中（在我的示例中为l_cnt）
并行"提示"使用错误。按照您的说法，它只是一个注解，而不是提示。提示看起来像select /*+ parallel */（您缺少一个加号）
dbms_output占用资源;如果你不需要它，就把它移走（你不需要;结果无论如何都被存储到表中）
插入目标表不需要动态SQL，因此-切换到普通的insert

好吧，给你，这是你可以尝试做的。
在SQL级别（重新）创建目标表：

SQL> DROP TABLE subs_profile_spcl_char;

Table dropped.

SQL> CREATE TABLE subs_profile_spcl_char
  2  (
  3     owner             VARCHAR2 (30),
  4     table_name        VARCHAR2 (30),
  5     column_name       VARCHAR2 (30),
  6     spcl_char_count   NUMBER
  7  );

Table created.

PL/SQL代码：

SQL> set timing on
SQL>
SQL> DECLARE
  2     TYPE t_list_rec IS RECORD
  3     (
  4        owner         all_tab_columns.owner%TYPE,
  5        table_name    all_tab_columns.table_name%TYPE,
  6        column_name   all_tab_columns.column_name%TYPE
  7     );
  8
  9     TYPE t_list IS TABLE OF t_list_rec;
 10
 11     v_array  t_list;
 12     --
 13     l_cnt    NUMBER;
 14  BEGIN
 15     EXECUTE IMMEDIATE 'truncate table subs_profile_spcl_char';
 16
 17     SELECT owner, table_name, column_name
 18       BULK COLLECT INTO v_array
 19       FROM all_tab_columns
 20      WHERE     owner NOT LIKE '%SYS%'
 21            AND owner NOT LIKE 'APEX%'
 22            AND data_type LIKE '%CHAR%';
 23
 24     FOR i IN 1 .. v_array.COUNT ()
 25     LOOP
 26        BEGIN
 27           EXECUTE IMMEDIATE   'select count(*) from '
 28                            || v_array (i).owner
 29                            || '.'
 30                            || v_array (i).table_name
 31                            || ' where not regexp_like ('
 32                            || v_array (i).column_name
 33                            || ',''^[A-za-z0-9.]+$'')'
 34              INTO l_cnt;
 35        EXCEPTION
 36           WHEN OTHERS
 37           THEN
 38              -- for tables you can't access
 39              NULL;
 40        END;
 41
 42        IF l_cnt > 0
 43        THEN
 44           INSERT INTO subs_profile_spcl_char
 45                VALUES (v_array (i).owner,
 46                        v_array (i).table_name,
 47                        v_array (i).column_name,
 48                        l_cnt);
 49        END IF;
 50     END LOOP;
 51  END;
 52  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.58
SQL> set timing off

它花了580毫秒（相对于你的1000秒）。
扫描的列数：

SQL> SELECT COUNT (*)
  2    FROM all_tab_columns
  3   WHERE     owner NOT LIKE '%SYS%'
  4         AND owner NOT LIKE 'APEX%'
  5         AND data_type LIKE '%CHAR%';

  COUNT(*)
----------
       172

不带过滤器：

SQL> SELECT COUNT (*)
  2    FROM all_tab_columns;

  COUNT(*)
----------
     39697

SQL>

如果可以检查200列，为什么还要检查40，000列？
最终结果：

SQL>   SELECT *
  2      FROM subs_profile_spcl_char
  3  ORDER BY owner DESC, table_name, column_name;

OWNER                TABLE_NAME                COLUMN_NAME          SPCL_CHAR_COUNT
-------------------- ------------------------- -------------------- ---------------
XDB                  PATH_VIEW                 PATH                             137
XDB                  RESOURCE_VIEW             ANY_PATH                         137
SCOTT                BONUS                     JOB                                1
SCOTT                DEPT                      LOC                                1
SCOTT                PRODUCTS                  PRODUCT_NAME                       4
SCOTT                SUBS_PROFILE_SPCL_CHAR    COLUMN_NAME                       18
SCOTT                SUBS_PROFILE_SPCL_CHAR    OWNER                              2
SCOTT                SUBS_PROFILE_SPCL_CHAR    TABLE_NAME                        24
ORDS_METADATA        USER_ORDS_OBJECTS         OBJECT_ALIAS                       3
ORDS_METADATA        USER_ORDS_OBJECTS         PARSING_OBJECT                     3
ORDDATA              ORDDCM_DBRELEASE_DOCS     DOC_TYPE                           3
ORDDATA              ORDDCM_DOCUMENT_TYPES     DOC_TYPE                           4
<snip>    

29 rows selected.

SQL>

oracle 如何使用PLSQL更快地执行代码

1条答案

相关问题

热门标签

最新问答