表格模板-SMSE10管理表对象 精品.ppt

Managing Tables管理表管理表ObjectivesAfter completing this lesson, you should be able todo the following:Identify the various methods of storing dataOutline Oracle data typesDistinguish between an extended versus a restricted ROWIDOutline the structure of a rowCreate regular and temporary tablesManage storage structures within a tableManage index-organized table,clusters and partitioned tableReorganize, truncate, drop a tableAdd, Drop or modify a column within a tableObtain table information Distribution of Rows Within a TableOracle Data TypesCHAR(N), NCHAR(N)VARCHAR2(N),NVARCHAR2(N)NUMBER(P,S)RAW(N)DATETIMESTAMPBLOB, CLOB,NCLOB, BFILELONG, LONG RAWROWID, UROWIDVARRAYTABLEREFData typeBuilt-inUser-definedScalarRelationshipCollectionRepresenting Numeric DataUse the NUMBERNUMBER datatype to store real numbers in a fixed-point or floating-point format. Numbers using this datatype are guaranteed to be portable among different Oracle platforms, and offer up to 38 decimal digits of precision. Oracle 9i DateTime SupportData TypeDescriptionDateFixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-YY) TIMESTAMPVaries from 7 to 11 bytes.A value representing a date and time, including fractional seconds. INTERVAL YEAR TO MONTHFixed at 5 bytes.Stored as an interval of years and monthsINTERVAL DAY TO SECONDFixed at 11 bytes.Stored as an interval of days to hours minutes and secondsTIMESTAMP WITH TIME ZONEFixed at 13 bytes. A value representing a date and time, plus an associated time zone setting. TIMESTAMP WITH LOCAL TIME ZONE Varies from 7 to 11 bytes.TO_DATE (November 13, 1992, MONTH DD, YYYY) TO_DATE(13-NOV-92 10:56 A.M.,DD-MON-YY HH:MI A.M. )CURRENT_DATE CURRENT_TIMESTAMPINTERVAL YEAR TO MONTH Data TypeINTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields.INTERVAL YEAR (year_precision) TO MONTHINTERVAL 123-2 YEAR(3) TO MONTHIndicates an interval of 123 years, 2 months.INTERVAL 123 YEAR(3)Indicates an interval of 123 years 0 months.INTERVAL 300 MONTH(3)Indicates an interval of 300 months.INTERVAL 123 YEARReturns an error, because the default precision is 2, and 123 has 3 digits. CREATE TABLE time_example2 (loan_duration INTERVAL YEAR (3) TO MONTH); INSERT INTO time_example2 (loan_duration) VALUES (INTERVAL 120 MONTH(3); SELECT TO_CHAR( sysdate+loan_duration, dd-mon-yyyy) FROM time_example2; -todays date is 03-May-2003-查询结果：查询结果：03-May-2013Data Types for Storing Large ObjectsOracle provides six data types for storing LOBsCLOB and LONG for large fixed-width character dataNCLOB for large fixed-width national character set dataBLOB and LONG RAW for storing unstructured dataBFILE for storing unstructured data in operating system filesThere are two distinct parts of LOB :LOB value and locator. The LOB column stores a locator to the LOBs valueLOB locatorLOB columnOf a tableLOB ValueData Types for Storing Large ObjectsLONG, LONG RAWSingle column per tableUp to 2 gigabytesSELECT returns dataData stored in-lineNo object type supportSequential access to chunksLOBMultiple columns per tableUp to 4 gigabytesSELECT returns locator Data stored in-line or out-of-lineSupports object typesRandom access to chunksOracle9i Application Developers Guide - Large Objects (LOBs)PDF:1011页页 Data Types for Storing Large ObjectsExternal LOBs(BFILE):Store a locator to the physical file.Definition of BFILE objectsAssociation of BFILE object to corresponding external filesSecurity for BFILEsExternal LOBs do not participate in transactions. Any support for integrity and durability must be provided by the underlying operating system. You cannot locate a single BFILEBFILE on more than one device.Data Types for Storing Large ObjectsInternal LOBs: the LOB value is stored in the databaseManaging internal LOBsPL/SQL package DBMS_LOBOracle Call Interface(OCI)Oracle Objects for object linking and embeddingODBC/JDBCSQLROWID Data TypeUnique identifier for each row in the database.Does not stored explicitly as a column value.Although the ROWID does not directly give the physical address of a row, it can be used to locate the row.ROWID provides the fastest means of accessing a row in a table.ROWIDs are stored in indexes to specify rows with a given set of key valuesOOOOOOBBBBBBFFFRRRData object numberRelative file numberRow numberBlock numberROWID Data TypeData object (segment) identifier Datafile identifier Block identifier Row identifier SELECT DATA_OBJECT_ID FROM ALL_OBJECTS WHERE OBJECT_NAME = Sys_DepotOperateDetail; select extent_id, file_id,block_id,blocks from dba_extents where segment_name=Sys_DepotOperateDetail;ROWID Data TypeBBBBBBBBFFFFRRRRBlock numberRow numberFile number.SELECT department_id, rowid FROM hr.departments;DEPARTMENT_ID ROWID10 AAABQMAAFAAAAA6AAA20 AAABQMAAFAAAAA6AAB30 AAABQMAAFAAAAA6AAC ExampleRestricted ROWID(INDEX)Can identify rows within a segmentNeeds less spaceCollection Data TypesA collection is an object that contains other objects, where each contained object is of the