Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Suhail.

Asked: November 26, 2003 - 12:57 pm UTC

Last updated: September 21, 2005 - 8:11 pm UTC

Version: 9.0.1.3.0

Viewed 1000+ times

You Asked

Tom,

I have a small function which generate 9 digit random number between 100100100 and 998998999. I have to make sure that number should not start with 0 and occurance of a digit cannot exceed with more than 2 ie 3 can only repeat in a number upto two times.If I generate 20000 numbers then this function works OK and just take only 5 minutes. However when I generate this for 200,000 rows it takes 40 minutes and for 1 million rows , it takes almost 3-4 hours.

Could you please recommend me to improve the performance. I need to generate at least 5 millions of these numbers first year and then 1/2 millions after every year.

My code starts here.....

FUNCTION FUNC_GENERATE_ID
RETURN NUMBER
IS

l_candidate_number NUMBER;
l_dummy VARCHAR2(1);
l_is_too_repetitive BOOLEAN;
l_chk_digit NUmber;

BEGIN
<<gen_id>>
LOOP
l_is_too_repetitive := FALSE;
-- VALUE function returns x such that low <= x < high,
-- hence high = ...9990 instead of ....9989.
SELECT TRUNC(DBMS_RANDOM.VALUE(100100100,998998999))
INTO l_candidate_number
FROM DUAL;
BEGIN
-- You'll do your uniqueness check here
SELECT NULL
INTO l_dummy
FROM a_tbl yt
WHERE yt.id = l_candidate_number;
EXCEPTION
WHEN NO_DATA_FOUND THEN
<<check_2>>
FOR i IN 0..9 LOOP
IF (LENGTH(l_candidate_number)
-
NVL(LENGTH(REPLACE(l_candidate_number
, TO_CHAR(i))),0) > 2) THEN
l_is_too_repetitive := TRUE;
EXIT check_2;
END IF;
END LOOP check_2;
IF (NOT l_is_too_repetitive) THEN
EXIT gen_id;
END IF;
END;
END LOOP gen_id;
--check digit calculation
--l_chk_di
l_chk_digit := to_number(substr(l_candidate_number,1,1)*2+
substr(l_candidate_number,2,1)*3+
substr(l_candidate_number,3,1)*4+
substr(l_candidate_number,4,1)*5+
substr(l_candidate_number,5,1)*6+
substr(l_candidate_number,6,1)*7+
substr(l_candidate_number,7,1)*8+
substr(l_candidate_number,8,1)*9+
substr(l_candidate_number,9,1)*10);
--dbms_output.put_line('check digit total = '||l_chk_digit);
--get remainder
select MOD(l_chk_digit,7) into l_chk_digit from dual;
--dbms_output.put_line( 'Check didgit = '||l_chk_digit);
l_candidate_number := to_number(l_candidate_number||l_chk_digit);
RETURN (l_candidate_number);
END func_generate_id;

I call this function as follows :

for i in 1..20000 loop
insert into a_tbl values(func_generate_id);

end loop;
commit;


Thank you for all your help.

Suhail


and Tom said...

we've all got to think in sets -- set based thinking is cool, it is efficient.

Now, I'm sure there are an infinite number of ways to do this -- the one I came up with is as follows.

basically -- we'll set up tables with data (a table with lots of 1..9's in it and another with 01234..9999 with the "bad ones" deleted (bad in this case was any number with the same digit two times)

Now, we just need to join! we can get upto

ops$tkyte@ORA920> select 9 * (select count(*) from t1) * (select count(*) from t1 where x <= '8999') from dual;

9*(SELECTCOUNT(*)FROMT1)*(SELECTCOUNT(*)FROMT1WHEREX<='8999')
-------------------------------------------------------------
206,443,008

unique values from this one. Here is my setup:



ops$tkyte@ORA920> create table t ( x varchar2(9) primary key ) organization index;

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> drop table t1;

Table dropped.

ops$tkyte@ORA920> drop table t2;

Table dropped.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create table t1 ( x varchar2(4) );

Table created.

ops$tkyte@ORA920> create table t2 ( x varchar2(1) );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t1
2 select to_char( rownum+122, 'fm0000' )
3 from all_objects
4 where rownum <= 9899-122;

9777 rows created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> delete from t1
2 where length(replace(x,'0','')) < 3
3 or length(replace(x,'1','')) < 3
4 or length(replace(x,'2','')) < 3
5 or length(replace(x,'3','')) < 3
6 or length(replace(x,'4','')) < 3
7 or length(replace(x,'5','')) < 3
8 or length(replace(x,'6','')) < 3
9 or length(replace(x,'7','')) < 3
10 or length(replace(x,'8','')) < 3
11 or length(replace(x,'9','')) < 3
12 /

4729 rows deleted.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t2
2 select mod(rownum,9)+1
3 from all_objects
4 where rownum <= (select count(*) from t1);

5048 rows created.


so, t2 has lots of 1..9's and t2 has all of the 4digit numbers we could want. This procedure:

ops$tkyte@ORA920> create or replace procedure gen_data( p_num_rows in number )
2 as
3 l_cnt number := 0;
4 begin
5 while (l_cnt < p_num_rows )
6 loop
7 insert into t
8 select t2.x || a.x || b.x
9 from (select rownum r, x from (select * from t2
order by dbms_random.random)) t2,
10 (select rownum r, x from (select * from t1
order by dbms_random.random)) a,
11 (select rownum r, x from (select * from t1
where x <= '8999' order by dbms_random.random)) b
12 where t2.r = a.r and a.r = b.r
13 and rownum <= (p_num_rows-l_cnt)
14 and not exists ( select null
15 from t
16 where x = t2.x || a.x || b.x );
17 l_cnt := l_cnt + sql%rowcount;
18 end loop;
19 end;
20 /

Procedure created.


generates our data for us. Now on my itty bitty desktop pc, I observed:

rows your time my time
20,000 5minutes 2.73 seconds
200,000 40minutes 30.98 seconds
1,000,000 3-4 hours 3 minutes 12 seconds

(that last one got my fan going pretty good :)


ops$tkyte@ORA920> set timing on
ops$tkyte@ORA920> exec gen_data(20000)
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.73

ops$tkyte@ORA920> exec gen_data(200000)
PL/SQL procedure successfully completed.
Elapsed: 00:00:30.98

ops$tkyte@ORA920> exec gen_data(1000000)
PL/SQL procedure successfully completed.
Elapsed: 00:03:12.08

ops$tkyte@ORA920> set timing off


ops$tkyte@ORA920> select count(*) from t;

COUNT(*)
----------
1220000


so, think "sets", think "bulk operations". row by row is synonymous with slow by slow!

other observations:

o use assignments, not selects from dual!
o don't peek at a table to see if you should insert the value (eg: don't
do your own 'primary key checking'. let the database do the work in a
single sql statement





Rating

  (23 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

very helpfu

Suhail, November 26, 2003 - 3:04 pm UTC

Tom, thanks a lot, it reallyhelped me. You the MAN.

Allows more than 2 occurences of the first digit!

Roger Andersson, November 27, 2003 - 3:47 am UTC

It's a really nice example of performance improvement using set based instead of row based techniques. I had to go through it piece by piece to understand how it works and when doing that I noticed that it doesn't fulfill the original requirements!

The requirement was that a digit can not exists more than twice. The supplied code actually allows the first digit to exists three times! This happens when the number generated by t2 exists in both the number generated by a and b.

To handle this add

AND INSTR(a.x || b.x, T2.x, 1, 2) = 0

to the where clause in the insert statement.


Tom Kyte
November 27, 2003 - 10:32 am UTC

there you go !! thanks :) missed that one.....

but thanks much for the "fix", nice

to make it simpler

reeds lau, November 28, 2003 - 12:35 am UTC

Hi,

I guest the validation of occurrence of digits in the original script can be make simpler :

select ...
where
substr(l_candidate_number,1,1) <> '0' and (
nvl(length(replace(translate(l_candidate_number,'012345678','aaaaaaaaa'),'a','')),1) <=2 and
nvl(length(replace(translate(l_candidate_number,'012345679','aaaaaaaaa'),'a','')),1) <=2 and
nvl(length(replace(translate(l_candidate_number,'012345689','aaaaaaaaa'),'a','')),1) <=2 and
nvl(length(replace(translate(l_candidate_number,'012345789','aaaaaaaaa'),'a','')),1) <=2 and
nvl(length(replace(translate(l_candidate_number,'012346789','aaaaaaaaa'),'a','')),1) <=2 and
nvl(length(replace(translate(l_candidate_number,'012356789','aaaaaaaaa'),'a','')),1) <=2 and
nvl(length(replace(translate(l_candidate_number,'012456789','aaaaaaaaa'),'a','')),1) <=2 and
nvl(length(replace(translate(l_candidate_number,'013456789','aaaaaaaaa'),'a','')),1) <=2 and
nvl(length(replace(translate(l_candidate_number,'023456789','aaaaaaaaa'),'a','')),1) <=2 and
nvl(length(replace(translate(l_candidate_number,'123456789','aaaaaaaaa'),'a','')),1) <=2)

Rgs,
Reeds

check for null value when deleting from t1

suresh govindarajan, November 28, 2003 - 2:33 am UTC

check for null value when deleting from t1
because it if all the digits are same like '5555' the entire value may become null and the delete fails.
should be nvl(length(replace(x,'0','')),0) < 3

Tom Kyte
November 28, 2003 - 10:29 am UTC

good point -- thanks

changed the code little bit but no improvement in performance

Suhail Ahmad, December 01, 2003 - 12:36 pm UTC

Tom,

As per your instruction and other reviewers , I tried on my end. I changed the get_data procedure a little bit, it calls a function chk_digit which calculate a check digit and add the check digit to the first position of the generated number so I am inserting a 10 digit number instead of 9 digit. I am also checking for UNIQUE_KEY exception since I donot want to stop the loop . However, I still donot see any performance improvements with 20000 number. Here are my changed code:


My check digit function .. as follows:

CREATE OR REPLACE function chk_digit(p_digit in number)
return number
IS
return number;
l_candidate_number NUMBER;
l_chk_digit number;
BEGIN
l_chk_digit := to_number(substr(p_digit,1,1)*2+
substr(p_digit,2,1)*3+
substr(p_digit,3,1)*4+
substr(p_digit,4,1)*5+
substr(p_digit,5,1)*6+
substr(p_digit,6,1)*7+
substr(p_digit,7,1)*8+
substr(p_digit,8,1)*9+
substr(p_digit,9,1)*10);
l_chk_digit := MOD(l_chk_digit,7);

l_candidate_number := to_number(p_digit||l_chk_digit);

return l_candidate_number;
end;


My gen_data procedure is as follows :

CREATE OR REPLACE procedure gen_data( p_num_rows in number )
as
l_cnt number := 0;
begin
while (l_cnt < p_num_rows )
loop
insert into t
select chk_digit(t2.x || a.x || b.x)
from (select rownum r, x from (select * from t2
order by dbms_random.random)) t2,
(select rownum r, x from (select * from t1
order by dbms_random.random)) a,
(select rownum r, x from (select * from t1
where x <= '8999' order by dbms_random.random)) b
where t2.r = a.r and a.r = b.r
and rownum <= (p_num_rows-l_cnt)
and not exists ( select null
from t
where x = chk_digit(t2.x || a.x || b.x ));
l_cnt := l_cnt + sql%rowcount;
end loop;
exception when others then
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line(' Unique Errors ='||errors);
COMMIT;
end;

I have the same table names set as you described. What I did is that I used chk_digit function in the SELECT clause and also in the WHERE clause. Do you see any problem in it?


Thanks for your help.

Suhail


Tom Kyte
December 02, 2003 - 7:57 am UTC

use the technique i used, no procedural code. INLINE "chk_digit"

Still performance issue

Suhail, December 03, 2003 - 9:08 am UTC

Tom,

I did as you said, I am using the procedure which you have with the same table, except I had written a db trigger which  checks for check digit and add the check digit at the last position. Trigger is as follows:

CREATE OR REPLACE TRIGGER cg$BIR_T
BEFORE INSERT ON T FOR EACH ROW
DECLARE

BEGIN

declare
     op number;
 oRes   varchar2(20);
BEGIN
    --call the check digit procedure
   proc_chk_digit(:new.x,op,oRes);
   IF oRes='SUCCESS' then
    :new.x := op;
   END IF;
END; 

My proc_chk_digit procedure is as follows :
CREATE OR REPLACE procedure proc_chk_digit(p_digit in  number,o_digit out number,o_Result out varchar2)
IS
l_candidate_number NUMBER;
l_chk_digit  number;
 l_is_too_repetitive BOOLEAN;
BEGIN
l_chk_digit := to_number(substr(p_digit,1,1)*2+
               substr(p_digit,2,1)*3+
               substr(p_digit,3,1)*4+
               substr(p_digit,4,1)*5+
               substr(p_digit,5,1)*6+
               substr(p_digit,6,1)*7+
               substr(p_digit,7,1)*8+
               substr(p_digit,8,1)*9+
               substr(p_digit,9,1)*10);
select MOD(l_chk_digit,7) into l_chk_digit from dual;

l_candidate_number := to_number(p_digit||l_chk_digit);
-- check again for digit occurance
 l_is_too_repetitive := FALSE;
 FOR i IN 0..9 LOOP
 IF (LENGTH(l_candidate_number)
 -
 NVL(LENGTH(REPLACE(l_candidate_number
 , TO_CHAR(i))),0) > 2) THEN
  l_is_too_repetitive := TRUE;
  EXIT;
 END IF;
 END LOOP ;
o_digit := l_candidate_number;
if  l_is_too_repetitive = FALSE
   then
      o_Result := 'SUCCESS';
else
      o_Result := 'FAILURE';
end if;
dbms_output.put_line('Digit is '||o_digit||' and Result is '||o_Result);
end;
/

when I run gen_data procedure which is same except I added exception to count how many numbers are rejected due to unique key, Its still taking lot of time. On my machine, I observed :

SQL> set time on
07:48:50 SQL> exec gen_data(20000)

PL/SQL procedure successfully completed.

07:50:04 SQL> exec gen_data(200000)

PL/SQL procedure successfully completed.

08:27:50 SQL> select count(*) from t;

  COUNT(*)
----------
    220000

My gen_data procedure is as follows:
CREATE OR REPLACE procedure gen_data( p_num_rows in number )
    as
        l_cnt number := 0;
          errors number;
    begin
        while (l_cnt < p_num_rows )
        loop
        BEGIN
            insert into t(x)
            select t2.x || a.x || b.x
              from (select rownum r, x from (select * from t2
                      order by dbms_random.random)) t2,
                  (select rownum r, x from (select * from t1
                      order by dbms_random.random)) a,
                  (select rownum r, x from (select * from t1
                     where x <= '8999' order by dbms_random.random)) b
            where t2.r = a.r and a.r = b.r
              and rownum <= (p_num_rows-l_cnt)
              and not exists ( select null
                                 from t
                                where x = t2.x || a.x || b.x );
           l_cnt := l_cnt + sql%rowcount;
        EXCEPTION
         when dup_val_on_index then 
         insert into tab_unque
         values(1);
         --dup_val_on_index then dbms_output.put_line('Whats the error dup value ='||sqlerrm);null;
         end;       
       end loop;

    COMMIT;
   end;
/



Do you see any reason, why the performance is not improving?

Thanks

Suhail
 

Tom Kyte
December 03, 2003 - 10:39 am UTC

remove the procedural code.

get RID of the trigger, do it ALL in sql (as demonstrated!!!!!!!)

can't write in sql

Suhail, December 03, 2003 - 2:24 pm UTC

Tom,
I could not get you. I am sorry for that, how can I write the pl/sql logic which i had written in procedure proc_chk_digit in a simple SELECT , I am using LOOP and some IF condition?
Hope I am not asking too much or may be my SQL is not good enough.

Tom Kyte
December 03, 2003 - 5:12 pm UTC

think about it a bit -- i can see it. it isn't "sql" here, it is taking a procedure and turning it into a single line of code.

for example:

l_chk_digit := to_number(substr(p_digit,1,1)*2+
substr(p_digit,2,1)*3+
substr(p_digit,3,1)*4+
substr(p_digit,4,1)*5+
substr(p_digit,5,1)*6+
substr(p_digit,6,1)*7+
substr(p_digit,7,1)*8+
substr(p_digit,8,1)*9+
substr(p_digit,9,1)*10);
select MOD(l_chk_digit,7) into l_chk_digit from dual;

why why WHY is that two lines of code?????

why why WHY is that using DUAL????? (i told you not to do that already!!!)

l_candidate_number := to_number(p_digit||l_chk_digit);


why is that yet another line of code -- so far I see one line of code!

that one line of code generates the number you apparently want (so thats what goes into the SELECT list there).

Now all you need to do is add the "where" clause check of

....
and length(candidate_number)-length(replace( candidate_number, '9', ''))<= 2
and length(candidate_number)-length(replace( candidate_number, '8', ''))<= 2
.....

to avoid inserting "bad" numbers.


variation on a theme here, take the concept and run with it.



A reader, December 03, 2003 - 3:59 pm UTC


super

Hannibal, December 04, 2003 - 2:30 am UTC


Another example

A reader, December 21, 2004 - 11:02 am UTC

I'd like to know if it is possible to avoid row by row processing and do this with single SQL statements:


There is a MASTER table with 25 columns. The table contains information about guests from a particular hotel chain. One of the columns is LENGTH_OF_STAY (which will I refer to as LOS from now on). Another column is ARRIVAL_DATE. I need to populate a DETAIL table which contains 15 columns out of the 25 columns from A. The DETAIL table does not have columns LOS or ARRIVAL_DATE but has a column called SERVICE_DATE. The logic to populate the DETAIL table is quite simple: For each MASTER record populate N number of records in the DETAIL table where N is LENGTH_OF_STAY and populate SERVICE_DATE starting with ARRIVAL_DATE. So, we want to do this:

MASTER Record:

Name Arrival_Date Length_of_Stay
--------------------------------------------------------------------
John Doe 12/01/2004 5

DETAIL Records:

Name Service Date
----------------------------------------
John Doe 12/01/2004
John Doe 12/02/2004
John Doe 12/03/2004
John Doe 12/04/2004
John Doe 12/05/2004


I am doing bulk inserts into the DETAIL table but still find it slow because I have to populate the arrays row by row in a loop. Is there a way to do this without loops? I can’t think of a way to do this in one SQL statement (assuming the SQL statement performs better than looping).



Tom Kyte
December 21, 2004 - 1:14 pm UTC

ops$tkyte@ORA9IR2> create or replace type myTabletype as table of Number
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2> create or replace function generate_rows( p_n in number ) return myTableType
  2  pipelined
  3  is
  4  begin
  5          for i in 1 .. p_n
  6          loop
  7                  pipe row(i);
  8          end loop;
  9          return;
 10  end;
 11  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table t
  2  as
  3  select 'John Doe' name, to_date( '12/01/2004', 'mm/dd/yyyy' ) adt, 5 los
  4    from dual;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t
  2  values( 'Jane Doe', to_date( '12/12/2004', 'mm/dd/yyyy' ), 3 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select name, adt+column_value-1 service_date
  2    from t, table( generate_rows(t.los) )
  3  /
 
NAME                           SERVICE_D
------------------------------ ---------
John Doe                       01-DEC-04
John Doe                       02-DEC-04
John Doe                       03-DEC-04
John Doe                       04-DEC-04
John Doe                       05-DEC-04
Jane Doe                       12-DEC-04
Jane Doe                       13-DEC-04
Jane Doe                       14-DEC-04
 
8 rows selected.
 

Simlpy amazing!

A reader, December 21, 2004 - 2:07 pm UTC

Thanks for the quick reply! I'll test it in my app!

Can't get it to work....

A reader, December 22, 2004 - 11:44 am UTC

Tom,

I modified my SQL statement to use your solution but now I am getting this error:

ERROR at line 14:
ORA-00600: internal error code, arguments: [kohdtf048], [], [], [], [], [], [],[]


Here's the SQL statement:

select prprty_code,
room_type_code,
rate_type_code,
dcp,
arrvl_date + column_value - 1,
frcst_date, bkng_mean_base,
bkng_mean_season,
bkng_mean_hse,
bkng_mean_inflnc,
bkng_mean_inflnc * (1 - cncl_mean_inflnc),
NULL,
'SIGNIFICANT'
from rm_frcst_sgtr_bkng_cncl@remote_database,
table (generate_rows(los_ctgry_code))
where frcst_date = '21-DEC-04' AND arrvl_date < '21-DEC-05'
group by prprty_code,
room_type_code,
rate_type_code,
dcp,
arrvl_date + column_value - 1,
frcst_date,
bkng_mean_base,
bkng_mean_season,
bkng_mean_hse,
bkng_mean_inflnc,
bkng_mean_inflnc * (1 - cncl_mean_inflnc)

Since los_ctgry_code is a varchar2(10) column, I modified the generate_rows function as:

FUNCTION GENERATE_ROWS(p_n in char)
return TabOfNum
pipelined is
cnt number;
begin
case p_n
when 'C1' then cnt := 1;
when 'C2' then cnt := 2;
when 'C3' then cnt := 3;
when 'C4' then cnt := 4;
when 'C5' then cnt := 5;
when 'C6' then cnt := 6;
when 'C7' then cnt := 7;
when 'C8' then cnt := 8;
when 'C9' then cnt := 9;
when 'C10-11' then cnt := 10;
when 'C12+' then cnt := 12;
end case;

for i in 1 .. cnt
loop
pipe row(i);
end loop;
return;
end;


Is the Oracle error caused by something I am doing wrong?

Thanks for your time.



Tom Kyte
December 22, 2004 - 1:44 pm UTC

ora-00600 = something Oracle is doing wrong.

Suggest in your case you build a table:

los_ctgry_code column_value
-------------- -------------------------
c1 1
c2 1
c2 2
c3 1
......

and join to it instead. but please file a tar with support with your example.

le, December 22, 2004 - 6:02 pm UTC

If your guests are good – (length_of_stay is not big) and you have a table :
Table t
Id
----------------
1
2
3
…
N >= max (length_of_stay)

Then, probably, this could help:

select m.Name, m.arrival_date,
m.arrival_date + row_number() over(partition by m.Name ORDER BY m.Name)-1 Service_date
from master m, t where (m.length_of_stay >= t.id)



Numerology calculator

James, January 13, 2005 - 5:25 am UTC

This is Numerology calculator. This proc finds out the value for the given name. Just curious to know whether there is any simple way to do the same.

CREATE OR REPLACE PROCEDURE num (
NAME IN VARCHAR2
)
IS
chars VARCHAR2 (50) := 'AIJQYBKRCGLSTDMEHNXUVWOZFP .,';
replaces VARCHAR2 (50) := '11111222333344455556667788000';
name1 VARCHAR2 (100);
replaced VARCHAR2 (100);
val1 PLS_INTEGER := 0;
val2 PLS_INTEGER := 0;
BEGIN
name1 := UPPER (NAME);

SELECT TRANSLATE (name1, chars, replaces)
INTO replaced
FROM DUAL;

FOR j IN 1 .. LENGTH (replaced)
LOOP
IF ASCII (SUBSTR (replaced, j, 1)) BETWEEN 48 AND 57
THEN
val1 := val1 + SUBSTR (replaced, j, 1);
END IF;
END LOOP;

IF val1 > 9
THEN
FOR k IN 1 .. LENGTH (TO_CHAR (val1))
LOOP
val2 := val2 + SUBSTR (TO_CHAR (val1), k, 1);
END LOOP;

val1 := val2;
END IF;

DBMS_OUTPUT.put_line (UPPER (NAME) || ' ' || val1);
END;
/


Thanks,
James

Tom Kyte
January 13, 2005 - 8:59 am UTC

well NEVER use dual unless you have to.

replaced := translate( upper(name), chars, replaces );



Can use functions in Select??

Bunny, January 14, 2005 - 12:51 am UTC

Dear Tom,
Does user defined functions(Contain more select and return a valeu) used in select statement improves the performance or its better to go for joins???
But i cant put any join on the tables used in the functions.

Tom Kyte
January 14, 2005 - 8:07 am UTC

databases were born to join.


trying to do the join yourself is not a good idea.

for the revierw "Another example December 21, 2004"

Steve, July 02, 2005 - 5:14 pm UTC

In your reply to above review "Another example December 21, 2004", is it possible to use 'select .. from dual connect by level < ..' instead of pipelined function?

Thanks!

Tom Kyte
July 02, 2005 - 7:13 pm UTC

yes

follow up my question

Steve, July 02, 2005 - 7:38 pm UTC

SQL> select name, adt+column_value-1 service_date
  2   from tt,(select level from dual connect by level <= tt.los )
  3  ;
      from tt,(select level from dual connect by level <tt.los )
                                                    *
ERROR at line 2:
ORA-00904: "TT"."LOS": invalid identifier 

Tom Kyte
July 03, 2005 - 7:43 am UTC

that would be like:

select * 
  from dept, (select * from emp where emp.deptno=dept.deptno)


think about it....

you'd have to do something like:


ops$tkyte@ORA9IR2> with some_rows
  2  as
  3  (select level l from (select max(los) los from t) connect by level <= los)
  4  select l, name, adt+some_rows.l-1 service_date
  5    from t, some_rows
  6   where some_rows.l <= t.los
  7   order by 2, 3
  8  /
 
         L NAME                           SERVICE_D
---------- ------------------------------ ---------
         1 Jane Doe                       12-DEC-04
         2 Jane Doe                       13-DEC-04
         3 Jane Doe                       14-DEC-04
         1 John Doe                       01-DEC-04
         2 John Doe                       02-DEC-04
         3 John Doe                       03-DEC-04
         4 John Doe                       04-DEC-04
         5 John Doe                       05-DEC-04
 
8 rows selected.
 
 

Generating Sub Sets

A.N.V.Pavan Kumar, July 03, 2005 - 9:02 am UTC

Hi,
I have a requirement. Suppose table t wiht one column, c contains the following data:
c
-----
1
2
3
4

I want to write a query which gives the following output:

new_col c
-------- -----
1 1
2 2
3 3
4 1
4 2
5 1
5 3
6 2
6 3
7 1
7 2
7 3.

In effect it generates all the subsets of the set {1,2,3,4}, So, if the no. of rows in t are n, the output should show pow(2,n)-1 rows.



Tom Kyte
July 03, 2005 - 10:34 am UTC

using cartesian joins and union alls and some pivots -- yes, but you'd need to know N beforehand.

Using a pipelined function would probably make most sense (procedurally produce the rows)

Likely the model clause in 10g too (but... no i don't have the example)

Could consider CONNECT BY also...

Padders, July 04, 2005 - 5:02 am UTC

Assuming you had string_to_table function handy it appears you could do this with SYS_CONNECT_BY_PATH. 

Now if those nice people at Oracle found the time to add a SYS_CONNECT_BY_COLLECT aggregate (along with SYS_CONNECT_BY_SUM et al) this would be easier (and cheaper) still ;-)

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production

SQL> CREATE TABLE t (c NUMBER);

Table created.

SQL> INSERT INTO t VALUES (1);

1 row created.

SQL> INSERT INTO t VALUES (2);

1 row created.

SQL> INSERT INTO t VALUES (3);

1 row created.

SQL> INSERT INTO t VALUES (4);

1 row created.

SQL> SELECT new_col, column_value c
  2  FROM  (SELECT ROWNUM new_col, path 
  3         FROM  (SELECT SUBSTR (
  4                         SYS_CONNECT_BY_PATH (c, ','), 2) path
  5                FROM   t
  6                WHERE  c != (SELECT MAX (c) FROM t)
  7                CONNECT BY c > PRIOR c
  8         ORDER BY LENGTH (path))), TABLE (string_to_table (path));

   NEW_COL C
---------- --------------------
         1 1
         2 2
         3 3
         4 1
         4 2
         5 2
         5 3
         6 1
         6 3
         7 1
         7 2
         7 3

12 rows selected.

SQL>  

Thanks

A.N.V.Pavan Kumar, July 04, 2005 - 12:22 pm UTC

Hi,
Thank you very much for your answer.
Thanks,

Occurance of a character in string

Mita, September 20, 2005 - 3:52 pm UTC

I have a string column in table and for some reason I need to find out total occurances of X in the string. how can I do it ??

Tom Kyte
September 21, 2005 - 1:37 pm UTC

length(string)-nvl(length(replace(string,'X','')),0)



Fun with REGEXPs

Bob B, September 21, 2005 - 2:15 pm UTC

Or in 10g ...

NVL( length( regexp_replace( string, '[^X]', '' ) ), 0 )

A Pure SQL solution for the "Generating Sub Sets" question posted on July 03, 2005

Frank Zhou, December 09, 2005 - 11:53 am UTC

Here is a pure sql solution for the "Generating Sub Sets" question posted on July 03, 2005 by Pavan Kumar  
The "string_to_table" user defined pl/sql function is not need in this sql.    (I have combined both Alberto Dell'Era and Padder's nice query into one :)  )

Frank

SQL> CREATE TABLE t (c NUMBER);

Table created.

SQL> INSERT INTO t VALUES (1);

1 row created.

SQL> INSERT INTO t VALUES (2);

1 row created.

SQL> INSERT INTO t VALUES (3);

1 row created.

SQL> INSERT INTO t VALUES (4);

1 row created.


SQL> SELECT new_col,
  2         SUBSTR(x,
  3                INSTR (x, ',', 1, LEVEL  ) + 1,
  4                INSTR (x, ',', 1, LEVEL+1) - INSTR (x, ',', 1, LEVEL) -1 ) c
  5  FROM
  6  ( SELECT ROWNUM new_col, path , ','||path||',' AS x
  7             FROM  ( SELECT SUBSTR (
  8                           SYS_CONNECT_BY_PATH (c, ','), 2) path
  9                     FROM  T
 10                     WHERE  c != (SELECT MAX (c) FROM T)
 11                     CONNECT BY c > PRIOR c
 12                     ORDER BY LENGTH (path)
 13                   )
 14   )
 15   CONNECT BY PRIOR new_col = new_col
 16   AND INSTR (x, ',', 1, LEVEL+1) > 0
 17   AND PRIOR dbms_random.string ('p', 10) IS NOT NULL
 18  ;

   NEW_COL C
---------- --
         1 1
         2 2
         3 3
         4 1
         4 2
         5 2
         5 3
         6 1
         6 3
         7 1
         7 2
         7 3

12 rows selected.

SQL> spool off; 

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library