Code Listing 5: Report for table with only 250 columns
SELECT C1 FROM T
call count cpu elapsed disk query current rows
————————— ———————— ——————————— —————————————— —————————————— —————————————— —————————————— ——————————
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 7230 0.62 0.62 1117 94520 0722790
————————— ———————— ——————————— —————————————— —————————————— —————————————— —————————————— ——————————
total 7241 0.62 0.62 1117 94520 0722790
SELECT C250 FROM T
call count cpu elapsed disk query current rows
————————— ———————— ——————————— —————————————— —————————————— —————————————— —————————————— ——————————
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 7230 0.96 0.97 7 94520 0722790
————————— ———————— ——————————— —————————————— —————————————— —————————————— —————————————— ——————————
total 7241 0.96 0.97 7 94520 0722790
be—it must be local in scope to a block of
PL/SQL code. I consider this to be neither
an advantage nor a disadvantage for either
type of cursor. I am not a huge fan of global
variables to begin with—I consider them to
be a bad practice in general—so I personally
never really take advantage of a regular cursor’s ability to be global. To show what this
particular cursor difference means, I need
just a small snippet of code:
cannot be declared as part
of a package
See how a package with a regular cursor
defined outside of a procedure or a function
compiles successfully but a package with a
ref cursor defined that way will not compile.
SQL> create or replace package my_pkg
2 as
3 cursor global_cursor is
select from dual;
4 end;
5 /
Package created.
OPTIMAL ORDERING
Is there an optimal order for creating columns
in a table? The DBA at my shop enforces a
standard of putting VARCHAR2 columns last,
and the likelihood of nulls is not considered. Is
there a real-world benefit to this order?
Here’s a little background first. A row
is stored in a manner similar to this in the
database block:
SQL> create or replace package my_pkg
2 as
3 global_cursor sys_refcursor;
4 end;
5 /
Warning: Package created with
compilation errors.
[null flag][length][data][null flag]
[length][data]. . . .
SQL> show err
Errors for PACKAGE MY_PKG:
LINE/COL ERROR
—————————— ————————————————————————————————————
3/16 PL/SQL: Declaration ignored
3/16 PLS-00994: Cursor Variables
In order to get to the third column in a
table, the database must parse through the
first two columns (not pointers—the database must read the row byte by byte)— even
if the columns are numbers, dates, whatever.
The columns will be stored as a length field
followed by the data, and the database must
parse through them to get to the next column.
So, in general you should put the most
frequently accessed columns first for the
best performance.
However, there is something to be said for
putting the column(s) most likely to be null
last, because they will consume zero bytes.
If the database hits the end of a row before
finding the Nth column, you know that that
column is NULL or the default column value
(for not-null columns added to the table with
fast add in Oracle Database 11 g).
However, there is a convention to put
primary key columns first in all cases.
However . . . there are too many howevers!
And in general, it really isn’t going to matter
too much. The bit about putting the most
frequently accessed columns first would
have the most weight in a data warehouse in
which you are scanning over many millions of
rows to build the answer (the repeated overhead of parsing over a lot of columns that are
not interesting would add up). In an online
transaction processing (OLTP) system, where
you are processing tens of rows, though, it
wouldn’t be as big a deal.
In a strange case involving dozens of
columns that are almost always null, putting
them last in a table with lots and lots of
rows could save you quite a few bytes, but
the table would have to be large and the
columns would have to be all null and frequently all null.
So, my recommendation is to put the
columns in the order that makes the
most sense from an aesthetic and design
perspective.
As for the DBA’s rule above (VARCHAR2
columns last and the likelihood of nulls not
considered), it would do nothing for performance or manageability. In many cases,
it could negatively affect performance. Just
remember that pretty much all database
data is stored in a stringlike fashion:
•;A string will have a null flag and a length
byte or length bytes (<=250 characters [ 1
byte], >250 characters [ 2 bytes]) followed
by the data.
•;A number will have a null flag and a length
byte or length bytes (numbers are 0– 22
bytes in length) followed by a varying
amount of data.
•;A binary_float will consume 5 bytes—a
leading null flag and a length byte followed
by 4 bytes of floating-point data.
•;A binary_double will consume 9 bytes—a
leading null flag and a length byte followed
by 8 bytes of floating-point data.
The database must read the length byte to
figure out how many bytes of that row con-
