Databases Reference
In-Depth Information
create table cust(
cust_id number
,last_name varchar2(30)
,first_name varchar2(30));
You also anticipate that queries will frequently execute against the table using the
LAST_NAME
column. Therefore, you create a B-tree index as follows:
create index cust_idx1
on cust(last_name);
Note
You need the
create index system
privilege to create an index. You also need privileges for consuming
space in the tablespace the index is placed within.
Next, thousands of rows are inserted into the table (not all of the rows are shown here):
insert into cust (cust_id, last_name, first_name) values(7, 'ACER','SCOTT');
insert into cust (cust_id, last_name, first_name) values(5, 'STARK','JIM');
insert into cust (cust_id, last_name, first_name) values(3, 'GREY','BOB');
insert into cust (cust_id, last_name, first_name) values(11,'KHAN','BRAD');
.....
insert into cust (cust_id, last_name, first_name) values(274, 'ACER','SID');
After the rows are inserted, ensure that the table statistics are up to date so as to provide the query
optimizer sufficient information to make good choices on how to retrieve the data, like so:
SQL> exec dbms_stats.gather_table_stats(ownname=>'MV_MAINT', -
tabname=>'CUST',cascade=>true);
Note
Oracle strongly recommends that you don't use the
ANALYZE
statement (with the
COMPUTE
and
ESTIMATE
clauses) to collect statistics. Oracle only provides this functionality for backward compatibility. Oracle does support
using the
ANALYZE
statement for non-statistics gathering uses such as validating objects and listing chained rows.
As rows are inserted into the table, Oracle will allocate extents that consist of physical database
blocks. Oracle will also allocate blocks for the index. For each record inserted into the table, Oracle will
also create an entry in the index that consists of the
ROWID
and column value (the value in
LAST_NAME
in
this example). The
ROWID
for each index entry points to the data file and block in which the table column
value is stored. Figure 2-1 shows a graphical representation of how data is stored in the table and the
corresponding B-tree index. For this example, data files 10 and 15 contain table data stored in associated
blocks and data file 22 stores the index blocks.
