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.
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
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
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
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.
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 cant think of a way to do this in one SQL statement (assuming the SQL statement performs better than looping).
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.
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
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.
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!
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
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.
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 ??
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;