1 / 86

As much as I can say about Statistics in 60 minutes …

As much as I can say about Statistics in 60 minutes …. Thomas Kyte http://asktom.oracle.com/. Agenda. Why do we gather statistics Statistics Basics Other ways to get statistics How to defeat statistics Some things to think about. Why do we gather statistics?. “Cardinality”.

greta
Télécharger la présentation

As much as I can say about Statistics in 60 minutes …

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. As much as I can say about Statistics in 60 minutes… Thomas Kyte http://asktom.oracle.com/

  2. Agenda • Why do we gather statistics • Statistics Basics • Other ways to get statistics • How to defeat statistics • Some things to think about

  3. Why do we gather statistics?

  4. “Cardinality”

  5. “Wrong Plan => Wrong Cardinality”

  6. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> create table t 2 as select decode( mod(rownum,2), 0, 'N', 'Y' ) flag1, 3 decode( mod(rownum,2), 0, 'Y', 'N' ) flag2, a.* 4 from all_objects a 5 / Table created. ops$tkyte%ORA11GR2> create index t_idx on t(flag1,flag2); Index created. ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats 3 ( user, 'T', 4 method_opt=>'for all indexed columns size 254' ); 5 end; 6 / PL/SQL procedure successfully completed.

  7. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> select 'select * from t', num_rows 2 from user_tables where table_name = 'T' 3 union all 4 select 'select * from t where flag1 = "N"', num_rows/2 5 from user_tables where table_name = 'T' 6 union all 7 select 'select * from t where flag2 = "N"', num_rows/2 8 from user_tables where table_name = 'T' 9 union all 10 select 'select * from t where flag1 = "N" and flag2 = "N"', num_rows/2/2 11 from user_tables where table_name = 'T'; 'SELECT*FROMT' NUM_ROWS ------------------------------------------------- ---------- select * from t 72726 select * from t where flag1 = "N" 36363 select * from t where flag2 = "N" 36363 select * from t where flag1 = "N" and flag2 = "N" 18181.5

  8. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> set autotrace traceonly explain ops$tkyte%ORA11GR2> select * from t where flag1='N'; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 36499 | 3635K| 301 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 36499 | 3635K| 301 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("FLAG1"='N')

  9. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> select * from t where flag2='N'; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 36227 | 3608K| 301 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 36227 | 3608K| 301 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("FLAG2"='N')

  10. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> select * from t where flag1='N' and flag2='N'; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18181 | 1810K| 301 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 18181 | 1810K| 301 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("FLAG2"='N' AND "FLAG1"='N')

  11. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> select /*+ gather_plan_statistics */ * 2 from t where flag1='N' and flag2='N'; no rows selected

  12. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.02 | 1080 | |* 1 | TABLE ACCESS FULL| T | 1 | 18181 | 0 |00:00:00.02 | 1080 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("FLAG2"='N' AND "FLAG1"='N')) 19 rows selected.

  13. Wrong Plan => Wrong Cardinality ops$tkyte%ORA11GR2> select /*+ dynamic_sampling(t 3) */ * from t where flag1='N' and flag2='N'; Execution Plan ---------------------------------------------------------- Plan hash value: 470836197 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 612 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 6 | 612 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_IDX | 6 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("FLAG1"='N' AND "FLAG2"='N') Note ----- - dynamic sampling used for this statement (level=2)

  14. Wrong Plan => Wrong Cardinality SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_00"), NVL(SUM(C2),:"SYS_B_01"), NVL(SUM(C3),:"SYS_B_02") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ :"SYS_B_03" AS C1, CASE WHEN "T"."FLAG1"= :"SYS_B_04" AND "T"."FLAG2"=:"SYS_B_05" THEN :"SYS_B_06" ELSE :"SYS_B_07" END AS C2, CASE WHEN "T"."FLAG2"=:"SYS_B_08" AND "T"."FLAG1"=:"SYS_B_09“ THEN :"SYS_B_10" ELSE :"SYS_B_11" END AS C3 FROM "OPS$TKYTE"."T" SAMPLE BLOCK (:"SYS_B_12" , :"SYS_B_13") SEED (:"SYS_B_14") "T") SAMPLESUB

  15. Small Change – but think about it… ops$tkyte%ORA11GR2> create table t 2 as 3 select substr(object_name, 1, 1 ) str, all_objects.* 4 from all_objects 5 order by dbms_random.random; Table created. ops$tkyte%ORA11GR2> create index t_idx on t(str,object_name); Index created. ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats 3 ( user, 'T', 4 method_opt => 'for all indexed columns size 254', 5 estimate_percent=>100 ); 6 end; 7 / PL/SQL procedure successfully completed.

  16. Small Change – but think about it… ops$tkyte%ORA11GR2> select count(subobject_name) from t t1 where str = 'T'; … -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 19 | 296 (0)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | 19 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 292 | 5548 | 296 (0)| 00:00:04 | |* 3 | INDEX RANGE SCAN | T_IDX | 292 | | 4 (0)| 00:00:01 | --------------------------------------------------------------------------------------

  17. Small Change – but think about it… ops$tkyte%ORA11GR2> insert into t 2 select 'T', all_objects.* 3 from all_objects 4 where rownum <= 1; 1 row created. ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats 3 ( user, 'T', 4 method_opt => 'for all indexed columns size 254', 5 estimate_percent=>100 ); 6 end; 7 / PL/SQL procedure successfully completed.

  18. Small Change – but think about it… ops$tkyte%ORA11GR2> select count(subobject_name) from t t2 where str = 'T'; … --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 19 | 297 (1)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | 19 | | | |* 2 | TABLE ACCESS FULL| T | 293 | 5567 | 297 (1)| 00:00:04 | ---------------------------------------------------------------------------

  19. Statistics Basics

  20. Things Change over Time • In 10g and before, it was very common to “program” our statistics gathering • Unique set of inputs for each and every table and index • In 11g, you should as often as possible allow everything except the segment name and DOP to default • Why is this? • However…

  21. You are being WATCHED! ops$tkyte%ORA11GR2> create table t 2 as 3 select a.*, 4 case when rownum < 500 5 then 1 6 else 99 7 end some_status 8 from all_objects a 9 / Table created.

  22. You are being WATCHED! ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats(user,'T'); 3 end; 4 / PL/SQL procedure successfully completed.

  23. You are being WATCHED! ops$tkyte%ORA11GR2> select histogram 2 from user_tab_cols 3 where table_name = 'T' 4 and column_name = 'SOME_STATUS'; HISTOGRAM --------------- NONE

  24. You are being WATCHED! ops$tkyte%ORA11GR2> create index t_idx on t(some_status); Index created.

  25. You are being WATCHED! ops$tkyte%ORA11GR2> select * from t where some_status = 99; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 36115 | 3526K| 300 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 36115 | 3526K| 300 (1)| 00:00:04 | --------------------------------------------------------------------------

  26. You are being WATCHED! ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats( user, 'T' ); 3 end; 4 / PL/SQL procedure successfully completed.

  27. You are being WATCHED! ops$tkyte%ORA11GR2> select histogram 2 from user_tab_cols 3 where table_name = 'T' 4 and column_name = 'SOME_STATUS'; HISTOGRAM --------------- FREQUENCY

  28. You are being WATCHED! ops$tkyte%ORA11GR2> select * from t where some_status = 1; Execution Plan ---------------------------------------------------------- Plan hash value: 470836197 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 539 | 53900 | 10 (0)| 00:00 | 1 | TABLE ACCESS BY INDEX ROWID| T | 539 | 53900 | 10 (0)| 00:00 |* 2 | INDEX RANGE SCAN | T_IDX | 539 | | 2 (0)| 00:00 --------------------------------------------------------------------------------

  29. You are being WATCHED! ops$tkyte%ORA11GR2> select * from t where some_status = 99; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 71683 | 7000K| 300 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 71683 | 7000K| 300 (1)| 00:00:04 | --------------------------------------------------------------------------

  30. Index Statistics – little known fact ops$tkyte%ORA11GR2> create table t 2 as 3 select * 4 from all_objects 5 / Table created. ops$tkyte%ORA11GR2> create index t_idx on t(object_id); Index created. ops$tkyte%ORA11GR2> select num_rows, sample_size, last_analyzed 2 from user_indexes 3 where index_name = 'T_IDX'; NUM_ROWS SAMPLE_SIZE LAST_ANAL ---------- ----------- --------- 72726 72726 17-JUL-12

  31. Other Ways to Get Statistics

  32. Other ways to get statistics • DBMS_STATS.SET_xxx • If you know the statistics, just tell us • DBMS_STATS.COPY_xxx • If you have something representative, just use to them to start • Dynamic Sampling… • SQL Profiles • Extended Statistics • Cardinality Feedback • Cardinality Hint

  33. Dynamic Sampling • http://www.oracle.com/technetwork/issue-archive/2009/09-jan/o19asktom-086775.html • Google: site:www.oracle.com kyte dynamic • Already demonstrated • Levels • 2 – sample any unanalyzed table during hard parse • 3 – same as 2 but add “sample during guess” for single column guesses • 4 – same as 3 but add “sample during guess” for multi-column guesses

  34. SQL Profiles • Analyzing a query • Stores better estimated cardinalities • Gets these by using your workload against your data • Important to be done in the right environment • Are just statistics really, so yes, they can go stale • Applied at hard parse time

  35. SQL Profiles ops$tkyte@ORA11G> create or replace procedure p 2 as 3 cursor c1 4 is 5 select object_id, object_name 6 from sqlprof 7 order by object_id; 9 l_object_id sqlprof.object_id%type; 10 l_object_name sqlprof.object_name%type; 11 begin 12 open c1; 13 for i in 1 .. 10 14 loop 15 fetch c1 into l_object_id, l_object_name; 16 exit when c1%notfound; 17 -- .... 18 end loop; 19 end; 20 / Procedure created.

  36. SQL Profiles SELECT OBJECT_ID, OBJECT_NAME FROM SQLPROF ORDER BY OBJECT_ID call count cpu elapsed query current rows ------- ------ -------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 Execute 1 0.00 0.00 0 0 0 Fetch 10 0.07 0.10 659 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- total 12 0.07 0.10 659 0 10 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 410 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 10 SORT ORDER BY (cr=659 pr=0 pw=0 time=101152 us) 47487 TABLE ACCESS FULL SQLPROF (cr=659 pr=0 pw=0 time=47604 us)

  37. SQL Profiles ops$tkyte@ORA11G> declare 2 l_sql_id v$sql.sql_id%type; 3 begin 4 select sql_id into l_sql_id 5 from v$sql 6 where sql_text = 'SELECT OBJECT_ID, OBJECT_NAME FROM SQLPROF ORDER BY OBJECT_ID'; 7 dbms_output.put_line( 8 sys.dbms_sqltune.create_tuning_task 9 ( sql_id => l_sql_id, 10 task_name => 'sqlprof_query' ) || ' in place...' ); 11 dbms_sqltune.execute_tuning_task 12 ( task_name => 'sqlprof_query' ); 13 end; 14 / PL/SQL procedure successfully completed.

  38. SQL Profiles ops$tkyte@ORA11G> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sqlprof_query') 2 FROM DUAL; DBMS_SQLTUNE.REPORT_TUNING_TASK('SQLPROF_QUERY') -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION … ------------------------------------------------------------------------------- SQL ID : 3zfpa86satsm3 SQL Text: SELECT OBJECT_ID, OBJECT_NAME FROM SQLPROF ORDER BY OBJECT_ID ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 99.45%) ------------------------------------------ Consider accepting the recommended SQL profile. execute :profile_name := dbms_sqltune.accept_sql_profile(task_name => 'sqlprof_query')

  39. SQL Profiles 1- Original With Adjusted Cost ------------------------------ Plan hash value: 1044598349 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time| -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 47487 | 1391K| | 546 (3)| 00:00:07 | | 1 | SORT ORDER BY | | 47487 | 1391K| 3736K| 546 (3)| 00:00:07 | | 2 | TABLE ACCESS FULL| SQLPROF | 47487 | 1391K| | 151 (2)| 00:00:02 | -------------------------------------------------------------------------------------- 2- Using SQL Profile -------------------- Plan hash value: 337606071 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10 | 300 | 3 (0)|00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| SQLPROF | 47487 | 1391K| 3 (0)|00:00:01 | | 2 | INDEX FULL SCAN | SQLPROF_PK | 10 | | 2 (0)|00:00:01 | ------------------------------------------------------------------------------------------

  40. Extended Statistics • https://blogs.oracle.com/optimizer/entry/extended_statistics • In fact, https://blogs.oracle.com/optimizer should be mandatory reading! • Create statistics on sets of columns (correlated columns) • Create statistics on expressions (functions)

  41. Extended Statistics ops$tkyte%ORA11GR2> create table t 2 as 3 select * 4 from all_objects; Table created. ops$tkyte%ORA11GR2> create index t_idx on t(owner,object_type); Index created. ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all columns size 254' ); PL/SQL procedure successfully completed.

  42. Extended Statistics ops$tkyte%ORA11GR2> set autotracetraceonly explain ops$tkyte%ORA11GR2> select * from t where owner = 'PUBLIC' and object_type = 'JAVA CLASS'; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9015 | 853K| 290 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 9015 | 853K| 290 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_TYPE"='JAVA CLASS' AND "OWNER"='PUBLIC')

  43. Extended Statistics ops$tkyte%ORA11GR2> select dbms_stats.create_extended_stats( user, 'T', '(owner,object_type)' ) x from dual; X -------------------------------------------------- SYS_STUXJ8K0YTS_5QD1O0PEA514IY ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all columns size 254' ); PL/SQL procedure successfully completed.

  44. Extended Statistics ops$tkyte%ORA11GR2> set autotracetraceonly explain ops$tkyte%ORA11GR2> select * from t where owner = 'PUBLIC' and object_type = 'JAVA CLASS'; Execution Plan ---------------------------------------------------------- Plan hash value: 470836197 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7 | 763 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 7 | 763 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_IDX | 7 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='JAVA CLASS')

  45. Extended Statistics • See https://blogs.oracle.com/optimizer/entry/how_do_i_know_what_extended_statistics_are_needed_for_a_given_workload for how to have these column groups automatically built for you • Extended statistics on functions done exactly the same

  46. Extended Statistics ops$tkyte%ORA11GR2> create table t 2 as 3 select rownum a, -rownum b, all_objects.* 4 from all_objects; Table created. ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all columns size 254' ); PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> select 0.05 * count(*) from t; 0.05*COUNT(*) ------------- 3635.15

  47. Extended Statistics ops$tkyte%ORA11GR2> set autotracetraceonly explain ops$tkyte%ORA11GR2> select * from t where (a+b)/2 > 50; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3635 | 383K| 321 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 3635 | 383K| 321 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("A"+"B")/2>50)

  48. Extended Statistics ops$tkyte%ORA11GR2> set autotrace off ops$tkyte%ORA11GR2> ops$tkyte%ORA11GR2> select dbms_stats.create_extended_stats( user, 'T', '((a+b)/2)' ) x from dual; X -------------------------------------------------- SYS_STUS9G#61NMFNG0T#HK9W8062Y ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all columns size 254' ); PL/SQL procedure successfully completed.

  49. Extended Statistics ops$tkyte%ORA11GR2> set autotracetraceonly explain ops$tkyte%ORA11GR2> select * from t where (a+b)/2 > 50; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 110 | 320 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 1 | 110 | 320 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("A"+"B")/2>50)

  50. Cardinality Feedback ops$tkyte%ORA11GR2> create or replace type str2tblType as table of varchar2(30) 2 / Type created. ops$tkyte%ORA11GR2> create or replace function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return str2tblType 2 PIPELINED 3 as 4 l_str long default p_str || p_delim; 5 l_n number; 6 begin 7 loop 8 l_n := instr( l_str, p_delim ); 9 exit when (nvl(l_n,0) = 0); 10 pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) ); 11 l_str := substr( l_str, l_n+1 ); 12 end loop; 13 return; 14 end; 15 / Function created.

More Related