表DUAL是一个公共表可用它做些计算或返回函数处理结果外部表使用ORGANIZATION external来创建一个外部表它是一个只读表其元数据存储在数据库中但其数据存储在数据库之外。[oracleocp~]$ cat/home/oracle/test.txt1,102,20SQLcreatedirectory testdiras/home/oracle;SQLgrantread,writeondirectory testdirtohr;SQLconn hr/123456SQLcreatetabletest_exter(hid1 number,hid2 number)ORGANIZATION external(typeoracle_loaderDEFAULTDIRECTORY testdir ACCESS PARAMETERS(RECORDS DELIMITEDBYNEWLINEfieldsterminatedby,)location(test.txt));SQLselect*fromtest_exter;HID1 HID2---------- ----------110220–ORGANIZATION external --外部表的关键字–type oracle_loader --外部表有两种类型这里使用oracle_loader,另一种类型是oracle_datapump–DEFAULT DIRECTORY testdir --外部表使用文件存放的directory目录–ACCESS PARAMETERS --参数列表RECORDS DELIMITED BY NEWLINE表示行由换行符来进行分隔fields terminated by ,表示字段由逗号来进行分隔–location --外部表使用的文件名称临时表事务级临时表的数据只在当前事务有效通过语句ON COMMIT DELETE ROWS 指定默认方式如下两行结果一致。CREATE GLOBAL TEMPORARY TABLE TEMP_TAB1(HID NUMBER);CREATE GLOBAL TEMPORARY TABLE TEMP_TAB1(HID NUMBER) ON COMMIT DELETE ROWS;SQLCREATEGLOBALTEMPORARYTABLETEMP_TAB1(HID NUMBER);Tablecreated.SQLinsertintoTEMP_TAB1values(1);1rowcreated.SQLselect*fromTEMP_TAB1;HID----------1SQLcommit;Commitcomplete.SQLselect*fromTEMP_TAB1;norowsselected会话级临时表的数据只在当前会话有效通过语句ON COMMIT PRESERVE ROWS指定。CREATE GLOBAL TEMPORARY TABLE TEMP_TAB2(HID NUMBER) ON COMMIT PRESERVE ROWS;会话1SQLCREATEGLOBALTEMPORARYTABLETEMP_TAB2(HID NUMBER)ONCOMMITPRESERVEROWS;Tablecreated.SQLinsertintoTEMP_TAB2values(1);1rowcreated.SQLselect*fromTEMP_TAB2;HID----------1SQLcommit;Commitcomplete.SQLselect*fromTEMP_TAB2;HID----------1在开一个会话2SQLinsertintoTEMP_TAB2values(2);1rowcreated.SQLcommit;Commitcomplete.SQLselect*fromTEMP_TAB2;HID----------2回到会话1SQLselect*fromTEMP_TAB2;HID----------1以上说明会话1、会话2互不影响进一步得出结论临时表不存在并发就不存在锁每个人看到的都是自己的东西 。SQLcreateviewvTEMP_TAB2asselect*fromTEMP_TAB2;--可以对临时表创建视图Viewcreated.SQLcreateindexidx_vTEMP_TAB2onTEMP_TAB2(hid);--临时表在用的时候无法创建索引createindexidx_vTEMP_TAB2onTEMP_TAB2(hid)*ERROR at line1: ORA-14452: attempttocreate,alterordropanindexontemporarytablealreadyinuseSQLexitsqlplus hr/oracleSQLcreateindexidx_vTEMP_TAB2onTEMP_TAB2(hid);--临时表不用的时候可以创建索引Indexcreated.分区表分区表产生的理由随着表的不断增大对表的查询会越来越慢。对于数据库中的超大型表可以通过把它的数据分成若干个片段每一个片段我们称为一个单个的分区。对于分区的访问我们不需要使用特殊的SQL查询语句或特定的DML语句直接单独的操作单个分区而不是整张表。表进行分区后逻辑上表仍然是一张完整的表。例如将不同年份的销售数据放在不同的表空间比如01年的销售数据存放到01分区02年的销售数据存放到02分区依次类推。对于外部应用程序来说虽然存在不同的分区且数据位于不同的表空间但逻辑上仍然是一张表。比喻分区表就像是一栋楼分区就像是楼层。如果你能知道自己的数据在几层你就能很快找到。反之如果你没有分区就得按照索引或者全表扫描去找必然要慢数据量越大就越慢。分区的优点1、每个分区都是一个独立的SEGMENT段由于将数据分散到各个分区中不同分区的数据可以放置到不同的表空间不同的表空间又可以映射到不同的物理磁盘上减少了IO争用也减少了数据损坏的可能性2、可以对单独的分区进行操作提高性能、可管理性、可用性3、要查询的数据只在某个分区内时执行计划可能只去扫描这个分区而不用扫描全表的所有分区。分区类型1、范围分区range)2、哈希分区hash3、列表分区list4、关联分区(Reference)分区表每个分区就是一个单独的段CREATETABLEtab_part1(hid number,hname varchar2(20))PARTITIONBYRANGE(hid)(PARTITIONp01VALUESLESS THAN(100)TABLESPACEusers,PARTITIONp02VALUESLESS THAN(200)TABLESPACEusers,PARTITIONp03VALUESLESS THAN(300)TABLESPACEusers,PARTITIONp04VALUESLESS THAN(MAXVALUE)TABLESPACEusers);--以上users表空间可以换成不同的表空间名称比分区p01对应users01表空间p02对应users02。。。insertintotab_part1values(1,1);insertintotab_part1values(101,101);insertintotab_part1values(201,201);insertintotab_part1values(301,301);CREATETABLEtab_nopart1(hid number,hname varchar2(20));insertintotab_nopart1values(1,1);insertintotab_nopart1values(101,101);insertintotab_nopart1values(201,201);insertintotab_nopart1values(301,301);select*fromuser_tab_partitionswheretable_nameTAB_PART1select*fromdba_segmentswheresegment_namein(TAB_PART1,TAB_NOPART1)--TAB_PART1有四行说明有4个段--TAB_NOPART1只有一行说明只有1个段select*fromtab_part1partition(p01);--只查询某个分区的值select*fromtab_part1partition(p01)unionallselect*fromtab_part1partition(p02);--查询多个分区SQLsetautotrace traceonlySQLselect*fromtab_part1wherehid201;------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Pstart|Pstop|------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|25|27(0)|00:00:01||||1|PARTITIONRANGE ITERATOR||1|25|27(0)|00:00:01|3|4||*2|TABLEACCESSFULL|TAB_PART1|1|25|27(0)|00:00:01|3|4|--------------------------------------------------------------------------------------------------------执行计划只去3-4分区内找数据不用扫描全表的所有分区分区表的索引分为local和global两类local索引分区和表分区形式一样global索引分区和表分区无关createindextt_01ontab_part1(hid)local;select*fromdba_segmentswheresegment_nameTT_01select*fromuser_ind_partitionswhereindex_nameTT_01select*fromuser_tab_partitionswheretable_nameTAB_PART1--local索引分区和表分区的形式一样dropindextt_01createindextt_01ontab_part1(hid)globalpartitionbyrange(hid)(partitioni_p1valuesless than(200)tablespaceexample,partitioni_p2valuesless than(maxvalue)tablespaceusers);select*fromdba_segmentswheresegment_nameTT_01select*fromuser_ind_partitionswhereindex_nameTT_01select*fromuser_tab_partitionswheretable_nameTAB_PART1--Global索引分区和表分区的形式无关Interval自动分区Interval支持的Range分区键类型只有number、date、timestamp三种类型CREATE TABLE tab_part2 (hid number,hname varchar2(20))PARTITION BY RANGE(hid) INTERVAL (100)( PARTITION p01 VALUES LESS THAN (100));number类型建立的分区表说明100之前的数据放入P01分区中之后的数据每100放入一个新一个分区比如102放入一个分区p02203放入一个分区p03insertintotab_part2values(1,1);insertintotab_part2values(101,101);insertintotab_part2values(201,201);select*fromuser_tab_partitionswheretable_nameTAB_PART2;--p01后面的分区名字系统自动建立CREATETABLEtab_part3(hdatedate,hname varchar2(20))PARTITIONBYRANGE(hdate)INTERVAL(NUMTODSINTERVAL(1,DAY))(PARTITIONp01VALUESLESS THAN(to_date(2018-01-01,yyyy-mm-dd)));DATE类型建立的分区表说明2018-01-01之前的数据放入P01分区中之后的数据每天一个分区insertintotab_part3values(sysdate-365,2017);insertintotab_part3values(sysdate,2018);insertintotab_part3values(sysdate1,2018);select*fromuser_tab_partitionswheretable_nameTAB_PART3;--p01后面的分区名字系统自动建立当如如果建表的时候没有指定interval修改每100为单位分区则可以手工修改如下alter table tablename set INTERVAL (100);–alter table tab_part1 set INTERVAL (100);–报错ORA-14759,因为存在LESS THAN (MAXVALUE)这个分区需要先把这个分区删除–alter table tab_part1 drop partition p04;–alter table tab_part1 set INTERVAL (100);–之前已经创建的分区P01P02P03还存在后面新增的分区名以SYS_名称开头。如果drop的分区p04已经有数据了那么就不能直接去drop这个分区了因为这个分区P04一旦drop掉这个已经存在分区P04中的数据也被drop掉了修改每天分区则可以手工修改如下alter table tablename set INTERVAL (NUMTODSINTERVAL(1, ‘DAY’));修改为每周分区则如下alter table tablename set INTERVAL(numtodsinterval(7,‘day’));修改为每月分区则如下alter table tablename set INTERVAL(numtoyminterval(1,‘MONTH’));修改为每年分区则如下alter table tablename set INTERVAL(numtoyminterval(1,‘YERA’));物化视图物化视图本质上是一种预先计算并存储查询结果的数据库对象它允许你将查询结果存储在一个物理位置以便快速访问而不是每次需要数据时都重新执行复杂的查询。这是一种以“空间换时间”的策略即在存储空间上做出一定的牺牲来换取查询性能的显著提升。适合于复杂查询加速和数据仓库预计算的场景。曾经遇到的案例一个系统视图dba_network_acl_privileges非常慢该系统视图很复杂直接select * from dba_network_acl_privileges显示的执行计划就非常复杂调用了大量的系统对象。我们使用物化视图代替该系统视图后性能提升明显create materialized view dba_acl_new refresh complete on demand as select * from dba_network_acl_privileges高频小更新使用FAST刷新物化视图日志。低频大变更采用COMPLETE刷新减少日志开销。Rowid 模式1.创建基表无主键CREATETABLEproducts(product_code VARCHAR2(50),product_name VARCHAR2(100),price NUMBER);CREATETABLEsales(sale_id NUMBER,product_code VARCHAR2(50),sale_dateDATE,quantity NUMBER);-- 插入测试数据INSERTINTOproductsVALUES(P001,Laptop,1000);INSERTINTOproductsVALUES(P002,Phone,500);INSERTINTOsalesVALUES(1,P001,SYSDATE,2);INSERTINTOsalesVALUES(2,P002,SYSDATE,3);COMMIT;2、创建物化视图日志WITHROWID 由于基表无主键物化视图日志必须显式包含 ROWIDCREATEMATERIALIZEDVIEWLOGONproductsWITHROWID INCLUDING NEWVALUES;CREATEMATERIALIZEDVIEWLOGONsalesWITHROWID INCLUDING NEWVALUES;3、创建物化视图FAST刷新3.1CREATEMATERIALIZEDVIEWsales_summary_mv REFRESH FASTONDEMAND-- 手动刷新模式后续通过定时任务触发ASSELECTp.product_code,p.product_name,p.price*s.quantityAStotal_amount,p.rowidASprod_rowid,-- 必须包含基表的 ROWIDs.rowidASsale_rowid-- 必须包含基表的 ROWIDFROMproducts pJOINsales sONp.product_codes.product_code;3.2CREATEORREPLACEPROCEDURErefresh_sales_mvASBEGINDBMS_MVIEW.REFRESH(sales_summary_mv,F);-- F 表示 FAST 刷新END;3.3BEGINDBMS_SCHEDULER.CREATE_JOB(job_nameJOB_REFRESH_SALES_MV,job_typeSTORED_PROCEDURE,job_actionrefresh_sales_mv,start_dateSYSTIMESTAMP,repeat_intervalFREQMINUTELY; INTERVAL180,-- 每3小时执行一次enabledTRUE);END;--From语句列表中的所有基表没有主键的话那么这些基表的rowid必须出现在select中--3.1中REFRESH FAST ON DEMAND改成REFRESH FAST ON COMMIT的话就不需要3.2和3.3了因为REFRESH FAST ON COMMIT的话基表数据一提交物化视图就刷新不过这样对性能影响比较大一般不推荐--3.1中REFRESH FAST ON DEMAND改成REFRESH FAST START WITH SYSDATE NEXT SYSDATE 3/24的话就不需要3.2和3.3了因为会自动建立job没隔3小时定时刷新基表只有一张表时使用REFRESHWITHROWID就可以了CREATETABLEtable1(sale_id NUMBER,product_code VARCHAR2(50),sale_dateDATE,quantity NUMBER);INSERTINTOtable1VALUES(1,P001,SYSDATE,2);INSERTINTOtable1VALUES(2,P002,SYSDATE,3);commitCREATEMATERIALIZEDVIEWLOGONtable1WITHROWID INCLUDING NEWVALUES;CREATEMATERIALIZEDVIEWtable2_mv REFRESHWITHROWID FASTSTARTWITHSYSDATENEXTSYSDATE1/4096ASSELECT*fromtable2外键约束父表不能随便删除数据子表不能随便新增数据外键约束之父表不能随便删除数据的例子createtabletest1(hid numberprimarykey,hname varchar2(10));createtabletest2(hid1 number(10)constrainthid_pkREFERENCEStest1(hid),hname1 varchar2(10));insertintotest1values(1,1);insertintotest2values(1,100);deletefromtest1;--报错解决方法1(先删除子表关联的记录再删除父表的记录)deletefromtest2;deletefromtest1;解决方法2(子表的外键约束加上ON DELETE CASCADE父记录删除的时候子记录被级联删除)insertintotest1values(1,1);insertintotest2values(1,100);altertabletest2dropconstrainthid_pk;ALTERTABLEtest2ADDCONSTRAINThid_pkFOREIGNKEY(hid1)REFERENCEStest1(hid)ONDELETECASCADE;deletefromtest1;解决方法3(保留子表记录但是子表对应字段值变成null如下test2的hid1为null了)insertintotest1values(1,1);insertintotest2values(1,100);altertabletest2dropconstrainthid_pk;ALTERTABLEtest2ADDCONSTRAINThid_pkFOREIGNKEY(hid1)REFERENCEStest1(hid)ONDELETESETNULL;deletefromtest1;外键约束之子表不能随便新增数据的例子createtabletest1(hid numberprimarykey,hname varchar2(10));createtabletest2(hid1 number(10)constrainthid_pkREFERENCEStest1(hid),hname1 varchar2(10));insertintotest1values(1,1);insertintotest2values(3,100);ORA-02291: 违反完整约束条件(test2.hid_pk)-未找到父项关键字