ÉèΪÊ×Ò³ ¼ÓÈëÊÕ²Ø

TOP

oracleÃæÊÔÌâ¼°Æä´ð°¸(Ò»)
2014-11-24 02:23:02 ¡¾´ó ÖРС¡¿ ä¯ÀÀ:2611´Î
Tags£ºoracle ÊÔÌâ ¼°Æä ´ð°¸

Ò»¡¢»ù´¡¸ÅÄî
1. Áоټ¸ÖÖ±íÁ¬½Ó·½Ê½
Answer£ºµÈÁ¬½Ó£¨ÄÚÁ¬½Ó£©¡¢·ÇµÈÁ¬½Ó¡¢×ÔÁ¬½Ó¡¢ÍâÁ¬½Ó£¨×ó¡¢ÓÒ¡¢È«£©
Or hash join/merge join/nest loop(cluster join)/index join £¿£¿
ORACLE 8i£¬9i ±íÁ¬½Ó·½·¨¡£


Ò»°ãµÄÏàµÈÁ¬½Ó£º select * from a, b where a.id = b.id; Õâ¸ö¾ÍÊôÓÚÄÚÁ¬½Ó¡£


¶ÔÓÚÍâÁ¬½Ó£º
OracleÖпÉÒÔʹÓá°(+) ¡±À´±íʾ£¬9i¿ÉÒÔʹÓÃLEFT/RIGHT/FULL OUTER JOIN


LEFT OUTER JOIN£º×óÍâ¹ØÁª
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
µÈ¼ÛÓÚ
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id(+)
½á¹ûΪ£ºËùÓÐÔ±¹¤¼°¶ÔÓ¦²¿ÃŵļǼ£¬°üÀ¨Ã»ÓжÔÓ¦²¿ÃűàºÅdepartment_idµÄÔ±¹¤¼Ç¼¡£


RIGHT OUTER JOIN£ºÓÒÍâ¹ØÁª
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
µÈ¼ÛÓÚ
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+)=d.department_id
½á¹ûΪ£ºËùÓÐÔ±¹¤¼°¶ÔÓ¦²¿ÃŵļǼ£¬°üÀ¨Ã»ÓÐÈκÎÔ±¹¤µÄ²¿ÃżÇ¼¡£


FULL OUTER JOIN£ºÈ«Íâ¹ØÁª
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
½á¹ûΪ£ºËùÓÐÔ±¹¤¼°¶ÔÓ¦²¿ÃŵļǼ£¬°üÀ¨Ã»ÓжÔÓ¦²¿ÃűàºÅdepartment_idµÄÔ±¹¤¼Ç¼ºÍûÓÐÈκÎÔ±¹¤µÄ²¿ÃżÇ¼¡£


ORACLE8iÊDz»Ö±½ÓÖ§³ÖÍêÈ«ÍâÁ¬½ÓµÄÓï·¨£¬Ò²¾ÍÊÇ˵²»ÄÜÔÚ×óÓÒÁ½¸ö±íÉÏͬʱ¼ÓÉÏ(+)£¬ÏÂÃæÊÇÔÚORACLE8i¿ÉÒԲο¼µÄÍêÈ«ÍâÁ¬½ÓÓï·¨
select t1.id,t2.id from table1 t1,table t2 where t1.id=t2.id(+)
union
select t1.id,t2.id from table1 t1,table t2 where t1.id(+)=t2.id


Á¬½ÓÀàÐÍ
¶¨Òå ͼʾ Àý×Ó
ÄÚÁ¬½Ó Ö»Á¬½ÓÆ¥ÅäµÄÐÐ select A.c1,B.c2 from A join B on A.c3 = B.c3;
×óÍâÁ¬½Ó °üº¬×ó±ß±íµÄÈ«²¿ÐУ¨²»¹ÜÓұߵıíÖÐÊÇ·ñ´æÔÚÓëËüÃÇÆ¥ÅäµÄÐУ©ÒÔ¼°Óұ߱íÖÐÈ«²¿Æ¥ÅäµÄÐÐ select A.c1,B.c2 from A left join B on A.c3 = B.c3;
ÓÒÍâÁ¬½Ó °üº¬Óұ߱íµÄÈ«²¿ÐУ¨²»¹Ü×ó±ßµÄ±íÖÐÊÇ·ñ´æÔÚÓëËüÃÇÆ¥ÅäµÄÐУ©ÒÔ¼°×ó±ß±íÖÐÈ«²¿Æ¥ÅäµÄÐÐ select A.c1,B.c2 from A right join B on A.c3 = B.c3;
È«ÍâÁ¬½Ó °üº¬×ó¡¢ÓÒÁ½¸ö±íµÄÈ«²¿ÐУ¬²»¹ÜÔÚÁíÒ»±ßµÄ±íÖÐÊÇ·ñ´æÔÚÓëËüÃÇÆ¥ÅäµÄÐÐ select A.c1,B.c2 from A full join B on A.c3 = B.c3;
£¨theta£©Á¬½Ó ʹÓõÈÖµÒÔÍâµÄÌõ¼þÀ´Æ¥Åä×ó¡¢ÓÒÁ½¸ö±íÖеÄÐÐ select A.c1,B.c2 from A join B on A.c3 != B.c3;
½»²æÁ¬½Ó Éú³ÉµÑ¿¨¶û»ý¡ª¡ªËü²»Ê¹ÓÃÈκÎÆ¥Åä»òÕßÑ¡È¡Ìõ¼þ£¬¶øÊÇÖ±½Ó½«Ò»¸öÊý¾ÝÔ´ÖеÄÿ¸öÐÐÓëÁíÒ»¸öÊý¾ÝÔ´µÄÿ¸öÐÐһһƥÅä ¡¡ select A.c1,B.c2 from A,B;


2. ²»½èÖúµÚÈý·½¹¤¾ß£¬ÔõÑù²é¿´sqlµÄÖ´Ðмƻ®
I) ʹÓÃExplain Plan,²éѯPLAN_TABLE;
EXPLAIN PLAN
SET STATEMENT_ID=¡¯QUERY1¡ä
FOR
SELECT *
FROM a
WHERE aa=1;
SELECT operation, options, object_name, object_type, ID, parent_id
FROM plan_table
WHERE STATEMENT_ID = ¡®QUERY1¡ä
ORDER BY ID;
II)SQLPLUSÖеÄSET TRACE ¼´¿É¿´µ½Execution Plan Statistics
SET AUTOTRACE ON;
3. ÈçºÎʹÓÃCBO,CBOÓëRULEµÄÇø±ð
IF ³õʼ»¯²ÎÊý OPTIMIZER_MODE = CHOOSE THEN ¨C(8I DEFAULT)
IF ×ö¹ý±í·ÖÎö
THEN ÓÅ»¯Æ÷ Optimizer=CBO(COST); /*¸ßЧ*/
ELSE
ÓÅ»¯Æ÷ Optimizer=RBO(RULE); /*¸ßЧ*/
END IF;
END IF;


Çø±ð£º
RBO¸ù¾Ý¹æÔòÑ¡Ôñ×î¼ÑÖ´Ðз¾¶À´ÔËÐвéѯ¡£
CBO¸ù¾Ý±íͳ¼ÆÕÒµ½×îµÍ³É±¾µÄ·ÃÎÊÊý¾ÝµÄ·½·¨È·¶¨Ö´Ðмƻ®¡£
ʹÓÃCBOÐèҪעÒ⣺
I) ÐèÒª¾­³£¶Ô±í½øÐÐANALYZEÃüÁî½øÐзÖÎöͳ¼Æ;
II) ÐèÒªÎȶ¨Ö´Ðмƻ®;
III)ÐèҪʹÓÃÌáʾ(Hint);
ʹÓÃRULEÐèҪעÒ⣺
I) Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò
II) ÓÅ»¯SQLµÄд·¨;


ÔÚoptimizer_mode=chooseʱ,Èç¹û±íÓÐͳ¼ÆÐÅÏ¢£¨·ÖÇø±íÍ⣩,ÓÅ»¯Æ÷½«Ñ¡ÔñCBO,·ñÔòÑ¡RBO¡£
RBO×ñÑ­¼òµ¥µÄ·Ö¼¶·½·¨Ñ§,ʹÓÃ15ÖÖ¼¶±ðÒªµã£¬µ±½ÓÊÕµ½²éѯ£¬ÓÅ»¯Æ÷½«ÆÀ¹ÀʹÓõ½µÄÒªµãÊýÄ¿,È»ºóÑ¡Ôñ×î¼Ñ¼¶±ð£¨×îÉÙµÄÊýÁ¿£©µÄÖ´Ðз¾¶À´ÔËÐвéѯ¡£
CBO³¢ÊÔÕÒµ½×îµÍ³É±¾µÄ·ÃÎÊÊý¾ÝµÄ·½·¨,ΪÁË×î´óµÄÍÌÍÂÁ¿»ò×î¿ìµÄ³õʼÏìӦʱ¼ä,¼ÆËãʹÓò»Í¬µÄÖ´Ðмƻ®µÄ³É±¾£¬²¢Ñ¡Ôñ³É±¾×îµÍµÄÒ»¸ö,¹ØÓÚ±íµÄÊý¾ÝÄÚÈݵÄͳ¼Æ±»ÓÃÓÚÈ·¶¨Ö´Ðмƻ®¡£
4. ÈçºÎ¶¨Î»ÖØÒª(ÏûºÄ×ÊÔ´¶à)µÄSQL
ʹÓÃCPU¶àµÄÓû§session
SELECT a.SID, spid, status, SUBSTR (a.program, 1, 40) prog, a.terminal,a.SQL_TEXT, osuser, VALUE / 60 / 100 VALUE
FROM v$session a, v$process b, v$sesstat c
WHERE c.statistic# = 12 AND c.SID = a.SID AND a.paddr = b.addr
ORDER BY VALUE DESC;


select sql_text from v$sql
where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 30000);
5. ÈçºÎ¸ú×Ùij¸ösessionµÄSQL
ÀûÓÃTRACE ¸ú×Ù
ALTER SESSION SET SQLTRACE ON;
COLUMN SQL format a200;
SELECT machine, sql_text SQL
FROM v$sqltext a, v$session b
WHERE address = sql_address
AND machine = ¡®&A¡¯
ORDER BY hash_value, piece;


exec dbms_

Ê×Ò³ ÉÏÒ»Ò³ 1 2 3 4 5 6 7 ÏÂÒ»Ò³ βҳ 1/9/9
¡¾´ó ÖРС¡¿¡¾´òÓ¡¡¿ ¡¾·±Ìå¡¿¡¾Í¶¸å¡¿¡¾Êղء¿ ¡¾ÍƼö¡¿¡¾¾Ù±¨¡¿¡¾ÆÀÂÛ¡¿ ¡¾¹Ø±Õ¡¿ ¡¾·µ»Ø¶¥²¿¡¿
ÉÏһƪ£ºÈçºÎÅжÏÒ»¶Î³ÌÐòÊÇÓÉ C ±àÒë³ÌÐò.. ÏÂһƪ£ºÓÃÁ´±íÄ£Äâ´óÕûÊý¼Ó·¨ÔËËã

×îÐÂÎÄÕÂ

ÈÈÃÅÎÄÕÂ

Hot ÎÄÕÂ

Python

C ÓïÑÔ

C++»ù´¡

´óÊý¾Ý»ù´¡

linux±à³Ì»ù´¡

C/C++ÃæÊÔÌâÄ¿