What is an Index?
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
The syntax for creating a index is:
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];
UNIQUE indicates that the combination of values in the indexed columns must be unique.
COMPUTE STATISTICS 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.
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.
The syntax for creating a function-based index is:
CREATE [UNIQUE] INDEX index_name
ON table_name (function1, function2, . function_n)
[ COMPUTE STATISTICS ];
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
The syntax for renaming an index is:
ALTER INDEX index_name
RENAME TO new_index_name;
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.
The syntax for collecting statistics on an index is:
ALTER INDEX index_name
REBUILD COMPUTE STATISTICS;
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
The syntax for dropping an index is:
DROP INDEX index_name;
For example:
DROP INDEX supplier_idx;
In this example, we're dropping an index called supplier_idx.
分享到:
相关推荐
1,心血来潮,把ThinkPHP项目部署到了nginx上,以上是在apache上跑的。...index index.html index.htm index.php default.html default.htm default.php; root /home/wwwroot/www.tp.com; include index.php.con
下面代码实现了将df中的column列作为index df.set_index([Column], inplace=True) 以上这篇Python将DataFrame的某一列作为index的方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持...
在输出代码行中,加入“index=False”如下: m_pred_survived.to_csv(clasified.csv,index=False) 以上这篇Python DataFrame 设置输出不显示index(索引)值的方法就是小编分享给大家的全部内容了,希望能给大家一个...
报错大致意思就是创建index.lock文件失败,因为已经存在index.lock文件了. index.lock文件是在.git下面, 而.git是一般是隐藏的, 那么可以通过以下命令删除即可. rm -rf /Users/dianji/project/ccpay.h5/.git/index....
你知道在C:Documents and Settings用户名Local SettingsTemporary Internet FilesContent.IE5文件夹下的index.dat文件吗? <br>不用找了,看不到的,打开查看所有文件也看不到,在Temporary Internet Files...
OpenAI 的 Generative Pre-trained Transformer 3 (GPT...使用 GPT-Index 在自定义数据集上微调 GPT-3,只需 10 行代码即可完成所有操作!GPT-Index 通过提供用于将外部知识库与 LLM 连接的高级 API 来完成繁重的工作。
index.php
index.rar index.rar index.rar
一个小项目(注释真的很多撒),支持以下几种方式,自己下载index.m3u8,给出网页基址然后自动下载ts,或者直接给出视频网页的地址(一般情况下,视频每一集的网页地址是有规律的,其视频所在标签会给出相应的m3u8文件...
index.m3u8
Index.dat Suite(index.dat清理工具)是一款专业的index.dat清理工具,平常我们清理的时候并不会把上网记录都清理掉,就算想清理也找不到文件,而这款软件就可以帮你轻松找到并且删除信息,不管是黑客还是其他人,都...
String index out of 4解决方法
index页面详解1
wangeditor 中index.js文件
index.php index.php
ThinkPHP 怎样才能URL地址不写index.php.ThinkPHP 怎样才能省略掉URL地址中的index.php字样
heh index页面好东西index页面好东西index页面好东西index页面好东西index页面好东西index页面好东西