Oracle中索引示例

--drop table customers;
--建测试用表
CREATE TABLE customers (
  customer_id INTEGER CONSTRAINT customers_pk PRIMARY KEY,
  first_name VARCHAR2(10) NOT NULL,
  last_name VARCHAR2(10) NOT NULL,
  dob DATE,
  phone VARCHAR2(12)
);
drop table customers;
--添加测试数据
INSERT INTO customers
VALUES (1, 'John', 'Brown', '01-1月-1965', '800-555-1211');
INSERT INTO customers 
VALUES (2, 'Cynthia', 'Green', '05-2月-1968', '800-555-1212');
INSERT INTO customers
VALUES (3, 'Steve', 'White', '16-3月-1971', '800-555-1213');
INSERT INTO customers
VALUES (4, 'Gail', 'Black', NULL, '800-555-1214');
INSERT INTO customers
VALUES (5, 'Doreen', 'Blue', '20-5月-1970', NULL);
commit;

select * from customers;
SELECT customer_id, first_name, last_name
FROM customers
WHERE last_name = 'Brown';

--创建普通的B树（默认索引类型）
CREATE INDEX i_customers_last_name 
ON customers(last_name);

--可以使用唯一索引强制列值的唯一
CREATE UNIQUE INDEX i_customers_phone
ON customers(phone);
--由于查询使用了UPPER()函数，这样的i_customers_last_name索引就不起作用。

--如果你的索引是基于函数处理的结果的话，那么可以使用函数索引
CREATE INDEX i_func_customers_last_name
ON customers(UPPER(last_name));
select last_name from customers;

--在order_status表的status列上创建位图索引，使用在性别列上，
CREATE BITMAP INDEX i_emp_ename
ON customers(first_name);
select * from emp;


--切换登录去执行
ALTER lianxi SET QUERY_REWRITE_ENABLED=TRUE;
--授权
grant create any INDEX to lianxi;
--可以通过查看user_indexes视图，了解索引的信息
SELECT index_name, table_name, uniqueness, status
FROM user_indexes
WHERE table_name IN ('CUSTOMERS')
ORDER BY index_name;
--customers_pk索引是在创建表时设置customer_id列为主键时数据库自动创建的

--可以通过user_ind_columns视图查看某列的索引信息
SELECT index_name, table_name, column_name
FROM user_ind_columns
WHERE table_name IN ('CUSTOMERS')
ORDER BY index_name;

--可以使用ALTER INDEX语句修改索引,修改i_customers_phone索引名为i_customers_phone_number索引
ALTER INDEX i_customers_phone RENAME TO i_customers_phone_number;

--可以使用DROP INDEX删除索引
DROP INDEX i_customers_phone_number;