°ü¸® ¾È³»¼
SQL¹®¿¡
´ëÇÑ Explian µ¥ÀÌÅ͸¦ ÃëµæÇϱâ Àü¿¡, Explain ±â´ÉÀ» È£ÃâÇÏ´Â
±ÇÇÑ ID¿Í µ¿ÀÏÇÑ ½ºÅ°¸¶¸¦ »ç¿ëÇÏ¿© Á¤ÀÇµÈ ÇÑ ¼¼Æ®ÀÇ
Explain Å×À̺íÀÌ ÀÖ¾î¾ß ÇÕ´Ï´Ù. Å×À̺í ÀÛ¼º ¹æ¹ý¿¡
°üÇÑ Á¤º¸´Â Explain Å×ÀÌºí¿¡ ´ëÇÑ Å×À̺í Á¤ÀÇ¿¡¼ ÀÚ¼¼ÇÑ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
ÀÏ´Ü À̵é
Å×À̺íÀÌ Á¤ÀǵǸé, SQL¹®ÀÌ ÄÄÆÄÀϵǰí Explain µ¥ÀÌÅͰ¡ ¿äûµÉ ¶§
Explain µ¥ÀÌÅͰ¡ ĸÃĵ˴ϴÙ.
- Á¤Àû ¶Ç´Â ÁõºÐ½Ä ¹ÙÀεå
SQL¹®ÀÇ °æ¿ì, Explain Å×À̺í Á¤º¸´Â
EXPLAIN ALL ¶Ç´Â EXPLAIN YES ¿É¼ÇÀÌ BIND ¶Ç´Â PREP ¸í·É¿¡ ÁöÁ¤µÇ°Å³ª
¼Ò½º ÇÁ·Î±×·¥¿¡ Á¤Àû EXPLAIN SQL¹®ÀÌ »ç¿ëµÉ °æ¿ì¿¡ ĸÃĵ˴ϴÙ.
ÁÖ: | ÁõºÐ½Ä ¹ÙÀεå SQL¹®ÀÌ ·±Å¸ÀÓ ½Ã ÄÄÆÄÀ쵃 °æ¿ì, ±× SQL¹®Àº
·±Å¸ÀÓ ½Ã Explain Å×ÀÌºí¿¡ ³õÀÌ¸ç ¹ÙÀÎµå ½Ã¿¡´Â ³õÀÌÁö ¾Ê½À´Ï´Ù.
¶ÇÇÑ, Explain Å×ÀÌºí¿¡ »ðÀÔÇϱâ À§ÇØ »ç¿ëµÇ´Â Explain Å×ÀÌºí ±ÔÁ¤ÀÚ¿Í
±ÇÇÑ ºÎ¿© ID´Â ÆÐŰÁö¸¦ ¼öÇàÇÒ ¶§ »ç¿ëÇÏ´Â °ÍÀÌ ¾Æ´Ï¶ó ÆÐŰÁö ¼ÒÀ¯ÀÚÀÇ °ÍÀÔ´Ï´Ù.
|
- µ¿Àû SQL¹®¿¡ ´ëÇØ explain Å×À̺í Á¤º¸´Â
´ÙÀ½ »óȲ¿¡ ÀÇÇØ ĸÃĵ˴ϴÙ.
-
EXPLAIN SQL¹®. FOR SNAPSHOTÀýÀÌ
»ç¿ëµÇÁö ¾ÊÀ» °æ¿ì, ¸ðµç Explain Á¤º¸°¡ ĸÃĵǾî Explain Å×ÀÌºí¿¡ º¸°üµË´Ï´Ù.
´ÙÀ½Àº EXPLAIN SQL¹®ÀÇ ¿¹ÀÔ´Ï´Ù.
EXPLAIN PLAN FOR <any valid DELETE, INSERT, SELECT, SELECT INTO,
UPDATE, VALUES, or VALUES INTO SQL statement>
-
CURRENT EXPLAIN MODE Ư¼ö ·¹Áö½ºÅͰ¡
YES·Î ¼³Á¤µË´Ï´Ù. ÀÌ ¼³Á¤°ªÀº SQL ÄÄÆÄÀÏ·¯°¡ Explain µ¥ÀÌÅ͸¦
ĸÃÄÇϵµ·Ï Çϸç, SQL¹®ÀÌ ½ÇÇàµÇ¾î Á¶È¸ÀÇ °á°ú°¡ ¸®Åϵǵµ·Ï
ÇÕ´Ï´Ù.
- CURRENT EXPLAIN MODE Ư¼ö ·¹Áö½ºÅͰ¡ EXPLAINÀ¸·Î ¼³Á¤µË´Ï´Ù.
ÀÌ ¼³Á¤°ªÀ¸·Î SQL ÄÄÆÄÀÏ·¯´Â Explain µ¥ÀÌÅ͸¦ ĸÃÄÇÏÁö¸¸,
SQL¹®À» ½ÇÇàÇÏÁö´Â ¾Ê½À´Ï´Ù.
- CURRENT EXPLAIN MODE Ư¼ö ·¹Áö½ºÅÍ´Â RECOMMEND INDEXES·Î
¼³Á¤µË´Ï´Ù. ÀÌ·¸°Ô ¼³Á¤Çϸé, SQL ÄÄÆÄÀÏ·¯°¡ Explain µ¥ÀÌÅÍ ¹×
±ÇÀåµÇ´Â »öÀÎÀ» ĸÃÄÇÏ¿© ADVISE_INDEX Å×ÀÌºí¿¡ ÀúÀåÇÕ´Ï´Ù.
±×·¯³ª, SQL¹®Àº ½ÇÇàµÇÁö ¾Ê½À´Ï´Ù.
- CURRENT EXPLAIN MODE Ư¼ö ·¹Áö½ºÅÍ´Â EVALUATE INDEXES·Î ¼³Á¤µË´Ï´Ù.
ÀÌ·¸°Ô ¼³Á¤Çϸé, SQL ÄÄÆÄÀÏ·¯´Â »ç¿ëÀÚ°¡ ADVISE_INDEX Å×ÀÌºí¿¡
³ÖÀº »öÀÎÀ» »ç¿ëÇÏ°Ô µË´Ï´Ù. »ç¿ëÀÚ´Â Æò°¡µÇ¾î¾ß ÇÒ °¢ »öÀο¡ ´ëÇÑ
»õ·Î¿î ÇàÀ» »ðÀÔÇÕ´Ï´Ù. °¢ »öÀο¡ ÇÊ¿äÇÑ Á¤º¸´Â »öÀÎ À̸§, Å×À̺í À̸§,
Æò°¡µÇ´Â »öÀÎÀ» ±¸¼ºÇÏ´Â Ä÷³ À̸§ÀÔ´Ï´Ù.
ÀÏ´Ü ÀÔ·ÂÇϸé, Ư¼ö ·¹Áö½ºÅÍÀÎ CURRENT EXPLAIN MODE°¡
EVALUATE INDEXES·Î ¼³Á¤µÇ¾î¾ß ÇÕ´Ï´Ù. ±×·¯¸é, SQL ÄÄÆÄÀÏ·¯´Â
USE_INDEX Çʵ尡 "Y"·Î ¼³Á¤µÈ ADVISE_INDEX Å×À̺íÀ» ½ºÄµÇÕ´Ï´Ù.
À̸¦ °¡»ó »öÀÎÀ̶ó°í ÇÕ´Ï´Ù. EVALUTE INDEXS ¸ðµå¿¡¼ ½ÇÇàµÇ´Â ¸ðµç µ¿Àû¹®¿¡´Â
ÀÌ °¡»ó »öÀÎÀÌ »ç¿ë °¡´ÉÇÑ °ÍÀ¸·Î ¼³¸íµÇ¾î ÀÖ½À´Ï´Ù. ¸í·É¹®ÀÇ ¼º´ÉÀÌ Çâ»óµÉ
°æ¿ì, SQL ÄÄÆÄÀÏ·¯´Â °¡»ó »öÀÎÀ» »ç¿ëÇϵµ·Ï ¼±ÅÃÇÕ´Ï´Ù.
±×·¸Áö ¾ÊÀ¸¸é, »öÀÎÀÌ ¹«½ÃµË´Ï´Ù. EXPLAIN °á°ú¸¦ °ËÅäÇÏ¿©, »ç¿ëÀÚ°¡ Á¦¾ÈÇÑ
»öÀÎÀÌ SQL ÄÄÆÄÀÏ·¯¿¡ ÀÇÇØ »ç¿ëµÇ¾úÀ½À» ¾Ë ¼ö ÀÖ½À´Ï´Ù.
»ç¿ëµÈ »öÀÎÀº ¾×¼¼½º °³¼±À» À§ÇØ ±¸ÇöµÉ °ÍÀ¸·Î °£ÁֵǾî¾ß ÇÕ´Ï´Ù.
- EXPLSNAP ALL ¿É¼ÇÀÌ BIND³ª PREP ¸í·É¿¡ ÁöÁ¤µÇ¾î ÀÖ½À´Ï´Ù. ÀÌ
¼³Á¤Àº CURRENT EXPLAIN MODE Ư¼ö ·¹Áö½ºÅÍÀÇ ¼³Á¤°ªÀÌ NOÀÌ´õ¶óµµ,
SQL ÄÄÆÄÀÏ·¯°¡ ·±Å¸ÀÓ µ¿Àû SQLÀÇ Explain µ¥ÀÌÅ͸¦ ĸÃÄÇϵµ·Ï
ÇÕ´Ï´Ù. ¶ÇÇÑ, SQL¹®µµ ½ÇÇàµÇ¾î Á¶È¸ °á°ú¸¦ ¸®ÅÏÇÕ´Ï´Ù.
ÁÖ: | Explain Á¤º¸´Â SQL¹®ÀÌ ÄÄÆÄÀ쵃 ¶§¿¡¸¸ ĸÃĵ˴ϴÙ. Ãʱâ ÄÄÆÄÀÏÀ»
µû¶ó µ¿Àû SQL¹®Àº ȯ°æ¿¡ ´ëÇÑ º¯°æÀÌ ÀçÄÄÆÄÀÏÀ» ¿ä±¸ÇÒ ¶§¿¡¸¸
ÀçÄÄÆÄÀϵ˴ϴÙ. µ¿ÀÏÇÑ PREPARE¹®ÀÌ µ¿ÀÏÇÑ SQL¹®¿¡ ´ëÇØ ¿¬¼ÓÀûÀ¸·Î
¹ßÇàµÉ ¶§¿¡´Â ȯ°æÀÌ º¯°æµÇÁö ¾Ê¾Ò´Ù°í °¡Á¤Çϰí, óÀ½ PREPARE¹®ÀÌ
¹ßÇàµÇ¾úÀ» ¶§ SQL¹®Àº ÄÄÆÄÀϵDZ⸸ Çϰí Explain µ¥ÀÌÅʹ ĸÃĵ˴ϴÙ.
|
EXPLAIN SQL¹® ¶Ç´Â CURRENT EXPLAIN MODE ·¹Áö½ºÅÍ »ç¿ë¿¡ ´ëÇØ¼´Â
SQL ÂüÁ¶¼ ¸Å´º¾óÀ» ÂüÁ¶ÇϽʽÿÀ. BIND ¹× PREP ¸í·É¿¡
´ëÇØ¼´Â Command Reference ¸Å´º¾óÀ» ÂüÁ¶ÇϽʽÿÀ.
Explain ½º³À¼¦ µ¥ÀÌÅÍ´Â SQL¹®ÀÌ ÄÄÆÄÀϵǰí Explain µ¥ÀÌÅͰ¡ ¿äûµÇ¸é
ĸÃĵ˴ϴÙ.
- Á¤Àû ¶Ç´Â ÁõºÐ½Ä ¹ÙÀεå
SQL¹®ÀÇ °æ¿ì, Explain ½º³À¼¦Àº
EXPLSNAP ALL ¶Ç´Â EXPLSNAP YESÀýÀÌ BIND ¶Ç´Â PREP ¸í·É¿¡
ÁöÁ¤µÇ°Å³ª, ¼Ò½º ÇÁ·Î±×·¥¿¡ FOR SNAPSHOT ¶Ç´Â WITH SNAPSHOTÀýÀ»
»ç¿ëÇÏ´Â Á¤Àû EXPLAIN SQL¹®ÀÌ »ç¿ëµÉ °æ¿ì¿¡ ĸÃĵ˴ϴÙ.
ÁÖ: | ÁõºÐ½Ä ¹ÙÀεå SQL¹®ÀÌ ·±Å¸ÀÓ ½Ã ÄÄÆÄÀ쵃 °æ¿ì,
±× SQL¹®Àº ·±Å¸ÀÓ ½Ã Explain Å×ÀÌºí¿¡ ³õÀÌ¸ç ¹ÙÀÎµå ½Ã¿¡´Â ³õÀÌÁö ¾Ê½À´Ï´Ù.
¶ÇÇÑ, Explain Å×ÀÌºí¿¡ »ðÀÔÇϱâ À§ÇØ »ç¿ëµÇ´Â Explain Å×ÀÌºí ±ÔÁ¤ÀÚ¿Í ±ÇÇÑ ºÎ¿© ID´Â
ÆÐŰÁö¸¦ ¼öÇàÇÒ ¶§ »ç¿ëÇÏ´Â °ÍÀÌ ¾Æ´Ï¶ó ÆÐŰÁö ¼ÒÀ¯ÀÚÀÇ °ÍÀÔ´Ï´Ù.
|
- µ¿Àû SQL¹®¿¡ ´ëÇØ Explain ½º³À¼¦Àº
´ÙÀ½ »óȲ¿¡ ÀÇÇØ ĸÃĵ˴ϴÙ.
- FOR SNAPSHOT ¶Ç´Â WITH SNAPSHOTÀýÀ» »ç¿ëÇÏ´Â EXPLAIN SQL¹®.
FOR SNAPSHOTÀý¿¡´Â Explain ½º³À¼¦°ú ¿¬°üµÈ Á¤º¸¸¦
Á¦¿ÜÇϸé ĸÃÄµÈ Explain Å×À̺í Á¤º¸°¡ ¾ø½À´Ï´Ù. WITH SNAPSHOTÀý¿¡´Â Explain ½º³À¼¦°ú
¿¬°üµÈ Á¤º¸¸¦ ºñ·ÔÇÏ¿© ĸÃÄµÈ ¸ðµç Explain Å×À̺í Á¤º¸°¡ ÀÖ½À´Ï´Ù.
´ÙÀ½Àº EXPLAIN SQL¹®À» »ç¿ëÇÏ´Â Explain ½º³À¼¦ÀÇ ¿¹ÀÔ´Ï´Ù.
EXPLAIN PLAN FOR SNAPSHOT FOR <any valid DELETE, INSERT, SELECT,
SELECT INTO, UPDATE, VALUES, or VALUES INTO SQL statement>
Explain ½º³À¼¦¸¸ ÂïÈ÷°í ĸÃÄµÈ Á¤º¸´Â
EXPLAIN_INSTANCE ¹× EXPLAIN_STATEMENT Å×ÀÌºí¿¡ º¸°üµË´Ï´Ù.
- CURRENT EXPLAIN SNAPSHOT Ư¼ö ·¹Áö½ºÅͰ¡ YES·Î ¼³Á¤µË´Ï´Ù.
ÀÌ ¼³Á¤°ªÀº SQL ÄÄÆÄÀÏ·¯°¡ Explain µ¥ÀÌÅÍÀÇ ½º³À¼¦À» Âïµµ·Ï Çϰí SQL¹®ÀÌ
½ÇÇàµÇ¾î Á¶È¸ÀÇ °á°ú°¡ ¸®Åϵǵµ·Ï ÇÕ´Ï´Ù.
- CURRENT EXPLAIN SNAPSHOT Ư¼ö ·¹Áö½ºÅͰ¡ EXPLAINÀ¸·Î
¼³Á¤µË´Ï´Ù. ÀÌ ¼³Á¤°ªÀ¸·Î SQL ÄÄÆÄÀÏ·¯´Â Explain µ¥ÀÌÅÍÀÇ ½º³À¼¦À» ÂïÁö¸¸
SQL¹®À» ½ÇÇàÇÏÁö´Â ¾Ê½À´Ï´Ù.
- EXPLSNAP ALL ¿É¼ÇÀÌ BIND³ª PREP ¸í·É¿¡ ÁöÁ¤µÇ¾î ÀÖ½À´Ï´Ù. ÀÌ
¼³Á¤°ªÀº CURRENT EXPLAIN SNAPSHOT Ư¼ö ·¹Áö½ºÅÍÀÇ
¼³Á¤°ªÀÌ NOÀÌ´õ¶óµµ, SQL ÄÄÆÄÀÏ·¯°¡ ·±Å¸ÀÓ Explain µ¥ÀÌÅÍÀÇ
½º³À¼¦À» ĸÃÄÇϵµ·Ï ÇÕ´Ï´Ù. ¶ÇÇÑ, SQL¹®µµ ½ÇÇàµÇ¾î
Á¶È¸ °á°ú¸¦ ³»º¸³À´Ï´Ù.
ÁÖ: | Explain Á¤º¸´Â SQL¹®ÀÌ ÄÄÆÄÀ쵃 ¶§¿¡¸¸ ĸÃĵ˴ϴÙ. Ãʱâ ÄÄÆÄÀÏÀ»
µû¶ó µ¿Àû SQL¹®Àº ȯ°æ¿¡ ´ëÇÑ º¯°æÀÌ ÀçÄÄÆÄÀÏÀ» ¿ä±¸ÇÒ ¶§¿¡¸¸
ÀçÄÄÆÄÀϵ˴ϴÙ. µ¿ÀÏÇÑ PREPARE¹®ÀÌ µ¿ÀÏÇÑ SQL¹®¿¡ ´ëÇØ ¿¬¼ÓÀûÀ¸·Î
¹ßÇàµÉ ¶§¿¡´Â ȯ°æÀÌ º¯°æµÇÁö ¾Ê¾Ò´Ù°í °¡Á¤Çϰí, óÀ½ PREPARE¹®ÀÌ
¹ßÇàµÇ¾úÀ» ¶§ SQL¹®Àº ÄÄÆÄÀϵDZ⸸ Çϰí Explain µ¥ÀÌÅʹ ĸÃĵ˴ϴÙ.
|
EXPLAIN SQL¹® ¹× FOR SNAPSHOT ¶Ç´Â WITH SNAPSHOTÀý »ç¿ë¿¡ ´ëÇØ¼´Â
SQL ÂüÁ¶¼ ¸Å´º¾óÀ» ÂüÁ¶ÇϽʽÿÀ. BIND ¹× PREP ¸í·É¿¡
´ëÇØ¼´Â Command Reference ¸Å´º¾óÀ» ÂüÁ¶ÇϽʽÿÀ.
[ ÆäÀÌÁöÀÇ ¸Ç À§ | ÀÌÀü ÆäÀÌÁö | ´ÙÀ½ ÆäÀÌÁö | ¸ñÂ÷ | »öÀÎ ]