Oracle中使用的若干技術(shù)經(jīng)典總結(jié)
2024-08-29 13:38:00
供稿：網(wǎng)友
怎么樣讓我的用戶名和密碼不泄漏？
=====================
在unix下，我用sqlplus sys/sys登陸，別的用戶很輕易就能看到我的密碼：怎么辦？
$ ps -efgrep sqlplus
Oracle 3787 3781 1 22:05:34 pts/3 0:00 sqlplus sys/sys
oracle 3789 3772 0 22:05:44 pts/2 0:00 grep sqlplus
采用sqlplus /nolog
sql>connect sys/sys，這樣別的用戶就看不到你的密碼啦?！?br />
怎樣生成建表的完整的DDL語(yǔ)句？
====================
用eXP ,再I(mǎi)mp，show=y可以看到?！?br />
或者使用某些Oracle 的小工具，比如quest的toad和sql*navigator.
truncate table和delete table有些什么區(qū)別？
==========================
truncate: DDL ,no rollback possibility and no rollback segment usage, quick ,release space used by the table except the original one.
delete: dml, can rollback, use rollback space, not release space, slow, delete large table may cause ora-1555 error.
如何刪除重復(fù)的記錄：
=============
第一個(gè)辦法： 1。生成建表的完整DDL語(yǔ)句,并且生成tab_bak的表名?！?br />
2。insert into tab_bak select distinct * from tab_name;
3。drop table tab_name, rename tab_bak to tab_name;
第二個(gè)辦法：
DELETE FROM table_name A WHERE ROWID >
( SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values);
第三個(gè)辦法：
Delete from my_table where rowid not in
( SQL> select max(rowid) from my_table
group by my_column_name );
第四個(gè)辦法：
delete from my_table t1
where exists (select 'x' from my_table t2
where t2.key_value1 = t1.key_value1
and t2.key_value2 = t1.key_value2
and t2.rowid > t1.rowid);
如何快速為已有的表加上一個(gè)主鍵？
=====================
加上一個(gè)非空的列，比如seqno，然后：
update table_name set seqno=rownum;
或者：
CREATE SEQUENCE testseq START WITH 1 INCREMENT BY 1;
update table_name set seqno=testseq.nextval;
SQL排序問(wèn)題：我怎么才能選擇出按照某個(gè)列排序后前N行來(lái)？
=====================================
在SQL*Server 里面，可以用這樣的語(yǔ)句：select top 10 col1,col2 from table_name;
從Oracle8i開(kāi)始，支持這樣的語(yǔ)法（在子查詢里面使用order by語(yǔ)句）
select * from (select col1,col2 from table_name order by col1,col2)
where rownum<11;
這樣就能夠起到同樣的效果。
在Oracle8或者以下，可以這樣：
SELECT col1,col2 FROM
(SELECT /*+ INDEX_DESC (table_name index_name) */ col1,col2 FROM table_name)
WHERE rownum < 6;
使用提示可以讓Oracle在子查詢返回結(jié)果之前先對(duì)他進(jìn)行排序，一般可以使用hintINDEX_DESC(TABLE_NAME,INDEX_NAME)來(lái)起到這個(gè)作用?！?br />
我們可以分別查看兩個(gè)SQL的執(zhí)行計(jì)劃：
scott@testdb> select * from sort_sample;
ID NAME
---------- ----------------------------------------
1 aa
5 33
90 23s
23 fdisk
746 2343
24 format
3 low format
7 rows selected.
scott@testdb> create index sort_id_idx on sort_sample(id);
Index created.
scott@testdb> set autotrace on explain
scott@testdb> --way 1:
scott@testdb> select * from (select * from sort_sample order by id desc) where rownum<3;
ID NAME
---------- ----------------------------------------
746 2343
90 23s
Execution Plan
-------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT (STOPKEY)
2 1 VIEW
3 2 SORT (ORDER BY STOPKEY)
4 3 TABLE access (FULL) OF 'SORT_SAMPLE'
scott@testdb> --way 2 :wrong result
scott@testdb> select * from sort_sample where rownum<3;
ID NAME
---------- ----------------------------------------
1 aa
5 33
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'SORT_SAMPLE'
scott@testdb> ANALYZE TABLE SORT_SAMPLE COMPUTE STATISTICS;
Table analyzed.
scott@testdb> ANALYZE INDEX SORT_ID_IDX COMPUTE STATISTICS;
Index analyzed.
scott@testdb> --way 3: can work in oracle8 and oracle7
scott@testdb> select * from (select /*+index_desc(sort_sample sort_id_idx)*/ * from sort_sample)
2 where rownum<3;
ID NAME
---------- ----------------------------------------
1 aa
5 33
//原因：col sort_id_idx列為nullable,所以CBO不能確定，加上not null約束即可達(dá)到目的。
用group by可以生成從小開(kāi)始的排序：
scott@testdb> SELECT ID,NAME FROM
2 (SELECT ID,NAME,COUNT(*) FROM SORT_SAMPLE GROUP BY ID, NAME)
3 WHERE ROWNUM<3;
ID NAME
---------- ----------------------------------------
1 aa
3 low format
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=7 Bytes=175)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=3 Card=7 Bytes=175)
3 2 SORT (GROUP BY STOPKEY) (Cost=3 Card=7 Bytes=56)
4 3 TABLE ACCESS (FULL) OF 'SORT_SAMPLE' (Cost=1 Card=7
6。怎么每隔N條記錄獲得一條記錄？比如第3，6，9等？
=================================
CHAO@PING>select * from testseq;
ID NAME
---------- -------------------------------------
1 this is 1th record
2 this is 2th record
3 this is 3th record
4 this is 4th record
5 this is 5th record
6 this is 6th record
7 this is 7th record
8 this is 8th record
9 this is 9th record
10 this is 10th record
10 rows selected.
CHAO@PING>select id, name from
2 (select id, name, rownum rz from testseq) temp
3 where mod(rz,3)=0;
ID NAME
---------- -------------------------------------------
3 this is 3th record
6 this is 6th record
9 this is 9th record
CHAO@PING>
如何刪除一個(gè)列？
===========
從Oracle8i開(kāi)始，Oracle支持一個(gè)列的刪除，語(yǔ)法如下：
alter table tab_name drop column col1;
7。如何重命名一個(gè)列？
==============
CHAO@PING> create table testrename(id number, nama varchar2(30));
Table created.
CHAO@PING> begin
2 for x in 1..10 loop
3 insert into testrename values(x,'this is 'to_char(x)'th record');
4 end loop;
5 end;
6 /
PL/SQL PRocedure sUCcessfully completed.
CHAO@PING> commit;
Commit complete.
CHAO@PING> alter table testrename add name varchar2(30);
Table altered.
CHAO@PING> update testrename set name=nama;
10 rows updated.
CHAO@PING> alter table testrename drop column nama;
Table altered.
CHAO@PING> select * from testrename;
ID NAME
---------- ---------------------------------------------
