[20250103]distinct的函数实现.txt

[20250103]distinct的函数实现.txt --//前天使用递归代替类似select distinct rtype from routine2; --//今天尝试使用函数是否可以实现,首先提一下，写pl/sql代码不是我擅长的工作，我的工作不需要写代码。 --//主要目的仅仅为了学习。 1.环境： SCOTT@book01p> @ ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.建立测试环境： create table t as select * from all_objects; create index i_t_owner on t(owner) COMPRESS 1; --//alter table t modify owner not null;这步不需要。 --//分析略。 CREATE OR REPLACE FUNCTION distinct2varlist (    p_table_name    IN VARCHAR2   ,p_column_name   IN VARCHAR2 )    RETURN vartabletype    PIPELINED AS    v_str   VARCHAR2 (100); BEGIN    EXECUTE IMMEDIATE       'select min(' || p_column_name || ')' || ' from ' || p_table_name       INTO v_str;    LOOP       EXIT WHEN (v_str IS NULL);       PIPE ROW (v_str);       EXECUTE IMMEDIATE 'select min('||p_column_name||')'||' from '||p_table_name||' where '||p_column_name||'> :j' into v_str using v_str;    END LOOP;    RETURN; END; / --//传入2个参数,表以及字段。 --//小插曲，调试函数遇到1个问题，PIPE ROW (v_str);一定要加括号，不然过不去。 SCOTT@book01p> show error Errors for FUNCTION DISTINCT2VARLIST: LINE/COL ERROR -------- ---------------------------------------------------------------------------------------------------- 17/16    PLS-00103: Encountered the symbol "V_STR" when expecting one of the following: ( 20/4     PLS-00103: Encountered the symbol "RETURN" when expecting one of the following:          end not pragma final instantiable persistable order          overriding static member constructor map 3.测试： SCOTT@book01p> @ sl all alter session set statistics_level = all; Session altered. SCOTT@book01p> select * from distinct2varlist('T','owner') ; COLUMN_VALUE --------------- APPQOSSYS AUDSYS BBB CTXSYS DBSFWUSER DBSNMP DVF DVSYS GSMADMIN_INTERNAL LBACSYS MDSYS OJVMSYS OLAPSYS ORACLE_OCM ORDDATA ORDPLUGINS ORDSYS OUTLN PUBLIC REMOTE_SCHEDULER_AGENT SCOTT SI_INFORMTN_SCHEMA SYS SYSTEM WMSYS XDB 26 rows selected. SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  9p7a9u1b3xp3r, child number 1 ------------------------------------- select * from distinct2varlist('T','owner') Plan hash value: 2418813107 ------------------------------------------------------------------------------------------------------------------------------------------------ | Id  | Operation                         | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT                  |                  |      1 |        |       |    29 (100)|          |     26 |00:00:00.01 |      54 | |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISTINCT2VARLIST |      1 |   8168 | 16336 |    29   (0)| 00:00:01 |     26 |00:00:00.01 |      54 | ------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$F5BB74E1 / "KOKBF$0"@"SEL$2" --//54个逻辑读，前面使用递归35个逻辑读。 --//测试返回数字的情况,按照上面的脚本修改如下： CREATE OR REPLACE TYPE numtabletype AS TABLE OF NUMBER / CREATE OR REPLACE FUNCTION distinct2numlist (    p_table_name    IN VARCHAR2   ,p_column_name   IN VARCHAR2 )    RETURN numtabletype    PIPELINED AS    v_str   NUMBER; BEGIN    EXECUTE IMMEDIATE       'select min(' || p_column_name || ')' || ' from ' || p_table_name       INTO v_str;    LOOP       EXIT WHEN (v_str IS NULL);       PIPE ROW (v_str);       EXECUTE IMMEDIATE             'select min('          || p_column_name          || ')'          || ' from '          || p_table_name          || ' where '          || p_column_name          || '> :j'          INTO v_str          USING v_str;    END LOOP;    RETURN; END; / SCOTT@book01p> select * from distinct2numlist('emp','deptno') ; COLUMN_VALUE ------------           10           20           30 SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  7ysn70g61z2pu, child number 0 ------------------------------------- select * from distinct2numlist('emp','deptno') Plan hash value: 1096181357 ------------------------------------------------------------------------------------------------------------------------------------------------ | Id  | Operation                         | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT                  |                  |      1 |        |       |    29 (100)|          |      3 |00:00:00.01 |      24 | |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISTINCT2NUMLIST |      1 |   8168 | 16336 |    29   (0)| 00:00:01 |      3 |00:00:00.01 |      24 | ------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$F5BB74E1 / "KOKBF$0"@"SEL$2" --//没有建立索引，可以发现执行效率不高,这是第2次执行测试的逻辑读24.。 --//建立索引后，重复测试： SCOTT@book01p> create index i_emp_deptno on emp(deptno); Index created. SCOTT@book01p> set feed on SCOTT@book01p> select * from distinct2numlist('emp','deptno') ; COLUMN_VALUE ------------           10           20           30 3 rows selected. SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  7ysn70g61z2pu, child number 0 ------------------------------------- select * from distinct2numlist('emp','deptno') Plan hash value: 1096181357 ------------------------------------------------------------------------------------------------------------------------------------------------ | Id  | Operation                         | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT                  |                  |      1 |        |       |    29 (100)|          |      3 |00:00:00.01 |       4 | |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISTINCT2NUMLIST |      1 |   8168 | 16336 |    29   (0)| 00:00:01 |      3 |00:00:00.01 |       4 | ------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$F5BB74E1 / "KOKBF$0"@"SEL$2" 4.总结： --//仅仅为了学习，没有实际的意义.在返回值很少并且相关字段索引存在的情况下也许执行效率高。 --//另外测试没有考虑NULL的情况。 --//理论还可以修改返回多个值，不在上面浪费时间。 --//再次提醒一些开发在写代码时想想，我开发的程序运行时间有多长，数据结构是否合理。 --//再贴一个生产系统看到的情况： SYS@127.0.0.1:9105/xtdb/xtdb1> @ sql_id 43cm4x9swk2ga -- SQL_ID = 43cm4x9swk2ga come from shared pool select distinct MR_Class from MED_EMR_ARCHIVE_DETIAL; SYS@127.0.0.1:9105/xtdb/xtdb1> @ seg2 %.MED_EMR_ARCHIVE_DETIAL     SEG_MB OWNER                SEGMENT_NAME                   SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK ---------- -------------------- ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------         41 MEDCOMM              MED_EMR_ARCHIVE_DETIAL         TABLE                TSP_MEDCOMM                          5248          2      19346 SYS@127.0.0.1:9105/xtdb/xtdb1> @ desczz MEDCOMM.MED_EMR_ARCHIVE_DETIAL MR_Class eXtended describe of MEDCOMM.MED_EMR_ARCHIVE_DETIAL DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT   OWNER.TABLE_NAME  <filters> SAMPLE  : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner      Table_Name           SAMPLE_SIZE LAST_ANALYZED       Col# Column Name          Null?      Type                 NUM_DISTINCT        Density  NUM_NULLS HISTOGRAM       NUM_BUCKETS Low_value High_value ---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- --------- ----------- MEDCOMM    MED_EMR_ARCHIVE_DETI        5518 2024-12-05 22:01:54    3 MR_CLASS             NOT NULL   VARCHAR2(10)                    1   .00000209385          0 FREQUENCY                 1 麻醉      麻醉            AL --//不同的值仅仅1个.无语. --//自己想想随着表数据增加，如果程序代码经常这样调用有意义吗？