¿ä¾à Å×À̺íÀº Á¶È¸ ÀÀ´ä ½Ã°£À» °³¼±Çϱâ À§ÇÑ °·ÂÇÑ ¹æ¹ýÀÔ´Ï´Ù. ÀϺΠ±âº» Á¶È¸ ±¸Á¶°¡ Âü¿©ÇÒ ¼ö ÀÖ´Â ¸¹Àº ȯ°æ¿¡¼ ¿ä¾à Å×À̺íÀ» »ç¿ëÇÏ¿© ´ÙÀ½À» ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.
¿ä¾à Å×ÀÌºí¿¡ ´ëÇÑ Áö½ÄÀº SQL ÄÄÆÄÀÏ·¯¿¡ ÅëÇյ˴ϴÙ. SQL ÄÄÆÄÀÏ·¯ ³»¿¡¼, Á¶È¸ Àç±â·Ï(SQL ÄÄÆÄÀÏ·¯¿¡ ÀÇÇÑ Á¶È¸ ÀçÀÛ¼º ÂüÁ¶) ¹× ÃÖÀûÈ ¾Ë°í¸®Áò(µ¥ÀÌÅÍ ¾×¼¼½º °³³ä ¹× ÃÖÀûÈ ÂüÁ¶)Àº Á¶È¸¸¦ ¿ä¾à Å×À̺í°ú ´ëÁ¶ÇÏ¿© ±âº» Å×ÀÌºí¿¡¼ Á¶È¸¿ë ¿ä¾à Å×À̺íÀ» ´ëüÇÒ °ÍÀÎ Áö¸¦ ÆÇº°ÇÒ ¶§ °ü·ÃµË´Ï´Ù. Á¶È¸¿¡ ÀÀ´äÇϱâ À§ÇØ ¿ä¾à Å×À̺íÀÌ »ç¿ëµÉ ¶§¸¶´Ù EXPLAIN ±â´É(SQL Explain ±â´É ÂüÁ¶)À» »ç¿ëÇÏ¿© ¼±ÅÃµÈ ¿ä¾à Å×À̺íÀ» ÆÇº°ÇÒ ¼ö ÀÖ½À´Ï´Ù. ¿ä¾à Å×À̺íÀº ¸¹Àº ¹æ½Ä¿¡¼ ÀÏ¹Ý Å×À̺í°ú °°ÀÌ ÀÛµ¿ÇϹǷÎ, Å×ÀÌºí °ø°£ Á¤ÀǸ¦ »ç¿ëÇÑ µ¥ÀÌÅÍ ¾×¼¼½º ÃÖÀûÈ, »öÀÎ ÀÛ¼º ¹× RUNSTATS ¹ßÇà¿¡ ´ëÇÑ µ¿ÀÏÇÑ °í·Á»çÇ×ÀÌ ¿ä¾à Å×ÀÌºí¿¡ Àû¿ëµË´Ï´Ù.
¿ä¾à Å×À̺íÀÇ °·ÂÇÔÀ» ½±°Ô ÀÌÇØÇÒ ¼ö ÀÖµµ·Ï Çϱâ À§ÇØ, ´ÙÀ½°ú °°Àº ´ÙÂ÷¿ø ºÐ¼® Á¶È¸ ¿¹¸¦ Á¦°øÇϰí ÀÌ ¶§ ¿ä¾à Å×À̺íÀ» ÀÌ¿ëÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù.
ÀÌ ¿¹¿¡¼´Â ¿þ¾îÇϿ콺¿¡ ÀÏ·ÃÀÇ °í°´µé°ú ÀÏ·ÃÀÇ ½Å¿ë Ä«µå °èÁ¤ÀÌ ÀÖ´Â ½Ã³ª¸®¿À¸¦ °¡Á¤ÇÕ´Ï´Ù. ¿þ¾îÇϿ콺´Â ½Å¿ë Ä«µå·Î ÀÌ·ç¾îÁö´Â ÀÏ·ÃÀÇ °Å·¡¸¦ ±â·ÏÇÕ´Ï´Ù. °¢ °Å·¡¿¡´Â ÇÔ²² ±¸ÀԵǴ ǰ¸ñ ¼¼Æ®°¡ Æ÷ÇԵ˴ϴÙ. ÀÌ È¯°æÀ» ¸ÖƼ ½ºÅ¸·Î ¹üÁÖÈÇÒ ¼ö Àִµ¥, ÀÌ´Â °Å·¡ ǰ¸ñÀ» Æ÷ÇÔÇÏ´Â ÇϳªÀÇ Å×À̺í°ú ±¸ÀÔ °Å·¡¸¦ ½Äº°ÇÏ´Â ´Ù¸¥ Å×À̺íÀÌ Å©°í ÇÔ²² ½ºÅ¸ÀÇ ÇãºêÀ̱⠶§¹®ÀÔ´Ï´Ù.
°Å·¡¸¦ ¼³¸íÇÏ´Â ¼¼ °³ÀÇ °èÃþÀû Â÷¿øÀÎ »óǰ, À§Ä¡ ¹× ½Ã°£ÀÌ ÀÖ½À´Ï´Ù. »óǰ °èÃþ±¸Á¶´Â »óǰ ±×·ì°ú »óǰ±ºÀ» ³ªÅ¸³»´Â µÎ °³ÀÇ Ç¥ÁØÈµÈ Å×ÀÌºí¿¡ ±â·ÏµË´Ï´Ù. À§Ä¡ °èÃþ±¸Á¶¿¡´Â ½Ã, µµ, ±¹°¡ Á¤º¸°¡ Æ÷ÇԵǰí, ÀÌ´Â ÇϳªÀÇ Ç¥ÁØÈµÇÁö ¾ÊÀº Å×À̺í·Î Ç¥½ÃµË´Ï´Ù. ½Ã°£ °èÃþ±¸Á¶¿¡´Â ÀÏ, ¿ù, ¿¬µµ Á¤º¸°¡ Æ÷ÇԵǰí ÇϳªÀÇ ³¯Â¥ Çʵå·Î ÄÚµùµË´Ï´Ù. ³¯Â¥ Â÷¿øÀº ³»Àå ÇÔ¼ö¸¦ »ç¿ëÇÏ¿© Æ®·£Àè¼ÇÀÇ ³¯Â¥ Çʵ忡¼ ÃßÃâµË´Ï´Ù. °í°´ ¹× °í°´ Á¤º¸¿¡ ´ëÇÑ °èÁ¤ Á¤º¸¸¦ ³ªÅ¸³»´Â ´Ù¸¥ Å×ÀÌºíµµ ÀÌ ½Ã³ª¸®¿À¿¡ ÀÖ½À´Ï´Ù.
¿ä¾à Å×À̺íÀº ´ÙÀ½ÀÇ °¢ ·¹º§¿¡ ´ëÇÑ ÆÇ¸Å ÇÕ°è¿Í ÆÇ¸Å ¼ö·Î ÀÛ¼ºµË´Ï´Ù.
±¤¹üÀ§ÇÑ Á¶È¸¿¡¼ ÀÌ ÀúÀåµÈ ÃÑ°è µ¥ÀÌÅͷκÎÅÍÀÇ ÀÀ´äÀ» ¼±Á¤ÇÒ ¼ö ÀÖ½À´Ï´Ù. ´ÙÀ½ ¿¹¿¡¼´Â »óǰ ±×·ì ¹× »óǰ±º Â÷¿øÀ» µû¶ó, ½Ã, µµ, ±¹°¡ Â÷¿øÀ» µû¶ó, ±×¸®°í ½Ã°£ Â÷¿øÀ» µû¶ó ÇÕ°è¿Í ÆÇ¸Å Ƚ¼ö¸¦ °è»êÇÕ´Ï´Ù. GROUP BYÀý¿¡ ¸î °³ÀÇ ´Ù¸¥ Ä÷³µµ Æ÷ÇԵ˴ϴÙ.
CREATE TABLE dba.PG_SALESSUM AS ( SELECT l.id AS prodline, pg.id AS pgroup, loc.country, loc.state, loc.city, l.name AS linename, pg.name AS pgname, YEAR(pdate) AS year, MONTH(pdate) AS month, t.status, SUM(ti.amount) AS amount, COUNT(*) AS count FROM cube.transitem AS ti, cube.trans AS t, cube.loc AS loc, cube.pgroup AS pg, cube.prodline AS l WHERE ti.transid = t.id AND ti.pgid = pg.id AND pg.lineid = l.id AND t.locid = loc.id AND YEAR(pdate) > 1990 GROUP BY l.id, pg.id, loc.country, loc.state, loc.city, year(pdate), month(pdate), t.status, l.name, pg.name ) DATA INITIALLY DEFERRED REFRESH DEFERRED; REFRESH TABLE dba.SALESCUBE;
¿ä¾à Å×À̺íÀº º¸Åë ±âº» »ç½Ç Å×ÀÌºíº¸´Ù ´õ Àû½À´Ï´Ù. ¿¹¿¡ ³ª¿Í ÀÖ´Â °Íó·³, DEFERRED ¿É¼ÇÀ» ÁöÁ¤ÇÏ¿© ¿ä¾à Å×À̺íÀ» »õ·Î °íÄ¥ ½Ã±â¸¦ Á¦¾îÇÒ ¼ö ÀÖ½À´Ï´Ù.
ÀÌ·¯ÇÑ »çÀü¿¡ °è»êµÈ Çհ踦 ÀÌ¿ëÇÒ ¼ö ÀÖ´Â Á¶È¸´Â ´ÙÀ½°ú °°½À´Ï´Ù.
Á¤È®ÇÑ ÀÀ´äÀÌ ÀÌ·¯ÇÑ Á¶È¸ Áß Çϳª¿¡ ´ëÇÑ ¿ä¾à Å×ÀÌºí¿¡ Æ÷ÇÔµÇÁö ¾Ê´Â ¹Ý¸é, ¿ä¾à Å×À̺íÀ» »ç¿ëÇÏ´Â ÀÀ´äÀ» °è»êÇÏ´Â ºñ¿ëÀÌ, ÀÀ´äÀÇ ºÎºÐÀÌ ÀÌ¹Ì °è»êµÇ¾î ÀÖÀ¸¹Ç·Î, Å« ±âº» Å×À̺íÀ» »ç¿ëÇÏ´Â °Íº¸´Ù ´ú ¼ÒºñµË´Ï´Ù. ºñ¿ëÀÌ ¸¹ÀÌ µå´Â ±âº» µ¥ÀÌÅÍÀÇ Á¶ÀÎ, Á¤·Ä ¹× Áý°è´Â ¿ä¾à Å×À̺íÀ» ÅëÇØ ÇÇÇÒ ¼ö Àְųª °¨¼ÒµË´Ï´Ù.
´ÙÀ½Àº Áß¿äÇÑ ¼º´É Çâ»óÀ» °¡Á®¿À´Â »ùÇà Á¶È¸ÀÔ´Ï´Ù. À̵é Á¶È¸´Â ÀÌ¹Ì °è»êµÈ ¿ä¾à Å×À̺íÀÇ °á°ú¸¦ »ç¿ëÇÒ ¼ö Àֱ⠶§¹®ÀÔ´Ï´Ù. ù¹øÂ° ¿¹¿¡¼´Â 1995³â°ú 1996³âÀÇ ÃÑ ÆÇ¸Å¾×À» ¸®ÅÏÇÕ´Ï´Ù.
SET CURRENT REFRESH AGE=ANY SELECT YEAR(pdate) AS year, SUM(ti.amount) AS amount FROM cube.transitem AS ti, cube.trans AS t, cube.loc AS loc, cube.pgroup AS pg, cube.prodline AS l WHERE ti.transid = t.id AND ti.pgid = pg.id AND pg.lineid = l.id AND t.locid = loc.id AND YEAR(pdate) IN (1995, 1996) GROUP BY year(pdate);
µÎ¹øÂ° ¿¹¿¡¼´Â 1995³â°ú 1996³âÀÇ Á¦Ç° ±×·ìº° ÃÑ ÆÇ¸Å¾×À» ¸®ÅÏÇÕ´Ï´Ù.
SET CURRENT REFRESH AGE=ANY SELECT pg.id AS "PRODUCT GROUP", SUM(ti.amount) AS amount FROM cube.transitem AS ti, cube.trans AS t, cube.loc AS loc, cube.pgroup AS pg, cube.prodline AS l WHERE ti.transid = t.id AND ti.pgid = pg.id AND pg.lineid = l.id AND t.locid = loc.id AND YEAR(pdate) IN (1995, 1996) GROUP BY pg.id;
±×·¯ÇÑ Á¶È¸¿¡ ´ëÇÑ ÀÀ´ä ½Ã°£À» ´õ °³¼±ÇÏ¸é ´õ ¸¹Àº µ¥ÀÌÅͺ£À̽º¿¡¼ ¾ÆÄ«À̺êµÉ ¼ö ÀÖ½À´Ï´Ù. ÀÌ´Â ±âº» Å×À̺íÀÇ Áõ°¡º¸´Ù ¿ä¾à Å×À̺íÀÇ Áõ°¡°¡ ´õ ´À¸®±â ¶§¹®¿¡ ¹ß»ýÇÕ´Ï´Ù. ¿ä¾à Å×À̺íÀÇ ÀÌÁ¡ Áß Çϳª´Â ¿ä¾à Å×À̺íÀ» ±¸ÃàÇÏ°í ¾ÆÁÖ ¸¹Àº Á¶È¸¿¡ ´ëÇØ ±× ³»¿ëÀ» Àç»ç¿ëÇÒ ¶§ Çѹø °è»êÀ» ¼öÇàÇÏ¿© DB2 Universal Database°¡ ÀÌ Å×À̺íÀ» ÅëÇØ Á¶È¸ »çÀÌÀÇ ÁßøµÇ´Â ÀÛ¾÷À» È¿À²ÀûÀ¸·Î ÇÇÇÏ´Â °ÍÀÔ´Ï´Ù.