26.oracleocp題庫(kù)dba經(jīng)典輔助學(xué)習(xí)vldb

1、目標(biāo)改善的VLDB 支持課程目標(biāo):創(chuàng)建并bigfile表空間參照bigfile 表空間解釋ROWID創(chuàng)建臨時(shí)表空間組分配臨時(shí)表空間給用戶(hù)創(chuàng)建列表分區(qū)索引組織的表在表DDL命令中為索引分區(qū)指定創(chuàng)建hash-partit oned全局索引特征hash-partit oned全局索引數(shù)據(jù)1數(shù)據(jù)21創(chuàng)建Bigfile空間數(shù)據(jù)6Bigfile表空間的使用模式只支持本地管理的表空間使用自動(dòng)段空間管理.用邏輯卷或者自動(dòng)管理使用BFTOMF提供完全透明的數(shù)據(jù)文件數(shù)據(jù)5Database Block Sizemended um Number of Extents2 KB100,0004 KB200,0008 KB

2、400,00016 KB800,000Bigfile表空間的好處顯著容量通過(guò)創(chuàng)建表空間(這個(gè)磁盤(pán)空間管理的主要)來(lái)簡(jiǎn)化大數(shù)據(jù)庫(kù)中數(shù)據(jù)文件的管理One-to-one map4 billion8EBblocks數(shù)據(jù)4BFT nBFT 1Bigfile表空間 :概覽Bigfile表空間包含一個(gè)單獨(dú)文件最大文件長(zhǎng)度可以從8TB到128T表空間在邏輯上等同數(shù)據(jù)文件DatabaseDaDatafifile數(shù)據(jù)3TablespaceIGFILESQL 語(yǔ)句修改和補(bǔ)充BFT 和 SQL 語(yǔ)句：例如DatabaseTablespaceData fileSmallfiigfileData file數(shù)據(jù)7數(shù)據(jù)82D

3、BMS_UTILITY 包和 BFTbigfiles不需要數(shù)據(jù)12DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK()DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE()配置參數(shù)和 BFTControl filesDB_FILESData filesSmallfilesBigfiles數(shù)據(jù)11MAXDATAFILESBigfile表空間和 DBVERIFY數(shù)據(jù)10$dbv FIFile1 START=10001$dbv FILE=SFile2$dbv FIFile1 START=1 END=10000$dbv FILE=SFile1Bigfil

4、e tablespaceBFile1Smallfile tablespaceSFile1SFile2數(shù)據(jù)字典的修改和補(bǔ)充數(shù)據(jù)9SELECT name, bigfile FROM V$TABLESPACE;SELECT tablespace_name, bigfile FROM DBA_TABLESPA;SELECT property_value FROM database_propertiesWHERE property_name=DEFAULT_TBS_TYPE;ALTER TABLESPACE users AUTOEXTEND ON ;ALTER TABLESPACE users RESI

5、ZE 2G;CREATE BIGFILE UNDO TABLESPACE bundo_tbsDATAFILE /u1/data/bundo_tbs.dbf SIZE 1G;ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;CREATE DATABASESET DEFAULT BIGFILE TABLESPACEDATAFILE /u0/data/system.dbf SIZE 200MSYSAUX DATAFILE /u0/data/sysaux.dbf SIZE 300M SMALLFILE DEFAULT TEMPORARY TABLESPAtem

6、p_tbsTEMPFILE /u3/data/stemp_tbs1.dbf SIZE 60M SMALLFILE UNDO TABLESPAundo_tbs DATAFILE/u2/data/sundo_tbs1.dbf SIZE 100M;K | M | G | TBIGFILE | SMALLFILE擴(kuò)展ROWID 格式和 BFT移植和bigfile表空間DBMS_ROWID.ROWID_BLOCK_NUMBERSmallfile tablespaBigfile tablespaBBBBBB RRROOOOOO LLL LLLLLL RRRRow numberData object num

7、berData blockData objectnumbernumberEncoded block numberRelative file numberRow number數(shù)據(jù)1314數(shù)據(jù)3創(chuàng)建臨時(shí)表空間組數(shù)據(jù)18臨時(shí)表空間組的優(yōu)點(diǎn)允許一個(gè)用戶(hù)使用多個(gè)表空間:多個(gè)會(huì)話(huà)中的同一用戶(hù)一個(gè)特別的并行操作HRSerialHRParallelTablespaceTablespaceTablespaceTEMP1TEMP2TEMP3Temporary tablespace group TEMP數(shù)據(jù)17臨時(shí)表空間組:總覽把臨時(shí)表空間群組合在一起特點(diǎn):至少有一個(gè)臨時(shí)表空間相同命名空間的作為一個(gè)表空間在第一次分

8、配時(shí)隱式創(chuàng)建沒(méi)有明確的刪除默認(rèn)臨時(shí)表空間組 TEMP默認(rèn)表空間EXLETablespaceTablespaceTEMP1TEMPn數(shù)據(jù)16DBMS_ROWIDY_數(shù)據(jù)15SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID,BIGFILE)FROM employees;PE_ININFOROWID_RELATIVE_FNOTS_TROWIDROWID_BLOCK_NUMBEROOOOOOData PumpCREATE TABLE AECTALTER TABLE MOVEBIGFILE tablespaceSMALLFILE tablespace臨時(shí)表空間組SQL

9、：例如數(shù)據(jù)204數(shù)據(jù)庫(kù)控制: 分區(qū)數(shù)據(jù)24數(shù)據(jù)庫(kù)控制:創(chuàng)建一個(gè)分區(qū)數(shù)據(jù)23數(shù)據(jù)字典修改數(shù)據(jù)22SELECT group_name, tablespace_name FROM DBA_TABLESPACE_GROUPS;臨時(shí)表空間 SQL：例如數(shù)據(jù)21ALTER USER hr TEMPORARY TABLESPACE group2;CREATE USER hr IDENTIFIED BY hr DEFAULT TABLESPACE TS1 TEMPORARY TABLESPACE group2;ALTER DATABASE DEFAULT TEMPORARY TABLESPACE group2

10、;ALTER TABLESPACE temp1 TABLESPACE GROUP group2;ALTER TABLESPACE temp2 TABLESPACE GROUP ;ALTER TABLESPACE temp3 TABLESPACE GROUP group2;CREATE TEMPORARY TABLESPACE temp3 TEMPFILE tmp3.fSIZE 50M TABLESPACE GROUP ;CREATE TEMPORARY TABLESPACE temp2 TEMPFILE tmp2.fSIZE 200M TABLESPACE GROUP group2;CREAT

11、E TEMPORARY TABLESPACE temp1 TEMPFILE tmp1.fSIZE 100M TABLESPACE GROUP group1;臨時(shí)表空間組數(shù)據(jù)19分區(qū)IOT增強(qiáng)本地分區(qū)索引增強(qiáng)Global indexmaenanceLocal bitmap IndexesList-partitioned IOTsLOBs數(shù)據(jù)25數(shù)據(jù)265Hash-Partitioned全局索引:優(yōu)點(diǎn)1000, 1001, 1002, 1003,1000, 1RNonreverse keyH ANAGSEReverse keyH1000, 1001, 1002, 1003,1000, 1001,

12、1002, 1003,數(shù)據(jù)30001, 1002, 1003,爭(zhēng)奪場(chǎng)景Sequen 10001002 Index10031001數(shù)據(jù)29Hash-Partitioned全局索引: 概述Range-partitioned global index900, 901,1000, 1001, 1000 SELECT index_name, partition_name,tablespace_name, susFROMuser_ind_partitionsWHERE index_name like SALES%;INDEX_NAMEPARTITION_NAME TABLESPACE_NAME SUS S

13、ALES_TEST_IX S_Q1USERSUSABLESALES_TEST_IX S_Q2USERSUSABLESALES_TEST_IX S_Q3EXLEUSABLESQL ALTER TABLE sales_test2MOVE PARTITION s_q3 TABLESPACE USERS2 3(sales_test_ix(PARTITION s_q3 TABLESPACE);EXLEUPDATE INDEXES創(chuàng)建Hash-Partitioned全局索引增加和整合分區(qū)數(shù)據(jù)31數(shù)據(jù)326Bitmap 索引增強(qiáng)Set COMPATIBLE to 10.0.0.0Severe slowdow

14、n before raisingCOMPATIBLE?slowdown after raisingYesCOMPATIBLE?數(shù)據(jù)36rebuildYesCreate new bitmap indexesFor each existing bitmap index使用案例PQ1 PQ2PQ3 PQ4PQ5 PQ6PQ7 PQ8HashPQ1PQ2PQ3Range200050008000 SELECT /*+ PARALLEL_INDEX(oi,ghoi_ix,8) */count(*)FROM order_items oiWHERE order_id BETN 1000 AND 6000;不支

15、持的操作1234數(shù)據(jù)34ALTER INDEX MODIFY PARTITIONALTER INDEX REBUILDALTER TABLE MERGE INDEX PARTITIONSALTER TABLE SPLIT INDEX PARTITIONRange 和 Hash Global Index令數(shù)據(jù)33DROP INDEXALTER INDEX RENAME PARTITIONALTER INDEX UNUSABLEALTER INDEX MODIFY DEFAULTRIBUTES ALTER INDEX MODIFY PARTITION UNUSABLEALTER INDEX mod

16、ify_index_storageALTER INDEX REBUILD PARTITIONALTER INDEX ghoi_ix COALESCE PARTITION PARALLEL; 2ALTER INDEX ghoi_ix ADD PARTITION p41TABLESPACE tbs_5 PARALLEL;SQL CREATE INDEX ghoi_ixON order_items (order_id) GLOBALPARTITION BY HASH (order_id)PARTITIONS 4STORE IN (tbs_1, tbs_2, tbs_3, tbs_4);SQL CREATE INDEX ghoi_ixON order_items (order_id) GLOBALPARTITION BY HASH (order_id) (PARTITION p1 TABLESPACE tbs_1,PARTITI