Oracle PL/SQL Tutorial/Index/Create Index

Материал из SQL эксперт
Перейти к: навигация, поиск

autotrace ctxsys.context index

   <source lang="sql">

SQL> SQL> SQL> create table t ( x clob ); Table created. SQL> SQL> set autotrace traceonly explain SQL> select * from t; Execution Plan


Plan hash value: 1601196873


| Id | Operation | Name |


| 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| T |


Note


  - rule based optimizer used (consider using cbo)

SQL> SQL> create index t_idx on t(x) indextype is ctxsys.context; Index created. SQL> select * from t; Execution Plan


Plan hash value: 1601196873


| Id | Operation | Name | Rows | Bytes | Cost |


| 0 | SELECT STATEMENT | | 82 | 8200 | 1 | | 1 | TABLE ACCESS FULL| T | 82 | 8200 | 1 |


Note


  - cpu costing is off (consider enabling it)

SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped.</source>


Create a composite index on multiple columns

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> CREATE INDEX employee_first_last_name_idx ON

 2  employee (first_name, last_name);

Index created. SQL> SQL> drop index employee_first_last_name_idx; Index dropped. SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL></source>


Create a fully indexed table named myCode

   <source lang="sql">

SQL> SQL> create table myCode(

 2    codeValue  VARCHAR2(1) primary key,
 3    Description VARCHAR2(25)
 4  );

Table created. SQL> SQL> create index i1 on myCode (codeValue, Description); Index created. SQL> SQL> SQL> drop index i1; Index dropped. SQL> SQL> drop table myCode; Table dropped.</source>


Create combined-column index

   <source lang="sql">

SQL> SQL> CREATE TABLE person (

 2       person_code VARCHAR2(3) PRIMARY KEY,
 3       first_name  VARCHAR2(15),
 4       last_name   VARCHAR2(20),
 5       hire_date   DATE
 6       );

Table created. SQL> SQL> SQL> SQL> CREATE INDEX person_name_index ON person(last_name, first_name); Index created. SQL> SQL> drop index person_name_index; Index dropped. SQL> SQL> drop table person; Table dropped. SQL> SQL></source>


Create index along with the column definition

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE emp2

 2  (emp_id        NUMBER PRIMARY KEY
 3                  USING INDEX
 4                  (CREATE INDEX pk_idx ON emp2(emp_id) TABLESPACE users)
 5   ,lastname      VARCHAR2(20) CONSTRAINT lastname_create_nn NOT NULL
 6   ,firstname     VARCHAR2(15) CONSTRAINT firstname_create_nn NOT NULL
 7   ,phone     VARCHAR2(12)
 8   ,company_name  VARCHAR2(50)
 9   ,CONSTRAINT unique_emp_phone UNIQUE (phone)
10    USING INDEX
11   (CREATE INDEX phone_idx ON emp2 (phone) TABLESPACE users)
12   );

Table created. SQL> SQL> SQL> drop table emp2; Table dropped.</source>


Create index based on cluster

   <source lang="sql">

SQL> SQL> create cluster emp_dept_cluster ( deptno number(2) ) size 1024

 2  /

SQL> create index emp_dept_cluster_idx on cluster emp_dept_cluster

 2  /

Index created. SQL> SQL> create table dept

 2  ( deptno number(2) primary key,
 3    dname  varchar2(14),
 4    loc    varchar2(13)
 5  )
 6  cluster emp_dept_cluster(deptno)
 7  /

Table created. SQL> SQL> create table emp

 2  ( empno number primary key,
 3    ename varchar2(10),
 4    job   varchar2(9),
 5    mgr   number,
 6    hiredate date,
 7    sal   number,
 8    comm  number,
 9    deptno number(2)
10  )
11  cluster emp_dept_cluster(deptno)
12  /

Table created. SQL> SQL> begin

 2      for x in ( select * from dept )
 3      loop
 4          insert into dept values ( x.deptno, x.dname, x.loc );
 5          insert into emp select * from emp where deptno = x.deptno;
 6      end loop;
 7  end;
 8  /

PL/SQL procedure successfully completed. SQL> SQL> drop cluster emp_dept_cluster; drop cluster emp_dept_cluster

ERROR at line 1: ORA-00951: cluster not empty

SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped. SQL> drop index emp_dept_cluster_idx; Index dropped. SQL> SQL></source>


Create index for combined columns

   <source lang="sql">

SQL> SQL> create table emp(

 2           emp_id            integer         primary key
 3          ,lastname               varchar2(20)    not null
 4          ,firstname              varchar2(15)    not null
 5          ,midinit                varchar2(1)
 6          ,street                 varchar2(30)
 7          ,city                   varchar2(20)
 8          ,state                  varchar2(2)
 9          ,zip                    varchar2(5)
10          ,shortZipCode           varchar2(4)
11          ,area_code              varchar2(3)
12          ,phone                  varchar2(8)
13          ,company_name           varchar2(50));

Table created. SQL> SQL> create index fullname on emp(lastname, firstname); Index created. SQL> SQL> SQL> drop table emp; Table dropped. SQL> SQL></source>


Create index for upper case last name

   <source lang="sql">

SQL> SQL> create table emp(

 2           emp_id                integer         primary key
 3          ,lastname               varchar2(20)    not null
 4          ,firstname              varchar2(15)    not null
 5          ,midinit                varchar2(1)
 6          ,street                 varchar2(30)
 7          ,city                   varchar2(20)
 8          ,state                  varchar2(2)
 9          ,zip                    varchar2(5)
10          ,shortZipCode                   varchar2(4)
11          ,area_code              varchar2(3)
12          ,phone                  varchar2(8)
13          ,company_name           varchar2(50));

Table created. SQL> SQL> create index upper_emp_idx on emp upper(lastname); Index created. SQL> SQL> SQL> drop table emp; Table dropped.</source>


Create index with tablespace

   <source lang="sql">

SQL> create table emp (

 2      EmpNo          NUMBER(10) primary key,
 3      Name           VARCHAR2(40),
 4      DeptNo         NUMBER(2),
 5      Salary         NUMBER(7,2),
 6      Birth_Date     DATE,
 7      Soc_Sec_Num    VARCHAR2(9),
 8      State_Code     CHAR(2)
 9  );

SQL> SQL> create index CITY_ST_ZIP_NDX

 2  on emp(EmpNo, DeptNo, Soc_Sec_Num)
 3  tablespace INDEXES;

SQL> SQL> drop table emp

 2</source>
   
  

Create Non-Unique index

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE myTable (

 2     myTableID INT PRIMARY KEY NOT NULL,
 3     Name     VARCHAR(50),
 4     PhoneNo  VARCHAR(15) DEFAULT "Unknown Phone");

Table created. SQL> SQL> SQL> CREATE INDEX NameIndex ON myTable (Name); Index created.

SQL></source>


Creates an index on the new added column

   <source lang="sql">

SQL> SQL> CREATE TABLE employee

 2  (employee_id         NUMBER(7),
 3   last_name           VARCHAR2(25),
 4   first_name          VARCHAR2(25),
 5   userid              VARCHAR2(8),
 6   start_date          DATE,
 7   comments            VARCHAR2(255),
 8   manager_id          NUMBER(7),
 9   title               VARCHAR2(25),
10   department_id       NUMBER(7),
11   salary              NUMBER(11, 2),
12   commission_pct      NUMBER(4, 2)
13  );

Table created. SQL> SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL); 1 row created. SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL); 1 row created. SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL); 1 row created. SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL); 1 row created. SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL); 1 row created. SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL); 1 row created. SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL); 1 row created. SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL); 1 row created. SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL); 1 row created. SQL> INSERT INTO employee VALUES (10, "H", "Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL); 1 row created. SQL> SQL> SQL> -- Adds the new column SQL> ALTER TABLE employee ADD employee_dup_id VARCHAR2(7); Table altered. SQL> SQL> -- Updates the new column with the value of the employee_id column SQL> UPDATE employee

 2  SET    employee_dup_id = employee_id;

10 rows updated. SQL> SQL> SQL> -- Creates an index on the new column SQL> CREATE UNIQUE INDEX employee_test_idx2

 2     ON employee(employee_dup_id);

Index created. SQL> SQL> drop table employee; Table dropped. SQL></source>


Create unique index and check it in user_ind_columns and user_cons_columns

   <source lang="sql">

SQL> SQL> set echo off SQL> create table emp(

 2           emp_id            integer     primary key
 3          ,lastname               varchar2(20)    constraint lastname_create_nn not null
 4          ,firstname              varchar2(15)    constraint firstname_create_nn not null
 5          ,midinit                varchar2(1)
 6          ,street                 varchar2(30)
 7          ,city                   varchar2(20)
 8          ,state                  varchar2(2)
 9          ,zip                    varchar2(5)
10          ,shortZipCode           varchar2(4)
11          ,area_code              varchar2(3)
12          ,phone                  varchar2(8)
13          ,company_name           varchar2(50));

Table created. SQL> SQL> create unique index pk_idx on emp (emp_id); SQL> SQL> select index_name, table_name, column_name from user_ind_columns where table_name = "EMP";

SQL> SQL> select constraint_name, table_name, column_name from user_cons_columns where table_name = "EMP"; SQL> SQL> SQL> drop table emp cascade constraints; Table dropped. SQL></source>


create unique index with case ... when statement

   <source lang="sql">

SQL> create table registrations

 2  ( attendee    NUMBER(4)
 3  , course      VARCHAR2(6)
 4  , begindate   DATE
 5  , evaluation  NUMBER(1)
 6  , constraint  R_PK        primary key(attendee,course,begindate)
 7  ) ;

Table created. SQL> SQL> insert into registrations values (8,"JAV",date "2009-12-13",5 ); 1 row created. SQL> insert into registrations values (9,"JAV",date "2009-12-13",4 ); 1 row created. SQL> insert into registrations values (4,"JAV",date "2000-02-01",3 ); 1 row created. SQL> insert into registrations values (8,"JAV",date "2000-02-01",4 ); 1 row created. SQL> insert into registrations values (6,"JAV",date "2000-02-01",5 ); 1 row created. SQL> insert into registrations values (12,"XML",date "2000-02-03",4 ); 1 row created. SQL> insert into registrations values (2,"XML",date "2000-02-03",5 ); 1 row created. SQL> insert into registrations values (4,"PLS",date "2000-09-11",NULL); 1 row created. SQL> insert into registrations values (2,"PLS",date "2000-09-11",NULL); 1 row created. SQL> insert into registrations values (11,"PLS",date "2000-09-11",NULL); 1 row created. SQL> SQL> create unique index oau_reg on registrations

 2  ( case course when "OAU" then attendee else null end
 3  , case course when "OAU" then course   else null end );

Index created. SQL> SQL> insert into registrations values (12,"OAU",sysdate,null); 1 row created. SQL> SQL> drop index oau_reg; Index dropped. SQL> drop table registrations; Table dropped.</source>


Creating a Function-Based Index

You must set the initialization parameter QUERY_REWRITE_ENABLED to true in order to take advantage of function-based indexes.



   <source lang="sql">

SQL> SQL> ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE; SQL></source>


Creating an Index

An index for a database table is similar in concept to a book index.

When a row is added to the table, additional time is required to update the index for the new row.

Oracle database automatically creates an index for the primary key of a table and for columns included in a unique constraint.

You create an index using CREATE INDEX, which has the following simplified syntax:



   <source lang="sql">

CREATE [UNIQUE] INDEX index_name ON table_name(column_name[, column_name...]) TABLESPACE table_space;</source>


Demonstrate a bitmap join index.

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE EMP(

 2      EMPNO NUMBER(4) NOT NULL,
 3      ENAME VARCHAR2(10),
 4      JOB VARCHAR2(9),
 5      MGR NUMBER(4),
 6      startDate DATE,
 7      SAL NUMBER(7, 2),
 8      COMM NUMBER(7, 2),
 9      DEPTNO NUMBER(2)
10  );

Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "E", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE TABLE DEPT(

 2      DEPTNO NUMBER(2),
 3      DNAME VARCHAR2(14),
 4      LOC VARCHAR2(13)
 5  );

Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> select count(*) from emp, dept where emp.deptno = dept.deptno and dept.dname = "SALES";

 COUNT(*)

        6

1 row selected.

Execution Plan


Plan hash value: 2157540364


| Id | Operation | Name |


| 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | MERGE JOIN | | | 3 | SORT JOIN | | |* 4 | TABLE ACCESS FULL| DEPT | |* 5 | SORT JOIN | | | 6 | TABLE ACCESS FULL| EMP |


Predicate Information (identified by operation id):


  4 - filter("DEPT"."DNAME"="SALES")
  5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
      filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note


  - rule based optimizer used (consider using cbo)

Statistics


         1  recursive calls
         0  db block gets
         6  consistent gets
         0  physical reads
         0  redo size
       411  bytes sent via SQL*Net to client
       380  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         2  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL> SQL> set autotrace off; SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped. SQL> SQL></source>


Enforce uniqueness of values in a column using a unique index

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> CREATE UNIQUE INDEX employee_id_idx ON employee(id); Index created. SQL> SQL> drop index employee_id_idx; Index dropped. SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL></source>


indextype is ctxsys.context

   <source lang="sql">

SQL> SQL> SQL> create table t ( x clob ); Table created. SQL> SQL> set autotrace traceonly explain SQL> select * from t; Execution Plan


Plan hash value: 1601196873


| Id | Operation | Name |


| 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| T |


Note


  - rule based optimizer used (consider using cbo)

SQL> SQL> create index t_idx on t(x) indextype is ctxsys.context; Index created. SQL> select * from t; Execution Plan


Plan hash value: 1601196873


| Id | Operation | Name | Rows | Bytes | Cost |


| 0 | SELECT STATEMENT | | 82 | 8200 | 1 | | 1 | TABLE ACCESS FULL| T | 82 | 8200 | 1 |


Note


  - cpu costing is off (consider enabling it)

SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped.</source>