JAKARTAPROJECT
JAKARTA TIPJSP TIPJSP Áú¹®&´äº¯DATABASE TIPJAVASCRIPT TIPWEBHACKING TIP±âŸ TIP
µ¥ÀÌÅͺ£À̽º ÆÁ
µ¥ÀÌÅͺ£À̽º ÆÁ
µ¥ÀÌÅͺ£À̽º ÆÁ °Ô½ÃÆÇ ÀÔ´Ï´Ù
ºÐÇÒ ±â´É
GoodBug
À̹ÌÁö ½½¶óÀÌ´õ º¸±â

from http://www.databaser.net/oracle/partition_ability.TXT

¾ÆÁÖ ½±°Ô ÀÚ¼¼È÷ ¼³¸íµÇ¾î ÀÖ½À´Ï´Ù ¸¹Àº µµ¿òÀÌ µË´Ï´Ù

----------------------------------------------------------------------


========
ºÐÇÒ±â´É
========

¿À¶óŬ 8 ¹öÀüÀ̻󿡼­´Â ºÐÇÒ±â´ÉÀ» Á¦°øÇÕ´Ï´Ù..
ºÐÇÒ±â´ÉÀº Å×ÀÌºí ¶Ç´Â À妽º¸¦ ¿©·¯°³ÀÇ Å×ÀÌºí½ºÆäÀ̽º¿¡ ³ª´©¾î ÀúÀåÇÏ´Â ±â´ÉÀÔ´Ï´Ù..
ºÐÇÒÀÇ ÀåÁ¡Àº ´ç±Ù ¼º´É¿¡ ÀÖ½À´Ï´Ù..
¿©·¯ Å×ÀÌºí½ºÆäÀ̽º·Î ºÐÇÒÇÏ¿© ÀúÀåÇÏ°Ô µÇ¸é ±×¸¸Å­ ó¸®ÀÇ ¹üÀ§°¡ ÁÙ¾î µé±â ¶§¹®¿¡
¼º´ÉÀÌ ¿Ã¶ó°¡´Â °ÍÀº ´ç¿¬ÇÕ´Ï´Ù.
Å×ÀÌºí½ºÆäÀ̽º¿¡ ¼ÓÇÑ µ¥ÀÌŸ ÆÄÀϵéÀÌ ¿©·¯ µð½ºÅ©·Î ºÐ»êµÇ¾î ÀÖ´Ù¸é ´õÇÒ³ªÀ§ ¾øÀÌ ÁÁ°ÚÁö¿©..


Ã¥ 1000ÆäÀÌÁö Â¥¸®¿¡¼­ A¶ó´Â °ÍÀ» ã´Â °Í°ú 1000ÆäÀÌÁöÁß 500ÆäÀÌÁö·Î ¹Ý ³ª´©¾î¼­ 1~500ÆäÀÌÁöÁß
A¸¦ ã¾Æ¶ó ÇÏ´Â °ÍÀº ³î¶ó¿î È¿°ú¸¦ °¡ÁöÁö¿©...
¹°·Ð A¶ó´Â °ÍÀº 1ÆäÀÌÁö¿¡ ÀÖ´Ù¸é 1000ÆäÀÌÁö³ª 500ÆäÀÌÁö³ª °°°ÚÁö¸¸...
ÀÌ°ÍÀº ÀϺÎÀÇ °æ¿ìÁö¿©..
¾ÏÆ°..ó¸® ´ë»óÀÇ ¹üÀ§¸¦ ÁÙ¿©Áشٴ °ÍÀº ¾ÆÁÖ Áß¿äÇÑ ¸ÞÄ¿´ÏÁòÀÌÁö¿©...
Àú´Â ÷¿¡ ºÐÇÒ±â´ÉÀ» Á¢ÇÏ°í ¾öû³­ °¨µ¿À» ¸Ô¾ú½¿´Ù....¾ÆÁÖ ¸ÚÁø ±â´ÉÀÌÁö¿©...

ºÎ°¡ÀûÀ¸·Î ºÐÇÒÀ» ÇÏ°ÔµÇ¸é ¹®Á¦ÀÇ ¹ß»ý¿¡ ´ëºñÇؼ­µµ ÁÁ½À´Ï´Ù..
¿©·¯ Å×ÀÌºí½ºÆäÀ̽º¿¡ ºÐÇÒ ÇÏ¿© ÀúÀåÇÏ¿´´Âµ¥ ƯÁ¤ Å×ÀÌºí½ºÆäÀ̽º°¡ ¹®Á¦°¡ »ý±â¸é
¹®Á¦°¡ ¹ß»ýÇÑ Å×ÀÌºí½ºÆäÀ̽ºÀÇ Á¤º¸¸¸ »ç¿ëÇÒ ¼ö ¾ø°í ³ª¸ÓÁö´Â »ç¿ëÇÒ ¼ö ÀÖ´Ù´Â °ÍÀÔ´Ï´Ù..

 

¶ÇÇÑ ³ª´©¾îÁ® Àֱ⶧¹®¿¡ °ü¸®ÀÚ°¡ °ü¸®Çϱ⵵ ´Ù¼Ò ½±°ÚÁö¿©..
ºÐÇÒÀº ÃÖ´ë 64000°³ ±îÁö ºÐÇÒ ÇÒ ¼ö ÀÖÀ¸¸ç, ºÐÇÒÀÌ ¾È? Å×ÀÌºí¿¡ ´ëÇؼ­ À妽º¸¦ »ý¼ºÇÒ ¶§
À妽º¸¸ µû·Î ºÐÇÒ ÇÒ ¼ö ÀÖ½À´Ï´Ù...´Ü, ºÐÇÒÀÌ ¾È? Å×À̺íÀÇ ºñÆ®¸Ê À妽º´Â ºÐÇÒÇÒ ¼ö ¾ø½À´Ï´Ù..


Å×À̺íÀÌ Ä¿Áö¸é À妽ºÀÇ Å©±âµµ Ä¿Áö±â ¶§¹®¿¡ ºÐÇÒÀº ÀåÁ¡Àº ³Ê¹«µµ ¸¹½À´Ï´Ù...

ºÐÇÒÀº Å©°Ô ´Ü¼øºÐÇÒ°ú º¹ÇÕºÐÇÒ·Î ³ª´¹´Ï´Ù..
´Ü¼øºÐÇÒÀº ´Ù½Ã ¹üÀ§ºÐÇÒ°ú Çؽ¬ºÐÇÒ·Î ³ª´¹´Ï´Ù..

¸ÕÀú ´Ü¼øºÐÇÒºÎÅÍ ¾Ë¾Æº¸µµ·Ï ÇÏ°Ú½À´Ï´Ù...¿ª½¬ ¹®¹ýÀº ¸Þ´º¾óÀ»..^^;

 

 

========
´Ü¼øºÐÇÒ
========

¹Ù·Î ½Ç½À¿¡ µé¾î°¡µµ·Ï ÇÏÁö¿©..
¸ÕÀú 5°³ÀÇ ºÐÇÒÀ» Çϵµ·Ï °áÁ¤À» ÇÏ°í.... °¢°¢ÀÇ ºÐÇÒ¿¡ µé¾î°¥ Å×ÀÌºí½ºÆäÀ̽º¸¦ »ý¼ºÇÕ´Ï´Ù..
¹°·Ð 5°³·Î ºÐÇÒÇϱâ·Î Çß´Ù¸é ¾î¶² ±âÁØÀÌ Á¤Çß°ÚÁö¿©..

 

SQL> connect system/manager
¿¬°áµÇ¾ú½À´Ï´Ù.

SQL> create tablespace data_199901
  2  datafile 'K:\oracle_tablespace\data_199901.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create tablespace data_199902
  2  datafile 'K:\oracle_tablespace\data_199902.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create tablespace data_199903
  2  datafile 'K:\oracle_tablespace\data_199903.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create tablespace data_199904
  2  datafile 'K:\oracle_tablespace\data_199904.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create tablespace data_maxvalues                                -------------> ±âÁØ¿¡ ¸ÂÁö ¾Ê´Â °ÍÀ» ¸ðµÎ ÀÌ Å×ÀÌºí½ºÆäÀ̽º¿¡ ¶§·Á ³Ö´Â´Ù..
  2  datafile 'K:\oracle_tablespace\data_maxvalues.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create table psales(
  2  year char(4),
  3  mon char(2),
  4  dd char(2),
  5  empno char(3),
  6  count number(5),
  7  amount number(8))
  8  partition by range (year, mon)                  ---------------> ¿©±â¿¡¼­ ºÐÇÒ ±âÁØÀÌ µÇ´Â Ä÷³À» ¸í½ÃÇØ ÁØ´Ù.
  9  (partition p1 values less than ('1999', '01')
 10  tablespace data_199902,
 11  partition p2 values less than ('1999', '02')
 12  tablespace data_199903,
 13  partition p3 values less than ('1999', '03')
 14  tablespace data_199904,
 15  partition p4 values less than ('1999','04')
 16  tablespace data_199905,
 17  partition p5 values less than (maxvalue, maxvalue)       ------------> ºÐÇÒ ±âÁØ¿¡¼­ ¹þ¾î³ª´Â °ÍÀº ÀÌ Å×ÀÌºí½ºÆäÀ̽º¿¡ ÀúÀåÇÑ´Ù...¿¡·¯¹æÁö..
 18  tablespace data_maxvalues);

Å×À̺íÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> insert into psales values ('1999', '01', '21', '111', 1111, 12345678);

1 °³ÀÇ ÇàÀÌ ¸¸µé¾îÁ³½À´Ï´Ù.

 

SQL> insert into psales values ('1999', '02', '1', '222', 22222, 12345678);

1 °³ÀÇ ÇàÀÌ ¸¸µé¾îÁ³½À´Ï´Ù.

 

SQL> insert into psales values ('1999', '03', '30', '333', 33333, 12345678);

1 °³ÀÇ ÇàÀÌ ¸¸µé¾îÁ³½À´Ï´Ù.

 

SQL> insert into psales values ('1999', '04', '22', '444', 44444, 12345678);

1 °³ÀÇ ÇàÀÌ ¸¸µé¾îÁ³½À´Ï´Ù.

 

SQL> insert into psales values ('1999', '12', '31', '012', 01212, 45678901);

1 °³ÀÇ ÇàÀÌ ¸¸µé¾îÁ³½À´Ï´Ù.

 

SQL> select table_name, partition_name, partition_position, tablespace_name          -----------> »ý¼ºÇÑ ¹üÀ§ ºÐÇÒ¿¡ ´ëÇÑ µ¥ÀÌŸ »çÀü °Ë»ö...
  2  from user_tab_partitions
  3  where table_name = 'PSALES';

TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ -------------------
PSALES                         P1                                              1 DATA_199901
PSALES                         P2                                              2 DATA_199902
PSALES                         P3                                              3 DATA_199903
PSALES                         P4                                              4 DATA_199904
PSALES                         P5                                              5 DATA_MAXVALUES


ºÐÇÒ¿¡¼­ 8.x ¹öÀü¿¡¼­´Â °»½ÅÇÒ¶§ ºÐÇÒÅ° Ä÷³ÀÇ µ¥ÀÌŸ¸¦ º¯°æÇÒ ¼ö ¾ø½À´Ï´Ù..
±×·¯³ª 8i ¹öÀü¿¡´Â º¯°æÀÌ °¡´ÉÇÕ´Ï´Ù....µ¥ÀÌŸ¸¦ º¯°æÇÒ¶§´Â alter table ¹®À» ¸ÕÀú ¼öÇàÇØ¾ß ÇÕ´Ï´Ù..
´ÙÀ½Àº ±× ¿¹ÀÔ´Ï´Ù..


SQL> alter table psales enable row movement;         ---------------> ºÐÇÒÅ°¿¡ ´ëÇÑ µ¥ÀÌŸ¸¦ º¯°æÇÒ ¼ö ÀÖ°Ô Å×À̺íÀ» º¯°æÇÑ´Ù.

Å×À̺íÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL>  update psales
  2   set year = '2001'
  3  where empno = '012';

1 ÇàÀÌ °»½ÅµÇ¾ú½À´Ï´Ù.

 

SQL> select * from psales;

YEAR MO DD EMP      COUNT     AMOUNT
---- -- -- --- ---------- ----------
1999 01 21 111       1111   12345678
1999 02 1  222      22222   12345678
1999 03 30 333      33333   12345678
1999 04 22 444      44444   12345678
2001 12 31 012       1212   45678901       ------------> ºÐÇÒÅ°°¡ '1999'¿¡¼­ '2001'·Î º¯°æµÇ¾ú´Ù..

 

SQL> create table gsales
  2  (no number(5),
  3  person varchar2(30),
  4  amount number(8),
  5  week number(2))
  6  partition by range (week)
  7  (partition p1 values less than (4),
  8   partition p2 values less than (8),
  9   partition p3 values less than (12));

Å×À̺íÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create index gsales_gidx on gsales (week, no)
  2  global
  3  partition by range (week)
  4  (partition g_p1 values less than (8),
  5   partition g_p2 values less than (maxvalue));

À妽º°¡ »ý¼ºµÇ¾ú½À´Ï´Ù.


´ÙÀ½Àº ¹üÀ§ºÐÇÒÀ» »ç¿ëÇÒ¶§ °í·ÁÇØ¾ß µÉ »çÇ×µéÀÓµÂ..

- ºÐÇÒÅ°´Â ÃÖ´ë 16°³±îÁö
- long, long raw µ¥ÀÌŸ ŸÀÔ¿¡´Â ºÐÇÒ±â´ÉÀ» »ç¿ëÇÒ ¼ö ¾ø´Ù.
- values less than Àý¿¡ Á¤ÀÇµÈ ºÐÇÒ ±âÁØ°ªÀº Á¤ÀÇµÈ °ªº¸´Ù ÀûÀº °ªÀ» ÀúÀåÇÏ°Ô µÈ´Ù.
- ºÐÇÒ ±âÁØ Ä÷³ÀÇ ³Î°ªÀº values less than Àý¿¡  maxvalue¿¡ ÇØ´çµÈ´Ù.

 


========
Çؽ¬ºÐÇÒ
========

´Ü¼øºÐÇÒÀÇ µÎ¹ø°ÀÎ Çؽ¬ºÐÇÒÀÔ´Ï´Ù...Çؽ¬ºÐÇÒÀº ¿À¶óŬÀÇ Çؽ¬¾Ë°í¸®Áò¿¡ ÀÇÇؼ­ ¿À¶óŬ¿¡¼­
ÀÚµ¿ÀûÀ¸·Î ³ª´©¾îÁö°Ô ÇÏ´Â ¹æ¹ýÀÔ´Ï´Ù..
¹°·Ð ¿À¶óŬÀÌ ÆÇ´ÜÇϱ⿡ ÃÖÀûÀ̶ó°í ÆǴܵǴ ºÐÇÒÀ» ÇϰԵ˴ϴÙ..
±×°ÍÀÌ ½ÇÁ¦·Î ÃÖÀûÀÌ µÉÁö´Â ¸ð¸£´Â °ÍÀÔ´Ï´Ù..ÀÌ°ÍÀº °ü¸®ÀÚ°¡ ¼º´É¿¡ ´ëÇÑ ÁÖ±âÀûÀÎ ¸ð´ÏÅ͸µÀ»
ÅëÇؼ­ ¾Ë¾Æ¾ß °ÚÁö¿©...
Çؽ¬ ¾Ë°í¸®Áò¿¡ ÀÇÇؼ­ ºÐÇÒÀ» ÇÏ°Ô µÇ¸é ÈξÀ ºÐÆ÷µµ°¡ ÁÁÀ» ¼ö ÀÖ½À´Ï´Ù..
º¸ÅëÀº 2,4,8, 16, 32.......... ´ÜÀ§·Î ºÐÇÒÀ» ÇϰԵ˴ϴÙ..
Çؽ¬ºÐÇÒÀÇ ¹®¹ýÀº ¿ª½¬..¸Þ´º¾óÀ» Âü°íÇϽñ¸¿©...
¿¹¸¦ º¸°Ú½À´Ï´Ù..

 

SQL> create tablespace tbs1
  2  datafile 'k:\oracle_tablespace\tbs1.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create tablespace tbs2
  2  datafile 'k:\oracle_tablespace\tbs2.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create tablespace tbs3
  2  datafile 'k:\oracle_tablespace\tbs3.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create table product1(                  -----------> Çؽ¬ºÐÇÒ ¹æ¹ý1
  2  id number(3),
  3  name varchar2(30)
  4  )
  5  partition by hash(id) partitions 8
  6  store in (tbs1, tbs2, tbs3);

Å×À̺íÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create table product2(                   -------------> Çؽ¬ºÐÇÒ ¹æ¹ý2
  2  id number(3),
  3  name varchar2(30)
  4  )
  5  partition by hash(id)
  6  (partition p1 tablespace tbs1,
  7   partition p2 tablespace tbs2,
  8   partition p3 tablespace tbs3);

 

Å×À̺íÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

ÀϹÝÀûÀÎ SQL¹®(select, insert, update, delete)À» »ç¿ëÇؼ­ ºÐÇÒ Å×À̺íÀ» »ç¿ëÇÒ ¶§´Â ´ÙÀ½°ú °°ÀÌ ºÐÇÒ¸íÀ»
¸í½ÃÇØ ÁÝ´Ï´Ù...

 

SQL> select * from psales partition(p5);

YEAR MO DD EMP      COUNT     AMOUNT
---- -- -- --- ---------- ----------
1999 04 22 444      44444   12345678
2001 12 31 012       1212   45678901

 

SQL> insert into product2 values (123, 'yasi');

1 °³ÀÇ ÇàÀÌ ¸¸µé¾îÁ³½À´Ï´Ù.

 

SQL> select * from product2 partition(p2);

¼±ÅÃµÈ ·¹Äڵ尡 ¾ø½À´Ï´Ù.

 

SQL> select * from product2 partition(p1);

¼±ÅÃµÈ ·¹Äڵ尡 ¾ø½À´Ï´Ù.

 

SQL> select * from product2 partition(p3);

        ID NAME
---------- ------------------------------
       123 yasi

°ü·Ã ÀÚ·á»çÀü....

DBA_PART_TABLES
DBA_TAB_PARTITIONS
DBA_PART_KEY_COLUMNS
DBA_TABLES
DBA_OBJECTS
DBA_SEGMENTS

ºÐÇÒ ¹æ¹ý1Àº 8°³ÀÇ ºÐÇÒ ±¸Á¶¸¦ °®°ÔµÇ°í, ºÐÇÒ ¹æ¹ý2 ´Â ¿À¶óŬÀÌ ¾Ë¾Æ¼­ ÃÖÀûÀÇ ºÐÇÒ ±¸Á¶¸¦ ¸¸µì´Ï´Ù..

 

 

===========
À妽º ºÐÇÒ
===========

¹üÀ§ºÐÇÒÁß...À妽º¸¦ ºÐÇÒÇÏ´Â ¹æ¹ý¿¡ ´ëÇؼ­ »ìÆ캸°Ú½À´Ï´Ù..
¿À¶óŬÀº 8.0.X ¹öÀüºÎÅÍ 4°¡Áö Á¾·ùÀÇ ºÐÇÒ À妽º¸¦ Á¦°øÇÕ´Ï´Ù..
±¸Á¶Àû Ãø¸é°ú ºÐÇÒÅ° »ç¿ëÀ» ±âÁØÀ¸·Î ³ª´¯´Ï´Ù..

±¸Á¶Àû Ãø¸é
- ±Û·Î¹ú À妽º : Å×À̺í°ú À妽ºÀÇ ºÐÇÒ±¸Á¶°¡ ¼­·Î ´Ù¸¥ À¯ÇüÀÇ À妽º
- ·ÎÄà À妽º   : Å×À̺í°ú À妽ºÀÇ ºÐÇÒ±¸Á¶°¡ ¼­·Î °°Àº À¯ÇüÀÇ À妽º

ºÐÇÒÅ° »ç¿ë ±âÁØ
- ÇÁ¸®ÇȽºµå À妽º    : À妽º¸¦ »ý¼ºÇÒ ¶§ ±âÁØÀ¸·Î ÇÏ´Â Ä÷³À» ºÐÇÒÅ°·Î »ç¿ëÇÑ À妽º
- ³í-ÇÁ¸®ÇȽºµå À妽º : Å×À̺í°ú À妽ºÀÇ ºÐÇÒ±¸Á¶°¡ ¼­·Î °°À¸ À¯ÇüÀÇ À妽º

±×·³...»ý¼ºÇØ º¸°Ú½À´Ï´Ù..
¸ÕÀú À妽º¸¦ À§ÇÑ Å×ÀÌºí½ºÆäÀ̽º¸¦ »ý¼ºÈÄ
¾Õ¼­¼­ »ý¼ºÇÑ Å×ÀÌºí¿¡ ´ëÇÑ À妽º¸¦ »ý¼ºÇÏ°Ú½À´Ï´Ù..

 

SQL> create tablespace idx_t1
  2  datafile 'K:\oracle_tablespace\idx_t1.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create tablespace idx_t2
  2  datafile 'K:\oracle_tablespace\idx_t2.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create tablespace idx_t3
  2  datafile 'K:\oracle_tablespace\idx_t3.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create tablespace idx_t4
  2  datafile 'K:\oracle_tablespace\idx_t4.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create tablespace idx_t5
  2  datafile 'K:\oracle_tablespace\idx_t5.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create index idx_psales_year_mon             -----------------------> psales Å×ÀÌºí¿¡ ´ëÇÑ À妽º »ý¼º...
  2  on psales(year, mon)
  3  global
  4  partition by range (year, mon)
  5  (partition idx_p1 values less than ('1999', '02') tablespace idx_t1,
  6   partition idx_p2 values less than ('1999', '03') tablespace idx_t2,
  7   partition idx_p3 values less than ('1999', '04') tablespace idx_t3,
  8   partition idx_p4 values less than ('1999', '05') tablespace idx_t4,
  9   partition idx_p5 values less than (maxvalue, maxvalue) tablespace idx_t5);

À妽º°¡ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> drop index idx_psales_year_mon;

À妽º°¡ »èÁ¦µÇ¾ú½À´Ï´Ù.

 

SQL> create index idx_psales_year_mon
  2  on psales(year, mon)
  3  local
  4  (partition p1 tablespace idx_t1,          ------> psales Å×ÀÌºí¿¡¼­ ³ª´« ÆÄƼ¼Ç ¸í°ú À妽º ÆÄƼ¼Ç Å×ÀÌºí½ºÆäÀ̽ºÀÇ ºÐÇÒ ±¸Á¶°¡ °°´Ù..
  5   partition p2 tablespace idx_t2,
  6   partition p3 tablespace idx_t3,
  7   partition p4 tablespace idx_t4,
  8   partition p5 tablespace idx_t5);

À妽º°¡ »ý¼ºµÇ¾ú½À´Ï´Ù.


´ÙÀ½Àº ±Û·¯¹ú ÇÁ¸®ÇȽº À妽º ºÐÇÒ ¹æ¹ý¿¡ ´ëÇؼ­ »ìÆ캸°Ú½À´Ï´Ù..
±Û·Î¹ú ÇÁ¸®ÇȽº À妽º´Â Å×ÀÌºí ºÐÇÒ±¸Á¶¿Í À妽º ºÐÇÒ±¸Á¶°¡ ¼­·Î ´Ù¸£Áö¿©...
À妽º »ý¼º½Ã ±âÁØÀÌ µÇ´Â Ä÷³ÀÌ ºÐÇÒÅ°·Î »ç¿ëµÇ´Â À妽º¸¦ ÀǹÌÇÕ´Ï´Ù...

 

SQL> create table gpsales
  2  (no number(5),
  3  person varchar2(30),
  4  amount number(8),
  5  week number(2))
  6  partition by range (week)
  7  (partition p1 values less than (4),
  8   partition p2 values less than (8),
  9   partition p3 values less than (12));

Å×À̺íÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create index gpsales_idx
  2  on gpsales(week, no)
  3  global
  4  partition by range (week)
  5  (partition g_p1 values less than(8),
  6   partition g_p2 values less than(maxvalue));

À妽º°¡ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

´ÙÀ½Àº ·ÎÄà ÇÁ¸®ÇȽº À妽º¿¡ ´ëÇؼ­ »ìÆ캾´Ï´Ù..
·ÎÄà ÇÁ¸®ÇȽº À妽º´Â Å×À̺í°ú À妽º°¡ °°Àº ±¸Á¶¸¦ °¡Áö°í ÀÖÀ¸¸ç À妽º »ý¼º½Ã ±âÁØÀ̵Ǵ Ä÷³ÀÌ ºÐÇÒÅ°·Î
»ç¿ëµÇ´Â À妽º ºÐÇÒ ¹æ½ÄÀÔ´Ï´Ù..

 

SQL> create table lssales
  2  (no number(5),
  3  person varchar2(30),
  4  amount number(8),
  5  week number(2))
  6  partition by range (week)
  7  (partition p1 values less than (4),
  8   partition p2 values less than (8));

Å×À̺íÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create index lssales_idx on lssales(week, no)
  2  local
  3  (partition g_p1 tablespace idx_t1,
  4   partition g_p2 tablespace idx_t2);

À妽º°¡ »ý¼ºµÇ¾ú½À´Ï´Ù.


´ÙÀ½Àº À妽º »ç¿ë¿¡ ´ëÇÑ ·Îµå¸ÊÀÔ´Ï´Ù...

- ·ÎÄà ÇÁ¸®ÇȽº À妽º
  -À妽º »ý¼º½Ã »ç¿ëµÇ´Â Ä÷³ÀÌ ºÐÇÒ Å×ÀÌºí »ý¼º½Ã ±âÁØÀÌ µÇ´Â Ä÷³

- ±Û·¯¹ú ÇÁ¸®ÇȽº À妽º
  - Ä÷³ÀÌ ºÐÇÒ¿¡ ±âÁØÀÌ µÇÁö ¾Ê°í, À¯ÀÏÇÑ À妽º ÀÏ °æ¿ì »ç¿ë
  - ÀÇ»ç °áÁ¤ ¾÷¹«(DSS)¿¡ Àû¿ëµÉ ¶§

- ·ÎÄà ³í-ÇÁ¸®ÇȽº À妽º
  - °Ë»ö½Ã ½ÇÇà¼Óµµ°¡ ¸Å¿ì Áß¿äÇÒ ¶§ »ç¿ë
  - OLTP¾÷¹«¿¡ »ç¿ë

´ÙÀ½Àº ºÐÇÒ °´Ã¼¿¡ ´ëÇÑ °ü¸®»óÀÇ ¹æ¹ýÀ» ¾Ë¾Æº¸°Ú½À´Ï´Ù...
Áï, alter table¹®À» ÀÌ¿ëÇؼ­ ºÐÇÒ °´Ã¼¸¦ °ü¸®ÇÕ´Ï´Ù....
¹®¹ýÀº ¸Þ´º¾óÀ» Âü°íÇϽñ¸¿©..

 

SQL> alter table psales
  2  drop partition p1;        --------> psales Å×ÀÌºí¿¡ p1 ÆÄƼ¼ÇÀ» »èÁ¦ÇÕ´Ï´Ù..

Å×À̺íÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> alter index idx_psales_year_mon
  2  drop partition p1;
alter index idx_psales_year_mon        --------> ¾Õ¿¡¼­ ¿ì¸®´Â psales Å×ÀÌºí¿¡ ´ëÇÑ ±Û·Î¹ú À妽º¸¦ drop Çß¾ú½¿´Ù...Áï, ÀÌ·± ¹æ½ÄÀº ±Û·Î¹ú À妽º¿¡¸¸ Àû¿ëµÇ´Â °ÍÀÔ´Ï´Ù..
            *
1Çà¿¡ ¿À·ù:
ORA-14076: DROP/SPLIT PARTITION Àº LOCAL À妽º ºÐÇÒ¿µ¿ªÀ¸·Î »ç¿ëµÉ ¼ö ¾ø½À´Ï´Ù  

 

SQL> alter table psales
  2  rename partition p2 to g2;         -------------> Å×À̺íÀÇ ºÐÇÒ¿µ¿ª¿¡ ´ëÇÑ À̸§ º¯°æ¹æ¹ý

Å×À̺íÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> alter index idx_psales_year_mon
  2  rename partition p2 to g2;             ---------------> À妽ºÀÇ ºÐÇÒ¿µ¿ª¿¡ ´ëÇÑ À̸§ º¯°æ¹æ¹ý
 
À妽º°¡ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> alter table gsales add partition p4               -------------> Å×À̺íÀÇ »õ·Î¿î ¹üÀ§Ãß°¡
  2  values less than (16) tablespace data_199903;

Å×À̺íÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> alter table product1 add partition;                ----------> Çؽ¬ºÐÇÒ Å×ÀÌºí¿¡ ´ëÇÑ »õ·Î¿î ¹üÀ§ Ãß°¡

Å×À̺íÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> alter table product2 add partition p4 tablespace tbs3;        ----------> Çؽ¬ºÐÇÒ Å×ÀÌºí¿¡ ´ëÇÑ »õ·Î¿î ¹üÀ§ Ãß°¡

Å×À̺íÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> alter table psales
  2  merge partitions g2, p3 into partition p2_3;          -----------> ºÐÇÒ¿µ¿ª g2¿Í p3¸¦ ÇϳªÀÇ ÆÄƼ¼ÇÀ¸·Î ÇÕÃÄ p2_3·Î ¸¸µç´Ù..

Å×À̺íÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> alter table product1 coalesce partition;       -----------> ºÐÇÒÀÇ °³¼ö°¡ Ȧ¼ö°³ÀÌ¸é °Ë»ö¼Óµµ°¡ ³ªºüÁú¼ö Àִµ¥...¦¼ö·Î ¸¸µé¾î ÁÖ´Â ¹®Àå..

Å×À̺íÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> alter table gsales
  2  modify partition p3 storage (maxextents 50);         ---------> ÆÄƼ¼Ç¿¡ ´ëÇÑ º¯°æ

Å×À̺íÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> alter table psales
  2  modify partition p4 unusable local indexes;            ------------> ·ÎÄà À妽º¸¦ »ç¿ëÇÏÁö ¸øÇÏ°Ô ÇÕ´Ï´Ù..

Å×À̺íÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

################################################################################
# unusableµÈ À妽º´Â alter session set skip_unusable_indexes = true; ¹®ÀåÀ»   #
# ½ÇÇàÇؾßÁö¸¸ insert, update, delete¹®À» ½ÇÇà ÇÒ¼ö ÀÖ½¿´Ù.                    #
################################################################################

 

SQL> alter index idx_psales_year_mon
  2  rebuild partition p5 tablespace tbs3;              ---------------> À妽ºÀÇ Àç»ý¼º

À妽º°¡ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> select table_name, partition_name, partition_position, tablespace_name
  2  from user_tab_partitions
  3  where table_name = 'PSALES';

TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ -------------------
PSALES                         P4                                              2 DATA_199904
PSALES                         P5                                              3 DATA_MAXVALUES
PSALES                         P2_3                                            1 SYSTEM

 

SQL> alter table sales
  2  merge partitions p1, p2 into partition p12;

Å×À̺íÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> alter table sales split partition p12
  2  at ('1999', '02')                        ---------------> ºÐÇÒ±âÁØÀÌ µÇ´Â Á¤º¸°¡ ³Ê¹« Å«°ªÀÌ´Ù..Áï, 1999³â 02 ³âµµ±îÁöÀÇ Á¤º¸¸¦ ÀúÀåÇÏ´Àµ¥ 1999³â 02¿ùÀ» ±âÁØÀ¸·Î ³ª´©·Á Çϱ⶧¹®¿¡ ¿¡·¯..
  3  into (partition p21 tablespace tbs2,
  4        partition p22 tablespace tbs3);
alter table sales split partition p12
                                  *
1Çà¿¡ ¿À·ù:
ORA-14080: ºÐÇÒ¿µ¿ªÀº ÁöÁ¤µÈ ³ôÀº ¹Ù¿îµå¿¡ µû¶ó ³ª´©¾îÁú ¼ö ¾ø½À´Ï´Ù


SQL> alter table sales split partition p12
  2  at ('1999', '01')
  3  into (partition p21 tablespace tbs2,
  4        partition p22 tablespace tbs3);

Å×À̺íÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

 

===============
ºÐÇÒ¿ÍÀÌÁî Á¶ÀÎ
===============

ºÐÇÒ ¿ÍÀÌÁî Á¶Àιæ½ÄÀº Á¶ÀεǴ Å×À̺íÀÌ ºÐÇҵǾî ÀÖ´Ù¸é ºÐÇÒµÈ ¿µ¿µ¸¸ Àоîµé¿© °Ë»öÇÏ´Â ¹æ½ÄÀ» ¸»ÇÕ´Ï´Ù..
µÎ°¡Áö ¹æ½ÄÀÌ ÀÖ½À´Ï´Ù..

- Àüü ºÐÇÒ ¿ÍÀÌÁî Á¶ÀÎ : Á¶ÀÎÇÒ ¶§ Å×À̺íÀÌ ¸ðµÎ ºÐÇҵǾî ÀÖ´Â °æ¿ìÀÇ Á¶ÀÎ
- ºÎºÐ ºÐÇÒ ¿ÍÀÌÁî Á¶ÀÎ : Á¶ÀÎÇÒ ¶§ Å×À̺íÀÇ ÀϺΰ¡ ºÐÇҵǾî ÀÖ°í ÀϺδ ºÐÇÒÀÌ ¾ø´Â Á¶ÀÎ ¹æ¹ý

 

SQL> alter session set nls_date_format = 'DD-MON-YYYY';

¼¼¼ÇÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> create table orders
  2  (ordid number,
  3  orderdate date,
  4  prductid number,
  5  quant number)
  6  partition by range (orderdate)
  7  subpartition by hash(prductid) subpartitions 8
  8  store in (tbs1, tbs2, tbs3)
  9  (partition q1 values less than ('01-APR-1999'),
 10   partition q2 values less than ('02-APR-1999'),
 11   partition q3 values less than ('03-APR-1999'),
 12   partition q4 values less than ('04-APR-1999'));
create table orders
*
1Çà¿¡ ¿À·ù:
ORA-01658: TBS3 Å×ÀÌºí½ºÆäÀ̽º¿¡ ¼¼±×¸ÕÆ®¿¡ ´ëÇÑ INITIAL È®Àå ¿µ¿ªÀ» ÀÛ¼ºÇÒ ¼ö ¾ø½À´Ï´Ù    --------->Å×ÀÌºí½ºÆäÀ̽º ¿µ¿ªÀÇ Å©±â¸¦ ´Ã·ÁÁà¾ß ÇÕ´Ï´Ù..


SQL> alter tablespace tbs3
  2  add datafile 'k:\oracle_tablespace/tbs3_2.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> create table orders
  2  (ordid number,
  3  orderdate date,
  4  prductid number,
  5  quant number)
  6  partition by range (orderdate)
  7  subpartition by hash(prductid) subpartitions 8
  8  store in (tbs1, tbs2, tbs3)
  9  (partition q1 values less than ('01-APR-1999'),
 10   partition q2 values less than ('02-APR-1999'),
 11   partition q3 values less than ('03-APR-1999'),
 12   partition q4 values less than ('04-APR-1999'));
create table orders
*
1Çà¿¡ ¿À·ù:
ORA-01658: TBS2 Å×ÀÌºí½ºÆäÀ̽º¿¡ ¼¼±×¸ÕÆ®¿¡ ´ëÇÑ INITIAL È®Àå ¿µ¿ªÀ» ÀÛ¼ºÇÒ ¼ö ¾ø½À´Ï´Ù


SQL> alter tablespace tbs2
  2  add datafile 'k:\oracle_tablespace/tbs2_2.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> alter tablespace tbs1
  2  add datafile 'k:\oracle_tablespace/tbs1_2.dbf' size 500k;

Å×ÀÌºí ¿µ¿ªÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> create table orders
  2  (ordid number,
  3  orderdate date,
  4  prductid number,
  5  quant number)
  6  partition by range (orderdate)
  7  subpartition by hash(prductid) subpartitions 8
  8  store in (tbs1, tbs2, tbs3)
  9  (partition q1 values less than ('01-APR-1999'),
 10   partition q2 values less than ('02-APR-1999'),
 11   partition q3 values less than ('03-APR-1999'),
 12   partition q4 values less than ('04-APR-1999'));
create table orders
*
1Çà¿¡ ¿À·ù:
ORA-01658: TBS2 Å×ÀÌºí½ºÆäÀ̽º¿¡ ¼¼±×¸ÕÆ®¿¡ ´ëÇÑ INITIAL È®Àå ¿µ¿ªÀ» ÀÛ¼ºÇÒ ¼ö ¾ø½À´Ï´Ù


SQL> alter tablespace tbs2
  2  add datafile 'k:\oracle_tablespace/tbs2_3.dbf' size 1M;

Å×ÀÌºí ¿µ¿ªÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> create table orders
  2  (ordid number,
  3  orderdate date,
  4  prductid number,
  5  quant number)
  6  partition by range (orderdate)
  7  subpartition by hash(prductid) subpartitions 8
  8  store in (tbs1, tbs2, tbs3)
  9  (partition q1 values less than ('01-APR-1999'),
 10   partition q2 values less than ('02-APR-1999'),
 11   partition q3 values less than ('03-APR-1999'),
 12   partition q4 values less than ('04-APR-1999'));
create table orders
*
1Çà¿¡ ¿À·ù:
ORA-01658: TBS1 Å×ÀÌºí½ºÆäÀ̽º¿¡ ¼¼±×¸ÕÆ®¿¡ ´ëÇÑ INITIAL È®Àå ¿µ¿ªÀ» ÀÛ¼ºÇÒ ¼ö ¾ø½À´Ï´Ù     --------------->  ¤Ñ¤Ñ; Â¥Áõ³²´Ï´Ù...

 

SQL> alter tablespace tbs1
  2  add datafile 'k:\oracle_tablespace/tbs1_3.dbf' size 1M;

Å×ÀÌºí ¿µ¿ªÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL>  create table orders
  2   (ordid number,
  3   orderdate date,
  4   prductid number,
  5   quant number)
  6   partition by range (orderdate)
  7   subpartition by hash(prductid) subpartitions 8
  8   store in (tbs1, tbs2, tbs3)
  9   (partition q1 values less than ('01-APR-1999'),
 10    partition q2 values less than ('02-APR-1999'),
 11    partition q3 values less than ('03-APR-1999'),
 12    partition q4 values less than ('04-APR-1999'));
 create table orders
*
1Çà¿¡ ¿À·ù:
ORA-01658: TBS3 Å×ÀÌºí½ºÆäÀ̽º¿¡ ¼¼±×¸ÕÆ®¿¡ ´ëÇÑ INITIAL È®Àå ¿µ¿ªÀ» ÀÛ¼ºÇÒ ¼ö ¾ø½À´Ï´Ù


SQL> alter tablespace tbs3
  2  add datafile 'k:\oracle_tablespace/tbs3_3.dbf' size 1M;

Å×ÀÌºí ¿µ¿ªÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> create table orders
  2  (ordid number,
  3  orderdate date,
  4  productid number,
  5  quant number)
  6  partition by range (orderdate)
  7  subpartition by hash(productid) subpartitions 8
  8  store in (tbs1, tbs2, tbs3)
  9  (partition q1 values less than ('01-APR-1999'),
 10   partition q2 values less than ('02-APR-1999'),
 11   partition q3 values less than ('03-APR-1999'),
 12   partition q4 values less than ('04-APR-1999'));

Å×À̺íÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create table product
  2  (productid number,
  3  name varchar2(60))
  4  partition by hash (productid) partitions 8
  5  store in (tbs1, tbs2, tbs3);

Å×À̺íÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> @E:\oracle\ora90\rdbms\admin\utlxplan.sql

Å×À̺íÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> set autotrace on

 

SQL> select * from orders o, product p
  2  where o.productid = p.productid;         ---------------->  Àüü ºÐÇÒ ¿ÍÀÌÁî Á¶ÀÎ...

¼±ÅÃµÈ ·¹Äڵ尡 ¾ø½À´Ï´Ù.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=93)
   1    0   PARTITION HASH (ALL)                                             -----------> Àüü¸¦ ¶æÇÏ´Â ALL
   2    1     NESTED LOOPS (Cost=2 Card=1 Bytes=93)
   3    2       PARTITION RANGE (ALL)
   4    3         TABLE ACCESS (FULL) OF 'ORDERS' (Cost=1 Card=1 Bytes=48)

   5    2       TABLE ACCESS (FULL) OF 'PRODUCT' (Cost=1 Card=328 Bytes=14760)


½ÇÇà °èȹ¼³¸íÀ» ÇÏÀÚ¸é....

1. ordersÅ×À̺íÀ» full ½ºÄµÇÕ´Ï´Ù..(¹°·Ð ºÐÇÒµÈ ¹üÀ§ÀÔ´Ï´Ù..)
2. productÅ×À̺íÀ» full ½ºÄµÇÕ´Ï´Ù..(¹°·Ð ¿©±âµµ ´ç¿¬È÷ ºÐÇÒµÈ ¹üÀ§¾È¿¡¼­ ÀÔ´Ï´Ù..)
3. Á¶ÀÎÇÕ´Ï´Ù..

 

Statistics
----------------------------------------------------------
        122  recursive calls
         96  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        501  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

         

=============================
ºÐÇÒµÈ °´Ã¼ÀÇ ÀͽºÆ÷Æ®/ÀÓÆ÷Æ®
=============================

exp »ç¿ëÀÚ¸í/Æнº¿öµå table = (Å×À̺í¸í:ºÐÇÒ¸í)

imp »ç¿ëÀÚ¸í/Æнº¿öµå table = (Å×À̺í¸í:ºÐÇÒ¸í)


(¿¹)
exp system/manager tables=(emp, psales:p1, psales:p2)

imp system/manager file=expdat.dmp tables=(psales:p1, psales:p2)

 

 

========
º¹ÇÕºÐÇÒ
========

º¹ÇÕºÐÇÒÀº ¾Õ¿¡¼­ »ìÆìºÃ´ø ¹üÀ§ºÐÇÒ°ú Çؽ¬ºÐÇÒÀÇ °áÇÕµÈ ÇüŸ¦ ¸»ÇÕ´Ï´Ù..
2°¡Áö ºÐÇÒ¹æ¹ýÀÇ ÀåÁ¡À» ¸ðµÎ»ç¿ëÇÒ ¼ö ÀÖ¾î ¸Å¿ì È¿°úÀûÀ̶ó´Âµ¥....ÂÁ....Àú´Â Á» ¾î·Æ³×¿©..ÇæÇæ~

¹Ù·Î ¿¹·Î µé¾î°¡Áö¿©...
Á¦ÀÏ ¸ÕÀú º¼ ¿¹Á¦´Â ¹üÀ§ºÐÇÒÀ» ÇÏ°í...±× ¹üÀ§ºÐÇÒ ³»¿¡¼­ ¶Ç´Ù½Ã Çؽ¬ºÐÇÒÀ» ÇÏ´Â ¿¹Á¦ÀÔ´Ï´Ù..

 

SQL> alter session set nls_date_format = 'dd-mon-yyyy';

¼¼¼ÇÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> create table orders
  2  (ordid number,
  3  orderdate date,
  4  productid number,
  5  quant number)
  6  partition by range (orderdate)
  7  subpartition by hash(productid) subpartitions 4
  8  store in (tbs1, tbs2, tbs3)
  9  (partition q1 values less than ('01-APR-1999'),
 10   partition q2 values less than ('02-APR-1999'),
 11   partition q3 values less than ('03-APR-1999'),
 12   partition q4 values less than ('04-APR-1999'));

Å×À̺íÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> select table_name,partition_name,subpartition_count, partition_position, tablespace_name
  2  from user_tab_partitions
  3  where table_name = 'ORDERS';

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_COUNT PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------ ---------------
ORDERS                         Q1                                              4                  1 USERS
ORDERS                         Q2                                              4                  2 USERS
ORDERS                         Q3                                              4                  3 USERS
ORDERS                         Q4                                              4                  4 USERS

SQL> select rownum,partition_name, segment_type, tablespace_name
  2  from dba_segments
  3  where owner = 'YASI' and segment_name = 'ORDERS';

    ROWNUM PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME
---------- ------------------------------ ------------------ ------------------------------
         1 SYS_SUBP369                    TABLE SUBPARTITION TBS1
         2 SYS_SUBP370                    TABLE SUBPARTITION TBS2
         3 SYS_SUBP371                    TABLE SUBPARTITION TBS3
         4 SYS_SUBP372                    TABLE SUBPARTITION TBS1
         5 SYS_SUBP373                    TABLE SUBPARTITION TBS1
         6 SYS_SUBP374                    TABLE SUBPARTITION TBS2
         7 SYS_SUBP375                    TABLE SUBPARTITION TBS3
         8 SYS_SUBP376                    TABLE SUBPARTITION TBS1
         9 SYS_SUBP377                    TABLE SUBPARTITION TBS1
        10 SYS_SUBP378                    TABLE SUBPARTITION TBS2
        11 SYS_SUBP379                    TABLE SUBPARTITION TBS3
        12 SYS_SUBP380                    TABLE SUBPARTITION TBS1
        13 SYS_SUBP381                    TABLE SUBPARTITION TBS1
        14 SYS_SUBP382                    TABLE SUBPARTITION TBS2
        15 SYS_SUBP383                    TABLE SUBPARTITION TBS3
        16 SYS_SUBP384                    TABLE SUBPARTITION TBS1

16 °³ÀÇ ÇàÀÌ ¼±ÅõǾú½À´Ï´Ù.

 

Áï, ¹üÀ§ºÐÇÒÀÇ ±âÁØÀ» ¸í½ÃÇѵ¥·Î Å×À̺íÀ» ºÐÇÒÀ» Çϱâ´Â Çϴµ¥ ÀúÀåÇÒ¶§´Â ¿À¶óŬÀÇ
Çؽ¬ ¾Ë°í¸®Áò¿¡ ÀÇÁ¸Çؼ­ °¢°¢ÀÇ Å×ÀÌºí½ºÆäÀ̽º¿¡ ÀúÀåÇÑ´Ù´Â ¶æÀÔ´Ï´Ù..
±×·±µ¥ ÀÌ ¿¹Á¦´Â Ã¥¿¡¼­ ¸ÕÀú ¹üÀ§ºÐÇÒÀ» 4°³ »ý¼ºÇÑÈÄ Å×ÀÌºí½ºÆäÀ̽º´Â tbs1, tbs2, tbs3 ÀÌ·¸°Ô 3°³¸¦ »ç¿ëÇÕ´Ï´Ù..
ÃÑ12°³·Î Å×À̺íÀ» ºÐÇÒ Çß½À´Ï´Ù..¶ó°í ÇÕ´Ï´Ù...
Àú·Î½á´Â µµÀúÈ÷ ÀÌÇØ°¡ ¾È°¡´Â ºÎºÐÀÔ´Ï´Ù...Á¦°¡ º¸±â¿£...µ¥ÀÌŸ»çÀüÀ» Á¶È¸ÇØ º» °á°ú 20°³·Î ºÐÇÒÇ߰ŵ¢¿ä...
Á¦°¡ µ¥ÀÌŸ »çÀüÀ» À߸ø ºÐÇÒ Çß³ª¿©???
¸ô°Ú½¿´Ù...¿¡±¸...

Á»´õ º¹ÀâÇÑ º¹ÇÕºÐÇÒÀ» Çغ¾½Ã´Ù..

 

SQL> alter session set nls_date_format = 'dd-mon-yyyy';

¼¼¼ÇÀÌ º¯°æµÇ¾ú½À´Ï´Ù.

 

SQL> create table order3
  2  (ordid number,
  3   orderdate date,
  4   productid number,
  5   quantity number)
  6   partition by range(orderdate)
  7   subpartition by hash(productid) subpartitions 8
  8   store in (tbs1, tbs2, tbs3)
  9   (partition q1 values less than ('01-JAN-1999')
 10     (subpartition q1_1 tablespace tbs1,
 11      subpartition q1_2 tablespace tbs2,
 12      subpartition q1_3 tablespace tbs3),
 13    partition q2 values less than ('01-FEB-1999')
 14     (subpartition q2_1 tablespace tbs1,
 15      subpartition q2_2 tablespace tbs2,
 16      subpartition q2_3 tablespace tbs3),
 17    partition q3 values less than ('01-MAR-1999')
 18     (subpartition q3_1 tablespace tbs1,
 19      subpartition q3_2 tablespace tbs2,
 20      subpartition q3_3 tablespace tbs3),
 21    partition q4 values less than (maxvalue)
 22   );

Å×À̺íÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> select table_name,partition_name,subpartition_count, partition_position, tablespace_name
  2  from user_tab_partitions
  3  where table_name = 'ORDER3';

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_COUNT PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------ ---------------
ORDER3                         Q1                                              3                  1 USERS
ORDER3                         Q2                                              3                  2 USERS
ORDER3                         Q3                                              3                  3 USERS
ORDER3                         Q4                                              8                  4 USERS

4 °³ÀÇ ÇàÀÌ ¼±ÅõǾú½À´Ï´Ù.

SQL> select rownum,partition_name, segment_type, tablespace_name
  2  from dba_segments
  3  where owner = 'YASI' and segment_name = 'ORDER3';

    ROWNUM PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME
---------- ------------------------------ ------------------ ------------------------------
         1 Q1_2                           TABLE SUBPARTITION TBS2
         2 Q1_3                           TABLE SUBPARTITION TBS3
         3 Q2_1                           TABLE SUBPARTITION TBS1
         4 Q2_2                           TABLE SUBPARTITION TBS2
         5 Q2_3                           TABLE SUBPARTITION TBS3
         6 Q3_1                           TABLE SUBPARTITION TBS1
         7 Q3_2                           TABLE SUBPARTITION TBS2
         8 Q3_3                           TABLE SUBPARTITION TBS3
         9 SYS_SUBP297                    TABLE SUBPARTITION TBS1
        10 SYS_SUBP298                    TABLE SUBPARTITION TBS2
        11 SYS_SUBP299                    TABLE SUBPARTITION TBS3
        12 SYS_SUBP300                    TABLE SUBPARTITION TBS1
        13 SYS_SUBP301                    TABLE SUBPARTITION TBS2
        14 SYS_SUBP302                    TABLE SUBPARTITION TBS3
        15 SYS_SUBP303                    TABLE SUBPARTITION TBS1
        16 SYS_SUBP304                    TABLE SUBPARTITION TBS2
        17 Q1_1                           TABLE SUBPARTITION TBS1

17 °³ÀÇ ÇàÀÌ ¼±ÅõǾú½À´Ï´Ù.

 

SQL> select table_name, partition_name, subpartition_name, tablespace_name
  2  from dba_tab_subpartitions
  3  where table_owner = 'YASI';

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ -------
ORDER3                         Q1                             Q1_1                           TBS1
ORDER3                         Q1                             Q1_2                           TBS2
ORDER3                         Q1                             Q1_3                           TBS3
ORDER3                         Q2                             Q2_1                           TBS1
ORDER3                         Q2                             Q2_2                           TBS2
ORDER3                         Q2                             Q2_3                           TBS3
ORDER3                         Q3                             Q3_1                           TBS1
ORDER3                         Q3                             Q3_2                           TBS2
ORDER3                         Q3                             Q3_3                           TBS3
ORDER3                         Q4                             SYS_SUBP393                    TBS1
ORDER3                         Q4                             SYS_SUBP394                    TBS2
ORDER3                         Q4                             SYS_SUBP395                    TBS3
ORDER3                         Q4                             SYS_SUBP396                    TBS1
ORDER3                         Q4                             SYS_SUBP397                    TBS2
ORDER3                         Q4                             SYS_SUBP398                    TBS3
ORDER3                         Q4                             SYS_SUBP399                    TBS1
ORDER3                         Q4                             SYS_SUBP400                    TBS2

17 °³ÀÇ ÇàÀÌ ¼±ÅõǾú½À´Ï´Ù.

Ã¥¿¡´Â ÀÌ ¿¹Á¦°¡ 3°³ÀÇ ¹üÀ§ ºÐÇÒ°ú 8°³ÀÇ ÇؼöºÐÇÒ·Î ¸ÕÀú ºÐÇҵǰí q2, q3´Â ´Ù½Ã 3°³ÀÇ Çؽ¬ ºÐÇÒ·Î
3 * 8 + 3 + 3 = 30°³ÀÇ ºÐÇÒÀÌ »ý¼ºµÇ¾ú´Ù°í ³ª¿À³×¿©...
ÀÌ»óÇÔµÂ....Á¦°¡ µ¥ÀÌŸ »çÀüÀ» Á¶È¸ Çغ»°á°ú.....yasi »ç¿ëÀÚ´Â ¸ÕÀú users Å×ÀÌºí½ºÆäÀ̽º¸¦ 4°³·Î ¹üÀ§ºÐÇÒÇÏ¿© »ç¿ëÇÏ°í ÀÖ½À´Ï´Ù..
±×¸®°í.......³ª¸ÓÁö ¼­ºêÆÄƼ¼ÇÀ¸·Î 17°³°¡ ÀÖ½À´Ï´Ù...±×·¯´Ï±î...21°³·Î ºÐÇÒÇؼ­ »ç¿ëÇÏ´Â °ÍÀÌÁö¿©...
users Å×ÀÌºí½ºÆäÀ̽º´Â ¸í½ÃÇÏÁöµµ ¾Ê¾Ò´Âµ¥..Á¶È¸°¡ µÆ´Ù±¸ ÀÌ»óÇÏ°Ô »ý°¢ÇϽÇÁö ¸ð¸£°ÚÁö¸¸...ÀÌ°ÍÀº ´ç¿¬ÇÑ °ÍÀÔ´Ï´Ù..
Á¦°¡ yasi »ç¿ëÀÚÀÇ ±âº» Å×ÀÌºí½ºÆäÀ̽º¸¦ users·Î ¼³Á¤Ç߰ŵ¢¿ä..

Çì±ò¸³´Ï´Ù.....Ã¥ÀÌ ³í¸®ÀûÀ¸·Î ¼³¸íÀ» ÇسùÀ¸¸é ¸ô°Ú´Âµ¥....°è»ê¹ýÀÌ ¾î¶»°Ô µÇ´Â °ÍÀÎÁö Àß ¸ô°Ú½¿´Ù..
¾Æ·¡´Â Á¦°¡ À§ÀÇ ¿¹Á¦¸¦ Á¶±Ý ¼öÁ¤Çؼ­ Çغ» °á°úÀÔ´Ï´Ù..

 

SQL> create tablespace data1
  2  datafile 'k:\oracle_tablespace\data1_1.dbf' size 5M;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create tablespace data2
  2  datafile 'k:\oracle_tablespace\data2_1.dbf' size 5M;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create tablespace data3
  2  datafile 'k:\oracle_tablespace\data3_1.dbf' size 5M;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> create tablespace data4
  2  datafile 'k:\oracle_tablespace\dat4_1.dbf' size 5M;

Å×ÀÌºí ¿µ¿ªÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.


SQL> create table order2
  2  (ordid number,
  3   orderdate date,
  4   productid number,
  5   quantity number)
  6   partition by range(orderdate)
  7   subpartition by hash(productid) subpartitions 8
  8   store in (tbs1, tbs2, tbs3)
  9   (partition q1 values less than ('01-JAN-1999') tablespace data1
 10     (subpartition q1_1 tablespace tbs1,
 11      subpartition q1_2 tablespace tbs2,
 12      subpartition q1_3 tablespace tbs3),
 13    partition q2 values less than ('01-FEB-1999') tablespace data2
 14     (subpartition q2_1 tablespace tbs1,
 15      subpartition q2_2 tablespace tbs2,
 16      subpartition q2_3 tablespace tbs3),
 17    partition q3 values less than ('01-MAR-1999') tablespace data3
 18     (subpartition q3_1 tablespace tbs1,
 19      subpartition q3_2 tablespace tbs2,
 20      subpartition q3_3 tablespace tbs3),
 21    partition q4 values less than (maxvalue) tablespace data4
 22   );

Å×À̺íÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù.

 

SQL> select table_name,partition_name,subpartition_count, partition_position, tablespace_name
  2  from user_tab_partitions
  3  where table_name = 'ORDER2';

TABLE_NAME          &n

2005-06-24 16:22:50
211.189.124.***

 

ÁÁÀº»ý°¢ ^^

0Á¡ (0¸í)
³×À̹ö Á¤½ÅÂ÷·Á¶ù! -_- GoodBug 2005-03-27
JS JS·Î ¹®¼­°Ë»öÇϱâ GoodBug 2005-03-29
Jakarta commons Commons DbUtils ¸î°¡Áö ¿¹Á¦ 3 Tag 2 GoodBug 2005-11-17
Database otn forum GoodBug 2006-05-24
Oracle ºÐÇÒ ±â´É GoodBug 2005-06-24
¼³Ä¡½Ã log4j°ü·Ã¿¡·¯ ¹Î°æ¹Î 2005-04-11
[´äº¯] Log4j ¸»°í ´Ù¸¥°Í »ç¿ëÇϱâ GoodBug 2005-04-11
Çǵå¹éÀÔ´Ï´Ù 1 ¹Î°æ¹Î 2005-04-11
µ¡±Û 0°³ | ÅÂ±× 0°³ | °ü·Ã±Ûº¸±â
ű×ÀÔ·Â
½±Ç¥(,)±¸ºÐÀ¸·Î Çѹø¿¡ ¿©·¯ ű׸¦ ÀÔ·ÂÇÒ¼ö ÀÖ½À´Ï´Ù
À̸§ ºñ¹Ð¹øÈ£
µ¥ÀÌÅͺ£À̽º ÆÁ
µ¥ÀÌÅͺ£À̽º ÆÁ °Ô½ÃÆÇ ÀÔ´Ï´Ù
! ¹øÈ£ Á¦¸ñ ±Û¾´ÀÌ ÀÏÀÚ Á¶È¸
181 import DB ºÎºÐÀûÀÎ µ¥ÀÌÅÍ export / import ¹æ¹ý 2 kaiser 2008-12-29 7,795
180 NOT IN NOT EXISTS not in °ú not existsÀÇ °³³ä ÀÌÇØ 1 kaiser 2008-12-17 19,574
179 IP Á¢¼ÓÀÚ ¾ÆÀÌÇÇ º¸±â 1 GoodBug 2008-06-27 9,451
178 meta ¿À¶óŬ ½ºÅ°¸¶ ½ºÅ©¸³Æ® ¹Þ±â 1 GoodBug 2008-06-05 19,843
177 ORA-00054 ORA-00054: resource busy and acquire with NOWAIT specified 1 GoodBug 2007-10-27 13,058
176 ÇÑ±Û MYSQL ¼­¹ö ij¸¯¼ÂÀÌ EUCKRÀÌ ¾Æ´Ò°æ¿ì Ŭ¶óÀ̾ðÆ® ¼³Á¤ 1 GoodBug 2007-10-09 8,344
175 archive ¾ÆÄ«ÀÌºê ¼º´É Çâ»ó 1 GoodBug 2007-10-02 7,939
174 Dictionary Dict table 1 GoodBug 2007-10-02 7,224
173 ¿À¶óŬƩ´× redo log file ±¸¼º ¸ð´ÏÅÍ 1 GoodBug 2007-09-20 7,905
172 dictionary ÀÚÁÖ¾²´Â µñ¼Å³Ê¸® Á¤º¸ 1 1 GoodBug 2007-09-20 8,352
171 TableSpace Tablespace º° »ç¿ë·® È®ÀÎ 1 GoodBug 2007-09-20 8,766
170 ¿À¶óŬ üũÆ÷ÀÎÆ® °ü·Ã ÆĶó¹ÌÅÍ 2 GoodBug 2007-09-20 7,420
169 ¿À¶óŬƩ´× Å×ÀÌºí ½ºÄµ Åë°è 1 GoodBug 2007-09-19 7,265
168 ¿À¶óŬƩ´× v$filestat·Î µð½ºÅ© I/O ¸¦ È®ÀÎÇغ¸ÀÚ 1 GoodBug 2007-09-19 10,248
167 ¿À¶óŬƩ´× ORA-04031 ¿¡·¯ ´ëó¹ý ¹× ¿¹¹æ¹ý 1 GoodBug 2007-09-17 13,717
166 ¿À¶óŬƩ´× DB_FILE_MULTIBLOCK_READ_COUNT 1 GoodBug 2007-09-17 7,272
165 TableSpace TableSpace °ü¸® (Dictionary, Locally) 1 GoodBug 2007-09-16 7,774
164 ¿À¶óŬƩ´× ¹®Á¦ÇØ°á ¹× Æ©´× °ü·Ã ºä 2 GoodBug 2007-09-16 7,698
163 segment ¿À¶óŬ SEGMENT 1 GoodBug 2007-09-16 8,628
162 startup ¿À¶óŬ startup½Ã ´Ü°èº° ¿ÀÇ ÆÄÀÏ 1 GoodBug 2007-09-16 7,531
copyright 2005-2024 by Unicorn