Llayland’s Food, Oracle, and Haskell

May 5, 2013

Update returning old values, possible solution

Filed under: Oracle — llayland @ 5:41 am

Warning:  While this appears to work, I do not know if the behavior is defined.

It seems that using a scalar subquery in the select list of an updatable view allows us to return the old value of a column.  I’ll update this post with more details when I have time to do some additional testing.

  •  Correctness
    • Can I get it to return incorrectly?
    • What happens when an update restarts?
  • Performance
    • How does this compare to pre-selecting?
    • Is it possible to have latch issues, even though using dual?
  • ?

Here is a sample script you can use to start your own tests.

drop table t1;

create table t1 as select n n1 ,n n2
from (select dbms_random.random() n from
(select * from dual connect by level <= 1000)
, (select * from dual connect by level <= 1000)
);

declare
type n3 is record (n1 number, n2 number, n3 number);
type n3_tab is table of n3;
rets n3_tab;

n1_cnt number := 0;
n2_cnt number := 0;
other_cnt number := 0;
minus_1_cnt number := 0;
begin
update (select n1, n2, (select n2 from dual) n3 from t1)
set n2 = n2 + 1 — where rownum <= 10
returning n1, n2, n3 bulk collect into rets;

for i in rets.first .. rets.last loop
declare
r n3 := rets(i);
begin
<<inc_cnts>> case
when r.n3 = r.n1 then
n1_cnt := n1_cnt + 1;
when r.n3 = r.n2 then
n2_cnt := n2_cnt + 1;
else
other_cnt := other_cnt + 1;
end case inc_cnts;
if r.n3 = r.n2-1 then
minus_1_cnt := minus_1_cnt + 1;
end if;
end;
end loop;
dbms_output.put_line(‘returned correctly cnt: ‘ || minus_1_cnt);
dbms_output.put_line(‘==n1: ‘ || n1_cnt);
dbms_output.put_line(‘==n2: ‘ || n2_cnt);
dbms_output.put_line(‘else: ‘ || other_cnt);
end;
/

 

Advertisements

June 14, 2012

Quiz: index including a constant

Filed under: Oracle — llayland @ 3:28 pm

I was surprised when i tried to create an index like this and it actually worked:

Create index t1idx on t1(n1,1);

The question is: Why would you want to do this?

There is at least one valid use case.

December 10, 2010

crazy rewrites

Filed under: Oracle — llayland @ 4:48 am

Jonathan Lewis’s posts on index joins inspired me to run a few test cases of my own. I thought it was pretty neat that the optimizer could split a single table query into a 6 way self join. It really blew my mind when I found it could do the reverse. I also found it quite cute that it does the opposite of what I tell it too. That’s just how stuff works for me.

This was run on a fresh install of the developer vm in VirtualBox:

Edit – I really need to learn to preview. Here is the corrected script and output
set echo on
set linesize 200

drop table t1;

create table t1 (n1,n2,n3,n4,n5,n6,filler)
nocache
as
select trunc(rownum/100),
trunc(rownum/100),
trunc(rownum/100),
trunc(rownum/100),
trunc(rownum/100),
trunc(rownum/100),
lpad(rownum, 1000, '0')
from dual connect by level <= 100000;

create bitmap index t1_n1 on t1(n1);
create bitmap index t1_n2 on t1(n2);
create bitmap index t1_n3 on t1(n3);
create bitmap index t1_n4 on t1(n4);
create bitmap index t1_n5 on t1(n5);
create bitmap index t1_n6 on t1(n6);

call dbms_stats.gather_table_stats('SCOTT','T1');

set timing on;

set autotrace traceonly;

set arraysize 5000;

select n1,n2,n3,n4,n5,n6 from t1;

select a.n1, b.n2, c.n3, d.n4, e.n5, f.n6
from t1 a, t1 b, t1 c, t1 d, t1 e, t1 f
where a.rowid = all(b.rowid, c.rowid, d.rowid, e.rowid, f.rowid);



SQL> select n1,n2,n3,n4,n5,n6 from t1;

100000 rows selected.

Elapsed: 00:00:01.65

Execution Plan
———————————————————-
Plan hash value: 362797529

——————————————————————————————————
| Id  | Operation                         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————————
|   0 | SELECT STATEMENT                  |                  |   100K|  2343K|  2018   (1)| 00:00:25 |
|   1 |  VIEW                             | index$_join$_001 |   100K|  2343K|  2018   (1)| 00:00:25 |
|*  2 |   HASH JOIN                       |                  |       |       |            |          |
|*  3 |    HASH JOIN                      |                  |       |       |            |          |
|*  4 |     HASH JOIN                     |                  |       |       |            |          |
|*  5 |      HASH JOIN                    |                  |       |       |            |          |
|*  6 |       HASH JOIN                   |                  |       |       |            |          |
|   7 |        BITMAP CONVERSION TO ROWIDS|                  |   100K|  2343K|    11   (0)| 00:00:01 |
|   8 |         BITMAP INDEX FULL SCAN    | T1_N1            |       |       |            |          |
|   9 |        BITMAP CONVERSION TO ROWIDS|                  |   100K|  2343K|    11   (0)| 00:00:01 |
|  10 |         BITMAP INDEX FULL SCAN    | T1_N2            |       |       |            |          |
|  11 |       BITMAP CONVERSION TO ROWIDS |                  |   100K|  2343K|    11   (0)| 00:00:01 |
|  12 |        BITMAP INDEX FULL SCAN     | T1_N3            |       |       |            |          |
|  13 |      BITMAP CONVERSION TO ROWIDS  |                  |   100K|  2343K|    11   (0)| 00:00:01 |
|  14 |       BITMAP INDEX FULL SCAN      | T1_N4            |       |       |            |          |
|  15 |     BITMAP CONVERSION TO ROWIDS   |                  |   100K|  2343K|    11   (0)| 00:00:01 |
|  16 |      BITMAP INDEX FULL SCAN       | T1_N5            |       |       |            |          |
|  17 |    BITMAP CONVERSION TO ROWIDS    |                  |   100K|  2343K|    11   (0)| 00:00:01 |
|  18 |     BITMAP INDEX FULL SCAN        | T1_N6            |       |       |            |          |
——————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – access(ROWID=ROWID)
3 – access(ROWID=ROWID)
4 – access(ROWID=ROWID)
5 – access(ROWID=ROWID)
6 – access(ROWID=ROWID)

Statistics
———————————————————-
1  recursive calls
0  db block gets
86  consistent gets
0  physical reads
0  redo size
526501  bytes sent via SQL*Net to client
628  bytes received via SQL*Net from client
21  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
100000  rows processed

SQL>
SQL> select a.n1, b.n2, c.n3, d.n4, e.n5, f.n6
2  from t1 a, t1 b, t1 c, t1 d, t1 e, t1 f
3  where a.rowid = all(b.rowid, c.rowid, d.rowid, e.rowid, f.rowid);

100000 rows selected.

Elapsed: 00:00:07.78

Execution Plan
———————————————————-
Plan hash value: 3617692013

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |   100K|  3515K|  3919   (1)| 00:00:48 |
|   1 |  TABLE ACCESS FULL| T1   |   100K|  3515K|  3919   (1)| 00:00:48 |
————————————————————————–

Statistics
———————————————————-
1  recursive calls
0  db block gets
14307  consistent gets
14286  physical reads
0  redo size
103737103  bytes sent via SQL*Net to client
628  bytes received via SQL*Net from client
21  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
100000  rows processed

February 12, 2009

Thanks Computer Junkyard

Filed under: Oracle — llayland @ 5:46 am

Just a quick thank you to Computer Junkyard I had a great experience there today.

If you are in or around Tampa, please check them out.

August 14, 2008

A quick gripe

Filed under: Oracle — llayland @ 5:14 am

I really did not want the Oracle section of this blog to be my whining about all the nasty things Oracle does to me. It is just hard to find the time and motivation to write up anything positive when I run into three new (to me at least) bugs in three days. So, I’m going to gripe:

I hit the bug in metalink note 469587.1 where “Error PLS-00167 : keyword BULK is used in a wrong context” is raised with an incorrect interval literal. Of course, I had to come up with a unique twist of my own. Apparently this:

INTERVAL '.1' SECOND

is not valid and should be:

INTERVAL '0.1' SECOND

The other thing I ran into is that binds of objects tend to cause bugs. This applies to both explicit binds in dynamic sql and the implicit binds of pl/sql variables in sql statements. I’ll write up a good post sometime soon, but for now just note that the type information seems to get lost and that using the treat function around the bound variable to get the type information back seems to fix things.

August 7, 2008

Default precision fractional for timestamp datatype is 6. Huh?

Filed under: Oracle — llayland @ 6:09 am

I’ve always read that the default precision for timestamps was 6.

From “Oracle® Database SQL Language Reference 11g Release 1 (11.1) Part Number B28286-03

TIMESTAMP Datatype

The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values. This datatype is useful for storing precise time values. Specify the TIMESTAMP datatype as follows:

TIMESTAMP [(fractional_seconds_precision)]

where fractional_seconds_precision optionally specifies the number of digits Oracle stores in the fractional part of the SECOND datetime field. When you create a column of this datatype, the value can be a number in the range 0 to 9. The default is 6.

I found a fun little case where the default precision is 0. It was the end of a long day when I did something similar to this:

SQL> create or replace type timestamp_tab as table of timestamp;
2 /

Type created.

SQL>
SQL> create or replace function get_ts return timestamp_tab
2 is
3 retval timestamp_tab;
4 begin
5 select
6 to_timestamp('20080102','YYYYMMDD')
7 + interval '0.1' second * (rownum - 2)
8 bulk collect into retval
9 from dual
10 connect by level <= 3;
11
12 return retval;
13 end get_ts;
14 /

Function created.

SQL>
SQL> begin
2 for rec in (select * from table(get_ts)) loop
3 dbms_output.put_line(rec.column_value);
4 dbms_output.put_line(to_char(rec.column_value, 'FF'));
5 end loop;
6 end ;
7 /
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000

PL/SQL procedure successfully completed.

Strange, I’ve lost my fractional seconds. Maybe I need to explicitly set the precision in my type:


SQL> create or replace type timestamp_tab as table of timestamp(4);
2 /Type created.

SQL>
SQL> begin
2 for rec in (select * from table(get_ts)) loop
3 dbms_output.put_line(rec.column_value);
4 dbms_output.put_line(to_char(rec.column_value, 'FF'));
5 end loop;
6 end ;
7 /
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000

PL/SQL procedure successfully completed.

No dice. Let’s go back to basics to make sure I’m not just braindead at this point:


SQL> begin
2 dbms_output.put_line(
3 to_timestamp('20080102','YYYYMMDD') + interval '0.1' second * -1
4 );
5 end;
6 /
2008-01-01 23:59:59.900000000PL/SQL procedure successfully completed.

SQL>
SQL> begin
2 for rec in (
3 select to_timestamp('20080102','YYYYMMDD')
4 + interval '0.1' second * -1 ts
5 from dual
6 ) loop
7 dbms_output.put_line(rec.ts);
8 end loop;
9 end;
10 /
2008-01-01 23:59:59.900000000

PL/SQL procedure successfully completed.

So, I do normally get six digits by default.  Maybe it is a bulk collect issue?


SQL> create or replace function get_ts2 return timestamp_tab
2 is
3 retval timestamp_tab := timestamp_tab();
4 begin
5 retval.extend(3);
6
7 for rec in (
8 select rownum n,
9 to_timestamp('20080102','YYYYMMDD')
10 + interval '0.1' second * (rownum - 2) ts
11 from dual
12 connect by level <= 3
13 ) loop
14 retval(rec.n) := rec.ts;
15 end loop;
16 return retval;
17 end get_ts2;
18 /Function created.

SQL>
SQL> begin
2 for rec in (select * from table(get_ts2)) loop
3 dbms_output.put_line(rec.column_value);
4 dbms_output.put_line(to_char(rec.column_value, 'FF'));
5 end loop;
6 end ;
7 /
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000

PL/SQL procedure successfully completed.

Nope.  Maybe an explicit cursor?


SQL>
SQL> declare
2 cursor ts_cur is
3 select * from table(get_ts);
4 begin
5 for rec in ts_cur loop
6 dbms_output.put_line(rec.column_value);
7 dbms_output.put_line(to_char(rec.column_value, 'FF'));
8 end loop;
9 end ;
10 /
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000PL/SQL procedure successfully completed.

Nope.  Hmm, I vaguely remember that you can declare the return type of a cursor but I’ve never had to do it before.


SQL>
SQL> declare
2 type ts_rec is record(ts timestamp);
3 cursor ts_cur return ts_rec is
4 select column_value from table(get_ts);
5 begin
6 for rec in ts_cur loop
7 dbms_output.put_line(rec.ts);
8 dbms_output.put_line(to_char(rec.ts, 'FF'));
9 end loop;
10 end ;
11 /
2008-01-01 23:59:59.900000
900000000
2008-01-02 00:00:00.000000
000000000
2008-01-02 00:00:00.100000
100000000PL/SQL procedure successfully completed.

Aha, so the default precision for a timestamp when returned from a table function into a cursor is 0. Just for grins:


SQL>
SQL> declare
2 type ts_rec is record(ts timestamp(0));
3 cursor ts_cur return ts_rec is
4 select column_value from table(get_ts);
5 begin
6 for rec in ts_cur loop
7 dbms_output.put_line(rec.ts);
8 dbms_output.put_line(to_char(rec.ts, 'FF'));
9 end loop;
10 end ;
11 /
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000PL/SQL procedure successfully completed.

So, problem solved. Hopefully this will save someone out there some time and head scratching.

Create a free website or blog at WordPress.com.