Solved

Clustered Indexes

Posted on 2004-10-11
10
20,530 Views
Last Modified: 2013-11-15
Hi,

I've successfully created a clustered index on Microsoft SQL server, but I'm not sure how to do this with Oracle as I don't usually have to work with Oracle.

On MSSQL I write

create CLUSTERED index [MyIndex] on MyTable (datestamp)

The need a clustered index so that I can force the physical order of the table. I'm adding log data to a table and basically want it sorted by the date.

If one could post the SQL syntax here and I would be very grateful.


Thanks,
Ingmar.
0
Comment
Question by:Netikus
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 12280802
there is NO "Clustered" index in Oracle. this is one of major differences btw these two rdbms.

In oracle , you use IOT (index organzied table) to enforce the physical order of the rows in a table.

create table <table_name>
(
col definition
....
)
organization index;  --here you specify .
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12280811
more demo to show syntax:

CREATE TABLE <table_name> (
<column_name> <data type and precision>,
<column_name> <data type and precision>,
CONSTRAINT <constraint_name>
PRIMARY KEY (<primary key constraint columns>))
ORGANIZATION INDEX;
CREATE TABLE LABOR_HOUR (
WORK_DATE DATE,
EMPLOYEE_NO VARCHAR2(8),
CONSTRAINT pk_labor_hour
PRIMARY KEY (work_date, employee_no))
ORGANIZATION INDEX;









CREATE TABLE <table_name> (
<column_name> <data type and precision>,
<column_name> <data type and precision>,
CONSTRAINT <constraint_name>
PRIMARY KEY (<primary key constraint columns>))
ORGANIZATION INDEX
INCLUDING <column_name>
OVERFLOW TABLESPACE <tablespace_name>;
CREATE TABLE labor_hour (
WORK_DATE DATE,
EMPLOYEE_NO VARCHAR2(8),
SUMMIT_WORK_ORDER_NO VARCHAR2(7),
DASH VARCHAR2(2),
CLASS_CODE VARCHAR2(6),
PAYCODE VARCHAR2(2),
ASSIGNED_CREW_NUMBER VARCHAR2(5),
TRANSFER_CREW_NUMBER VARCHAR2(5),
REFERENCE_TYPE VARCHAR2(1),
REFERENCE_NUMBER VARCHAR2(10),
OVERTIME_CODE VARCHAR2(1),
SHIFT_DIFFERENTIAL VARCHAR2(1) NOT NULL,
HOURS NUMBER(4,2) NOT NULL,
MOD_USER_ID VARCHAR2(30) DEFAULT USER,
MOD_USER_DATE DATE DEFAULT SYSDATE,
CONSTRAINT pk_labor_hour
PRIMARY KEY (work_date, employee_no, summit_work_order_no, dash, class_code, paycode, assigned_crew_number, transfer_crew_number, reference_type, reference_number, overtime_code, shift_differential))
ORGANIZATION INDEX
INCLUDING hours
OVERFLOW TABLESPACE data_sml;
0
 
LVL 1

Author Comment

by:Netikus
ID: 12281073
Hmm, thanks.

So, if I had a table like this:

create table MyLog
(
  number int,
  log int,
  message varchar2(1024),
  timestamp date
)

then how would I need to modify this to set a IOT for the "timestamp" column?


Thanks!
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 23

Expert Comment

by:seazodiac
ID: 12281814
you should use INCLUDE keyword to include that timestamp column.

btw, you better change the "timestamp" column name, it's the keyword in Oracle
0
 
LVL 22

Expert Comment

by:earth man2
ID: 12282173
number is also a keyword but you can double quote them to make it work.
I don;t recommend that as it makes queries v torturous.

create table MyLog
(
  "number" int,
  log int,
  message varchar2(1024),
  "timestamp" date,
  primary key ( "timestamp", "number", log, message )
) ORGANIZATION INDEX INCLUDING message
OVERFLOW TABLESPACE users;

0
 
LVL 1

Author Comment

by:Netikus
ID: 12282512
Thanks. I won't actually use the names "number" or "timestamp", I just changed them for the sample.

Now, I see that the date value, "timestamp", is made a primary key - or is the combination of all of them made a primary key? I am asking because the timestamp field will not be unique.

Also, what does the "including message" mean? Why is message specified in the IOT, and not the timestamp?

Thanks!
0
 
LVL 9

Expert Comment

by:konektor
ID: 12284466
what makes u to create clustered indexes ?
don't be better create partitioned table insetead of it ?
0
 
LVL 1

Author Comment

by:Netikus
ID: 12286334
Well, I asked for clustered indexes because that's the feature I'm familiar with on MS SQL Server. I basically want to store the data in the table sorted by a particular column.

I want this because all queries against this table will be sorted by this one particular column, the date in this example.

So I am open to any type of feature - so as long as somebody can just briefly post the SQL statement with a brief explanation!

So what's a partioned table? :-)

Thank you!
0
 
LVL 22

Accepted Solution

by:
earth man2 earned 300 total points
ID: 12286506
An index organized table can also be partioned !

The primary key IS the combination of all of them.

The INCLUDING clause specifies which colums are stored outside of the index.  So here you speify the last column to indicate all columns are to be contained within the index.

Why don't YOU read he Oracle Documentation on CREATE TABLE ?

INDEX indicates that table is created as an index-organized table. In an index-organized table, the data rows are held in an index defined on the primary key for the table.

Use the index_org_table_clause to create an index-organized table. Oracle Database maintains the table rows, both primary key column values and nonkey column values, in an index built on the primary key. Index-organized tables are therefore best suited for primary key-based access and manipulation.

You must specify a primary key for an index-organized table, because the primary key uniquely identifies a row. The primary key cannot be DEFERRABLE. Use the primary key instead of the rowid for directly accessing index-organized rows.

The index_org_overflow_clause lets you instruct the database that index-organized table data rows exceeding the specified threshold are placed in the data segment specified in this clause.

      When you create an index-organized table, Oracle Database evaluates the maximum size of each column to estimate the largest possible row. If an overflow segment is needed but you have not specified OVERFLOW, then the database raises an error and does not execute the CREATE TABLE statement. This checking function guarantees that subsequent DML operations on the index-organized table will not fail because an overflow segment is lacking.

      All physical attributes and storage characteristics you specify in this clause after the OVERFLOW keyword apply only to the overflow segment of the table. Physical attributes and storage characteristics for the index-organized table itself, default values for all its partitions, and values for individual partitions must be specified before this keyword.

      If the index-organized table contains one or more LOB columns, then the LOBs will be stored out-of-line unless you specify OVERFLOW, even if they would otherwise be small enough be to stored inline.
 
      If table is partitioned, then the database equipartitions the overflow data segments with the primary key index segments.

INCLUDING column_name

Specify a column at which to divide an index-organized table row into index and overflow portions. The primary key columns are always stored in the index. column_name can be either the last primary key column or any non primary key column. All non primary key columns that follow column_name are stored in the overflow data segment.

If an attempt to divide a row at column_name causes the size of the index portion of the row to exceed the specified or default PCTTHRESHOLD value, then the database breaks up the row based on the PCTTHRESHOLD value.

Restriction on the INCLUDING Clause

You cannot specify this clause for individual partitions of an index-organized table.

You cannot use the TO_LOB function to convert a LONG column to a LOB column in the subquery of a CREATE TABLE ... AS SELECT statement if you are creating an index-organized table. Instead, create the index-organized table without the LONG column, and then use the TO_LOB function in an INSERT ... AS SELECT statement.
0
 
LVL 1

Author Comment

by:Netikus
ID: 12458583
Hi Earthman2,

Sorry for the delay and thanks for your comments, I'm quite sure that I understand this now.

The reason why I haven't read the manual is that work with Oracle very rarely, and as such it is quite difficult to dwelve into all those topics. Oracle is quite hard to understand for administrators who don't often work with it, and if everybody read the manual then there would be very few postings on web sites like this one.

Sometimes a question can be answered in 2 minutes by an expert, yet would take a long time, even hours, for somebody who is unfamiliar with the topic. I work with Oracle to little to justify taking a course or reading a book, yet I needed help in that particular instance.

Thank you!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question