1 / 10

Department of Computer and Information Science, School of Science, IUPUI

Department of Computer and Information Science, School of Science, IUPUI. Tablespaces. Dale Roberts, Lecturer Computer Science, IUPUI E-mail: droberts@cs.iupui.edu. Tablespaces and terminology. Tablespace Tablespaces are a logical organization of space.

zuzela
Download Presentation

Department of Computer and Information Science, School of Science, IUPUI

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Department of Computer and Information Science,School of Science, IUPUI Tablespaces Dale Roberts, Lecturer Computer Science, IUPUI E-mail: droberts@cs.iupui.edu

  2. Tablespacesand terminology • Tablespace • Tablespaces are a logical organization of space. • Tablespaces owns the database’s datafiles. • Database objects are stored within tablespaces. • Database: a logical collection of shared data stored in tablespaces. • File: A physical datafile belonging to a single tablespace. • Segment: A set of one or more extents that contains all the data for a specific structure within a tablespace. (TABLE, INDEX, CLUSTER, ROLLBACK, TEMP, etc.) • Extent: A set of contiguous data blocks with in a database that make up a segment. • Block: One or more physical file blocks allocated from an existing datafile. 2

  3. Oracle Tablespace Architecture TABLESPACE Segment 112K (Table, Index, etc.) Extent 28K Extent 84K Database Blocks datafile 2 datafile 1 datafile 3 datafile 4 datafile 5

  4. Tablespace Data Model • Entity-Relationship Diagram showing: • Database • Tablespace • Extents • Segments • Files • Blocks 4

  5. Tablespaceclause syntax • Tablespace Clause: indicates Tablespace and other storage options for tables, indexes, constraints. • Storage Clause: indicates how extents are managed, overrides the default storage parameters of tablespace. • Syntax: CREATE TABLEtablename(column_a type NOT NULL,column_b type,...) STORAGE ( INITIAL 100K NEXT 20K MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 50 ) TABLESPACE USER_DATA PCTFREE 5 PCTUSED 30; The number of create table options is voluminous: NOLOGGING, NOPARALLEL, etc. 5

  6. Tablespaceand Segment Example SQL> select file_name, tablespace_name, bytes, blocks from dba_data_files FILE_NAME TABLESPACE_NAME BYTES BLOCKS ------------------------------------------ --------------- ---------- ---------- /opt/oracle/u1/oradata/OS80/users01.dbf USERS 1048576 512 /opt/oracle/u1/oradata/OS80/tools01.dbf TOOLS 26214400 12800 /opt/oracle/u1/oradata/OS80/temp01.dbf TEMP 6291456 3072 /opt/oracle/u1/oradata/OS80/rbs01.dbf RBS 15728640 7680 /opt/oracle/u1/oradata/OS80/system01.dbf SYSTEM 83886080 40960 /opt/oracle/u1/oradata/OS80/n311_t1.dbf N311TBS 95180800 46475 /opt/oracle/u1/oradata/OS80/csgrad_t1.dbf USERDATA_GRAD 10485760 5120 SQL> l 1 select tablespace_name, segment_name, segment_type, bytes, blocks, extents 2 from dba_segments 3* where owner = 'SCOTT' TABLESPACE_NAME SEGMENT_NA SEGMENT_TYPE BYTES BLOCKS EXTENTS --------------- ---------- ------------------ ---------- ---------- ---------- SYSTEM BONUS TABLE 10240 5 1 SYSTEM SALGRADE TABLE 10240 5 1 SYSTEM WORKER TABLE 10240 5 1 SYSTEM TEMP_USERS TABLE 10240 5 1 SYSTEM LEDGER TABLE 20480 10 2 SYSTEM DEPT TABLE 10240 5 1 SYSTEM EMP TABLE 10240 5 1 SYSTEM PK_DEPT INDEX 10240 5 1 SYSTEM PK_EMP INDEX 10240 5 1 9 rows selected. 6

  7. CREATE INDEX • Indexes: • Contains values of data from a table and their location • Pro: Used to speed data retrieval, also can enforce uniqueness. • Con: Slows updates and deletes. • When to use: if columns are typically used in where clause searches • Primary keys and unique constraints create a unique index automatically. • NULL values are not indexed. • Syntax: • CREATE [BITMAP|UNIQUE] INDEX index ON table(COL1 [, COL2…] )[REVERSE]; • Other Oracle Index Features: • Reverse indexes builds an index on the indexed column in reverse byte order thus increasing the dispersal of values. • Bitmap indexes, new in 8 or 8i ?, associates a bitmap to values and only stores the bitmap in the index. Use with low cardinality values (e.g. T/F) • Function based indexes, new in 8i. Normal indexes are not used if a function is used on the where clause column that you’re searching on. 7

  8. CREATE CLUSTER • Clusters: • Stores different tables physically together. • The cluster key is the column that the data would normally be joined together with. • Tables that share the same cluster are actually stored together and I/O is decreased when the two tables are joined. • Syntax Example: • Create cluster BOOKandAUTHOR( • col1 varchar2(1000)); • Create table BOOKSHELF (title varchar2(100) primary key, • … ) • cluster BOOKandAUTHOR(title); 8

  9. CREATE SEQUENCE • Sequences: • Creates a unique sequentially valued number. • Used during insert and update commands usually. • Typically used to create a primary key. • NextVal and CurrVal get the next/current value of the sequence. • Syntax: • Create sequence myseq increment by 1 start with 1; • Insert into CUSTOMER (id, name) values (myseq.nextval, ‘a’); • Update OTHERTABLE set id = myseq.currval; 9

  10. Acknowledgements • Loney, Oracle Database 10g The Complete Reference

More Related