Oracle Magazine - July/August 2011

ASK TOM
Code Listing 5: Limited digits cannot accurately represent 1/19

SQL> set numformat 0.999999999999999999999999999999999999999999999 SQL> select 1/19 from dual;

1/19

——————————————————————————————————————————————————————————————————————— 0.052631578947368421052631578947368421052600000

Code Listing 6: 38 digits of precision are not enough to equal 1

SQL> select 1/19* 19 from dual;

1/19* 19

——————————————————————————————————————————————————————————————————————— 0.999999999999999999999999999999999999999400000

Code Listing 7: Reduced precision with floating-point numbers

SQL> select 1f/19f from dual;

1F/19F

——————————————————————————————————————————————————————————————————————— 0.052631579300000000000000000000000000000000000

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: