Solved

Clustered Indexes

Posted on 2004-10-11
10
20,526 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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.
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

706 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now