Creating Cluster tables in Oracle 11g

-


The flow is

* First you need to create a cluster.
* Then create tables that need to be clustered by using the newly created cluster.

The idea of clustered table is to minimize space used by a group of tables by clustering column that occur commonly in these tables.

Suppose there are 3 tables namely T1, T2, T3 and they all have a column named Emp_ID with the same values, then clustering these table using this column Emp_ID reduce space occupied by the column. This column will be stored only one time in a cluster instead of storing in 3 tables.

Here is the command to create a cluster


create cluster test_cluster (Emp_ID varchar2(32));

Create the tables that need to clustered by using the above created cluster.

create table T1 (Col1 varchar2(100), Emp_ID varchar2(32)) cluster test_cluster(Emp_ID);

create table T2 (Col1 varchar2(100), Emp_ID varchar2(32)) cluster test_cluster(Emp_ID);

etc.

Make sure you match the type of the cluster column and the column you assign for clustering while creating the table. You will get error as shown below if you don't.


create table clus_test (col2 number, keys number) cluster test_cluster (col1);

Error report:
SQL Error: ORA-00945: specified clustered column does not exist
00945. 00000 -  "specified clustered column does not exist"

By default oracle creates Index Clusters, you must create an cluster index for this cluster before you can make a DML command on the associated table of this cluster.

You will get the below error if you try to issue a DML for a unindexed cluster table.


insert into T1 values ('testing');

Error report:
SQL Error: ORA-02032: clustered tables cannot be used before the cluster index is built
02032. 00000 -  "clustered tables cannot be used before the cluster index is built"
*Cause:    User attempted to perform a DML statement on a clustered table
           for which no cluster index has yet been created.
*Action:   Create the cluster index.
 
 
Create an index for this cluster.
 
create index clust_idx_test on cluster test_cluster;
 
Results:

index CLUS_TEST_IDX created.
 
If you try a DML it will succeed.

Comments

Post a Comment

Popular posts from this blog

Current Variables in T24

-
CURRENT VARIABLE Introduction In addition to the system common variables that can be used in ENQUIRY and VERSION there is a feature where the user can populate a variable of their own and use it later. There are now a series of new system variables and a much wider option for users to create and use their own which are: CURRENT.CUSTOMER CURRENT.ARRANGEMENT CURRENT.EXTERNAL.USER These may be set on login by an external user and cleared when they logout. This means that ENQUIRY & VERSION can be tailored to accept the content of the variable instead of forcing a user to enter their own customer number or arrangement etc. Similarly in VERSION you can now default a field content by using the !CURRENT.CUSTOMER to populate a customer field with the current content of the variable. So the variable could be set in one Enquiry and used in either another Enquiry linked to it; or via VERSION to populate fields in an application triggered from the Enquiry. Note: ...
Read more

All accounting entries of an T24 account

-
There is a core routine that will return all the accounting entries for an account it is EB.ACCT.ENTRY.LIST Syntax: EB.ACCT.ENTRY.LIST(ACCOUNT.NUMBER,FROM.DATE,END.DATE,YID.LIST,OPENING.BAL,ER) Passed Parameters. ACCOUNT.NUMBER :- Account for which balance & entries is to be returned. FROM.DATE :- Start date for opening balance and entries. END.DATE :- The last date to be considered. Outgoing : YID.LIST :- List of statement entry ids. OPENING.BAL :- Opening balance on the startt date. ER :- Any errors found
Read more

SQL queries in JBase

-
The query Language of Jbase is really restrictive. Data analysis and comparison between two table is not possible without the use of spreadsheet software. I recently came to know that Jbase is providing a command called SQLSELECT through which you can actually issue SQL query command directly on Jbase tables. A sample would be like SQLSELECT * FROM FBNK.ACCOUNT WHERE SHORT_NAME LIKE '%PETER%' AND CATEGORY = 6601 Limitations: The command though have serious limitations such as  The table you query should not have dot charecter for example you cannot query FBNK.CUSTOMER.ACCOUNT with this command. But there is a workaround for the above drawback. Make a copy of the VOC record FBNK.CUSTOMER.ACCOUNT into a new one without ID in its ID. For Example: COPY FROM VOC FBNK.CUSTOMER.ACCOUNT,FBNK.CUSACC SQLSELECT * FROM FBNK.CUSACC; will work. The field name should not have dot character. You can overcome this by changing the dot character to underscore. For...
Read more