1 / 37

Schema Objects the different types of objects contained in a user’s schema are: tables views sequences synonyms indexes

Schema Objects the different types of objects contained in a user’s schema are: tables views sequences synonyms indexes clusters hash clusters Other schema objects such as packages, and triggers will be discussed later. Schema Objects. There is a schema associated with each database

muhammad
Download Presentation

Schema Objects the different types of objects contained in a user’s schema are: tables views sequences synonyms indexes

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. Schema Objects • the different types of objects contained in a user’s schema are: • tables • views • sequences • synonyms • indexes • clusters • hash clusters • Other schema objects such as packages, and triggers will be discussed later.

  2. Schema Objects • There is a schema associated with each database • A schema is a collection of schema objects. Examples of schema objects include tables, views, sequences, synonyms, indexes, clusters, database links, procedures, and packages. • Schema objects are logical data storage structures. • Schema objects do not have a one–to–one correspondence to physical files on disk that store their information. • There is no relationship between schemas and tablespaces: a tablespace can contain objects from different schemas, and the objects for a schema can be contained in different tablespaces.

  3. How Table Data Is Stored • Schema Objects: TABLES • Tables are the basic unit of data storage in an Oracle database. • Data is stored in rows and columns. • You define a table with a table name (such as EMP) and set of columns. You give each column a column name (such as EMPNO, ENAME, and JOB), a datatype (such as VARCHAR2, DATE, or NUMBER), and a width (the width might be predetermined by the datatype, as in DATE) or precision and scale (for columns of the NUMBER datatype only). • A row is a collection of column information corresponding to a single record. • You can optionally specify rules for each column of a table. These rules are called integrity constraints. One example is a NOT NULL integrity constraint. • Once you create a table, you insert rows of data using SQL statements. • Table data can then be queried, deleted, or updated using SQL.

  4. CREATE TABLE • When you create a non-clustered table, Oracle automatically allocates a data segment in a tablespace to hold the table’s future data. • To create a table, the basic structure to hold user data, specifying the following information: • column definitions • integrity constraints • the table’s tablespace • storage characteristics • an optional cluster • data from an arbitrary query • degree of parallelism used to create the table and the default degree of parallelism for queries on the table

  5. Example I • To define the EMP table owned by SCOTT, you could issue the following statement: • CREATE TABLE scott.emp • (empno NUMBER CONSTRAINT pk_emp PRIMARY KEY, • ename VARCHAR2(10) • CONSTRAINT nn_ename NOT NULL • CONSTRAINT upper_ename • CHECK (ename =UPPER(ename)), • job VARCHAR2(9), • mgr NUMBER • CONSTRAINT fk_mgr REFERENCES scott.emp(empno), • hiredate DATE DEFAULT SYSDATE, • sal NUMBER(10,2) CONSTRAINT ck_sal • CHECK (sal > 500), • comm NUMBER(9,0) DEFAULT NULL, • deptno NUMBER(2) • CONSTRAINT nn_deptno NOT NULL • CONSTRAINT fk_deptno • REFERENCES scott.dept(deptno) ) • PCTFREE 5 • PCTUSED 75 ; • Notes • This table contains 8 columns. For example, the EMPNO column is of datatype NUMBER and has an associated integrity constraint named PK_EMP. • The HIRDEDATE column is of datatype DATE and has a default value of SYSDATE. • PCTFREE of 5 indicates that 5% of space is reserved for future updates • PCTUSED of 75, indicates that a minimum of 75% of the table should be used on each data block • The constraint on the mgr field indicates that such a value (mgr’s id) must also be found in the scott.emp table (a recursive reference)

  6. Example II • To define the sample table SALGRADE in the HUMAN_RESOURCE tablespace with a small storage and limited allocation potential, issue the following statement: • CREATE TABLE salgrade • ( grade NUMBER CONSTRAINT pk_salgrade • PRIMARY KEY • USING INDEX TABLESPACE users_a, • losal NUMBER, • hisal NUMBER ) • TABLESPACE human_resource • STORAGE (INITIAL 6144 • NEXT 6144 • MINEXTENTS 1 • MAXEXTENTS 5 • PCTINCREASE 5); • Notes • The above statement defines a PRIMARY KEY constraint on the GRADE column and specifies that the index Oracle should generate to enforce this constraint is created in the USERS_A tablespace. • An INITIAL block of 6144 bytes is allocate to EMP when the table is first created. • If a growth is needed the NEXT block will include another 6144 bytes. A maximum of 5 extents (MAXEXTENTS) is given to the table. • A MINEXTENTS of 1 indicates that 1 block (of 6144 bytes) must be allocated when the table is first created. • After the second extend the block size should increase by 5% (PCTINCREASE 5).

  7. example : یمكن كتابة Dictionary Managed Table لتكوین CREATE TABLE HR.TEST2 (FIRST VARCHAR2 (10), LAST NUMBER) TABLESPACE EXAMPLE PCTFREE 10 PCTUSED 20 MAXTRANS 200 INITRANS 1 STORAGE (INITIAL 200K NEXT 200K PCTINCREASE 50 MINEXTENTS 1 MAXEXTENTS 5 FREELISTS 1 FREELIST GROUPS 1 ) ; : Storage في جملة ال .Extent تحدد حجم أول :INITIAL . الثانیة Extent حیث تحدد جحم ال ،Extent عند زیادة حجم البیانات یتطلب ذلك اضافة :NEXT التالیة (لیست الثالثة فقط ، إنما كل ما یأتي بعد الثانیة) Extents تحدد حجم ال :PCTINCREASE +200 نصف حجم K) 300K التالیة Extents باضافة نسبة مئویة. مثلاً في ھذه الحالة یصبح حجم ال .(Extent %50 ) ال ) .Table التي یمكن أن تخصص لل Extents الحد الأدنى من ال :MINEXTENT .Table التي یمكن أن تخصص لل Extents الحد الأقصى من ال :MAXEXTENTS .Table التي یمكن أن تخصص لل Freelist عدد مجموعات ال :FREELIST GROUP ضمن المجموعة. Freelist عدد ال :FREELISTS

  8. SEGMENT EXTENT EXTENT

  9. Example III Assuming you have the parallel query option, then the fastest method to create a table that has the same columns as the EMP table, but only for those employees in department 10, is to issue a command similar to the following: CREATE TABLE emp_tmp UNRECOVERABLE PARALLEL (DEGREE 3) AS SELECT * FROM emp WHERE deptno = 10; Notes • The UNRECOVERABLE keyword speeds up table creation because there is no overhead in generating and logging redo information. • Using parallelism speeds up the creation of the table because three processes are used to create the table. • After the table is created, querying the table is also faster because the same degree of parallelism is used to access the table.

  10. Row Format and Size • Oracle stores each row of a database table as one or more row pieces. • If an entire row can be inserted into a single data block, Oracle stores the row as one row piece. However, if all of a row’s data cannot be inserted into a single data block or an update to an existing row causes the row to outgrow its data block, Oracle stores the row using multiple row pieces. • A data block usually contains only one row piece per row. When Oracle must store a row in more than one row piece, it is “chained” across multiple blocks. • A chained row’s pieces are chained together using the ROWIDs of the pieces. • Each row piece, chained or unchained, contains a row header and data for all or some of the row’s columns. • To conserve space, a null in a column only stores the column length (zero). Oracle does not store data for the null column. • Also, for trailing null columns, Oracle does not store the column length because the row header signals the start of a new row (for example, the last three columns of a table are null, thus there is no information stored for those columns). • The ROWID identifies each row piece by its location or address. Once assigned, a given row piece retains its ROWID until the corresponding row is deleted • A null is the absence of a value in a column of a row. Nulls indicate missing, unknown, or inapplicable data. A null should not be used to imply any other value, such as zero. A column allows nulls unless a NOT NULL or PRIMARY KEY integrity constraint has been defined for the column, in which case no row can be inserted without a value for that column.

  11. VIEWS • A view is a tailored presentation of the data contained in one or more tables (or other views). • A view takes the output of a query and treats it as a table; therefore, a view can be thought of as a “stored query” or a “virtual table”. • You can use views in most places where a table can be used. Example • The EMP table has several columns and numerous rows of information. If you only want users to see five of these columns, or only specific rows, you can create a view of that table for other users to access. • Figure 3 shows an example of a view called STAFF derived from the base table EMP. Notice that the view shows only five of the columns in the base table.

  12. Storage for Views Since views are derived from tables, many similarities exist between the two. For example • You can define views with up to 254 columns, just like a table. • You can query views, and with some restrictions users can update, insert into, and delete from views. • All operations performed on a view actually affect data in the base tables of the view and are subject to the integrity constraints and triggers of the base tables. Note You cannot explicitly define integrity constraints and triggers on views, but you can define them for the underlying base tables referenced by the view. Unlike a table, a view is not allocated any storage space (like a table), nor does a view actually contain data; rather, a view is defined by a query that extracts or derives data from the tables the view references. Example Create View Dept20 as select ename, sal*12 Annual_Salary from emp where deptno = 20;

  13. The Sequence Generator • The sequence generator provides a sequential series of numbers. • Sequence numbers are Oracle integers defined in the database of up to 38 digits. • A sequence definition indicates the name of the sequence, whether it ascends or descends, the interval between numbers, and other information. CREATE SEQUENCE emp_serial_number increment by 10 start with 1000; maxvalue 1050; nocycle; SQL> select emp_serial_number.NEXTVAL from DUAL; NEXTVAL 1000 SQL> select emp_serial_number.NEXTVAL from DUAL; NEXTVAL 1010 Oracle stores the definitions of all sequences for a particular database as rows in a single data dictionary table in the SYSTEM tablespace. Therefore, all sequence definitions are always available

  14. Schema Objects: Synonyms 1- A synonym is an alias for any table, view, snapshot, sequence, procedure, function, or package. CREATE SYNONYM NE_Sales for US_NORTH_EAST_RETAIL_SALES_TABLE; select * from NE_Sales; 2-Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary. 3-Synonyms are often used for security and convenience. For example, they can do the following: • mask the name and owner of an object • provide location transparency for remote objects of a distributed database • simplify SQL statements for database users 4-Synonyms are very useful in both distributed and non–distributed database environments because they hide the identity of the underlying object, including its location in a distributed system. CREATE SYNONYN External_Emp for scott.emp@CANADA.SALES.COM select * from External_Emp;

  15. Schema Objects: Index • Oracle automatically maintains and uses indexes once they are created • Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicated values on the columns that define the index CREATE INDEX ix_emp_ssn ON emp(ssn); • Oracle enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key. • A composite index (also called a concatenated index) is an index that you create on multiple columns in a table. Columns in a composite index can appear in any order and need not be adjacent in the table. CREATE INDEX ix_stock_part_supplier ON stock (partnumb, supnumb); • Composite indexes can speed retrieval of data for SELECT statements in which the WHERE clause references all or the leading portion of the columns in the composite index.

  16. Creating Indexes • To create an index in your own schema, at least one of the following conditions must be true: • The table or cluster to be indexed is in your own schema. • You have INDEX privilege on the table to be indexed. • You have CREATE ANY INDEX system privilege. • To create an index in another schema, all of the following conditions must be true: • You have CREATE ANY INDEX system privilege. • The owner of the other schema has a quota for the tablespaces to contain the index or index partitions, or UNLIMITED TABLESPACE system privilege.

  17. Specifying the Index Associated with a Constraint If you require more explicit control over the indexes associated with UNIQUE and PRIMARY KEY constraints, the database lets you: • Specify an existing index that the database is to use to enforce the constraint • Specify a CREATE INDEX statement that the database is to use to create the index and enforce the constraint • These options are specified using the USING INDEX clause. • Example 1: CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1))); • Example 2: CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi); • Example 3: CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci; If a single statement creates an index with one constraint and also uses that index for another constraint, the system will attempt to rearrange the clauses to create the index before reusing it.

  18. Viewing Index Information The following views display information about indexes:

  19. Schema Objects: Synonyms Oracle uses B*_tree indexes that are balanced to equalize access time to any row. The B*–tree structure has the following advantages: • All leaf blocks of the tree are at the same depth, so retrieval of any record from anywhere in the index takes approximately the same amount of time. • B*–tree indexes automatically stay balanced. • All blocks of the B*–tree are three–quarters full on the average. • B*–trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches. • Inserts, updates, and deletes are efficient, maintaining key order for fast retrieval. • B*–tree performance is good for both small and large tables, and does not degrade as the size of a table grows.

  20. Indexed Clusters

  21. Clusters store data from different tables in the same physical data blocks. • This approach can improve performance when application processing requires that records from related tables be frequently accessed simultaneously. 

  22. The use of a cluster decreases the number of database block reads that are necessary to retrieve the needed data for the cluster.  • This improves performance for queries that require data stored in both (or more than two if that is the case) tables in the cluster. • While clustered retrieval performance for all tables in the cluster may improve, performance for other types of transactions may suffer. • This is especially true for transactions that only require one of the tables in the cluster because all data within the data blocks will be transferred from disk to memory for these transactions even though the rows from one of the tables are not needed. • Further performance gains can be obtained because the distinct value (cluster key) in each cluster column is stored only once, regardless of the number of times that it occurs in the tables and rows; thereby, reducing the storage space requirements.

  23. Cluster Key and Index • The columns within a cluster index are often also used as the cluster key. This is a set of one or more columns that both tables in the cluster have in common.  • The cluster key must be columns of the same size and data type in both tables, but need not be the same column name. • Typically, the cluster key is the foreign key from the member table that references the primary key from the owner table in the cluster. The cluster key may also be part of a concatenated primary key in the member table.

  24. Steps in Creating an Indexed Cluster The steps in creating a cluster are: (1) create the cluster definition. (2) create the cluster index definition. (3) create the tables that form the cluster by defining them. (4) load data into the tables

  25. example • a cluster of two tables will be created: TestOrders and TestOrderDetails.  These two tables have the following fields and characteristics.

  26. The cluster index will be on the OrderId field since this column is in common between the two tables. This column also serves as the Primary Key of TestOrders, the "owner" table in this relationship. • The PCTFREE and PCTUSED parameters are set during creation of the cluster with the CREATE CLUSTER command. Do notset values for the PCTFREE and PCTUSED parameters for tables that form a cluster - if you set them in the CREATE TABLE command, they will be ignored. Also, clustered tables always use the PCTFREE and PCTUSED settings of the cluster.

  27. The CREATE CLUSTER command has an optional argument, SIZE. This parameter is the estimated number of bytes required by an average cluster key and associated rows. • Oracle uses SIZE to estimate the number of cluster keys (and rows) that fit into a clustered data block; and to limit the number of cluster keys placed in a clustered data block. • If all rows for a unique cluster key will not fit into a single data block, extra data blocks are chained to the first block to improve speed of access to all values for a given cluster key.

  28. The cluster is named OrderCluster in the CREATE CLUSTER command shown here. CREATE CLUSTER OrderCluster     ( OrderId NUMBER(3) )     PCTUSED 60     PCTFREE 40     SIZE 1200     TABLESPACE Users;

  29. In step 2, the cluster index is created.  This step is skipped for a hashed cluster. Note that the keyword UNIQUE is not allowed with a cluster index. CREATE INDEX OrderClusterIndex     ON CLUSTER OrderCluster         INITRANS 2         MAXTRANS 5     TABLESPACE Indexes;

  30. In step 3, the index cluster tables are created. • Note that you cannot specify a TABLESPACE clause for a table that is part of a cluster because that option was specified when the cluster was created.  Attempting to specify a tablespace will return an Oracle error code. CREATE TABLE TestOrders ( OrderId           NUMBER(3) CONSTRAINT TestOrders_PK Primary Key USING INDEX TABLESPACE Indexes, OrderDate         DATE CONSTRAINT OrderDate_NN NOT NULL, Order_Amount       NUMBER(10,2) ) CLUSTER OrderCluster (OrderId) ; CREATE TABLE TestOrderDetails ( ProductId         NUMBER(5), OrderId           NUMBER(3), Quantity_Ordered   NUMBER(3) CONSTRAINT Quantity_Ordered_CK     CHECK (Quantity_Ordered >= 0), ItemPrice         NUMBER(10,2), CONSTRAINT TestOrderDetails_FK FOREIGN KEY (OrderId)     REFERENCES TestOrders     ON DELETE CASCADE, CONSTRAINT TestOrderDetails_PK PRIMARY KEY (ProductId, OrderId) USING INDEX TABLESPACE Indexes ) CLUSTER OrderCluster (OrderId) ;

  31. In step 4, data rows are inserted into the tables. In this first example, valid data rows are entered for the TestOrders table and the associated TestOrderDetails records for a single order with OrderId = 111 for two items that total to $75.95. The ProductIds for the items are "55555" and "66666". INSERT INTO TestOrders     VALUES (111,'23-Jun-05',75.95); INSERT INTO TestOrderDetails     VALUES (55555,111,1,50.00); INSERT INTO TestOrderDetails     VALUES (66666,111,1,25.95);

  32. Cluster Information in the Data Dictionary Querying the data dictionary User_Clusters view will provide information about the cluster that was created in these notes. SELECT Cluster_Name, TableSpace_Name     FROM User_Clusters; CLUSTER_NAME     TABLESPACE_NAME ------------    --------------- ORDERCLUSTER    USERS

  33. Schema Objects: Clusters Clusters are an optional method of storing table data. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together. Example I The following statement creates an indexed cluster named PERSONNEL with the cluster key column DEPARTMENT_NUMBER, a cluster size of 512 bytes, and storage parameter values: CREATE CLUSTER personnel ( department_number NUMBER(2) ) SIZE 512 STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 10) The following statements add the EMP and DEPT tables to the cluster: CREATE TABLE emp (empno NUMBER PRIMARY KEY, ... deptno NUMBER(2) NOT NULL ) CLUSTER personnel (deptno) CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(9), loc VARCHAR2(9)) CLUSTER personnel (deptno) The following statement creates the cluster index on the cluster key of PERSONNEL: CREATE INDEX idx_personnel ON CLUSTER personnel After creating the cluster index, you can insert rows into either the EMP or DEPT tables.

  34. Schema Objects: Clusters Cont.

  35. Creating Clusters • To create a cluster in your schema, you must have the CREATE CLUSTER system privilege and a quota for the tablespace intended to contain the cluster or the UNLIMITED TABLESPACE system privilege. • To create a cluster in another user's schema you must have the CREATE ANY CLUSTER system privilege, and the owner must have a quota for the tablespace intended to contain the cluster or the UNLIMITED TABLESPACE system privilege. • You create a cluster using the CREATE CLUSTER statement. The following statement creates a cluster named emp_dept, which stores the emp and dept tables, clustered by the deptno column: CREATE CLUSTER emp_dept (deptno NUMBER(3)) SIZE 600 TABLESPACE users STORAGE (INITIAL 200K NEXT 300K MINEXTENTS 2 MAXEXTENTS 20 PCTINCREASE 33); If no INDEX keyword is specified, as is true in this example, an index cluster is created by default

  36. Creating Clustered Tables • To create a table in a cluster, you must have either the CREATE TABLE or CREATE ANY TABLE system privilege. You do not need a tablespace quota or the UNLIMITED TABLESPACE system privilege to create a table in a cluster. • You create a table in a cluster using the CREATE TABLE statement with the CLUSTER clause. The emp and dept tables can be created in the emp_dept cluster using the following statements: CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, ename VARCHAR2(15) NOT NULL, . . . deptno NUMBER(3) REFERENCES dept) CLUSTER emp_dept (deptno); CREATE TABLE dept ( deptno NUMBER(3) PRIMARY KEY, . . . ) CLUSTER emp_dept (deptno);

  37. Schema Objects: Hashing • Hashing is an optional way of storing table data to improve the performance of data retrieval. • To use hashing, you create a hash cluster and load tables into the cluster. Oracle physically stores the rows of a table in a hash cluster and retrieves them according to the results of a hash function. • The key of a hash cluster (like the key of an index cluster) can be a single column or a composite key.

More Related