¡¾×ª¡¿ ORACLE ·ÖÇø±í PARTITION table
ORACLE ·ÖÇø±í PARTITION table
http://blog.chinaunix.net/u/6889/showart_315897.html
1.1 ·ÖÇø±íPARTITION table
ÔÚORACLEÀïÈç¹ûÓöµ½Ìرð´óµÄ±í£¬¿ÉÒÔʹÓ÷ÖÇøµÄ±íÀ´¸Ä±äÆäÓ¦ÓóÌÐòµÄÐÔÄÜ¡£
1.1.1 ·ÖÇø±íµÄ½¨Á¢£º
ij¹«Ë¾µÄÿÄê²úÉú¾Þ´óµÄÏúÊۼǼ£¬DBAÏò¹«Ë¾½¨Òéÿ¼¾¶ÈµÄÊý¾Ý·ÅÔÚÒ»¸ö·ÖÇøÄÚ£¬ÒÔÏÂʾ·¶µÄÊǸù«Ë¾1999ÄêµÄÊý¾Ý(¼ÙÉèÿÔ²úÉú30MµÄÊý¾Ý)£¬²Ù×÷ÈçÏ£º
·¶Î§·ÖÇø±í£º
CREATE TABLE sales
(invoice_no NUMBER,
...
sale_date DATE NOT NULL )
PARTITION BY RANGE (sale_date)
(PARTITION sales1999_q1
VALUES LESS THAN (TO_DATE(‘1999-04-01’,’YYYY-MM-DD’)
TABLESPACE ts_sale1999q1,
PARTITION sales1999_q2
VALUES LESS THAN (TO_DATE(‘1999-07-01’,’YYYY-MM-DD’)
TABLESPACE ts_sale1999q2,
PARTITION sales1999_q3
VALUES LESS THAN (TO_DATE(‘1999-10-01’,’YYYY-MM-DD’)
TABLESPACE ts_sale1999q3,
PARTITION sales1999_q4
VALUES LESS THAN (TO_DATE(‘2000-01-01’,’YYYY-MM-DD’)
TABLESPACE ts_sale1999q4 );
--values less than (maxvalue)
Áбí·ÖÇø±í£º
create table emp (
empno number(4),
ename varchar2(30),
location varchar2(30))
partition by list (location)
(partition p1 values ('±±¾©'),
partition p2 values ('ÉϺ£','Ìì½ò','ÖØÇì'),
partition p3 values ('¹ã¶«','¸£½¨')
partition p0 values (default)
);
¹þÏ£·ÖÇø£º
create table emp (
empno number(4),
ename varchar2(30),
sal number)
partition by hash (empno)
partitions 8
store in (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);
×éºÏ·ÖÇø£º
·¶Î§¹þÏ£×éºÏ·ÖÇø£º
create table emp (
empno number(4),
ename varchar2(30),
hiredate date)
partition by range (hiredate)
subpartition by hash (empno)
subpartitions 2
(partition e1 values less than (to_date('20020501','YYYYMMDD')),
partition e2 values less than (to_date('20021001','YYYYMMDD')),
partition e3 values less than (maxvalue));
·¶Î§Áбí×éºÏ·ÖÇø£º
CREATE TABLE customers_part (
customer_id NUMBER(6),
cust_first_name VARCHAR2(20),
cust_last_name VARCHAR
Ïà¹ØÎĵµ£º
µÚÒ»²½
¶ÔÊý¾Ý¿âÕûÌå×öÁËÒ»¸öÁ˽â
$ORACLE_HOME/bin/sqlplus "sys/sys061201 as sysdba" << EOF
spool peiyz_healthcheck_info.txt
set linesize 200
set pagesize 1000
set time on
set timing on
col owner for a10
col segment_name for a30
col index_name for a30
col table_name for a ......
1.¾ø¶ÔÖµ
S:select abs(-1) value
O:select abs(-1) value from dual
2.È¡Õû(´ó)
S:select ceiling(-1.001) value
O:select ceil(-1.001) value from dual
3.È¡Õû£¨Ð¡£©
S:select floor(-1.001) value
O:select floor(-1.001) value from dual
4.È ......
1.¾ø¶ÔÖµ
S:select abs(-1) value
O:select abs(-1) value from dual
2.È¡Õû(´ó)
S:select ceiling(-1.001) value
O:select ceil(-1.001) value from dual
3.È¡Õû£¨Ð¡£©
S:select floor(-1.001) value
O:select floor(-1.001) value from dual
4.È¡Õû£¨½ØÈ¡£©
S:select cast(-1.002 as int) value
O:selec ......
ÏîÄ¿ÖÐÐèҪʹÓõ½°´¹æÔòÉú³ÉÐòÁкţ¬¿ªÊ¼µÄ½â¾ö°ì·¨ÊǸù¾ÝÌõ¼þÈ¡µÃ×î´óIDºÅ½øÐзֽâ+1Ôٺϲ¢µÄ½â¾ö·½°¸£¬ºóÀ´·¢ÏÖÈç¹ûÁ½¸ö¿Í»§Í¬Ê±²Ù×÷µÄʱºò³öÏÖÎÊÌ⣺Ìí¼ÓµÄʱºòIDÒѾ´æÔÚ£¬ÔÒòÊÇ£ºAÓû§ÔÚ·ÃÎÊÈ¡µÃµÚMAX(N)¼Ç¼deID£¬µ«ÊÇ»¹Ã»ÓнøÐÐÔö¼Ó²Ù×÷£¬Õâʱ£¬BÓû§Ò²È¡µÃµÚMAX(N)¼Ç¼deID£¬ÕâÊÇ£Á½øÐÐÌí¼Ó²Ù×÷ͬʱ£¬£ÂҲͬʱ½øÐÐ ......
ORACLE 10 ѧϰ±Ê¼Ç-µÚ3¿Î-ÃüÁî¡£
1.view ÏÞÖÆ
create or replace view my_view01
as select * from emp
with check option
/
2.synonym ͬÒå´Ê
create synonym dept for scott.dept;
select * from dept;
3.sysdba ¹«¹²
drop synonym dept;
create public synonym dept for scott.dept;
desc dba_synonyms
......