Plsql index

Plsql index DEFAULT

3 Indexes and Index-Organized Tables

Key Compression

Oracle Database can use key compression to compress portions of the primary key column values in a B-tree index or an index-organized table. Key compression can greatly reduce the space consumed by the index.

In general, index keys have two pieces, a grouping piece and a unique piece. Key compression breaks the index key into a prefixentry, which is the grouping piece, and a suffixentry, which is the unique or nearly unique piece. The database achieves compression by sharing the prefix entries among the suffix entries in an index block.

Note:

If a key is not defined to have a unique piece, then the database provides one by appending a rowid to the grouping piece.

By default, the prefix of a unique index consists of all key columns excluding the last one, whereas the prefix of a nonunique index consists of all key columns. For example, suppose that you create a composite index on the table as follows:

CREATE INDEX orders_mod_stat_ix ON orders ( order_mode, order_status );

Many repeated values occur in the and columns. An index block may have entries as shown in Example 3-3.

Example 3-3 Index Entries in Orders Table

online,0,AAAPvCAAFAAAAFaAAa online,0,AAAPvCAAFAAAAFaAAg online,0,AAAPvCAAFAAAAFaAAl online,2,AAAPvCAAFAAAAFaAAm online,3,AAAPvCAAFAAAAFaAAq online,3,AAAPvCAAFAAAAFaAAt

In Example 3-3, the key prefix would consist of a concatenation of the and values. If this index were created with default key compression, then duplicate key prefixes such as , and , would be compressed. Conceptually, the database achieves compression as shown in the following example:

online,0 AAAPvCAAFAAAAFaAAa AAAPvCAAFAAAAFaAAg AAAPvCAAFAAAAFaAAl online,2 AAAPvCAAFAAAAFaAAm online,3 AAAPvCAAFAAAAFaAAq AAAPvCAAFAAAAFaAAt

Suffix entries form the compressed version of index rows. Each suffix entry references a prefix entry, which is stored in the same index block as the suffix entry.

Alternatively, you could specify a prefix length when creating a compressed index. For example, if you specified prefix length , then the prefix would be and the suffix would be . For the values in Example 3-3, the index would factor out duplicate occurrences of as follows:

online 0,AAAPvCAAFAAAAFaAAa 0,AAAPvCAAFAAAAFaAAg 0,AAAPvCAAFAAAAFaAAl 2,AAAPvCAAFAAAAFaAAm 3,AAAPvCAAFAAAAFaAAq 3,AAAPvCAAFAAAAFaAAt

The index stores a specific prefix once per leaf block at most. Only keys in the leaf blocks of a B-tree index are compressed. In the branch blocks the key suffix can be truncated, but the key is not compressed.

Sours: https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm

Summary: in this tutorial, you will learn how to use the Oracle statement to create a new index for a table.

Introduction to Oracle statement

To create a new index for a table, you use the statement as follows:

Code language:SQL (Structured Query Language)(sql)

In this syntax:

  • First, specify the name of the index. The index name should be meaningful and includes table alias and column name(s) where possible, along with the suffix such as:<table_name>_<column_name>_I
  • Second, specify the name of the table followed by one or more indexed columns surrounded by parentheses.

By default, the statement creates a btree index.

When you create a new table with a primary key, Oracle automatically creates a new index for the primary key columns.

Unlike other database systems, Oracle does not automatically create an index for the foreign key columns.

Oracle examples

The following statement creates a new table named that stores members’ data:

Code language:SQL (Structured Query Language)(sql)

To load data into the   table, you use the following script:

Download load_member_data Script

The table has a primary key column, therefore, Oracle created a new index for this column. To view all indexes of a table, you query from the view:

Code language:SQL (Structured Query Language)(sql)

Here is the output:

Oracle Create Index - Index for the primary key

Creating an index on one column example

Suppose, you often want to look up members by the last name and you find that the query is quite slow. To speed up the lookup, you create an index for the column:

Code language:SQL (Structured Query Language)(sql)

Now, showing the indexes again, you will find that the table has two indexes:

Code language:SQL (Structured Query Language)(sql)

The output is:

Oracle Create Index - Index for one column

The following statement finds members whose last name is :

Code language:SQL (Structured Query Language)(sql)

To check if a query uses the index for lookup or not, you follow these steps:

First, add the clause immediately before the SQL statement:

Code language:SQL (Structured Query Language)(sql)

This explains the execution plan into the table.

Then, use the procedure to show the content of the :

Code language:SQL (Structured Query Language)(sql)

The following shows the output:

Oracle Create Index - Plan for execution

Removing an index

To remove an index, you use the statement:

Code language:SQL (Structured Query Language)(sql)

For example, to drop the index, you use the following statement:

Code language:SQL (Structured Query Language)(sql)

You will learn more about how to drop an index in the next tutorial.

Creating an index on multiple columns example

The following example creates an index on both last name and first name columns:

Code language:SQL (Structured Query Language)(sql)

The following query finds members whose last name starts with the letter and first name starts with the letter :

Code language:SQL (Structured Query Language)(sql)

This statement definitely uses the index as shown in the execution plan below:

Code language:SQL (Structured Query Language)(sql)

In this tutorial, you have learned how to use Oracle statement to create a new index on one or more columns of a table.

Sours: https://www.oracletutorial.com/oracle-index/oracle-create-index/
  1. Sabrina bryan baby
  2. Serendipity sea monster
  3. Vector table lighter insert
  4. G35 subframe brace
  5. Subaru ascent australia

Oracle / PLSQL: Indexes

totn Oracle / PLSQL

This Oracle tutorial explains how to create, rename and drop indexes in Oracle with syntax and examples.

What is an Index in Oracle?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

Create an Index

Syntax

The syntax for creating an index in Oracle/PLSQL is:

CREATE [UNIQUE] INDEX index_name ON table_name (column1, column2, ... column_n) [ COMPUTE STATISTICS ];
UNIQUE
It indicates that the combination of values in the indexed columns must be unique.
index_name
The name to assign to the index.
table_name
The name of the table in which to create the index.
column1, column2, ... column_n
The columns to use in the index.
COMPUTE STATISTICS
It tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.

Example

Let's look at an example of how to create an index in Oracle/PLSQL.

For example:

CREATE INDEX supplier_idx ON supplier (supplier_name);

In this example, we've created an index on the supplier table called supplier_idx. It consists of only one field - the supplier_name field.

We could also create an index with more than one field as in the example below:

CREATE INDEX supplier_idx ON supplier (supplier_name, city);

We could also choose to collect statistics upon creation of the index as follows:

CREATE INDEX supplier_idx ON supplier (supplier_name, city) COMPUTE STATISTICS;

Create a Function-Based Index

In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes.

Syntax

The syntax for creating a function-based index in Oracle/PLSQL is:

CREATE [UNIQUE] INDEX index_name ON table_name (function1, function2, ... function_n) [ COMPUTE STATISTICS ];
UNIQUE
It indicates that the combination of values in the indexed columns must be unique.
index_name
The name to assign to the index.
table_name
The name of the table in which to create the index.
function1, function2, ... function_n
The functions to use in the index.
COMPUTE STATISTICS
It tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.

Example

Let's look at an example of how to create a function-based index in Oracle/PLSQL.

For example:

CREATE INDEX supplier_idx ON supplier (UPPER(supplier_name));

In this example, we've created an index based on the uppercase evaluation of the supplier_name field.

However, to be sure that the Oracle optimizer uses this index when executing your SQL statements, be sure that UPPER(supplier_name) does not evaluate to a NULL value. To ensure this, add UPPER(supplier_name) IS NOT NULL to your WHERE clause as follows:

SELECT supplier_id, supplier_name, UPPER(supplier_name) FROM supplier WHERE UPPER(supplier_name) IS NOT NULL ORDER BY UPPER(supplier_name);

Rename an Index

Syntax

The syntax for renaming an index in Oracle/PLSQL is:

ALTER INDEX index_name RENAME TO new_index_name;
index_name
The name of the index that you wish to rename.
new_index_name
The new name to assign to the index.

Example

Let's look at an example of how to rename an index in Oracle/PLSQL.

For example:

ALTER INDEX supplier_idx RENAME TO supplier_index_name;

In this example, we're renaming the index called supplier_idx to supplier_index_name.

Collect Statistics on an Index

If you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics at a later date.

Syntax

The syntax for collecting statistics on an index in Oracle/PLSQL is:

ALTER INDEX index_name REBUILD COMPUTE STATISTICS;
index_name
The index in which to collect statistics.

Example

Let's look at an example of how to collect statistics for an index in Oracle/PLSQL.

For example:

ALTER INDEX supplier_idx REBUILD COMPUTE STATISTICS;

In this example, we're collecting statistics for the index called supplier_idx.

Drop an Index

Syntax

The syntax for dropping an index in Oracle/PLSQL is:

DROP INDEX index_name;
index_name
The name of the index to drop.

Example

Let's look at an example of how to drop an index in Oracle/PLSQL.

For example:

DROP INDEX supplier_idx;

In this example, we're dropping an index called supplier_idx.

Sours: https://www.techonthenet.com/oracle/indexes.php
Oracle PL/SQL avanzado: Crear Index By tables

CREATE INDEX

Purpose

Use the statement to create an index on:

  • One or more columns of a table, a partitioned table, an index-organized table, or a cluster

  • One or more scalar typed object attributes of a table or a cluster

  • A nested table storage table for indexing a nested table column

An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. Oracle Database supports several types of index:

  • Normal indexes. (By default, Oracle Database creates B-tree indexes.)

  • Bitmap indexes, which store rowids associated with a key value as a bitmap

  • Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table

  • Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.

  • Domain indexes, which are instances of an application-specific index of type

Additional Topics

Prerequisites

To create an index in your own schema, one of the following conditions must be true:

  • The table or cluster to be indexed must be in your own schema.

  • You must have the object privilege on the table to be indexed.

  • You must have the system privilege.

To create an index in another schema, you must have the system privilege. Also, the owner of the schema to contain the index must have either the system privilege or space quota on the tablespaces to contain the index or index partitions.

To create a domain index in your own schema, in addition to the prerequisites for creating a conventional index, you must also have the object privilege on the indextype. If you are creating a domain index in another user's schema, then the index owner also must have the object privilege on the indextype and its underlying implementation type. Before creating a domain index, you should first define the indextype.

To create a function-based index, in addition to the prerequisites for creating a conventional index, if the index is based on user-defined functions, then those functions must be marked . Also, you must have the object privilege on any user-defined function(s) used in the function-based index if those functions are owned by another user.

Syntax

create_index::=

Description of create_index.gif follows
Description of the illustration create_index.gif

cluster_index_clause::=

Description of cluster_index_clause.gif follows
Description of the illustration cluster_index_clause.gif

(index_attributes::=)

table_index_clause ::=

Description of table_index_clause.gif follows
Description of the illustration table_index_clause.gif

(index_properties::=)

bitmap_join_index_clause ::=

Description of bitmap_join_index_clause.gif follows
Description of the illustration bitmap_join_index_clause.gif

(local_partitioned_index ::=, index_attributes::=)

index_expr::=

Description of index_expr.gif follows
Description of the illustration index_expr.gif

index_properties::=

Description of index_properties.gif follows
Description of the illustration index_properties.gif

(global_partitioned_index ::=, local_partitioned_index ::=, index_attributes::=, domain_index_clause ::=)

index_attributes::=

Description of index_attributes.gif follows
Description of the illustration index_attributes.gif

(physical_attributes_clause ::=, logging_clause::=, key_compression=, parallel_clause::=)

physical_attributes_clause ::=

Description of physical_attributes_clause.gif follows
Description of the illustration physical_attributes_clause.gif

(storage_clause::=)

logging_clause::=

Description of logging_clause.gif follows
Description of the illustration logging_clause.gif

key_compression=

Description of key_compression.gif follows
Description of the illustration key_compression.gif

domain_index_clause ::=

Description of domain_index_clause.gif follows
Description of the illustration domain_index_clause.gif

(parallel_clause::=)

global_partitioned_index::=

Description of global_partitioned_index.gif follows
Description of the illustration global_partitioned_index.gif

(index_partitioning_clause::=, individual_hash_partitions::=, hash_partitions_by_quantity::=)

individual_hash_partitions::=

Description of individual_hash_partitions.gif follows
Description of the illustration individual_hash_partitions.gif

(partitioning_storage_clause::=)

partitioning_storage_clause::=

Description of partitioning_storage_clause.gif follows
Description of the illustration partitioning_storage_clause.gif

hash_partitions_by_quantity::=

Description of hash_partitions_by_quantity.gif follows
Description of the illustration hash_partitions_by_quantity.gif

index_partitioning_clause::=

Description of index_partitioning_clause.gif follows
Description of the illustration index_partitioning_clause.gif

(segment_attributes_clause::=)

local_partitioned_index ::=

Description of local_partitioned_index.gif follows
Description of the illustration local_partitioned_index.gif

(on_range_partitioned_table ::=, on_list_partitioned_table::=, on_hash_partitioned_table ::=, on_comp_partitioned_table ::=)

on_range_partitioned_table ::=

Description of on_range_partitioned_table.gif follows
Description of the illustration on_range_partitioned_table.gif

(segment_attributes_clause::=)

on_list_partitioned_table::=

Description of on_list_partitioned_table.gif follows
Description of the illustration on_list_partitioned_table.gif

(segment_attributes_clause::=)

segment_attributes_clause::=

Description of segment_attributes_clause.gif follows
Description of the illustration segment_attributes_clause.gif

(physical_attributes_clause ::=, logging_clause::=

on_hash_partitioned_table ::=

Description of on_hash_partitioned_table.gif follows
Description of the illustration on_hash_partitioned_table.gif

on_comp_partitioned_table ::=

Description of on_comp_partitioned_table.gif follows
Description of the illustration on_comp_partitioned_table.gif

(segment_attributes_clause::=, index_subpartition_clause::=)

index_subpartition_clause::=

Description of index_subpartition_clause.gif follows
Description of the illustration index_subpartition_clause.gif

parallel_clause::=

Description of parallel_clause.gif follows
Description of the illustration parallel_clause.gif

(storage_clause::=)

Semantics

UNIQUE

Specify to indicate that the value of the column (or columns) upon which the index is based must be unique.

Restrictions on Unique Indexes Unique indexes are subject to the following restrictions:

  • You cannot specify both and .

  • You cannot specify for a domain index.

BITMAP

Specify to indicate that is to be created with a bitmap for each distinct key, rather than indexing each row separately. Bitmap indexes store the rowids associated with a key value as a bitmap. Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. The internal representation of bitmaps is best suited for applications with low levels of concurrent transactions, such as data warehousing.

Note:

Oracle does not index table rows in which all key columns are null except in the case of bitmap indexes. Therefore, if you want an index on all rows of a table, then you must either specify constraints for the index key columns or create a bitmap index.

Restrictions on Bitmap Indexes Bitmap indexes are subject to the following restrictions:

  • You cannot specify when creating a global partitioned index.

  • You cannot create a bitmap secondary index on an index-organized table unless the index-organized table has a mapping table associated with it.

  • You cannot specify both and .

  • You cannot specify for a domain index.

schema

Specify the schema to contain the index. If you omit , then Oracle Database creates the index in your own schema.

index

Specify the name of the index to be created.

cluster_index_clause

Use the to identify the cluster for which a cluster index is to be created. If you do not qualify cluster with , then Oracle Database assumes the cluster is in your current schema. You cannot create a cluster index for a hash cluster.

table_index_clause

Specify the table on which you are defining the index. If you do not qualify with , then Oracle Database assumes the table is contained in your own schema.

You create an index on a nested table column by creating the index on the nested table storage table. Include the pseudocolumn of the storage table to create a index, which effectively ensures that the rows of a nested table value are distinct.

You can perform DDL operations (such as , , ) on a temporary table only when no session is bound to it. A session becomes bound to a temporary table by performing an operation on the table. A session becomes unbound to the temporary table by issuing a statement or at session termination, or, for a transaction-specific temporary table, by issuing a or statement.

Restrictions on the table_index_clause This clause is subject to the following restrictions:

  • If is locally partitioned, then must be partitioned.

  • If is index-organized, this statement creates a secondary index. The index contains the index key and the logical rowid of the index-organized table. The logical rowid excludes columns that are also part of the index key. You cannot specify for this secondary index, and the combined size of the index key and the logical rowid should be less than the block size.

  • If is a temporary table, then will also be temporary with the same scope (session or transaction) as . The following restrictions apply to indexes on temporary tables:

    • The only part of you can specify is .

    • Within , you cannot specify the , the , the , or .

    • You cannot create a domain index on a temporary table.

t_alias

Specify a correlation name (alias) for the table upon which you are building the index.

index_expr

For , specify the column or column expression upon which the index is based.

column  Specify the name of one or more columns in the table. A bitmap index can have a maximum of 30 columns. Other indexes can have as many as 32 columns. These columns define the indexkey.

If the index is local nonprefixed (see local_partitioned_index ), then the index key must contain the partitioning key.

You can create an index on a scalar object attribute column or on the system-defined column of the nested table storage table. If you specify an object attribute column, then the column name must be qualified with the table name. If you specify a nested table column attribute, then it must be qualified with the outermost table name, the containing column name, and all intermediate attribute names leading to the nested table column attribute.

Restriction on Index Columns You cannot create an index on columns or attributes whose type is user-defined, , , LOB, or , except that Oracle Database supports an index on type columns or attributes that have been defined with a clause.

column_expression Specify an expression built from columns of , constants, SQL functions, and user-defined functions. When you specify , you create a function-based index.

Name resolution of the function is based on the schema of the index creator. User-defined functions used in are fully name resolved during the operation.

After creating a function-based index, collect statistics on both the index and its base table using the package. Such statistics will enable Oracle Database to correctly decide when to use the index.

Function-based unique indexes can be useful in defining a conditional unique constraint on a column or combination of columns. Please refer to "Using a Function-based Index to Define Conditional Uniqueness: Example" for an example.

Notes on Function-based Indexes The following notes apply to function-based indexes:

  • When you subsequently query a table that uses a function-based index, you must ensure in the query that is not null. However, Oracle Database will use a function-based index in a query even if the columns specified in the clause are in a different order than their order in the that defined the function-based index.

  • If the function on which the index is based becomes invalid or is dropped, then Oracle Database marks the index . Queries on a index fail if the optimizer chooses to use the index. DML operations on a index fail unless the index is also marked and the parameter is set to . Please refer to ALTER SESSION for more information on this parameter.

  • If a public synonym for a function, package, or type is used in , and later an actual object with the same name is created in the table owner's schema, then Oracle Database disables the function-based index. When you subsequently enable the function-based index using ... or ... , the function, package, or type used in the continues to resolve to the function, package, or type to which the public synonym originally pointed. It will not resolve to the new function, package, or type.

  • If the definition of a function-based index generates internal conversion to character data, then use caution when changing NLS parameter settings. Function-based indexes use the current database settings for NLS parameters. If you reset these parameters at the session level, then queries using the function-based index may return incorrect results. Two exceptions are the collation parameters ( and ). Oracle Database handles the conversions correctly even if these have been reset at the session level.

Restrictions on Function-based Indexes Function-based indexes are subject to the following restrictions:

  • The value returned by the function referenced in is subject to the same restrictions as are the index columns of a B-tree index. Please refer to "Restriction on Index Columns".

  • Any user-defined function referenced in must be declared as .

  • For a function-based globally partitioned index, the cannot be the partitioning key.

  • The can be any form of expression except a scalar subquery expression.

  • All functions must be specified with parentheses, even if they have no parameters. Otherwise Oracle Database interprets them as column names.

  • Any function you specify in must return a repeatable value. For example, you cannot specify the or function or the pseudocolumn.

  • The cannot contain any aggregate functions.

ASC | DESC

Use or to indicate whether the index should be created in ascending or descending order. Indexes on character data are created in ascending or descending order of the character values in the database character set.

Oracle Database treats descending indexes as if they were function-based indexes. As with other function-based indexes, the database does not use descending indexes until you first analyze the index and the table on which the index is defined. See the column_expression clause of this statement.

Ascending unique indexes allow multiple values. However, in descending unique indexes, multiple values are treated as duplicate values and therefore are not permitted.

Restriction on Ascending and Descending Indexes You cannot specify either of these clauses for a domain index. You cannot specify for a reverse index. Oracle Database ignores if is bitmapped or if the initialization parameter is set to a value less than 8.1.0.

index_attributes

Specify the optional index attributes.

physical_attributes_clause  Use the to establish values for physical and storage characteristics for the index.

If you omit this clause, then Oracle Database sets to 10 and to 2.

Restriction on Index Physical Attributes You cannot specify the parameter for an index.

TABLESPACE For , specify the name of the tablespace to hold the index, index partition, or index subpartition. If you omit this clause, then Oracle Database creates the index in the default tablespace of the owner of the schema containing the index.

For a local index, you can specify the keyword in place of . New partitions or subpartitions added to the local index will be created in the same tablespace(s) as the corresponding partitions or subpartitions of the underlying table.

key_compression Specify to enable key compression, which eliminates repeated occurrence of key column values and may substantially reduce storage. Use to specify the prefix length (number of prefix columns to compress).

  • For unique indexes, the valid range of prefix length values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1.

  • For nonunique indexes, the valid range of prefix length values is from 1 to the number of key columns. The default prefix length is the number of key columns.

    Oracle Database compresses only nonpartitioned indexes that are nonunique or unique indexes of at least two columns.

Restriction on Key Compression You cannot specify for a bitmap index.

NOCOMPRESS  Specify to disable key compression. This is the default.

SORT | NOSORT  By default, Oracle Database sorts indexes in ascending order when it creates the index. You can specify to indicate to the database that the rows are already stored in the database in ascending order, so that Oracle Database does not have to sort the rows when creating the index. If the rows of the indexed column or columns are not stored in ascending order, then the database returns an error. For greatest savings of sort time and space, use this clause immediately after the initial load of rows into a table. If you specify neither of these keywords, then is the default.

Restrictions on NOSORT This parameter is subject to the following restrictions:

  • You cannot specify with this clause.

  • You cannot use this clause to create a cluster index partitioned or bitmap index.

  • You cannot specify this clause for a secondary index on an index-organized table.

REVERSE  Specify to store the bytes of the index block in reverse order, excluding the rowid.

Restrictions on Reverse Indexes Reverse indexes are subject to the following restrictions:

  • You cannot specify with this clause.

  • You cannot reverse a bitmap index or an index on an index-organized table.

logging_clause Specify whether the creation of the index will be logged () or not logged () in the redo log file. This setting also determines whether subsequent Direct Loader (SQL*Loader) and direct-path operations against the index are logged or not logged. is the default.

If is nonpartitioned, then this clause specifies the logging attribute of the index.

If is partitioned, then this clause determines:

  • The default value of all partitions specified in the statement, unless you specify the in the description clause

  • The default value for the segments associated with the index partitions

  • The default value for local index partitions or subpartitions added implicitly during subsequent ... operations

The logging attribute of the index is independent of that of its base table.

If you omit this clause, then the logging attribute is that of the tablespace in which it resides.

ONLINE  Specify to indicate that DML operations on the table will be allowed during creation of the index.

Restrictions on Online Index Building Online index building is subject to the following restrictions:

  • Parallel DML is not supported during online index building. If you specify and then issue parallel DML statements, then Oracle Database returns an error.

  • You cannot specify for a bitmap index or a cluster index.

  • You cannot specify for a conventional index on a column.

  • For a nonunique secondary index on an index-organized table, the number of index key columns plus the number of primary key columns that are included in the logical rowid in the index-organized table cannot exceed 32. The logical rowid excludes columns that are part of the index key.

COMPUTE STATISTICS  In earlier releases, you could use this clause to start or stop the collection of statistics on an index. This clause has been deprecated. Oracle Database now automatically collects statistics during index creation and rebuild. This clause is supported for backward compatibility and will not cause errors.

Restriction on COMPUTE STATISTICS Clause You cannot specify this clause for a domain index.

parallel_clause

Specify the if you want creation of the index to be parallelized.

For complete information on this clause, please refer to parallel_clause in the documentation on .

Index Partitioning Clauses

Use the clause and the clauses to partition .

The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle Database Administrator's Guide for a discussion of these restrictions.

global_partitioned_index

The clause lets you specify that the partitioning of the index is user defined and is not equipartitioned with the underlying table. By default, nonpartitioned indexes are global indexes.

You can partition a global index by range or by hash. In both cases, you can specify up to 32 columns as partitioning key columns. The partitioning column list must specify a left prefix of the index column list. That is, if the index is defined on columns , , and , then for the columns you can specify , , , or (, , or (, , but you cannot specify (, or or (, ). If you omit the partition names, then Oracle Database assigns names of the form .

GLOBAL PARTITION BY RANGE Use this clause to create a range-partitioned global index. Oracle Database will partition the global index on the ranges of values from the table columns you specify in the column list.

GLOBAL PARTITION BY HASH Use this clause to create a hash-partitioned global index. Oracle Database assigns rows to the partitions using a hash function on values in the partitioning key columns.

Restrictions on Global Partitioned Indexes Global partitioned indexes are subject to the following restrictions:

  • The partitioning key column list cannot contain the pseudocolumn or a column of type .

  • The only property you can specify for hash partitions is tablespace storage. Therefore, you cannot specify LOB or varray storage clauses in the of .

  • You cannot specify the clause of , as that clause is valid only for index-organized table partitions.

Note:

If your enterprise has or will have databases using different character sets, then use caution when partitioning on character columns. The sort sequence of characters is not identical in all character sets.

index_partitioning_clause Use this clause to describe the individual index partitions. The number of repetitions of this clause determines the number of partitions. If you omit , then Oracle Database generates a name with the form .

For (), specify the noninclusive upper bound for the current partition in a global index. The value list is a comma-delimited, ordered list of literal values corresponding to the column list in the clause. Always specify as the value of the last partition.

Note:

If the index is partitioned on a column, and if the date format does not specify the first two digits of the year, then you must use the function with a 4-character format mask for the year. The date format is determined implicitly by or explicitly by . Please refer to Oracle Database Globalization Support Guidefor more information on these initialization parameters.

local_partitioned_index

The clauses let you specify that the index is partitioned on the same columns, with the same number of partitions and the same partition bounds as . Oracle Database automatically maintains local index partitioning as the underlying table is repartitioned.

on_range_partitioned_table  This clause lets you specify the names and attributes of index partitions on a range-partitioned table. If you specify this clause, then the number of clauses must be equal to the number of table partitions, and in the same order. If you omit , then Oracle Database generates a name that is consistent with the corresponding table partition. If the name conflicts with an existing index partition name, then the database uses the form .

You cannot specify key compression for an index partition unless you have specified key compression for the index.

on_list_partitioned_table The clause is identical to on_range_partitioned_table .

on_hash_partitioned_table  This clause lets you specify names and tablespace storage for index partitions on a hash-partitioned table.

If you specify any clauses, then the number of these clauses must be equal to the number of table partitions. If you omit , then Oracle Database generates a name that is consistent with the corresponding table partition. If the name conflicts with an existing index partition name, then the database uses the form . You can optionally specify tablespace storage for one or more individual partitions. If you do not specify tablespace storage either here or in the clause, then the database stores each index partition in the same tablespace as the corresponding table partition.

The clause lets you specify one or more tablespaces across which Oracle Database will distribute all the index hash partitions. The number of tablespaces need not equal the number of index partitions. If the number of index partitions is greater than the number of tablespaces, then the database cycles through the names of the tablespaces.

on_comp_partitioned_table  This clause lets you specify the name and tablespace storage of index partitions on a composite-partitioned table.

The clause is valid only for range-hash composite-partitioned tables. It lets you specify one or more default tablespaces across which Oracle Database will distribute all index hash subpartitions. You can override this storage by specifying different tablespace storage for the subpartitions of an individual partition in the second clause in the .

For range-list composite-partitioned tables, you can specify default tablespace storage for the list subpartitions in the clause. You can override this storage by specifying different tablespace storage for the list subpartitions of an individual partition in the clause of the .

You cannot specify key compression for an index partition unless you have specified key compression for the index.

index_subpartition_clause This clause lets you specify names and tablespace storage for index subpartitions in a composite-partitioned table.

The clause is valid only for hash subpartitions of a range-hash composite-partitioned table. It lets you specify one or more tablespaces across which Oracle Database will distribute all the index hash subpartitions. The clause is valid for subpartitions of both range-hash and range-list composite-partitioned tables.

If you specify any clauses, then the number of those clauses must be equal to the number of table subpartitions. If you omit , then the database generates a name that is consistent with the corresponding table subpartition. If the name conflicts with an existing index subpartition name, then the database uses the form .

The number of tablespaces need not equal the number of index subpartitions. If the number of index subpartitions is greater than the number of tablespaces, then the database cycles through the names of the tablespaces.

If you do not specify tablespace storage for subpartitions either in the clause or in the , then Oracle Database uses the tablespace specified for . If you also do not specify tablespace storage for , then the database stores the subpartition in the same tablespace as the corresponding table subpartition.

domain_index_clause

Use the to indicate that is a domain index, which is an instance of an application-specific index of type .

Creating a domain index requires a number of preceding operations. You must first create an implementation type for an indextype. You must also create a functional implementation and then create an operator that uses the function. Next you create an indextype, which associates the implementation type with the operator. Finally, you create the domain index using this clause. Please refer to Appendix E, "Examples", which contains an example of creating a simple domain index, including all of these operations.

index_expr In the (in ), specify the table columns or object attributes on which the index is defined. You can define multiple domain indexes on a single column only if the underlying indextypes are different and the indextypes support a disjoint set of user-defined operators.

Restrictions on Domain Indexes Domain indexes are subject to the following restrictions:

  • The (in ) can specify only a single column, and the column cannot be of datatype , varray, nested table, , or .

  • You cannot create a bitmap or unique domain index.

  • You cannot create a domain index on a temporary table.

indextype For , specify the name of the indextype. This name should be a valid schema object that has already been created.

If you have installed Oracle Text, you can use various built-in indextypes to create Oracle Text domain indexes. For more information on Oracle Text and the indexes it uses, please refer to Oracle Text Reference.

parallel_clause Use the to parallelize creation of the domain index. For a nonpartitioned domain index, Oracle Database passes the explicit or default degree of parallelism to the cartridge routine, which in turn establishes parallelism for the index.

PARAMETERS In the clause, specify the parameter string that is passed uninterpreted to the appropriate ODCI indextype routine. The maximum length of the parameter string is 1000 characters.

When you specify this clause at the top level of the syntax, the parameters become the default parameters for the index partitions. If you specify this clause as part of the [] clause, you override any default parameters with parameters for the individual partition.

After the domain index is created, Oracle Database invokes the appropriate ODCI routine. If the routine does not return successfully, the domain index is marked . The only operations supported on an failed domain index are and (for non-local indexes) .

bitmap_join_index_clause

Use the to define a bitmap join index. A bitmap join index is defined on a single table. For an index key made up of dimension table columns, it stores the fact table rowids corresponding to that key. In a data warehousing environment, the table on which the index is defined is commonly referred to as a fact table, and the tables with which this table is joined are commonly referred to as dimension tables. However, a star schema is not a requirement for creating a join index.

ON In the clause, first specify the fact table, and then inside the parentheses specify the columns of the dimension tables on which the index is defined.

FROM In the clause, specify the joined tables.

WHERE In the clause, specify the join condition.

If the underlying fact table is partitioned, you must also specify one of the clauses (see local_partitioned_index ).

Restrictions on Bitmap Join Indexes In addition to the restrictions on bitmap indexes in general (see BITMAP), the following restrictions apply to bitmap join indexes:

  • You cannot create a bitmap join index on an index-organized table or a temporary table.

  • No table may appear twice in the clause.

  • You cannot create a function-based join index.

  • The dimension table columns must be either primary key columns or have unique constraints.

  • If a dimension table has a composite primary key, each column in the primary key must be part of the join.

  • You cannot specify the unless the fact table is partitioned.

See Also:

Oracle Data Warehousing Guidefor information on fact and dimension tables and on using bitmap indexes in a data warehousing environment

Examples

General Index Examples

Creating an Index: Example The following statement shows how the sample index on the column of the sample table was created:

CREATE INDEX ord_customer_ix ON orders (customer_id);

Compressing an Index: Example To create the index with the clause, you might issue the following statement:

CREATE INDEX ord_customer_ix_demo ON orders (customer_id, sales_rep_id) COMPRESS 1;

The index will compress repeated occurrences of column values.

Creating an Index in NOLOGGING Mode: Example If the sample table had been created using a fast parallel load (so all rows were already sorted), you could issue the following statement to quickly create an index.

/* Unless you first sort the table oe.orders, this example fails because you cannot specify NOSORT unless the base table is already sorted. */ CREATE INDEX ord_customer_ix_demo ON orders (order_mode) NOSORT NOLOGGING;

Creating a Cluster Index: Example To create an index for the cluster, which was created in "Creating a Cluster: Example", issue the following statement:

CREATE INDEX idx_personnel ON CLUSTER personnel;

No index columns are specified, because cluster indexes are automatically built on all the columns of the cluster key. For cluster indexes, all rows are indexed.

Creating an Index on an XMLType Table: Example The following example creates an index on the area element of the table (created in "XMLType Table Examples"):

CREATE INDEX area_index ON xwarehouses e (EXTRACTVALUE(VALUE(e),'/Warehouse/Area'));

Such an index would greatly improve the performance of queries that select from the table based on, for example, the square footage of a warehouse, as shown in this statement:

SELECT e.getClobVal() AS warehouse FROM xwarehouses e WHERE EXISTSNODE(VALUE(e),'/Warehouse[Area>50000]') = 1;

Function-Based Index Examples

The following examples show how to create and use function-based indexes.

Creating a Function-Based Index: Example The following statement creates a function-based index on the table based on an uppercase evaluation of the column:

CREATE INDEX upper_ix ON employees (UPPER(last_name));

See the "Prerequisites" for the privileges and parameter settings required when creating function-based indexes.

To ensure that Oracle Database will use the index rather than performing a full table scan, be sure that the value returned by the function is not null in subsequent queries. For example, this statement is guaranteed to use the index:

SELECT first_name, last_name FROM employees WHERE UPPER(last_name) IS NOT NULL ORDER BY UPPER(last_name);

Without the clause, Oracle Database may perform a full table scan.

In the next statements showing index creation and subsequent query, Oracle Database will use index even though the columns are in reverse order in the query:

CREATE INDEX income_ix ON employees(salary + (salary*commission_pct)); SELECT first_name||' '||last_name "Name" FROM employees WHERE (salary*commission_pct) + salary > 15000;

Creating a Function-Based Index on a LOB Column: Example The following statement uses the function created in "Using a Packaged Procedure in a Function: Example" to create a function-based index on a LOB column in the sample schema. The example then collects statistics on the function-based index and selects rows from the sample table where that column has fewer than 1000 characters.

CREATE INDEX src_idx ON print_media(text_length(ad_sourcetext)); ANALYZE INDEX src_idx COMPUTE STATISTICS; SELECT product_id FROM print_media WHERE text_length(ad_sourcetext) < 1000; PRODUCT_ID ---------- 3060 2056 3106 2268

Creating a Function-based Index on a Type Method: Example This example entails an object type containing two number attributes: and . The method computes the area of the rectangle.

CREATE TYPE rectangle AS OBJECT ( length NUMBER, width NUMBER, MEMBER FUNCTION area RETURN NUMBER DETERMINISTIC ); CREATE OR REPLACE TYPE BODY rectangle AS MEMBER FUNCTION area RETURN NUMBER IS BEGIN RETURN (length*width); END; END;

Now, if you create a table of type , you can create a function-based index on the method as follows:

CREATE TABLE rect_tab OF rectangle; CREATE INDEX area_idx ON rect_tab x (x.area());

You can use this index efficiently to evaluate a query of the form:

SELECT * FROM rect_tab x WHERE x.area() > 100;

Using a Function-based Index to Define Conditional Uniqueness: Example  The following statement creates a unique function-based index on the table that prevents a customer from taking advantage of promotion ID 2 ("blowout sale") more than once:

CREATE UNIQUE INDEX promo_ix ON orders (CASE WHEN promotion_id =2 THEN customer_id ELSE NULL END, CASE WHEN promotion_id = 2 THEN promotion_id ELSE NULL END); INSERT INTO orders (order_id, order_date, customer_id, order_total, promotion_id) VALUES (2459, systimestamp, 106, 251, 2); 1 row created. INSERT INTO orders (order_id, order_date, customer_id, order_total, promotion_id) VALUES (2460, systimestamp+1, 106, 110, 2); insert into orders (order_id, order_date, customer_id, order_total, promotion_id) * ERROR at line 1: ORA-00001: unique constraint (OE.PROMO_IX) violated

The objective is to remove from the index any rows where the is not equal to 2. Oracle Database does not store in the index any rows where all the keys are . Therefore, in this example, we map both and to unless promotion_id is equal to 2. The result is that the index constraint is violated only if promotion_id is equal to 2 for two rows with the same value.

Partitioned Index Examples

Creating a Range-Partitioned Global Index: Example The following statement creates a global prefixed index on the sample table with three partitions that divide the range of costs into three groups:

CREATE INDEX cost_ix ON sales (amount_sold) GLOBAL PARTITION BY RANGE (amount_sold) (PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (2500), PARTITION p3 VALUES LESS THAN (MAXVALUE));

Creating a Hash-Partitioned Global Index: Example The following statement creates a hash-partitioned global index on the sample table with four partitions:

CREATE INDEX cust_last_name_ix ON customers (cust_last_name) GLOBAL PARTITION BY HASH (cust_last_name) PARTITIONS 4;

Creating an Index on a Hash-Partitioned Table: Example The following statement creates a local index on the column of the partitioned table (which was created in "Hash Partitioning Example"). The clause immediately following indicates that is hash partitioned. Oracle Database will distribute the hash partitions between the and tablespaces:

CREATE INDEX prod_idx ON hash_products(product_id) LOCAL STORE IN (tbs_01, tbs_02);

The creator of the index must have quota on the tablespaces specified. See CREATE TABLESPACE for examples that create tablespaces and .

Creating an Index on a Composite-Partitioned Table: Example The following statement creates a local index on the table, which was created in "Composite-Partitioned Table Examples". The clause specifies default storage attributes for the index. However, this default is overridden for the five subpartitions of partitions and , because separate storage is specified.

The creator of the index must have quota on the tablespaces specified. See CREATE TABLESPACE for examples that create tablespaces and .

CREATE INDEX sales_ix ON composite_sales(time_id, prod_id) STORAGE (INITIAL 1M MAXEXTENTS UNLIMITED) LOCAL (PARTITION q1_1998, PARTITION q2_1998, PARTITION q3_1998, PARTITION q4_1998, PARTITION q1_1999, PARTITION q2_1999, PARTITION q3_1999, PARTITION q4_1999, PARTITION q1_2000, PARTITION q2_2000 (SUBPARTITION pq2001, SUBPARTITION pq2002, SUBPARTITION pq2003, SUBPARTITION pq2004, SUBPARTITION pq2005, SUBPARTITION pq2006, SUBPARTITION pq2007, SUBPARTITION pq2008), PARTITION q3_2000 (SUBPARTITION c1 TABLESPACE tbs_02, SUBPARTITION c2 TABLESPACE tbs_02, SUBPARTITION c3 TABLESPACE tbs_02, SUBPARTITION c4 TABLESPACE tbs_02, SUBPARTITION c5 TABLESPACE tbs_02), PARTITION q4_2000 (SUBPARTITION pq4001 TABLESPACE tbs_03, SUBPARTITION pq4002 TABLESPACE tbs_03, SUBPARTITION pq4003 TABLESPACE tbs_03, SUBPARTITION pq4004 TABLESPACE tbs_03) );

Bitmap Index Example

The following creates a bitmap join index on the table , which was created in "Hash Partitioning Example":

CREATE BITMAP INDEX product_bm_ix ON hash_products(list_price) TABLESPACE tbs_1 LOCAL(PARTITION ix_p1 TABLESPACE tbs_02, PARTITION ix_p2, PARTITION ix_p3 TABLESPACE tbs_03, PARTITION ix_p4, PARTITION ix_p5 TABLESPACE tbs_04 );

Because is a partitioned table, the bitmap join index must be locally partitioned. In this example, the user must have quota on tablespaces specified. See CREATE TABLESPACE for examples that create tablespaces , , and .

Indexes on Nested Tables: Example

The sample table contains a nested table column , which is stored in storage table . The following example creates a unique index on storage table :

CREATE UNIQUE INDEX nested_tab_ix ON textdocs_nestedtab(NESTED_TABLE_ID, document_typ);

Including pseudocolumn ensures distinct rows in nested table column .

Indexing on Substitutable Columns: Examples

You can build an index on attributes of the declared type of a substitutable column. In addition, you can reference the subtype attributes by using the appropriate function. The following example uses the table , which is created in "Substitutable Table and Column Examples". The statement creates an index on the attribute of all employee authors in the table:

CREATE INDEX salary_i ON books (TREAT(author AS employee_t).salary);

The target type in the argument of the function must be the type that added the attribute being referenced. In the example, the target of is , which is the type that added the attribute.

If this condition is not satisfied, then Oracle Database interprets the function as any functional expression and creates the index as a function-based index. For example, the following statement creates a function-based index on the attribute of part-time employees, assigning nulls to instances of all other types in the type hierarchy.

CREATE INDEX salary_func_i ON persons p (TREAT(VALUE(p) AS part_time_emp_t).salary);

You can also build an index on the type-discriminant column underlying a substitutable column by using the function.

Note:

Oracle Database uses the type-discriminant column to evaluate queries that involve the condition. The cardinality of the typeid column is normally low, so Oracle recommends that you build a bitmap index in this situation.

The following statement creates a bitmap index on the typeid of the author column of the books table:

CREATE BITMAP INDEX typeid_i ON books (SYS_TYPEID(author));
Sours: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5010.htm

Index plsql

Indexes

An index is a schema object that has the role to provide direct and fast access without reading the entire table. Indexes are created explicitly or automatically.
When using indexes you must consider the following tips:

  • Create indexes after inserting table data and index on the correct tables / columns, that are most used in queries.
  • Order index columns for performance and limit the number of indexes for each table.
  • Assign index size and set storage parameters.
  • Indexes are created in the default tablespace of the schema, you can specify the tablespace for each index.
  • Drop indexes that are no longer useful or no longer needed.

Create index oracle examples
Alter index oracle examples
Drop index oracle example

B-tree indexes

B-tree indexes – By default, Oracle Database creates B-tree indexes(normal indexes).

CREATE INDEX index_name ON table_name(column_name);

Bitmap indexes

The Bitmap indexes store the rowids column value in bits.

CREATE BITMAP INDEX bitmap_index_name ON table_name(column_name) TABLESPACE tbs_1 LOCAL(PARTITION ix_p1 TABLESPACE tbs_02, PARTITION ix_p2, PARTITION ix_p3 TABLESPACE tbs_03);

Partitioned indexes

Partitioned indexes are partitions that store an entry for each value that appears in the indexed column of the table.

CREATE INDEX index_name ON table_name (column_name) GLOBAL PARTITION BY RANGE (column_name) (PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (200), PARTITION p3 VALUES LESS THAN (MAXVALUE));

Function-based indexes

Function-based indexes are based on expressions. You can build queries that evaluate the value returned by an expression.

CREATE INDEX index_name ON table_name(UPPER(column_name));

Domain indexes

A domain index is an instance of an index which is accessed by routines supplied by an indextype.

Sours: https://www.plsql.co/indexes.html
SQL tutorial 62: Indexes In Oracle Database By Manish Sharma RebellionRider

PL/SQL Tutorial

PL/SQL tutorial

PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL. PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java. This tutorial will give you great understanding on PL/SQL to proceed with Oracle database and other advanced RDBMS concepts.

This tutorial is designed for Software Professionals, who are willing to learn PL/SQL Programming Language in simple and easy steps. This tutorial will give you great understanding on PL/SQL Programming concepts, and after completing this tutorial, you will be at an intermediate level of expertise from where you can take yourself to a higher level of expertise.

Before proceeding with this tutorial, you should have a basic understanding of software basic concepts like what is database, source code, text editor and execution of programs, etc. If you already have an understanding on SQL and other computer programming language, then it will be an added advantage to proceed.

Sours: https://www.tutorialspoint.com/plsql/index.htm

You will also like:

PL/SQL - Collections



In this chapter, we will discuss the Collections in PL/SQL. A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection.

PL/SQL provides three collection types −

  • Index-by tables or Associative array
  • Nested table
  • Variable-size array or Varray

Oracle documentation provides the following characteristics for each type of collections −

Collection TypeNumber of ElementsSubscript TypeDense or SparseWhere CreatedCan Be Object Type Attribute
Associative array (or index-by table)UnboundedString or integerEitherOnly in PL/SQL blockNo
Nested tableUnboundedIntegerStarts dense, can become sparseEither in PL/SQL block or at schema levelYes
Variablesize array (Varray)BoundedIntegerAlways denseEither in PL/SQL block or at schema levelYes

We have already discussed varray in the chapter 'PL/SQL arrays'. In this chapter, we will discuss the PL/SQL tables.

Both types of PL/SQL tables, i.e., the index-by tables and the nested tables have the same structure and their rows are accessed using the subscript notation. However, these two types of tables differ in one aspect; the nested tables can be stored in a database column and the index-by tables cannot.

Index-By Table

An index-by table (also called an associative array) is a set of key-value pairs. Each key is unique and is used to locate the corresponding value. The key can be either an integer or a string.

An index-by table is created using the following syntax. Here, we are creating an index-by table named table_name, the keys of which will be of the subscript_type and associated values will be of the element_type

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; table_name type_name;

Example

Following example shows how to create a table to store integer values along with names and later it prints the same list of names.

DECLARE TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20); salary_list salary; name VARCHAR2(20); BEGIN -- adding elements to the table salary_list('Rajnish') := 62000; salary_list('Minakshi') := 75000; salary_list('Martin') := 100000; salary_list('James') := 78000; -- printing the table name := salary_list.FIRST; WHILE name IS NOT null LOOP dbms_output.put_line ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name))); name := salary_list.NEXT(name); END LOOP; END; /

When the above code is executed at the SQL prompt, it produces the following result −

Salary of James is 78000 Salary of Martin is 100000 Salary of Minakshi is 75000 Salary of Rajnish is 62000 PL/SQL procedure successfully completed.

Example

Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as −

Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +----+----------+-----+-----------+----------+ DECLARE CURSOR c_customers is select name from customers; TYPE c_list IS TABLE of customers.Name%type INDEX BY binary_integer; name_list c_list; counter integer :=0; BEGIN FOR n IN c_customers LOOP counter := counter +1; name_list(counter) := n.name; dbms_output.put_line('Customer('||counter||'):'||name_lis t(counter)); END LOOP; END; /

When the above code is executed at the SQL prompt, it produces the following result −

Customer(1): Ramesh Customer(2): Khilan Customer(3): kaushik Customer(4): Chaitali Customer(5): Hardik Customer(6): Komal PL/SQL procedure successfully completed

Nested Tables

A nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in the following aspects −

  • An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.

  • An array is always dense, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it.

A nested table is created using the following syntax −

TYPE type_name IS TABLE OF element_type [NOT NULL]; table_name type_name;

This declaration is similar to the declaration of an index-by table, but there is no INDEX BY clause.

A nested table can be stored in a database column. It can further be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array cannot be stored in the database.

Example

The following examples illustrate the use of nested table −

DECLARE TYPE names_table IS TABLE OF VARCHAR2(10); TYPE grades IS TABLE OF INTEGER; names names_table; marks grades; total integer; BEGIN names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); marks:= grades(98, 97, 78, 87, 92); total := names.count; dbms_output.put_line('Total '|| total || ' Students'); FOR i IN 1 .. total LOOP dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i)); end loop; END; /

When the above code is executed at the SQL prompt, it produces the following result −

Total 5 Students Student:Kavita, Marks:98 Student:Pritam, Marks:97 Student:Ayan, Marks:78 Student:Rishav, Marks:87 Student:Aziz, Marks:92 PL/SQL procedure successfully completed.

Example

Elements of a nested table can also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as −

Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +----+----------+-----+-----------+----------+ DECLARE CURSOR c_customers is SELECT name FROM customers; TYPE c_list IS TABLE of customerS.No.ame%type; name_list c_list := c_list(); counter integer :=0; BEGIN FOR n IN c_customers LOOP counter := counter +1; name_list.extend; name_list(counter) := n.name; dbms_output.put_line('Customer('||counter||'):'||name_list(counter)); END LOOP; END; /

When the above code is executed at the SQL prompt, it produces the following result −

Customer(1): Ramesh Customer(2): Khilan Customer(3): kaushik Customer(4): Chaitali Customer(5): Hardik Customer(6): Komal PL/SQL procedure successfully completed.

Collection Methods

PL/SQL provides the built-in collection methods that make collections easier to use. The following table lists the methods and their purpose −

S.NoMethod Name & Purpose
1

EXISTS(n)

Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.

2

COUNT

Returns the number of elements that a collection currently contains.

3

LIMIT

Checks the maximum size of a collection.

4

FIRST

Returns the first (smallest) index numbers in a collection that uses the integer subscripts.

5

LAST

Returns the last (largest) index numbers in a collection that uses the integer subscripts.

6

PRIOR(n)

Returns the index number that precedes index n in a collection.

7

NEXT(n)

Returns the index number that succeeds index n.

8

EXTEND

Appends one null element to a collection.

9

EXTEND(n)

Appends n null elements to a collection.

10

EXTEND(n,i)

Appends n copies of the ith element to a collection.

11

TRIM

Removes one element from the end of a collection.

12

TRIM(n)

Removes n elements from the end of a collection.

13

DELETE

Removes all elements from a collection, setting COUNT to 0.

14

DELETE(n)

Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.

15

DELETE(m,n)

Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

Collection Exceptions

The following table provides the collection exceptions and when they are raised −

Collection ExceptionRaised in Situations
COLLECTION_IS_NULLYou try to operate on an atomically null collection.
NO_DATA_FOUNDA subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNTA subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMITA subscript is outside the allowed range.
VALUE_ERRORA subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.
Sours: https://www.tutorialspoint.com/plsql/plsql_collections.htm


1272 1273 1274 1275 1276