Varrays and Clusters in Oracle

Varrays and Clusters in Oracle

1.What is VARRAYS?How to create?

A:varrays can also be used to create one-to-many relationship with in the table.
*creating varray:
Ex: create type dependent_brithdate_t5 as varray(10) of date;

2.Differences between nested tables and varrays***

Nested tables  :                                                
1.There is no restriction on size.      
2.Data is stored in special auxiliary Tables called as store tables.              

Varrays:
1. We need to define the maximum size.
2. Data is stored inline to the rest of the table data.

3.Why we are using Execute immediate?

A:One can call DDL statement like create, drop, truncate and etc from PL/SQL by using the “Execute immediate” statement

4.What is a cluster Key ?

The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.

5.Can Long/Long RAW be clustered ?

No

5.What is clusters ?

Group of tables physically stored together because they share common columns and are often used together is called Cluster.

6.What are the advantages of clusters ?

Access time reduced for joins.

7.What are the disadvantages of clusters ?

The time for Insert increases.

8.BULK COLLECT:

Bulk collect feature helps in improving the performance of explicit cursor programs.
Fetch statement can fetch all the rows from the cursor to the programs local variable at once thus helps in improving the performance.

9.What is the purpose of a cluster?

A:Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS.

10.schema

A:Specify the schema to contain the cluster. If you omit schema, Oracle Database creates the cluster in your current schema.

11.datatype

A:Specify the datatype of each cluster key column.

Restrictions on Cluster Datatypes Cluster datatypes are subject to the following restrictions:

You cannot specify a cluster key column of datatype LONG, LONG RAW, REF, nested table, varray, BLOB, CLOB, BFILE, or user-defined object type.
You cannot use the HASH IS clause if any column datatype is not INTEGER or NUMBER with scale 0.
You can specify a column of type ROWID, but Oracle Database does not guarantee that the values in such columns are valid rowids.

0 comments:

Post a Comment