Code Listing 5: Limited digits cannot accurately represent 1/19
SQL> set numformat 0.999999999999999999999999999999999999999999999
SQL> select 1/19 from dual;
Code Listing 6: 38 digits of precision are not enough to equal 1
SQL> select 1/19* 19 from dual;
Code Listing 7: Reduced precision with floating-point numbers
SQL> select 1f/19f from dual;
As you can see, we definitely would use a
bitmap index to perform a fast full index
scan—in fact, we can do it even when
OWNER (the only indexed column) is
NULLABLE. That is because, unlike the
T_IDX B*Tree index, bitmap indexes always
index NULL keys. Every row in a table will be
pointed to by a bitmap index, regardless of
the data values.
And last, there is the statement that “a
fast full index scan is faster than a full index
scan, because it can use multiblock I/O and
can be parallelized just like a table scan.” I
have a saying that goes like this: “Never say
never and never say always, I always say.”
When you see any statement such as “X is
faster than Y,” be suspicious. If X were always
faster than Y, technology Y would not have
been implemented or invented in the first
place! There must be some times when the
converse is true. So, from a performance perspective, that statement is something that
may or may not be true.
The answer to whether that statement is
true is, “It depends.” Again using the table T
example, if I run the query
order by status, owner;
the optimizer will sometimes pick a fast full
index scan on T_IDX and sometimes pick a
full index scan on T_IDX. It all depends on
the estimated cardinalities involved. If this
query is going to retrieve a very small set of
rows, the cost of sorting them will be very
low, and we’ll probably use a fast full index
scan to reduce the I/O cost. On the other
hand, if the query is going to retrieve a lot
of rows, the cost of sorting them begins to
go way up, and we might choose a full index
scan, with a higher I/O cost but zero cost
for sorting, instead. This is borne out by the
example in Listing 4.
As Listing 4 demonstrates, when we are
going to retrieve about 35,000 rows, we use
a full index scan to avoid the sort. When we
go after about 8 rows, we use the fast full
index scan and just sort the results.
select status, owner from t
where owner = 'some user'
ROLLBACK SEGMENT FOR SELEC T
Does Oracle Database need a rollback segment
for queries? I thought it used rollback segments
only for data manipulation language (DML)
operations, but it recently failed, resulting in
the error message “Unable to extend rollback
segment” for a SELEC T statement.
We added some space to that segment,
and then the SELECT statement worked fine.
Does the database use rollback segments for
SELEC T statements?
First, let’s refine the question. Instead of
“does the database use rollback segments
for SELECT statements,” I’ll answer the
question “does a SELECT statement generate
or read rollback segments?” The verb use is
just not specific enough.
Every SELECT statement has the potential to “use” undo (rollback) data. Oracle
Database uses undo naturally when processing all queries to produce read-consistent result sets. (See bit.ly/elhCUS
for more details on this topic.)
However, this use of undo would not
cause an “unable to extend” error message.
It could quite easily cause the dreaded
“ORA-1555 Snapshot too old” error message,
but it would not by itself generate an ORA-
1650 or ORA-1651 error message, “Unable to
extend rollback/undo segment. . . .”
To generate the ORA-1650/1651 error
message, the SELEC T statement would have
to be generating undo, possible causes of
which would be
The SELECT statement was an UPDATE in •
disguise—the SELEC T statement contained
the FOR UPDATE clause.
Auditing was enabled, which is what raised •
the ORA-1650/1651 error message.
The SELECT statement was invoking some •
sort of autonomous transaction function
that wrote to the database, and that is what
caused the ORA-1650/1651 error message.
I would suspect that the first reason was
the likely cause of this “Unable to extend
rollback/undo segment . . .” error message.
When you issue a SELEC T FOR UPDATE,
Oracle Database will lock all the rows
for you before you get the first row back.
Locking a row in Oracle Database involves
modifying the database block to place the
lock there. And anytime you modify a database block, you generate undo (and redo)
for that operation. You can see this easily:
SQL> create table t
4 from all_objects;