本章提要
-------------------------------
本章是概述性章节
1. 介绍了了解数据库内部结构对于开发的重要性
2. 介绍了如何才能开发好的数据库应用程序
-------------------------------
varchar2 类型定义时, 个人认为应该选择byte 类型, 即 varchar2(20), oracle 支持的最大的字符串是 varchar2(4000), 同时, 个人认为, 当你定义一个varchar2时, 首先预判这个地段大概能放多少内容, 比如20个字符,但是由于多字符集中, 比如utf8, 一个字符并不是对应一个字节, 比如有可能对应4个字节, 所以, 比如我们定义的字段有20个字, 那么, 我们需要定义80个字节以上, 比如 varchar2(100), 这样是比较保险的
1.
基本上 95% 的问题都可以通过 SQL解决, %5 PL/SQL 和 C 解决
自治事物的作用( 不推荐使用自治事物 )
1) error-logging: 记录错误, 无论你的transaction是否提交, 都需要知道你曾经做过的内容
2) demonstration concept: 用来演示, 比如你想演示两个独立的session 等
作为一个developer必须要基本上清楚database内部结构, 不能将database看成是一个"黑盒"
举例: bitmap索引, 你需要了解bitmap索引是如何工作的, 否则就会出问题
-- 01 test_autonomous_transaction&bitmap.sql
/*
* Test autonomous_transaction, simulation two session
* bitmap will lock the column, when uncommit transaction exist
*/ create table t
( test_flag varchar2(1)); create bitmap index t_idx on t(test_flag); -- uncommit
insert into t values('N'); -- antonomous_transaction
declare
pragma autonomous_transaction;
begin
insert into t values('Y');
end;
/
01 test_autonomous_transcation&bitmap
例子说明: 因为bitmap索引的特点是, 会对column增加锁, 所以正确的做法是对该列增加B*tree索引
举例: 函数索引
-- 02 function_index.sql
/*
* This program will test index on function,
* so when you want to use index on where condition,
* You need to use function.
*/ create table t
( id number primary key,
test_flag varchar2(1),
payload varchar2(20)
); -- if test_flag == 'N', return 'N'
create index t_idx on t(decode(test_flag, 'N', 'N')); insert into t
select r,
case
when mod(r, 2) = 0 then 'N'
else 'Y'
end,
'payload ' || r
from (select level r
from dual
connect by level <= 5);
/ select * from t; create or replace function get_first_unlocked_row
return t%rowtype
as
resource_busy exception;
pragma exception_init(resource_busy, -54);
l_rec t%rowtype;
begin
for x in (select rowid rid
from t
where decode(test_flag, 'N', 'N') = 'N') -- index function
loop
begin
select * into l_rec
from t
where rowid = x.rid and test_flag = 'N'
for update nowait;
return l_rec;
exception
when resource_busy then null;
when no_data_found then null;
end;
end loop;
return null;
end;
/ -- test function
declare
l_rec t%rowtype;
begin
l_rec := get_first_unlocked_row; dbms_output.put_line('I got row ' || l_rec.id || ', ' || l_rec.payload);
commit;
end;
/
-- result is 2. declare
l_rec t%rowtype;
cursor c
is
select *
from t
where decode(test_flag, 'N', 'N') = 'N' -- use decode function is for index
for update
skip locked;
begin
open c;
fetch c into l_rec;
if (c%found)
then
dbms_output.put_line('I got row ' || l_rec.id || ', ' || l_rec.payload);
end if;
close c;
end;
/ -- result is 2 declare
l_rec t%rowtype;
pragma autonomous_transaction;
cursor c
is
select *
from t
where decode(test_flag, 'N', 'N') = 'N' -- use decode function is for index
for update
skip locked; -- if you don't use this statement, the process will hang and wait
begin
open c;
fetch c into l_rec;
if (c%found)
then
dbms_output.put_line('I got row ' || l_rec.id || ', ' || l_rec.payload);
end if;
close c;
commit;
end;
/ -- result is 4
2.
怎样才能更好的创建application ?
1) understanding oracle architecture
2) Use a single connection in Oracle( 即多个查询使用一个connection, 这样当然节省资源)
3) Use Bind variables
这里涉及到软解析和硬解析的概念, 另外, 如果不使用绑定变量, 有可能引起 SQL INJECTION(SQL注入),
即用户输入了不合法数据, 使应用存在安全隐患.
-- 03 & 04
/*
* This program will show use bind variable is very effect
*/ create table t(x int); create or replace procedure proc1
as
begin
for i in 1..10000
loop
execute immediate
'insert into t values(:x)'
using i;
end loop;
end;
/ create or replace procedure proc2
as
begin
for i in 1..10000
loop
execute immediate
'insert into t values('|| i ||')';
end loop;
end;
/ -- use runstats_pkg test to procedure
exec runstats_pkg.rs_start;
exec proc1;
exec runstats_pkg.rs_middle;
exec proc2;
exec runstats_pkg.rs_stop(10000);
03
/*
* This program will test SQL injection when you don't use bind variable.
*/ create or replace procedure inj(p_date in date)
as
l_rec all_users%rowtype;
c sys_refcursor;
l_query long;
begin
l_query := '
select *
from all_users
where created = ''' || p_date || '''';
dbms_output.put_line(l_query);
open c for l_query; for i in 1..5
loop
fetch c into l_rec;
exit when c%notfound;
dbms_output.put_line(l_rec.username || '.....');
end loop;
close c;
end;
/ -- you want to show
exec inj(sysdate); -- show the dangerous part about this inj procedure
create table user_pw
( uname varchar2(30) primary key,
pw varchar2(30)
); insert into user_pw(uname, pw)
values('TKYTE', 'TO SECRET');
COMMIT;
-- now, some user don't know user_pw table exist, simulation this table
-- +is very important.
grant execute on inj to scott; -- so now, scott connect the database, and do as below
alter session set nls_date_format = '"''union select tname, 0, null from tab--"';
exec leon.inj(sysdate);
/*
* The result is:
* -----------------------------------------
select *
from all_users
where created = ''union select tname, 0, null from tab--'
* -----------------------------------------
* we know table information, some important table.
*/
-- in this way, scott can see the table user_pw(very important table)
-- now they want to try to select this important table.
select * from leon.user_pw; -- but they can not, because they don't have privilege. alter session set nls_date_format = '"''union select tname || cname, 0, null from col--"';
exec leon.inj(sysdate);
/*
* The result is:
* -----------------------------------------
select *
from all_users
where created = ''union select tname || cname, 0, null from col--'
* -----------------------------------------
* we know the column information in some important table.
*/ -- use bind variable to pertect you.
create or replace procedure NOT_inj(p_date in date)
as
l_rec all_users%rowtype;
s sys_refcursor;
l_query long;
begin
l_query := '
select *
from all_users
where created = :x';
dbms_output.put_line(l_query);
open c for l_query using P_DATE;
for i in 1..5
loop
fetch c into l_rec;
exit when c%notfound;
dbms_output.put_line(l_rec.username || '....');
end loop;
close c;
end;
/ -- test not_inj
exec not_inj(sysdate);
/*
* The result is:
* -----------------------------------------
select *
from all_users
where created = :x
* -----------------------------------------
*/ -- so from now on, you must use bind variable. ^^
04
4) understanding concurrency control
并发是很难控制的, 而且很容易导致我们的应用程序出现问题, 锁可以用来处理并发, 但是如果锁利用不当,
就会给程序的可扩展和多用户操作造成阻碍, 所以, in your database is vital if you are to develop a scalable,
correct application.
举例: 控制并发
-- 05 control_concurrency.sql
create table resources
( resource_name varchar2(25) primary key,
other_data varchar2(25)
); create table schedules
( resource_name varchar2(25) references resources,
start_time date,
end_time date
); -- the purpose about schedules is when someone want to modify table resources
-- he needs to check the schedules status, but if someone
-- modify the table schedules, and you select schedules, you can not find
-- the result modified.
select count(*)
from schedules
where resource_name = :resource_name
and (start_time < :new_start_time)
and (end_time > :new_end_time);
-- so the correct selectment is as below:
select * from resources where resource_name := resource_name for update;
-- for update is very important, it is lock on the row. so if someone want to
-- modify the table, you will know it.
5) implementing locking ( 使用锁, 上边已经有例子了)
6) flashback
SCN: This SCN is Oracle’s internal clock: every time a commit occurs, this clock ticks upward (increments).
flashback举例:
-- 06 flashback_example.sql
variable scn number;
exec :scn := dbms_flashback.get_system_change_number;
print scn; select count(*) from emp; -- 14 rows
delete from emp;
select count(*) from emp; -- 0 rows -- use flashback, as of scn, as of timestamp
select count(*)
:scn then_scn,
dbms_flashback.get_system_change_number now_scn
from emp as of scn :scn; -- get the time point at :scn
-- the result is
/*
COUNT(*) THEN_SCN NOW_SCN
---------- ---------- ----------
14 6294536 6294537
*/
-- you can see the result is 14.
commit; -- commit the transaction
select cnt_now, cnt_then, :scn then_scn,
dbms_flashback.get_system_change_number now scn
from (select count(*) cnt_now from emp),
(select count(*) cnt_then, from emp as of scn :scn)
/
/*
CNT_NOW CNT_THEN THEN_SCN NOW_SCN
---------- ---------- ---------- ----------
14 14 6294536 6294552
*/ flashback table emp to scn :scn; -- the data return
7) Read Consistency and Non-Blocking Reads
读一致性, 并且没有读锁.
8) Database Independence
当需要数据库迁移时, 你要知道, 即便是相同的数据库, 比如都是 oracle, 那么它们的内部运作机制也可能不一样, 这也有可能
给你带来一些问题, 更别提那些不同数据库之间的迁移问题, 比如: 将标准SQL转换成plsql:
07 convert_SQL_to_PLSQL.sql
declare
l_some_varibale varchar2(25);
begin
if (some_condition)
then
l_some_variable := f(...);
end if; for x in (select * from t where x = l_some_variable)
loop
...
end loop;
-- as this statement
-- in oracle, this query return no data when l_some_variable was not set to a specific value
-- in sybase or sql server, the query would find rows where x was set to a null value. -- in oracle null can not use = to set condition, like as below:
select * from dual where null = null; -- return 0 rows selected
select * from dual where null <> null; -- return 0 rows selected
select * from dual where null is null; -- return X. -- to solve this problem
select * from t
where nvl(x, -1) = nvl(l_some_variable, -1); -- and you need to create a function index
create index t_idx on t(nvl(x, -1));
9) The impact of standards
SQL99 is an ANSI/ISO standard for databases, 各个数据库在实现这个标准时有不同, 另外这个标准有些时候也是有问题的, 所以
你只要专注你目前使用的数据库的标准.
10) Layered Programming (分层Programming)
Let’s say you are programming using JDBC, 不同的数据库之间, 要使用不同的方法实现, 并且利用存储过程实现.
11) Knowing What's Out There
不是完全了解SQL的特性
example: inline views
-- inline views
select p.id, c1_sum1, c2_sum2
from p,
(select id, sum(q1) c1_sum1
from c1
group by id) c1,
(select id, sum(q2) c2_sum2
from c2
group by id) c2
where p.id = c1.id
and p.id = c2.id -- sub query that run another query per row
select p.id,
(select sum(q1) from c1 where c1.id = p.id) c1_sum1,
(select sum(q2) from c2 where c2.id = p.id) c2_sum2
from p
where p.name = '' -- sub factory with clause
with c1_vw as
(select id, sum(q1) c1_sum1
from c1
group by id),
c2_vw as
(select id, sum(q2) c2_sum2
from c2
group by id),
c1_c2 as
(select c1.id, c1.c1_sum1, c2.c2_sum2
from c1_vw c1, c2_vw c2
where c1.id = c2.id)
select p.id, c1_sum1, c2_sum2
from p, c1_c2
where p.id = c1_c2.id
12) sloving problems simply
选择简单的办法来解决事情, 比如想控制某个用户只能一个session连接上来, 简单的办法是:
09 do_job_easy_way.sql
create profile one_session limit sessions_per_user 1; alter user scott profile one_session; alter system set resource_limit = true;
13) How Do i make it run faster ?
80%以上的性能问题都出现在设计实现级, 而不是数据库级. 在对数据库上运行的应用进行优化之前, 不要对数据库进行优化.