\u5206\u533a\u8868\u7684EXCHANGE\u4ea4\u6362\u5206\u533a\u4e0d\u68c0\u67e5\u6570\u636e\u6709\u6548\u6027\uff0c\u53ef\u80fd\u5bfc\u81f4LOCAL\u4e3b\u952e\u7d22\u5f15\u51fa\u73b0\u91cd\u590d\u503c\u3002
\n\u901a\u8fc7\u4e00\u4e2a\u7b80\u5355\u7684\u4f8b\u5b50\u6765\u8bf4\u660e\u8fd9\u4e2a\u95ee\u9898\uff1a<\/p>\n
SQL> CREATE TABLE T_PART_EXCHANGE (ID NUMBER, NAME VARCHAR2(30), TYPE VARCHAR2(18))\r\n 2 PARTITION BY LIST (TYPE)\r\n 3 (PARTITION P1 VALUES ('TABLE'), \r\n 4 PARTITION P2 VALUES (DEFAULT));\r\nTable created.\r\nSQL> CREATE INDEX IND_PART_EXCHANGE_TYPEID ON T_PART_EXCHANGE(TYPE, ID) LOCAL;\r\nIndex created.\r\nSQL> ALTER TABLE T_PART_EXCHANGE ADD PRIMARY KEY (TYPE, ID) \r\n 2 USING INDEX IND_PART_EXCHANGE_TYPEID;\r\nTable altered.\r\nSQL> CREATE TABLE T_EXCHANGE_TEMP \r\n 2 AS SELECT * FROM T_PART_EXCHANGE;\r\nTable created.\r\nSQL> CREATE INDEX IND_EXCHANGE_TEMP_TYPEID ON T_EXCHANGE_TEMP(TYPE, ID);\r\nIndex created.\r\nSQL> ALTER TABLE T_EXCHANGE_TEMP ADD PRIMARY KEY (TYPE, ID) \r\n 2 USING INDEX IND_EXCHANGE_TEMP_TYPEID;\r\nTable altered.\r\nSQL> INSERT INTO T_EXCHANGE_TEMP \r\n 2 SELECT ROWNUM, TABLE_NAME, 'TABLE'\r\n 3 FROM USER_TABLES;\r\n3 rows created.\r\nSQL> SELECT * FROM T_EXCHANGE_TEMP;\r\n ID NAME TYPE\r\n---------- ------------------------------ ------------------\r\n 1 T_EXCHANGE_TEMP TABLE\r\n 2 T TABLE\r\n 3 T_PART_EXCHANGE TABLE\r\nSQL> INSERT INTO T_EXCHANGE_TEMP VALUES (4, 'V_T', 'VIEW');\r\n1 row created.\r\nSQL> COMMIT;\r\nCommit complete.<\/pre>\n\u5efa\u7acb\u4e00\u4e2a\u5206\u533a\u8868\uff0c\u4e00\u4e2a\u4e34\u65f6\u8868\u7528\u6765\u4ea4\u6362\u6570\u636e\uff0c\u5206\u533a\u8868\u4e0a\u7684\u4e3b\u952e\u4f7f\u7528LOCAL\u7d22\u5f15\uff0c\u800c\u4e34\u65f6\u8868\u4e0a\u7684\u5bf9\u5e94\u5217\u4e5f\u5efa\u7acb\u4e86\u7d22\u5f15\u5e76\u6dfb\u52a0\u4e86\u4e3b\u952e\u3002
\n\u968f\u540e\u5411\u4e34\u65f6\u8868\u4e2d\u6dfb\u52a0\u8bb0\u5f55\uff0c\u9664\u4e86\u4e09\u6761TYPE\u4e3aTABLE\u7684\u8bb0\u5f55\u5916\uff0c\u8fd8\u589e\u52a0\u4e86\u4e00\u6761TYPE\u4e3aVIEW\u7684\u8bb0\u5f55\u3002
\n\u7136\u540e\u6267\u884c\u5206\u533a\u4ea4\u6362\u64cd\u4f5c\uff1a<\/p>\n
SQL> ALTER TABLE T_PART_EXCHANGE EXCHANGE PARTITION P1 WITH TABLE T_EXCHANGE_TEMP \r\n 2 INCLUDING INDEXES WITHOUT VALIDATION; \r\nTable altered.\r\nSQL> SELECT * FROM T_PART_EXCHANGE PARTITION (P1);\r\n ID NAME TYPE\r\n---------- ------------------------------ ------------------\r\n 1 T_EXCHANGE_TEMP TABLE\r\n 2 T TABLE\r\n 3 T_PART_EXCHANGE TABLE\r\n 4 V_T VIEW\r\nSQL> INSERT INTO T_PART_EXCHANGE VALUES (4, 'V_T', 'VIEW');\r\n1 row created.\r\nSQL> COMMIT;\r\nCommit complete.\r\nSQL> SELECT * FROM T_PART_EXCHANGE; \r\n ID NAME TYPE\r\n---------- ------------------------------ ------------------\r\n 1 T_EXCHANGE_TEMP TABLE\r\n 2 T TABLE\r\n 3 T_PART_EXCHANGE TABLE\r\n 4 V_T VIEW\r\n 4 V_T VIEW<\/pre>\n\u7531\u4e8eOracle\u4e0d\u68c0\u6d4bEXCHANGE\u8fdb\u53bb\u7684\u6570\u636e\u662f\u5426\u5408\u6cd5\uff0c\u5c31\u9020\u6210\u4e86\u6570\u636e\u91cd\u590d\u7684\u73b0\u573a\u3002\u8fd9\u65f6\u5982\u679c\u901a\u8fc7\u4e3b\u952e\u8bbf\u95ee\uff0c\u53ea\u4f1a\u8fd4\u56de\u4e00\u6761\u8bb0\u5f55\uff0c\u800c\u5982\u679c\u5168\u8868\u626b\u63cf\u5219\u4f1a\u8fd4\u56de\u4e24\u6761\u8bb0\u5f55\uff1a<\/p>\n
SQL> SET AUTOT ON EXP\r\nSQL> SELECT * FROM T_PART_EXCHANGE WHERE ID = 4 AND TYPE = 'VIEW';\r\n ID NAME TYPE\r\n---------- ------------------------------ ------------------\r\n 4 V_T VIEW\r\n\r\nExecution Plan\r\n----------------------------------------------------------\r\nPlan hash value: 3202076975\r\n----------------------------------------------------------------------------------------\r\n|Id|Operation |Name |Rows|Cost|Pstart|Pstop|\r\n----------------------------------------------------------------------------------------\r\n| 0|SELECT STATEMENT | | 1| 1| | |\r\n| 1| PARTITION LIST SINGLE | | 1| 1| KEY | KEY|\r\n| 2| TABLE ACCESS BY LOCAL INDEX ROWID|T_PART_EXCHANGE | 1| 1| 2 | 2|\r\n|*3| INDEX RANGE SCAN |IND_PART_EXCHANGE_TYPEID| 1| 1| 2 | 2|\r\n----------------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n 3 - access(\"TYPE\"='VIEW' AND \"ID\"=4)\r\nSQL> SELECT * FROM T_PART_EXCHANGE WHERE ID = 4;\r\n ID NAME TYPE\r\n---------- ------------------------------ ------------------\r\n 4 V_T VIEW\r\n 4 V_T VIEW\r\nExecution Plan\r\n----------------------------------------------------------\r\nPlan hash value: 820685725\r\n-------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |\r\n-------------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 2 | 82 | 4 (0)| | |\r\n| 1 | PARTITION LIST ALL| | 2 | 82 | 4 (0)| 1 | 2 |\r\n|* 2 | TABLE ACCESS FULL| T_PART_EXCHANGE | 2 | 82 | 4 (0)| 1 | 2 |\r\n-------------------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n 2 - filter(\"ID\"=4)\r\nNote\r\n-----\r\n - dynamic sampling used for this statement<\/pre>\n\u5373\u4f7f\u626b\u63cf\u5168\u8868\uff0c\u67e5\u8be2\u7684\u7ed3\u679c\u4ecd\u7136\u53ef\u80fd\u662f\u9519\u8bef\u7684\uff1a<\/p>\n
SQL> SELECT ID, TYPE, COUNT(*)\r\n 2 FROM T_PART_EXCHANGE\r\n 3 GROUP BY ID, TYPE;\r\n ID TYPE COUNT(*)\r\n---------- ------------------ ----------\r\n 4 VIEW 1\r\n 1 TABLE 1\r\n 3 TABLE 1\r\n 2 TABLE 1\r\n 4 VIEW 1\r\nExecution Plan\r\n----------------------------------------------------------\r\nPlan hash value: 2336647613\r\n------------------------------------------------------------------------------------------\r\n|Id|Operation |Name |Rows|Bytes| Cost (%CPU)|Pstart|Pstop|\r\n------------------------------------------------------------------------------------------\r\n| 0|SELECT STATEMENT | | 5| 120| 3 (34)| | |\r\n| 1| PARTITION LIST ALL | | 5| 120| 3 (34)| 1 | 2|\r\n| 2| HASH GROUP BY | | 5| 120| 3 (34)| | |\r\n| 3| INDEX FAST FULL SCAN|IND_PART_EXCHANGE_TYPEID| 5| 120| 2 (0)| 1 | 2|\r\n------------------------------------------------------------------------------------------\r\nNote\r\n-----\r\n - dynamic sampling used for this statement\r\nSQL> SELECT ID, TYPE, COUNT(*)\r\n 2 FROM T_PART_EXCHANGE\r\n 3 GROUP BY ID, TYPE \r\n 4 ORDER BY ID;\r\n ID TYPE COUNT(*)\r\n---------- ------------------ ----------\r\n 1 TABLE 1\r\n 2 TABLE 1\r\n 3 TABLE 1\r\n 4 VIEW 2\r\n\r\nExecution Plan\r\n----------------------------------------------------------\r\nPlan hash value: 98113653\r\n------------------------------------------------------------------------------------------\r\n|Id|Operation |Name |Rows|Bytes| Cost (%CPU)|Pstart|Pstop|\r\n------------------------------------------------------------------------------------------\r\n| 0|SELECT STATEMENT | | 5| 120| 3 (34)| | |\r\n| 1| SORT GROUP BY | | 5| 120| 3 (34)| | |\r\n| 2| PARTITION LIST ALL | | 5| 120| 2 (0)| 1 | 2|\r\n| 3| INDEX FAST FULL SCAN|IND_PART_EXCHANGE_TYPEID| 5| 120| 2 (0)| 1 | 2|\r\n------------------------------------------------------------------------------------------\r\nNote\r\n-----\r\n - dynamic sampling used for this statement<\/pre>\n\u53ef\u4ee5\u770b\u5230\u5982\u679c\u91c7\u7528HASH GROUP BY\uff0c\u5219GROUP BY\u88ab\u63a8\u5230\u5206\u533a\u64cd\u4f5c\u5185\u90e8\uff0c\u56e0\u6b64\u5b8c\u5168\u76f8\u540c\u7684\u8bb0\u5f55\u88ab\u8ba1\u7b97\u4e24\u6b21\u3002\u800c\u52a0\u4e0aORDER BY\u8bed\u53e5\uff0c\u5219Oracle\u91c7\u7528SORT GROUP BY\u64cd\u4f5c\uff0c\u8fd9\u65f6GROUP BY\u5728\u5206\u533a\u64cd\u4f5c\u4e4b\u5916\uff0c\u56e0\u6b64\u5f97\u5230\u7684\u7ed3\u679c\u662f\u6b63\u5e38\u7684\u3002
\n\u5176\u5b9e\u9488\u5bf9\u8fd9\u4e2a\u9519\u8bef\uff0c\u5012\u662f\u5f88\u5bb9\u6613\u89e3\u51b3\uff0c\u6307\u5b9a\u5206\u533a\u8fdb\u884c\u5220\u9664\u5373\u53ef\uff1a<\/p>\n
SQL> DELETE T_PART_EXCHANGE PARTITION (P1) WHERE ID = 4;\r\n1 row deleted.\r\nExecution Plan\r\n----------------------------------------------------------\r\nPlan hash value: 2501039200\r\n-----------------------------------------------------------------------------------------\r\n|Id|Operation |Name |Rows|Bytes|Cost (%CPU)|Time |Pstart|Pstop|\r\n-----------------------------------------------------------------------------------------\r\n| 0|DELETE STATEMENT | | 1| 24| 3 (0)|00:00:01| | |\r\n| 1| DELETE |T_PART_EXCHANGE| | | | | | |\r\n| 2| PARTITION LIST SINGLE| | 1| 24| 3 (0)|00:00:01| KEY | KEY|\r\n|*3| TABLE ACCESS FULL |T_PART_EXCHANGE| 1| 24| 3 (0)|00:00:01| 1 | 1|\r\n-----------------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n 3 - filter(\"ID\"=4)\r\nNote\r\n-----\r\n - dynamic sampling used for this statement\r\nSQL> SET AUTOT OFF\r\nSQL> SELECT * FROM T_PART_EXCHANGE; \r\n ID NAME TYPE\r\n---------- ------------------------------ ------------------\r\n 1 T_EXCHANGE_TEMP TABLE\r\n 2 T TABLE\r\n 3 T_PART_EXCHANGE TABLE\r\n 4 V_T VIEW\r\nSQL> INSERT INTO T_PART_EXCHANGE VALUES (4, 'V_T', 'VIEW');\r\nINSERT INTO T_PART_EXCHANGE VALUES (4, 'V_T', 'VIEW')\r\n*\r\nERROR at line 1:\r\nORA-00001: unique constraint (TEST.SYS_C007282) violated\r\nSQL> COMMIT;\r\nCommit complete.<\/pre>\n","protected":false},"excerpt":{"rendered":"\u5206\u533a\u8868\u7684EXCHANGE\u4ea4\u6362\u5206\u533a\u4e0d\u68c0\u67e5\u6570\u636e\u6709\u6548\u6027\uff0c\u53ef\u80fd\u5bfc\u81f4LOCAL\u4e3b\u952e\u7d22\u5f15\u51fa\u73b0\u91cd\u590d\u503c\u3002 \u901a\u8fc7\u4e00\u4e2a\u7b80\u5355\u7684\u4f8b\u5b50\u6765\u8bf4\u660e\u8fd9\u4e2a\u95ee\u9898\uff1a SQL> CREATE TABLE T_PART_EXCHANGE (ID NUMBER, NAME VARCHAR2(30), TYPE VARCHAR2(18)) 2 PARTITION BY LIST (TYPE) 3 (PARTITION P1 VALUES (‘TABLE’), 4 PARTITION P2 VALUES (DEFAULT)); Table created. SQL> CREATE INDEX IND_PART_EXCHANGE_TYPEID ON T_PART_EXCHANGE(TYPE, ID) LOCAL; Index created. SQL> … Continue reading