- The Oracle 9i manuals
are supposed to be online (free username/password required)
- A PL/SQL package to generate random numbers
What if you want a random number in the where clause, like, so you
can choose 1/10 of the rows at random? Use a hash instead.
select * from emp where dbms_utility.get_hash_value(dump(rowid),0,100)<10;
- The biggest difference between Oracle and everyone else is the locking
mechanism. Everyone else gets share locks on data when they query it.
That means other queries can also see the data, but queries block writes
and writes block queries. (Update: the default for everyone else, for
for example for DB2, is for queries not to block writes. Instead they
show any data committed at the time the query internally fetches the
data. This means each query result can be internally inconsistent, but
it allows greater concurrency.) Oracle doesn't have share locks. Instead it
uses snapshots, which are points in time. A query looks at the data as of
some point in time. If someone changes the data later, that's OK, the query
still sees the old version of the data. Each query result is internally
consistent, but consecutive queries may not be consistent with one another.
Queries don't block writes and writes don't block queries. Writes still
block writes. Both share locks and snapshots are reasonable concurrency
models, but they aren't the same. It's wrong to think in terms of one when
working with the other.
- Derived tables (from-clause subqueries, inline views):
select * from (select * from emp);
insert into (select deptno from dept)
select trunc(a) from (select sum(empno)/5000 a from emp);
This has the same effect as if you used a view. It is useful
for queries that have a repeated complex expression; for example
select x from (select avg(sin(empno)) x from emp group by deptno)
where x > 0;
It's also useful when you want to use one of the values found by a
subquery, for example when deleting all outdated entries from a log:
delete from log where rowid in
(select rowid from log
minus
select a.rowid
from log a, (select id, max(time) mtime from log group by id) b
where a.id = b.id and a.time = b.mtime);
As of 9i, inline views can also be expressed with a "WITH clause",
for example
with sub1 as (select deptno, avg(sin(empno)) x from emp group by deptno)
select a.deptno, a.x, b.x from sub1 a, sub1 b where a.deptno > b.deptno;
- When should varchar2 be used instead of char? Always. Varchar2 is
always faster, more space efficient, less buggy, and its comparison
rules are more likely to be what you expect.
- Is SQL case sensitive? No. Can I give tables case-sensitive
names with weird characters in them? Yes. Quote them, like so:
create table "Table" ("My column is KEWL!!!" int);
- How do I insert data into an nvarchar2 column? From 9i onwards, just
like any other character column, and the character set is guaranteed to
hold the Unicode repertoire and measure characters using UCS2 codepoints.
There is implicit conversion between char and nchar. Before 9i, avoid
nvarchar2. If you really must use it, you insert like so: "insert
into nemp (ename) values (n'SCOTT')".
- Building a big bogus table:
create table a (a1 number, a2 varchar2(50));
begin
for i in 1..10000 loop
insert into a values (i, 'I am a unique and extraordinary individual');
end loop;
commit;
end;
/
alter table a add constraint apk primary key (a1);
select count(*) from a; -- 10000 rows
- Like the making of sausage and politics, it is best not to understand
the making of Oracle data types.
select dump(1), dump(-1), dump(sysdate) from dual;
Ouch! Ouch! You're twisting my arm. I'll explain, for Oracle numbers
at least.
- All data on disk looks like <length><stuff>
- All data on disk is byte-sortable (other than the lengths).
- Digits are base 100, and the first byte is the (base-100)
exponent. For example, the number 1 has exponent 193, 10 also has 193,
100 has 194, 0.01 has 192.
- Negative numbers complement the exponent. 62 for -1, 63 for -.01,
61 for -100.
- Why? Because the exponent has to be byte-sortable.
- Digits. Positive digits are 1..100. Negative digits are 101..2.
Again, to be byte-sortable. Base-100 digits use about 7 out of 8 bits
of each byte, so this is a reasonably efficient way of storing
numbers.
- Negative numbers have a trailing byte of value 102, unless the
maximum number of digit bytes (20) are used. To make things byte
sortable. -1 > -1.01, right? But (62,100) < (62,100,100). The
trailing 102 corrects that: (62,100,102) > (62,100,100,102).
- But why isn't the exponent for 1 192, for -1 63, and why are the
digits 1..100 and 101..2 instead of 0..99 and 99..0? Because, way
back in prehistory, Oracle used 0 as a null terminator, so you
couldn't store 0 on disk for any other purpose. So 1 was added to
everything. There's no reason not to store 0 on disk anymore because
all values store their lengths separately, but the number format hasn't
changed.
- My guess is that negative infinity was originally (1,1), pushing
the negative numbers out to 101..2 instead of 100..1. That's not
what negative infinity is now, and SQL doesn't expose the infinities
anyhow.
- To find out how a query is executed, create a plan table with
UTLXPLAN.SQL, fill the table with
explain plan for select ... ;
and then look at the results with a connect-by script, something like this:
select substrb(to_char(cost),1,6) cost,
substrb(to_char(cardinality),1,6) card,
substrb(substr(' . , . , . ',1,level) || operation,1,20) type,
substrb(options,1,15) subtype,
substrb(object_owner,1,5) owner,
substrb(object_name,1,5) name,
substrb(object_node,1,5) link from plan_table
connect by parent_id = prior id
start with id = 0;
delete from plan_table;
- Insert and update with check option:
insert into (select * from emp where deptno = 10 with check option)
values (...);
update (select * from (select sal, empno from emp, dept
where emp.deptno=dept.deptno and loc='AKRON')
where sal > 100000 with check option)
set sal = :1 where empno = :2;
- Mutating/Constraining errors.
Before 8i:
- You only get these when you modify a table being read
or you read a table being modified. Foreign key constraints read some
tables implicitly. A table that might be deleted from due to delete
cascade counts as modifying.
- You only get these errors from per-row triggers, not from after
statement or before statement triggers (unless they are under per-row
triggers).
- Delete cascade enforcement fires statement triggers every time it
delete cascades a row, so all those statement triggers are subject to
mutating errors too.
When you get the error, use your per-row trigger to fill a PL/SQL
table instead. Then use an after-statement trigger to apply the
changes based on that PL/SQL table. There are generic packages out
there for generating the triggers, PL/SQL tables, and so forth needed
to implement update cascade. Often you need more PL/SQL variables to
keep track of whether you are already under a trigger, to avoid
recursion.
After 8i: Half the mutating errors
go away. Specifically, it is still illegal to read or modify a
mutating table, but modifying a table that is being read is fine.
This allows the obvious before-row trigger implementation of update
cascade, for example. The problem of delete cascade firing statement
triggers under row triggers is fixed in Oracle8i too.
- How to enable constraints without locking tables for hours on end
(using Oracle8 or later). First put all constraints in the ENABLE
NOVALIDATE mode, then ENABLE (or VALIDATE) them individually. The
ENABLE NOVALIDATE modifies only metadata. The VALIDATE hold no locks,
can run in parallel, and many such enables can be run concurrently.
- How to store data case sensitive but to index it case insensitive
(from 8i on): Set compatible=8.1.0.0.0,
query_rewrite_integrity=trusted, and query_rewrite_enabled=true.
create table a (a1 varchar2(10));
create index ai on a (upper(a1));
analyze table a compute statistics;
This will use the cost-based optimizer. From what I've heard, that's
OK. As of Oracle8i the cost-based optimizer is a good thing, and it
can use several access methods the rule-based optimizer doesn't know
about.