Creating Index-Organized Tables
You use the CREATE TABLE statement to create index-organized tables, but you must provide additional information:
An ORGANIZATION INDEX qualifier, which indicates that this is an index-organized table
A primary key, specified through a column constraint clause (for a single column primary key) or a table constraint clause (for a multiple-column primary key).
Optionally, you can specify the following:
An OVERFLOW clause, which preserves dense clustering of the B-tree index by enabling the storage of some of the nonkey columns in a separate overflow data segment.
A PCTTHRESHOLD value, which, when an overflow segment is being used, defines the maximum size of the portion of the row that is stored in the index block, as a percentage of block size. Rows columns that would cause the row size to exceed this maximum are stored in the overflow segment. The row is broken at a column boundary into two pieces, a head piece and tail piece. The head piece fits in the specified threshold and is stored along with the key in the index leaf block. The tail piece is stored in the overflow area as one or more row pieces. Thus, the index entry contains the key value, the nonkey column values that fit the specified threshold, and a pointer to the rest of the row.
An INCLUDING clause, which can be used to specify the nonkey columns that are to be stored in the index block with the primary key.
Example: Creating an Index-Organized Table
The following statement creates an index-organized table:
CREATE TABLE admin_docindex(
token char(20),
doc_id NUMBER,
token_frequency NUMBER,
token_offsets VARCHAR2(2000),
CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))
ORGANIZATION INDEX
TABLESPACE admin_tbs
PCTTHRESHOLD 20
OVERFLOW TABLESPACE admin_tbs2;
This example creates an index-organized table named admin_docindex, with a primary key composed of the columns token and doc_id. The OVERFLOW and PCTTHRESHOLD clauses specify that if the length of a row exceeds 20% of the index block size, then the column that exceeded that threshold and all columns after it are moved to the overflow segment. The overflow segment is stored in the admin_tbs2 tablespace.
Restrictions for Index-Organized Tables
The following are restrictions on creating index-organized tables.
The maximum number of columns is 1000.
The maximum number of columns in the index portion of a row is 255, including both key and nonkey columns. If more than 255 columns are required, you must use an overflow segment.
The maximum number of columns that you can include in the primary key is 32.
PCTTHRESHOLD must be in the range of 1&50. The default is 50.
All key columns must fit within the specified threshold.
If the maximum size of a row exceeds 50% of the index block size and you do not specify an overflow segment, the CREATE TABLE statement fails.
Index-organized tables cannot have virtual columns.
Creating Index-Organized Tables that Contain Object Types
Index-organized tables can store object types. The following example creates object type admin_typ, then creates an index-organized table containing a column of object type admin_typ:
CREATE OR REPLACE TYPE admin_typ AS OBJECT
(col1 NUMBER, col2 VARCHAR2(6));
CREATE TABLE admin_iot (c1 NUMBER primary key, c2 admin_typ)
ORGANIZATION INDEX;
You can also create an index-organized table of object types. For example:
CREATE TABLE admin_iot2 OF admin_typ (col1 PRIMARY KEY)
ORGANIZATION INDEX;
Another example, that follows, shows that index-organized tables store nested tables efficiently. For a nested table column, the database internally creates a storage table to hold all the nested table rows.
CREATE TYPE project_t AS OBJECT(pno NUMBER, pname VARCHAR2(80));
CREATE TYPE project_set AS TABLE OF project_t;
CREATE TABLE proj_tab (eno NUMBER, projects PROJECT_SET)
NESTED TABLE projects STORE AS emp_project_tab
((PRIMARY KEY(nested_table_id, pno))
ORGANIZATION INDEX)
RETURN AS LOCATOR;
The rows belonging to a single nested table instance are identified by a nested_table_id column. If an ordinary table is used to store nested table columns, the nested table rows typically get de-clustered. But when you use an index-organized table, the nested table rows can be clustered based on the nested_table_id column.
Choosing and Monitoring a Threshold Value
Choose a threshold value that can accommodate your key columns, as well as the first few nonkey columns (if they are frequently accessed).
After choosing a threshold value, you can monitor tables to verify that the value you specified is appropriate. You can use the ANALYZE TABLE ... LIST CHAINED ROWS statement to determine the number and identity of rows exceeding the threshold value.
Using the INCLUDING Clause
In addition to specifying PCTTHRESHOLD, you can use the INCLUDING clause to control which nonkey columns are stored with the key columns. The database accommodates all nonkey columns up to and including the column specified in the INCLUDING clause in the index leaf block, provided it does not exceed the specified threshold. All nonkey columns beyond the column specified in the INCLUDING clause are stored in the overflow segment. If the INCLUDING and PCTTHRESHOLD clauses conflict, PCTTHRESHOLD takes precedence.
The following CREATE TABLE statement is similar to the one shown earlier in
but is modified to create an index-organized table where the token_offsets column value is always stored in the overflow area:
CREATE TABLE admin_docindex2(
token CHAR(20),
doc_id NUMBER,
token_frequency NUMBER,
token_offsets VARCHAR2(2000),
CONSTRAINT pk_admin_docindex2 PRIMARY KEY (token, doc_id))
ORGANIZATION INDEX
TABLESPACE admin_tbs
PCTTHRESHOLD 20
INCLUDING token_frequency
OVERFLOW TABLESPACE admin_tbs2;
Here, only nonkey columns prior to token_offsets (in this case a single column only) are stored with the key column values in the index leaf block.
Parallelizing Index-Organized Table Creation
The CREATE TABLE...AS SELECT statement enables you to create an index-organized table and load data from an existing table into it. By including the PARALLEL clause, the load can be done in parallel.
The following statement creates an index-organized table in parallel by selecting rows from the conventional table hr.jobs:
CREATE TABLE admin_iot3(i PRIMARY KEY, j, k, l)
ORGANIZATION INDEX
AS SELECT * FROM hr.
This statement provides an alternative to parallel bulk-load using SQL*Loader.
Using Key Compression
Creating an index-organized table using key compression enables you to eliminate repeated occurrences of key column prefix values.
Key compression breaks an index key into a prefix and a suffix entry. Compression is achieved by sharing the prefix entries among all the suffix entries in an index block. This sharing can lead to huge savings in space, allowing you to store more keys in each index block while improving performance.
You can enable key compression using the COMPRESS clause while:
Creating an index-organized table
Moving an index-organized table
You can also specify the prefix length (as the number of key columns), which identifies how the key columns are broken into a prefix and suffix entry.
CREATE TABLE admin_iot5(i INT, j INT, k INT, l INT, PRIMARY KEY (i, j, k))
ORGANIZATION INDEX COMPRESS;
The preceding statement is equivalent to the following statement:
CREATE TABLE admin_iot6(i INT, j INT, k INT, l INT, PRIMARY KEY(i, j, k))
ORGANIZATION INDEX COMPRESS 2;
For the list of values (1,2,3), (1,2,4), (1,2,7), (1,3,5), (1,3,4), (1,4,4) the repeated occurrences of (1,2), (1,3) are compressed away.
You can also override the default prefix length used for compression as follows:
CREATE TABLE admin_iot7(i INT, j INT, k INT, l INT, PRIMARY KEY (i, j, k))
ORGANIZATION INDEX COMPRESS 1;
For the list of values (1,2,3), (1,2,4), (1,2,7), (1,3,5), (1,3,4), (1,4,4), the repeated occurrences of 1 are compressed away.
You can disable compression as follows:
ALTER TABLE admin_iot5 MOVE NOCOMPRESS;
One application of key compression is in a time-series application that uses a set of time-stamped rows belonging to a single item, such as a stock price. Index-organized tables are attractive for such applications because of the ability to cluster rows based on the primary key. By defining an index-organized table with primary key (stock symbol, time stamp), you can store and manipulate time-series data efficiently. You can achieve more storage savings by compressing repeated occurrences of the item identifier (for example, the stock symbol) in a time series by using an index-organized table with key compression.
Maintaining Index-Organized Tables
Index-organized tables differ from ordinary tables only in physical organization. Logically, they are manipulated in the same manner as ordinary tables. You can specify an index-organized table just as you would specify a regular table in INSERT, SELECT, DELETE, and UPDATE statements.
Altering Index-Organized Tables
All of the alter options available for ordinary tables are available for index-organized tables. This includes ADD, MODIFY, and DROP COLUMNS and CONSTRAINTS. However, the primary key constraint for an index-organized table cannot be dropped, deferred, or disabled
You can use the ALTER TABLE statement to modify physical and storage attributes for both primary key index and overflow data segments. All the attributes specified prior to the OVERFLOW keyword are applicable to the primary key index segment. All attributes specified after the OVERFLOW key word are applicable to the overflow data segment. For example, you can set the INITRANS of the primary key index segment to 4 and the overflow of the data segment INITRANS to 6 as follows:
ALTER TABLE admin_docindex INITRANS 4 OVERFLOW INITRANS 6;
You can also alter PCTTHRESHOLD and INCLUDING column values. A new setting is used to break the row into head and overflow tail pieces during subsequent operations. For example, the PCTHRESHOLD and INCLUDING column values can be altered for the admin_docindex table as follows:
ALTER TABLE admin_docindex PCTTHRESHOLD 15 INCLUDING doc_
By setting the INCLUDING column to doc_id, all the columns that follow token_frequency and token_offsets, are stored in the overflow data segment.
For index-organized tables created without an overflow data segment, you can add an overflow data segment by using the ADD OVERFLOW clause. For example, you can add an overflow segment to table admin_iot3 as follows:
ALTER TABLE admin_iot3 ADD OVERFLOW TABLESPACE admin_tbs2;
Moving (Rebuilding) Index-Organized Tables
Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.
The following statement rebuilds the index-organized table admin_docindex:
ALTER TABLE admin_docindex MOVE;
You can rebuild index-organized tables online using the ONLINE keyword. The overflow data segment, if present, is rebuilt when the OVERFLOW keyword is specified. For example, to rebuild the admin_docindex table but not the overflow data segment, perform a move online as follows:
ALTER TABLE admin_docindex MOVE ONLINE;
To rebuild the admin_docindex table along with its overflow data segment perform the move operation as shown in the following statement. This statement also illustrates moving both the table and overflow data segment to new tablespaces.
ALTER TABLE admin_docindex MOVE TABLESPACE admin_tbs2
OVERFLOW TABLESPACE admin_tbs3;
In this last statement, an index-organized table with a LOB column (CLOB) is created. Later, the table is moved with the LOB index and data segment being rebuilt and moved to a new tablespace.
CREATE TABLE admin_iot_lob
(c1 number (6) primary key,
admin_lob CLOB)
ORGANIZATION INDEX
LOB (admin_lob) STORE AS (TABLESPACE admin_tbs2);
ALTER TABLE admin_iot_lob MOVE LOB (admin_lob) STORE AS (TABLESPACE admin_tbs3);
Creating Secondary Indexes on Index-Organized Tables
You can create secondary indexes on an index-organized tables to provide multiple access paths. Secondary indexes on index-organized tables differ from indexes on ordinary tables in two ways:
They store logical rowids instead of physical rowids. This is necessary because the inherent movability of rows in a B-tree index results in the rows having no permanent physical addresses. If the physical location of a row changes, its logical rowid remains valid. One effect of this is that a table maintenance operation, such as ALTER TABLE ... MOVE, does not make the secondary index unusable.
The logical rowid also includes a physical guess which identifies the database block address at which the row is likely to be found. If the physical guess is correct, a secondary index scan would incur a single additional I/O once the secondary key is found. The performance would be similar to that of a secondary index-scan on an ordinary table.
Unique and non-unique secondary indexes, function-based secondary indexes, and bitmap indexes are supported as secondary indexes on index-organized tables.
Creating a Secondary Index on an Index-Organized Table
The following statement shows the creation of a secondary index on the docindex index-organized table where doc_id and token are the key columns:
CREATE INDEX Doc_id_index on Docindex(Doc_id, Token);
This secondary index allows the database to efficiently process a query, such as the following, the involves a predicate on doc_id:
SELECT Token FROM Docindex WHERE Doc_id = 1;
Maintaining Physical Guesses in Logical Rowids
A logical rowid can include a guess, which identifies the block location of a row at the time the guess is made. Instead of doing a full key search, the database uses the guess to search the block directly. However, as new rows are inserted, guesses can become stale. The indexes are still usable through the primary key-component of the logical rowid, but access to rows is slower.
Collect index statistics with the DBMS_STATS package to monitor the staleness of guesses. The database checks whether the existing guesses are still valid and records the percentage of rows with valid guesses in the data dictionary. This statistic is stored in the PCT_DIRECT_ACCESS column of the DBA_INDEXES view (and related views).
To obtain fresh guesses, you can rebuild the secondary index. Note that rebuilding a secondary index on an index-organized table involves reading the base table, unlike rebuilding an index on an ordinary table. A quicker, more light weight means of fixing the guesses is to use the ALTER INDEX ... UPDATE BLOCK REFERENCES statement. This statement is performed online, while DML is still allowed on the underlying index-organized table.
After you rebuild a secondary index, or otherwise update the block references in the guesses, collect index statistics again.
Bitmap Indexes
Bitmap indexes on index-organized tables are supported, provided the index-organized table is created with a mapping table. This is done by specifying the MAPPING TABLE clause in the CREATE TABLE statement that you use to create the index-organized table, or in an ALTER TABLE statement to add the mapping table later.
Analyzing Index-Organized Tables
Just like ordinary tables, index-organized tables are analyzed using the DBMS_STATS package, or the ANALYZE statement.
Collecting Optimizer Statistics for Index-Organized Tables
To collect optimizer statistics, use the DBMS_STATS package.
For example, the following statement gathers statistics for the index-organized countries table in the hr schema:
EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('HR','COUNTRIES');
The DBMS_STATS package analyzes both the primary key index segment and the overflow data segment, and computes logical as well as physical statistics for the table.
The logical statistics can be queried using USER_TABLES, ALL_TABLES or DBA_TABLES.
You can query the physical statistics of the primary key index segment using USER_INDEXES, ALL_INDEXES or DBA_INDEXES (and using the primary key index name). For example, you can obtain the primary key index segment physical statistics for the table admin_docindex as follows:
SELECT LAST_ANALYZED, BLEVEL,LEAF_BLOCKS, DISTINCT_KEYS
FROM DBA_INDEXES WHERE INDEX_NAME= 'PK_ADMIN_DOCINDEX';
You can query the physical statistics for the overflow data segment using the USER_TABLES, ALL_TABLES or DBA_TABLES. You can identify the overflow entry by searching for IOT_TYPE = 'IOT_OVERFLOW'. For example, you can obtain overflow data segment physical attributes associated with the admin_docindex table as follows:
SELECT LAST_ANALYZED, NUM_ROWS, BLOCKS, EMPTY_BLOCKS
FROM DBA_TABLES WHERE IOT_TYPE='IOT_OVERFLOW'
and IOT_NAME= 'ADMIN_DOCINDEX';
Validating the Structure of Index-Organized Tables
Use the ANALYZE statement if you want to validate the structure of your index-organized table or to list any chained rows. These operations are discussed in the following sections located elsewhere in this book:
Using the ORDER BY Clause with Index-Organized Tables
If an ORDER BY clause only references the primary key column or a prefix of it, then the optimizer avoids the sorting overhead, as the rows are returned sorted on the primary key columns.
The following queries avoid sorting overhead because the data is already sorted on the primary key:
SELECT * FROM admin_docindex2 ORDER BY token, doc_
SELECT * FROM admin_docindex2 ORDER BY
If, however, you have an ORDER BY clause on a suffix of the primary key column or non-primary-key columns, additional sorting is required (assuming no other secondary indexes are defined).
SELECT * FROM admin_docindex2 ORDER BY doc_
SELECT * FROM admin_docindex2 ORDER BY token_
Converting Index-Organized Tables to Regular Tables
You can convert index-organized tables to regular tables using the Oracle import or export utilities, or the CREATE TABLE...AS SELECT statement.
To convert an index-organized table to a regular table:
Export the index-organized table data using conventional path.
Create a regular table definition with the same definition.
Import the index-organized table data, making sure IGNORE=y (ensures that object exists error is ignored).
Scripting on this page enhances content navigation, but does not change the content in any way.int c = getchar()? - Stack Overflow
to customize your list.
Join the Stack Overflow Community
Stack Overflow is a community of 6.6 million programmers, just like you, helping each other.
J it only takes a minute:
ok so im reading this book: The C Programming Language - By Kernighan and Ritchie (second Edition) and one of the examples im having trouble understanding how things are working.
#include &stdio.h&
#define MAXLINE 1000
int getline(char line[], int maxline);
void copy(char to[], char from[]);
int main(int argc, char *argv[])
char line[MAXLINE];
char longest[MAXLINE];
while((len = getline(line, MAXLINE)) & 1)
if(len & max)
copy(longest, line);
if(max & 0)
printf("%s", longest);
getchar();
getchar();
int getline(char s[], int lim)
for(i = 0; i & lim - 1 && (c = getchar()) != EOF && c != '\n'; ++i)
if(c == '\n')
s[i] = '\0';
void copy(char to[], char from[])
while((to[i] = from[i]) != '\0')
the line : for(i = 0; i & lim - 1 && (c = getchar()) != EOF && c != '\n'; ++i)
where it says c = getchar(), how can an integer = characters input from the command line? Integers yes but how are the characters i type being stored?
Thanks in advance
Unlike some other languages you may have used, chars in C are integers. char is just another integer type, usually 8 bits and smaller than int, but still an integer type.
So, you don't need ord() and chr() functions that exist in other languages you may have used. In C you can convert between char and other integer types using a cast, or just by assigning.
Unless EOF occurs, getchar() is defined to return "an unsigned char converted to an int" (), so if it helps you can imagine that it reads some char, c, then returns (int)(unsigned char)c.
You can convert this back to an unsigned char just by a cast or assignment, and if you're willing to take a slight loss of theoretical portability, you can convert it to a char with a cast or by assigning it to a char.
207k21302560
getchar() returns an integer which is the representation of the character entered. If you enter the character A, you will get 'A' or 0x41 returned (upgraded to an int and assuming you're on an ASCII system of course).
The reason it returns an int rather than a char is because it needs to be able to store any character plus the EOF indicator where the input stream is closed.
And, for what it's worth, that's not really a good book for beginners to start with. It's from the days where efficiency mattered more than readability and maintainability.
While it shows how clever the likes of K&R were, you should probably be looking at something more ... newbie-friendly.
In any case, the last edition of it covered C89 and quite a lot has changed since then. We've been through C99 and now have C11 and the book hasn't been updated to reflect either of them, so it's horribly out of date.
507k12310071461
The C char type is 8 bits, which means it can store the range of integers from -128 to 127 (255 this is the range of ASCII).
getchar() returns int, which will be at least 16 bits (usually 32 bits on modern machines).
This means that it can store the range of char, as well as more values.
The reason why the return type is int is because the special value EOF is returned when the end of the input stream is reached.
If the return type were char, then there would be no way to signal that the end of the stream was encountered (unless it took a pointer to a variable where this condition was recorded).
86.7k7167205
Every character (including numbers) entered on the command line is read as a character and every character has an integer value based on its ASCII code .
Answer for your Question is answered. But just add 1 more thing.
As you are declaring variable c as int. It is pretty clear that you are taking values from 0 to 9 having ascii value of 48-57.
So you can just add 1 more line to the code-
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Post as a guest
By posting your answer, you agree to the
Not the answer you're looking for?
Browse other questions tagged
rev .24741
Stack Overflow works best with JavaScript enabled