MV NOTES

References MVIEWS
CREATE Materialized view xxx.yyy REFRESH COMPLETE START WITH date() SYSDATE WITH PRIMARY KEY 
 AS SELECT * FROM @ACERLINK

TCPDUMP USAGE METHOD

TCPDUMP is a usage tool , when you want to trace  a connection or connections .
for example
 tcpdump -i eth1 udp
#-i mean which interface . such as  -i  eth1, eth0
#in different os . have different  interface name

ORA-ERRORS

ORA-16401:	archivelog rejected by RFS
Cause:	An attempt was made to re-archive an existing archivelog. This usually happens because either a multiple primary database or standby database(s) or both are trying to archive to this standby database.
Action:	See alert log and trace file for more details. No action is necessary; this is an informational statement provided to record the event for diagnostic purposes.

調校一個DBLINK 連線時間太久

由於我方(政府機關機房)的DB 要連線去 另一個政府機關的ORACLE DB 去作DBLINK, 可是反映說連線動作時間太久,  經過思索一番認為是FW及IPS  檢查封包時間太久(是可以過)
due to a dataflow change , origin they sync data  to 2 Database from local government  to two Bureau under Ministry of Economic Affair. one of  database local on in my office . another one is main database on
other government  machines farm. now is change 3 tier layout just keep one database . our database is canceling soon. we need create a dblink  to their db. but when we test . to slow .
so I think it will be TCP/IP head check  spend many time.
原本(ORIGIN Response  time ) need : 16 MINS .
after  addition IP and port to white list on FW.  it from 16 mins to 13 Mins.then
I ask soc to add 2 ip on 2 IPS . it from 13 mins to 7mins .
still to slow .

SINGLE ROW-FUNCTIONS

SINGLE ROW-FUNCTIONS
可以用於 SELECT NVL(), 或Where xx=nvl(),order by
可以修改 資料型態
--
http://ist.marshall.edu/ist466/functions.html
--
Any arithmetic operation on a NULL value results in a NULL. The same model is true for most functions as well. Only functions

    * NVL
    * NVL2
    * CONCAT
    * DECODE
    * DUMP
    * and REPLACE

can return non-NULL value when called with a NULL argument.

JOIN 的技巧

可分為LEFT JOIN 及 RIGHT JOIN ,nature join
在現今的ORACLE ANSI SQL 有2個方式 作
1. 用 select x.e, i.x from iae i , xxo x  where i.id=x.id;
2. 在新版的 要加關鍵字 ON 
3. nature join

在範例要求用新版的ANSI SQL 規範來作答　所以要用　ＯＮ

GROUPING

不同於GROUP BY 放 在FROM .. WHERE 之後 (其實功用也不同)
Grouping 可以SELECT 當時就可以將 欄位GROUPING 起來
並且可以將 GROUPING 出來的欄位 匯整起來,如有相同類別及資料就會SHOW 出1 , 如有NULL 就會出現 0 .
參考來源:http://www.remote-dba.net/pl_sql/t_sql_grouping.htm

範例
:

SELECT

   deptno,

   job,

   count(*),

   sum(sal),
GROUPING(Job)
FROM

   emp
GROUP BY

   ROLLUP(deptno,job);