SQL Types vs Package Types
Reader.., September 10, 2004 - 1:05 pm UTC
Hi Tom. I see often you create SQL type objects even if they exists in supplied packages, etc.
For example, you did:
ops$tkyte@ORA9IR2> create or replace type numArray as table of number
2 /
..whereas a number_table type in DBMS_SQL package exists. Could you tell what do you prefer each one over the other ?
Thanks
September 10, 2004 - 1:10 pm UTC
try the example with dbms_sql types and see what happens :)
in order to use a type in SQL, it must be a SQL type.
but in any case -- in order to protect yourself from changes, you should create your own types for your code.
foo .. here too!
Pravesh Karthik from chennai, September 10, 2004 - 1:35 pm UTC
Tom,
A non-technical question. I googled a lot, but never got the answer and here you did the same ...
why are that most sample scripts come up with foo, foobar as the name? ( create or replace function foo( ...)
Hope, not wasting your time -- just a break!
Thanks,
Pravesh Karthik
September 10, 2004 - 1:44 pm UTC
Another way?
Bob B, September 10, 2004 - 2:32 pm UTC
Tom,
<<QUOTE>>
You need to make sure all_objects or whatever table you use to drive this has AT
LEAST as many rows as you need output!!!
<<END QUOTE>>
I believe there must be enough rows in the driving table to cover the largest range. Instead of trying to join each number in each range to one row in the driving table, wouldn't it be better to join the nth number of each range to the nth number in the driving table? Example follows:
SELECT t.b + val NUM
FROM
t,
(
SELECT ROWNUM - 1 val
FROM ALL_OBJECTS
WHERE ROWNUM <= ( SELECT MAX( b - s + 1 ) FROM t )
)
WHERE t.b + val <= t.e
Perhaps you could show some performance comparisons between all three methods. I would guess that this query would outperform the first query, but both would be outperformed by the pipelined function.
September 10, 2004 - 2:49 pm UTC
<quote>
Perhaps you could show some performance comparisons between all three methods.
</quote>
right back at you! :)
(but your observations about this one being faster will be true in some cases, not in others. depends on the size of T :)
Performance Comparisons
Bob B, September 10, 2004 - 2:59 pm UTC
I would show the performance comparisons if I had access to a 9i system to test the pipelined version. If I can't do that one, than the other two comparisons are only meaningful to each other. Personally I was most interested in seeing if the pipelined version is faster and by what amount.
September 10, 2004 - 3:10 pm UTC
ops$tkyte@ORA9IR2> select * from t;
B E
---------- ----------
1 10
11 11
12 14
14 10000
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly statistics;
ops$tkyte@ORA9IR2> @trace
ops$tkyte@ORA9IR2> alter session set events '10046 trace name context forever, level 12';
Session altered.
ops$tkyte@ORA9IR2> select * from table( foo(cursor(select * from t) ) )
2 /
10001 rows selected.
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
132879 bytes sent via SQL*Net to client
7825 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10001 rows processed
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> SELECT t.b + val NUM
2 FROM
3 t,
4 (
5 SELECT ROWNUM - 1 val
6 FROM ALL_OBJECTS
7 WHERE ROWNUM <= ( SELECT MAX( e - b + 1 ) FROM t )
8 )
9 WHERE t.b + val <= t.e
10 /
10001 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
132761 consistent gets
0 physical reads
0 redo size
132873 bytes sent via SQL*Net to client
7825 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10001 rows processed
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select r
2 from t, (select rownum r
3 from all_objects
4 where rownum <= (select max(e) from t) - (select min(b) from t) + 1)
5 where r between t.b and t.e
6 /
10001 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
132933 consistent gets
0 physical reads
0 redo size
132868 bytes sent via SQL*Net to client
7825 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10001 rows processed
and tkprof says...
select * from table( foo(cursor(select * from t) ) )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 668 0.06 0.07 0 0 0 10001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 0.06 0.07 0 0 0 10001
SELECT t.b + val NUM
FROM
t,
(
SELECT ROWNUM - 1 val
FROM ALL_OBJECTS
WHERE ROWNUM <= ( SELECT MAX( e - b + 1 ) FROM t )
)
WHERE t.b + val <= t.e
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 7 0 0
Fetch 668 1.17 1.25 0 132754 0 10001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 1.17 1.25 0 132761 0 10001
select r
from t, (select rownum r
from all_objects
where rownum <= (select max(e) from t) - (select min(b) from t) + 1)
where r between t.b and t.e
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 14 0 0
Fetch 668 1.15 1.14 0 132919 0 10001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 1.15 1.14 0 132933 0 10001
Great!
A reader, September 10, 2004 - 4:21 pm UTC
SELECT t.b + val NUM
FROM
t,
(
SELECT ROWNUM - 1 val
FROM ALL_OBJECTS
WHERE ROWNUM <= ( SELECT MAX( e - b + 1 ) FROM t )
)
WHERE t.b + val <= t.e
Hey, this looks fishy! Only Tom is allowed to write such clever queries! :)
Great solutions presented, thanks all!
Pipelined stats
A reader, September 10, 2004 - 4:27 pm UTC
select * from table( foo(cursor(select * from t) ) )
1. Why does the autotrace show 15 LIO but the tkprof shows 0?
2. Why does the tkprof show 0 LIO being done? It has to atleast FTS the table 't', right? Or does tkprof not show stats from pipelined functions?
3. Why does tkprof show 668 fetch calls for 100001 fetched rows?
Thanks
September 10, 2004 - 5:06 pm UTC
1) 15 recursive calls in there hiding out (the query against T for example...)
2) I did not show that query
3) sqlplus array fetched by default using 15 rows at a time.
ops$tkyte@ORA9IR2> select 10001/15 from dual;
10001/15
----------
666.733333
so it took 667 fetches to exhaust the result set and sqlplus always over fetches by "one"
avoid all objects or make it smaller
pet, September 10, 2004 - 4:57 pm UTC
the table has 50000 records and all_tables does not have that many records., The result will have 99999 records. It's actually zip code table. Is there anyway to avoid other objects or make the range smaller.? The maximum difference is 999 between the start and end for a given zip range. for ex. 09000 - 09899
September 10, 2004 - 5:16 pm UTC
create your own table
create table mytable
as
select 1 x from all_objects;
insert into mytable select * from mytable;
(as many times as you need....)
s/all_objects/all_nulls/g
Alberto Dell'Era, September 10, 2004 - 5:03 pm UTC
But all_objects is the most complex view of the planet ... the SQL-only approach is killed by it!
SQL> create table all_nulls (x varchar2(1));
SQL> insert into all_nulls select null from all_objects;
29043 rows created.
SQL> exec dbms_stats.gather_table_stats (user, 'all_nulls');
SQL> create table t (b int, e int);
SQL> insert into t (b,e) values (1, 10);
SQL> insert into t (b,e) values (10001, 10010);
SQL> exec dbms_stats.gather_table_stats (user, 't');
select *
from
table( foo(cursor(select * from t) ) )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 30 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.06 0.06 0 0 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.10 0.11 0 30 0 20
select r
from t, (select rownum r
from all_nulls
where rownum <= (select max(e) from t) - (select min(b) from t) + 1
)
where r between t.b and t.e
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 3 0.04 0.04 0 50 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.06 0.06 0 50 0 20
So, performance-wise, don't they look essentially equivalent ?
Coding-effort-wise - isn't the pure-SQL approach simpler... :) and ;)
September 10, 2004 - 7:27 pm UTC
scale it up.
select * from table( foo(cursor(select * from t) ) )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 668 0.09 0.09 0 0 0 10001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 0.09 0.09 0 0 0 10001
********************************************************************************
SELECT t.b + val NUM
FROM
t,
(
SELECT ROWNUM - 1 val
FROM mytable
WHERE ROWNUM <= ( SELECT MAX( e - b + 1 ) FROM t )
)
WHERE t.b + val <= t.e
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 7 0 0
Fetch 668 0.29 0.32 0 70596 0 10001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 0.29 0.32 0 70603 0 10001
********************************************************************************
select r
from t, (select rownum r
from mytable
where rownum <= (select max(e) from t) - (select min(b) from t) + 1)
where r between t.b and t.e
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 14 0 0
Fetch 668 0.27 0.29 0 70684 0 10001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 0.28 0.29 0 70698 0 10001
Getting by with a table of just 999 records
Logan Palanisamy, September 10, 2004 - 5:58 pm UTC
You can get by with a table of just 999 rows by modifying Tom's query like this. No need to create a rownum table with large number of rows.
SQL> select (t.b + r - 1)
2 from t, (select rownum r
3 from all_nulls
4 where rownum <= (select max(e) from t) - (select min(b)
5 from t) + 1)
6 where (t.b + r - 1) between t.b and t.e
7 order by 1;
Here is the demo:
SQL> create table t (b number, e number);
Table created.
SQL> insert into t values (3, 10);
1 row created.
SQL> insert into t values (1000, 1005);
1 row created.
SQL> insert into t values (09000, 09999);
1 row created.
SQL>
SQL> drop table all_nulls;
Table dropped.
SQL> create table all_nulls (x varchar2(1));
Table created.
SQL>
SQL> insert into all_nulls select null from all_objects where rownum < 1000;
999 rows created.
SQL>
SQL> select r
2 from t, (select rownum r
3 from all_nulls
4 where rownum <= (select max(e) from t) - (select min(b)
5 from t) + 1)
6 where r between t.b and t.e
7 /
R
----------
3
4
5
6
7
8
9
10
8 rows selected.
See how the above query missed the ranges [1000, 1005], [09000, 09999];
The next query will fix it. Here the rownum table only needs to have max(e - b + 1) number of rows, instead of max(e) rows.
SQL>
SQL> select (t.b + r - 1)
2 from t, (select rownum r
3 from all_nulls
4 where rownum <= (select max(e) from t) - (select min(b)
5 from t) + 1)
6 where (t.b + r - 1) between t.b and t.e
7 order by 1
8 /
(T.B+R-1)
----------
3
4
5
6
7
8
9
10
1000
1001
1002
1003
1004
1005
9000
9001
9002
9003
9004
9005
....
....
....
9997
9998
1013 rows selected.
SQL> spool off
Zip Code Table
Bob B, September 10, 2004 - 5:58 pm UTC
If its a zip code table, I think a parent child lookup might be in order here. Create a child table of the zip code table that maps each zip code range to all the values in that range. That'll allow you to get the exact zip codes you want and will also help account for when you go international.
Once you have the child table, you have an entire oracle toolkit to optimize the performance of your query. Just a thought!
Still a more meaningful query
A reader, September 10, 2004 - 6:06 pm UTC
SQL> select (t.b + r - 1)
2 from t, (select rownum r
3 from all_nulls
4 where rownum <= (select max(e - b + 1) from t))
5 where (t.b + r - 1) between t.b and t.e
6 order by 1
7 /
The rownum table (all_nulls) needs to have only MAX(e-b+1) rows.
Alberto Dell'Era, September 11, 2004 - 6:25 am UTC
> scale it up.
But my test was made using *your* statements (the ones contained in your original answer), only using all_nulls instead of all_objects. I was basically comparing *your* solutions, just factoring out the all_objects massive overhead, and analyzing all the tables to level the playing field.
Let's scale it up to 1million:
create table all_nulls (x varchar2(1));
insert into all_nulls select null from all_objects, all_objects where rownum <= 1000010;
exec dbms_stats.gather_table_stats (user, 'all_nulls');
create table t (b int, e int);
insert into t (b,e) values (1, 10);
insert into t (b,e) values (11, 1000000);
commit;
exec dbms_stats.gather_table_stats (user, 't');
-- suppress output, factor out sqlplus display speed
set autotrace traceonly statistics
alter session set sql_trace = true;
select *
from
table( foo(cursor(select * from t) ) )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 66668 2.45 2.61 0 0 0 1000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 66670 2.45 2.61 0 0 0 1000000
select r
from t, (select rownum r
from all_nulls
where rownum <= (select max(e) from t) - (select min(b) from t) + 1
)
where r between t.b and t.e
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 66668 2.65 2.76 0 69719 0 1000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 66670 2.65 2.76 0 69719 0 1000000
Only difference: the 69719 consistent gets (latches...), that would prevent scaling in a real multiuser situation. But by setting arraysize to 1000 (quite appropriate if you're going to fetch 1million rows):
select *
from
table( foo(cursor(select * from t) ) )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1001 1.20 1.86 0 0 0 1000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1003 1.20 1.86 0 0 0 1000000
select r
from t, (select rownum r
from all_nulls
where rownum <= (select max(e) from t) - (select min(b) from t) + 1
)
where r between t.b and t.e
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1001 1.77 2.59 170 4052 0 1000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1003 1.77 2.59 170 4052 0 1000000
Consistent gets down to 4052.
My point is simply that using all_nulls instead of all_objects makes the two approaches comparable. I would consider both your solutions, when solving a real-life problem, since each one has its pros and cons ...
September 11, 2004 - 10:41 am UTC
In my test, the one with tkprofs -- where I compared the different queries, I had:
ops$tkyte@ORA9IR2> select * from t;
B E
---------- ----------
1 10
11 11
12 14
14 10000
that is what I was comparing to.
Yes, I concurr, with your own "dual" like table, it is more efficient than all_objects.
Alberto Dell'Era, September 11, 2004 - 11:30 am UTC
Excuse my persisting, but my results are different (9.2.0.5):
dellera@ORACLE9I> select * from t;
B E
---------- ----------
1 10
11 11
12 14
14 10000
dellera@ORACLE9I> select count(*) from all_nulls;
COUNT(*)
----------
1000010
NB Both t and all_nulls are analyzed
dellera@ORACLE9I> set autotrace traceonly statistics
dellera@ORACLE9I> alter session set sql_trace = true;
select *
from
table( foo(cursor(select * from t) ) )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 668 0.09 0.07 0 0 0 10001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 0.09 0.07 0 0 0 10001
select r
from t, (select rownum r
from all_nulls
where rownum <= (select max(e) from t) - (select min(b) from t) + 1
)
where r between t.b and t.e
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 668 0.05 0.07 0 738 0 10001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 0.05 0.07 0 738 0 10001
Your "mytable" rows contain the value "1" in it instead of "null" - can it impact the CBO so much ?
BTW The plan for the SQL statement is
Rows Row Source Operation
------- ---------------------------------
10001 NESTED LOOPS
4 TABLE ACCESS FULL OBJ#(31028) [table t]
10001 VIEW
40000 COUNT STOPKEY
40000 TABLE ACCESS FULL OBJ#(31027) [table all_nulls]
September 11, 2004 - 12:00 pm UTC
I would rate them more or less as "equivalent" and add a your mileage may vary (by cpu, whatever)
I used 9204 -- the cpu timings in mine might have been affected by a sql_trace issue that was resolved in 9205.
so i did it without tracing, using runstats:
ops$tkyte@ORA9IR2> @test
ops$tkyte@ORA9IR2> /*
DOC>drop table t;
DOC>drop table mytable;
DOC>
DOC>create table t (b int, e int);
DOC>insert into t values ( 1,10);
DOC>insert into t values ( 11,11);
DOC>insert into t values ( 12,14);
DOC>insert into t values ( 14, 10000 );
DOC>
DOC>create or replace type numArray as table of number
DOC>/
DOC>create or replace function foo( p_cursor in sys_refcursor ) return numArray
DOC>pipelined
DOC>as
DOC> l_b number;
DOC> l_e number;
DOC>begin
DOC> loop
DOC> fetch p_cursor into l_b, l_e;
DOC> exit when p_cursor%notfound;
DOC> for i in l_b .. l_e
DOC> loop
DOC> pipe row(i);
DOC> end loop;
DOC> end loop;
DOC> close p_cursor;
DOC> return;
DOC>end;
DOC>/
DOC>
DOC>create table mytable ( x int );
DOC>insert into mytable
DOC>select null from all_objects;
DOC>
DOC>create or replace procedure p1( n in number )
DOC>as
DOC> cursor c is
DOC> select * from table( foo(cursor(select * from t) ) );
DOC> type array is table of number;
DOC> l_data array;
DOC>begin
DOC> for i in 1 .. n
DOC> loop
DOC> open c;
DOC> loop
DOC> fetch c bulk collect into l_data limit 500;
DOC> exit when c%notfound;
DOC> end loop;
DOC> close c;
DOC> end loop;
DOC>end;
DOC>/
DOC>create or replace procedure p2( n in number )
DOC>as
DOC> cursor c is
DOC>select r
DOC> from t, (select rownum r
DOC> from mytable
DOC> where rownum <= (select max(e) from t) - (select min(b) from t) + 1)
DOC> where r between t.b and t.e;
DOC> type array is table of number;
DOC> l_data array;
DOC>begin
DOC> for i in 1 .. n
DOC> loop
DOC> open c;
DOC> loop
DOC> fetch c bulk collect into l_data limit 500;
DOC> exit when c%notfound;
DOC> end loop;
DOC> close c;
DOC> end loop;
DOC>end;
DOC>/
DOC>*/
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec p1( 50 )
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec p2( 50 )
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_stop;
Run1 ran in 59 hsecs
Run2 ran in 113 hsecs
run 1 ran in 52.21% of the time
Name Run1 Run2 Diff
LATCH.enqueues 11 10 -1
LATCH.session idle bit 13 12 -1
LATCH.simulator lru latch 0 1 1
STAT...cleanout - number of kt 1 0 -1
STAT...consistent gets - exami 1 0 -1
STAT...shared hash latch upgra 1 0 -1
STAT...table fetch by rowid 1 0 -1
STAT...index scans kdiixs1 1 0 -1
STAT...active txn count during 1 0 -1
LATCH.session timer 0 1 1
LATCH.archive control 0 2 2
STAT...bytes received via SQL* 876 874 -2
STAT...free buffer requested 8 10 2
LATCH.archive process latch 0 2 2
LATCH.cache buffers lru chain 7 10 3
STAT...redo entries 509 506 -3
LATCH.messages 0 4 4
STAT...db block changes 1,043 1,038 -5
STAT...enqueue releases 7 2 -5
STAT...enqueue requests 8 2 -6
STAT...db block gets 548 532 -16
STAT...execute count 106 55 -51
STAT...parse count (total) 57 6 -51
STAT...opened cursors cumulati 57 6 -51
STAT...Elapsed Time 64 117 53<b>
STAT...CPU used by this sessio 55 112 57
STAT...CPU used when call star 55 112 57</b>
STAT...recursive cpu usage 48 109 61
LATCH.session allocation 202 1 -201
LATCH.library cache pin alloca 341 41 -300
STAT...table scans (short tabl 50 350 300
STAT...redo size 65,352 65,024 -328
STAT...recursive calls 1,561 1,103 -458
LATCH.simulator hash latch 0 641 641
STAT...calls to get snapshot s 204 951 747
LATCH.library cache pin 2,674 174 -2,500
LATCH.shared pool 3,579 128 -3,451
STAT...buffer is not pinned co 403 4,950 4,547
STAT...no work - consistent re 402 4,950 4,548
STAT...table scan blocks gotte 400 4,950 4,550
LATCH.library cache 5,069 219 -4,850
STAT...session logical reads 1,054 6,383 5,329
STAT...consistent gets 506 5,851 5,345
LATCH.cache buffers chains 3,657 14,364 10,707
STAT...session pga memory max 41,368 65,536 24,168
STAT...session uga memory 65,464 0 -65,464
STAT...session pga memory 106,904 0 -106,904
STAT...table scan rows gotten 200 2,000,600 2,000,400
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
16,092 16,149 57 99.65%
PL/SQL procedure successfully completed.
Alberto Dell'Era, September 11, 2004 - 1:22 pm UTC
I've reproduced your runstats results on 9.2.0.5 (I have also analyzed the tables) - the following figures were basically the same:
run 1 ran in 58.39% of the time
STAT...CPU used by this sessio 83 138 55
STAT...consistent gets 530 5,854 5,324
LATCH.shared pool 3,615 178 -3,437
LATCH.library cache 5,026 241 -4,785
LATCH.cache buffers chains 1,143 11,804 10,661
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
13,133 13,110 -23 100.18%
So it's a matter of hitting the shared pool (pipelined version) or the buffer cache (pure-SQL version); seems quite intuitive.
Thanks for your time, always a pleasure to "discuss" with you!
PS You may find interesting trying the runstats example using native compilation for the pipelined (i cannot unfortunately).
An Oracle Support member told me some weeks ago that this will cut down the latches acquisitions for the shared pool, since the stored function becomes a .so library and so the "bytecode" doesn't have to be fetched from the library cache anymore.
If that happened, the pipelined version would scale much better than the SQL one ... looks promising in general, too, to look at native compilation as a scalability enabler.
Your very first solution needs a revision ...
Gabe, September 23, 2004 - 6:24 pm UTC
flip@flop> select * from t;
B E
---------- ----------
3 5
7 10
flip@flop> select r
2 from t, (select rownum r
3 from all_objects
4 where rownum <= (select max(e) from t)-(select min(b) from t)+1
5 )
6 where r between t.b and t.e
7 order by 1
8 ;
R
----------
3
4
5
7
8
September 24, 2004 - 10:16 am UTC
flip flop, nice ;)
you just added the order by right?
Way strange ...
Gabe, September 24, 2004 - 12:16 am UTC
Funny thing is Logan built on your strategy for that query and came up with the correct implementation ... but he didn't realise it and attribute it to some magic 999 number. His output right there shows he still missed out on the very last number: 9999 ... he returned 1013 rows when it should've been (10-3+1) + (1005-1000+1) + (9999-9000+1) = 1014
No offense Logan ... your query works (and you get the credit for it) ... but you still need as many numbers in ALL_NULLS as the largest/widest range (with your data ... 1000).
Bob's query is way cool for a straight SQL solution ... the tkprof numbers may need to be restated.
I'll take something back and I'll shut up.
Gabe, September 24, 2004 - 12:38 am UTC
Well, the tkprof numbers do not need to be restated
in fact Logans query ...
SQL> select (t.b + r - 1)
2 from t, (select rownum r
3 from all_nulls
4 where rownum <= (select max(e) from t) - (select min(b)
5 from t) + 1)
6 where (t.b + r - 1) between t.b and t.e
7 order by 1
8 /
does the same arithmetic as Bobs
SELECT t.b + val NUM
FROM
t,
(
SELECT ROWNUM - 1 val
FROM ALL_NULLS
WHERE ROWNUM <= ( SELECT MAX( e - b + 1 ) FROM t )
)
WHERE t.b + val <= t.e
It just does an extra MIN(b) and uses way more records from ALL_NULLS than it is required. Bob stops at the widest range
Logan uses the overall coverage of all ranges (with his data, 9999-3+1=9997 vs. 1000 for Bob). No need for tkprof.
Sorry about stuttering my observations.
September 24, 2004 - 10:46 am UTC
if T is large, the scalar subqueries can each to MIN/MAX optimizations whereas max(e-b+1) cannot.
(select max(e) from t) - (select min(b) from t) + 1)
is "in general" going to outperform
( SELECT MAX( e - b + 1 ) FROM t )
for large T's (assumed index on E and B)
Apples to Oranges
Bob B, September 24, 2004 - 11:00 am UTC
With appropriate indices
select
(select max(e) from t) - (select min(b) from t) + 1 val
from
dual
will outperform
select max( e - b + 1 ) val from t
However, they answer two COMPLETELY different questions. The first yields the number of continuous numbers that completely cover the desired result set. The second yields the number of continuous numbers that cover the largest range.
Its like saying
SELECT NAME
FROM emp
WHERE emp_id = 3
outperforms
SELECT NAME
FROM emp
WHERE dept_no = 10
If we're allowed to change the data structure for t, we could store and index a new column, range, that would store e - b + 1 for each row and then test out the queries, apples to apples. The min/max optimization goes out the window if there is not an index on both b and e.
September 24, 2004 - 12:07 pm UTC
doh - you are right :) didn't even think to look at that!
both would be applicable in different circumstances, thanks for pointing that out as always
"Apples to Oranges" ... hmmm
Gabe, September 24, 2004 - 12:43 pm UTC
I didn't compare the inline selects, did I? I compared the two approaches to explode the ranges.
The inlines both have ROWNUM ... hence will be _materialized_ prior to joining to T. They are both calculating how many rows to _generate_ ... one (Bob'd) finds the optimum, the other goes ballistic.
<quote>With appropriate indices</quote>?
FBI on (e - b + 1) would be an appropriate index, wouldn't it? So the indexes-argument doesn't fly unless prepared to use them in one case and not the other.
In any case, the _big_ thing is not the MAX/MIN stuff; it is how big the results from the inline views have to be.
Consider:
flip@flop> select * from t;
B E
---------- ----------
-999999999 -999999999
999999999 999999999
flip@flop> select count(0) from all_nulls;
COUNT(0)
----------
52576
Note: I don't really want it to create a monster ALL_NULLS just to prove a point.
flip@flop> SELECT t.b + val NUM
2 FROM
3 t,
4 (
5 SELECT ROWNUM - 1 val
6 FROM ALL_NULLS
7 WHERE ROWNUM <= ( SELECT MAX( e - b + 1 ) FROM t )
8 )
9 WHERE t.b + val <= t.e
10 ;
NUM
----------
-999999999
999999999
Elapsed: 00:00:00.01
flip@flop> select (t.b + r - 1)
2 from t, (select rownum r
3 from all_nulls
4 where rownum <= (select max(e) from t) - (select min(b)
5 from t) + 1)
6 where (t.b + r - 1) between t.b and t.e
7 order by 1
8 /
(T.B+R-1)
----------
-999999999
999999999
Elapsed: 00:00:01.07
I see two orders of magnitude right there in elapsed time ... if you are patient enough to look at the sql*plus screen just run the inline from the last query!
At the end of the day (given the 2 records from t), one approach build a 2 by 1 matrix and then start filetring out ... the other builds a 2 by lowest(1999999999,count from all_nulls) and then starts filtering. I don't really need tkprof to tell me which one is better.
Thanks.
Q on Your very first solution needs a revision ... September 23, 2004
steve, June 27, 2005 - 8:02 pm UTC
the result is not right?
should it include 9 and 10?
thanks!
June 27, 2005 - 8:25 pm UTC
can you be more precise? not sure what you are refering to?
I'm looking for the term for this problem
Mikito Harakiri, June 27, 2005 - 9:21 pm UTC
What would be the good name for this problem? "Interval Exploding" seems inaccurate and somewhat dumbed down. "Unpacking the intervals" is what Date et.al. uses in "Temporal data model". "Discrete interval sampling" is probably the most presize, but does it sound OK to somebody who never took signal processing class?
There is a way you can do away with using all_objects
milotan, July 01, 2005 - 1:32 pm UTC
Great!! But there is a way you can do away with using all_objects using SQL. By using the Dual table and Connect By, u can generate as many rows as you need.
(Select r
From (Select rownum r
From Dual
Connect By rownum <= 20000)
Or
Select *
From (
Select t.b + r val
From tt t,
(Select rownum -1 r
From dual,
(Select Max(e - b) + 1 maxRow
From tt) b
Connect By rownum<= b.maxRow)
Where t.b+ r <= t.e
)
Order By 1
Can't claimed to have discovered, I just stumbled into this query on Connor MacDonald's website.
July 01, 2005 - 1:56 pm UTC
not in 8i (and Mikito posted the concept originally of using dual as a row source generator, but it works only in 9i and up)
we've been using
with X
as
(select level l from dual connect by level <= :n)
select .....
ERROR ora-01426 numeric overflow in function foo
Ernesto Villarruel, August 03, 2007 - 4:47 pm UTC
Tom,
I get the following error message:
domaine: /home/test $ sqlplus dwhadmin
SQL*Plus: Release 10.1.0.4.0 - Production on Vie Ago 3 15:26:10 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> create or replace type numArray as table of number
2
3 /
Type created.
Elapsed: 00:00:00.32
SQL> create or replace type numArray as table of number
2 /
Type created.
Elapsed: 00:00:00.06
SQL> create or replace function foo( p_cursor in sys_refcursor ) return
2 numArray
3 pipelined
4 as
5 l_b number;
6 l_e number;
7 begin
8 loop
9 fetch p_cursor into l_b, l_e ;
10 exit when p_cursor%notfound;
11 for i in l_b .. l_e
12 loop
13 pipe row(i);
14 end loop;
15 end loop;
16 close p_cursor;
17 return;
18 end;
19 /
Function created.
Elapsed: 00:00:00.06
SQL> SELECT sum(e-b) total_numregexpand
2 ,count(*) total_numreg_on_t
3 ,max(e-b) max_range
4 FROM T;
TOTAL_NUMREGEXPAND TOTAL_NUMREG_ON_T MAX_RANGE
------------------ ----------------- ----------
176588899 50000 9999
Elapsed: 00:00:00.05
SQL> create table test_20070803_seriesexp
2 tablespace tdata22
3 nologging
4 nocache
5 nomonitoring
6 as
7 select * from table( foo(cursor(select * from t) ) );
select * from table( foo(cursor(select * from t) ) )
*
ERROR at line 7:
ORA-01426: numeric overflow
ORA-06512: at "DWHADMIN.FOO", line 11
Elapsed: 00:01:36.42
SQL> select * from t where rownum <=5;
B E
---------- ----------
18355000 18359999
18360000 18361999
18362000 18362999
18370000 18375999
18376000 18379999
Elapsed: 00:00:00.00
exists any limitation on type numArray or in function pipelined ?????
I would greatly appreciate your help on this one.
Ernesto Villarruel
August 05, 2007 - 1:50 pm UTC
I cannot reproduce in 9.2 or 10.2 - I do not have 10.1 anywhere
numeric overflow in function foo
Ernesto Villarruel Miranda, August 06, 2007 - 2:32 pm UTC
domaine: /home/test $ sqlplus dwhadmin
SQL*Plus: Release 10.1.0.4.0 - Production on Lun Ago 6 12:54:00 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> set timing on
SQL> set serveroutput on size 1000000
SQL> CREATE OR REPLACE function DWHADMIN.foo( p_cursor in sys_refcursor ) return
2 numArray
3 pipelined
4 as
5 l_b number;
6 l_e number;
7 begin
8 loop
9 fetch p_cursor into l_b, l_e ;
10 exit when p_cursor%notfound;
11 for i in l_b .. l_e
12 loop
13 pipe row(i);
14 end loop;
15 end loop;
16 exception when others then
17 dbms_output.put_line('Error on l_b:'||l_b|| ' l_e:'||l_e );
18 close p_cursor;
19 return;
20 end;
21 /
Function created.
Elapsed: 00:00:00.08
SQL> create table test_20070806_seriesexp
2 tablespace tdata22
3 nologging
4 nocache
5 nomonitoring
6 as
7 select * from table( foo(cursor(select * from t) ) );
Error on l_b:7564756128 l_e:7564756399
Table created.
Elapsed: 00:00:00.16
SQL> truncate table t;
Table truncated.
Elapsed: 00:00:02.02
SQL> insert into t ( b , e ) values (7564756128,7564756399);
1 row created.
Elapsed: 00:00:00.01
SQL> commit;
Commit complete.
Elapsed: 00:00:00.03
SQL> select * from table( foo(cursor(select * from t) ) );
no rows selected
Error on l_b:7564756128 l_e:7564756399
Elapsed: 00:00:00.02
SQL> CREATE OR REPLACE function DWHADMIN.foo( p_cursor in sys_refcursor ) return
2 numArray
3 pipelined
4 as
5 l_b number;
6 l_e number;
7 begin
8 loop
9 fetch p_cursor into l_b, l_e ;
10 exit when p_cursor%notfound;
11 for i in l_b .. l_e
12 loop
13 pipe row(i);
14 end loop;
15 end loop;
16 close p_cursor;
17 return;
18 end;
19 /
Function created.
Elapsed: 00:00:00.16
SQL> select * from table( foo(cursor(select * from t) ) );
select * from table( foo(cursor(select * from t) ) )
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at "DWHADMIN.FOO", line 11
Elapsed: 00:00:00.02
SQL> select * from t;
B E
---------- ----------
7564756128 7564756399
Elapsed: 00:00:00.01
SQL> CREATE OR REPLACE function DWHADMIN.foov1( p_cursor in sys_refcursor ) return
2 numArray
3 pipelined
4 as
5 l_b number;
6 l_e number;
7 begin
8 loop
9 fetch p_cursor into l_b, l_e ;
10 exit when p_cursor%notfound;
11
12 for i in 1 .. l_e - l_b + 1
13 loop
14 pipe row(l_b + i - 1);
15 end loop;
16 end loop;
17 close p_cursor;
18 return;
19 end;
20 /
Function created.
Elapsed: 00:00:00.16
SQL> select * from table( foov1(cursor(select * from t) ) );
COLUMN_VALUE
------------
7564756128
7564756129
7564756130
7564756131
7564756132
7564756133
7564756134
7564756135
7564756136
7564756137
7564756138
COLUMN_VALUE
------------
7564756139
7564756140
.
.
.
.
.
272 rows selected.
Elapsed: 00:00:00.02
SQL> CREATE OR REPLACE function DWHADMIN.foov2( p_cursor in sys_refcursor ) return
2 numArray
3 pipelined
4 as
5 l_b number;
6 l_e number;
7 begin
8 loop
9 fetch p_cursor into l_b, l_e ;
10 exit when p_cursor%notfound;
11 pipe row(l_b);
12
13 for i in 1 .. l_e - l_b 14 loop
15 pipe row(l_b + i );
16 end loop;
17 end loop;
18 close p_cursor;
19 return;
20 end;
21 /
Function created.
Elapsed: 00:00:00.11
SQL> select * from table( foov2(cursor(select * from t) ) );
COLUMN_VALUE
------------
7564756128
7564756129
7564756130
7564756131
7564756132
7564756133
7564756134
7564756135
7564756136
7564756137
7564756138
COLUMN_VALUE
------------
7564756139
7564756140
.
.
.
.
.
272 rows selected.
Elapsed: 00:00:00.03
SQL>
Database:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
S.O
SunOS domaine 5.10 Generic_125100-08 sun4u sparc SUNW,Sun-Fire-15000
I have the follow questions:
1.- is this a bug in instruction "FOR" inside of function foo for the values 7564756128 and 7564756399?
2.- With function foov1 and foov2 , what is the best performance? is this the best code?
I'm working on datawarehouse.
I would greatly appreciate your help on this one.
Ernesto Villarruel Miranda
August 07, 2007 - 9:50 am UTC
ops$tkyte%ORA10GR2> begin
2 for i in 7564756128..7564756399
3 loop
4 null;
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 2
http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14261/loop_statement.htm#sthref2852 ...
Internally, PL/SQL assigns the values of the bounds to temporary PLS_INTEGER variables, and, if necessary, rounds the values to the nearest integer. The magnitude range of a PLS_INTEGER is -2147483648 to 2147483647, represented in 32 bits. If a bound evaluates to a number outside that range, you get a numeric overflow error when PL/SQL attempts the assignment.
...
You could instead
for i in 1 .. (upper-lower)
loop
pipe row( lower+i )
end loop;
PLEASE NEVER USE WHEN OTHERS WITHOUT FOLLOWING IT WITH RAISE.
Do you see the 'table created', yet error obviously happened. This is the classic example of why when others is EVIL
either
o remove it
o put RAISE to re-raise the error after it.
NUMERIC OVERFLOW IN FUNCTION FOO
ERNESTO VILLARRUEL MIRANDA, August 08, 2007 - 11:08 am UTC
Excellent !!!
You are the number one !!!.
Thanks Tom.
ORA-01426: numeric overflow
Kubilay, December 03, 2007 - 4:55 am UTC
Hi Tom
As I have found ORA-01426 in this thread only, I thought best is to ask here
I am trying to do this in SQL*Plus Oracle 10g R2
SQL>select power(-0.0048, -300) from dual;
and I get
ORA-01426: numeric overflow
Is there a workaround to get a result from such a calculation in SQL*Plus or PL/SQL?
Many thanks for all your help!
Kubilay