熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle中使用的若干技術經典總結

2013-11-13 16:12:52  來源: Oracle 

  怎麼樣讓我的用戶名和密碼不洩漏? 
  =====================
  在unix下我用sqlplus sys/sys登陸別的用戶很容易就能看到我的密碼怎麼辦? 
   
  $ ps ef|grep sqlplus 
  oracle :: pts/ : sqlplus sys/sys 
  oracle :: pts/ : grep sqlplus 
  采用sqlplus /nolog 
   
  sql>connect sys/sys這樣別的用戶就看不到你的密碼啦 
   
  怎樣生成建表的完整的DDL語句? 
  ====================
  用exp 再Impshow=y可以看到 
   
  或者使用某些Oracle 的小工具比如quest的toad和sql*navigator 
   
  truncate table和delete table有些什麼區別? 
  ==========================
  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 error 
   
  如何刪除重復的記錄 
  =============
  第一個辦法 生成建表的完整DDL語句並且生成tab_bak的表名 
   
  insert into tab_bak select distinct * from tab_name; 
   
  drop table tab_name rename tab_bak to tab_name; 
   
  第二個辦法 
   
  DELETE FROM table_name A WHERE ROWID > 
  ( SELECT min(rowid) FROM table_name B 
   
  WHERE Akey_values = Bkey_values); 
   
  第三個辦法 
   
  Delete from my_table where rowid not in 
  ( SQL> select max(rowid) from my_table 
   
  group by my_column_name ); 
   
  第四個辦法 
   
  delete from my_table t 
  where exists (select x from my_table t 
   
  where tkey_value = tkey_value 
   
  and tkey_value = tkey_value 
   
  and trowid > trowid); 
   
  如何快速為已有的表加上一個主鍵? 
  =====================
  加上一個非空的列比如seqno然後 
   
  update table_name set seqno=rownum; 
   
  或者 
   
  CREATE SEQUENCE testseq START WITH INCREMENT BY ; 
  update table_name set seqno=testseqnextval; 
   
  SQL排序問題我怎麼才能選擇出按照某個列排序後前N行來? 
  =====================================
  在SQL*Server 裡面可以用這樣的語句select top colcol from table_name; 
   
  從Oraclei開始支持這樣的語法(在子查詢裡面使用order by語句) 
   
  select * from (select colcol from table_name order by colcol) 
   
  where rownum<; 
   
  這樣就能夠起到同樣的效果 
   
  在Oracle或者以下可以這樣 
   
  SELECT colcol FROM 
  (SELECT /*+ INDEX_DESC (table_name index_name) */ colcol FROM table_name) 
  WHERE rownum < ; 
   
  使用提示可以讓Oracle在子查詢返回結果之前先對他進行排序一般可以使用hintINDEX_DESC(TABLE_NAMEINDEX_NAME)來起到這個作用 
   
  我們可以分別查看兩個SQL的執行計劃 
   
  scott@testdb> select * from sort_sample; 
   
  ID NAME 
    
   aa 
    
   s 
   fdisk 
    
   format 
   low format 
   rows selected 
   
  scott@testdb> create index sort_id_idx on sort_sample(id); 
   
  Index created 
  scott@testdb> set autotrace on explain 
  scott@testdb> way : 
  scott@testdb> select * from (select * from sort_sample order by id desc) where rownum<; 
   
  ID NAME 
    
    
   s 
   
  Execution Plan 
   
   SELECT STATEMENT Optimizer=CHOOSE 
   COUNT (STOPKEY) 
   VIEW 
   SORT (ORDER BY STOPKEY) 
   TABLE ACCESS (FULL) OF SORT_SAMPLE 
   
  scott@testdb> way :wrong result 
  scott@testdb> select * from sort_sample where rownum<; 
   
  ID NAME 
    
   aa 
    
   
  Execution Plan 
   
   SELECT STATEMENT Optimizer=CHOOSE 
   COUNT (STOPKEY) 
   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 : can work in oracle and oracle 
  scott@testdb> select * from (select /*+index_desc(sort_sample sort_id_idx)*/ * from sort_sample) 
   where rownum<; 
   
  ID NAME 
    
   aa 
    
   
  //原因col sort_id_idx列為nullable所以CBO不能確定加上not null約束即可達到目的 
   
  用group by可以生成從小開始的排序 
   
  scott@testdb> SELECT IDNAME FROM 
   (SELECT IDNAMECOUNT(*) FROM SORT_SAMPLE GROUP BY ID NAME) 
   WHERE ROWNUM<; 
   
  ID NAME 
    
   aa 
   low format 
   
  Execution Plan 
   
   SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=) 
   COUNT (STOPKEY) 
   VIEW (Cost= Card= Bytes=) 
   SORT (GROUP BY STOPKEY) (Cost= Card= Bytes=) 
   TABLE ACCESS (FULL) OF SORT_SAMPLE (Cost= Card= 
   
  怎麼每隔N條記錄獲得一條記錄?比如第等? 
  =================================
  CHAO@PING>select * from testseq; 
   
  ID NAME 
    
   this is th record 
   this is th record 
   this is th record 
   this is th record 
   this is th record 
   this is th record 
   this is th record 
   this is th record 
   this is th record 
   this is th record 
   
   rows selected 
   
  CHAO@PING>select id name from 
   (select id name rownum rz from testseq) temp 
   where mod(rz)=; 
   
  ID NAME 
    
   this is th record 
   this is th record 
   this is th record 
   
  CHAO@PING> 
   
  如何刪除一個列? 
  ===========
  從Oraclei開始Oracle支持一個列的刪除語法如下 
   
  alter table tab_name drop column col; 
   
  如何重命名一個列? 
  ==============
  CHAO@PING> create table testrename(id number nama varchar()); 
   
  Table created 
   
  CHAO@PING> begin 
   for x in loop 
   insert into testrename values(xthis is ||to_char(x)||th record); 
   end loop; 
   end; 
   / 
   
  PL/SQL procedure successfully completed 
   
  CHAO@PING> commit; 
   
  Commit complete 
   
  CHAO@PING> alter table testrename add name varchar(); 
   
  Table altered 
   
  CHAO@PING> update testrename set name=nama; 
   
   rows updated 
   
  CHAO@PING> alter table testrename drop column nama; 
   
  Table altered 
   
  CHAO@PING> select * from testrename; 
   
  ID NAME 
    

From:http://tw.wingwit.com/Article/program/Oracle/201311/17969.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.