Database Reference
In-Depth Information
EODA@ORA12CR1> select a from t;
A
----------
1
4
3
Adjust columns
B
and
C
to be appropriate for your block size if you would like to reproduce this. For example, if
you have a 2KB block size, you do not need column
C
, and column
B
should be a
VARCHAR2(1500)
with a default of
1,500 asterisks. Since data is managed in a heap in a table like this, as space becomes available, it will be reused.
■
When using assM or MssM, you'll find rows end up in different places. the underlying space management
routines are very different; the same operations executed against a table in assM and MssM may well result in different
physical order. the data will logically be the same, but it will be stored in different ways.
Note
A full scan of the table will retrieve the data as it hits it, not in the order of insertion. This is a key concept to
understand about database tables: in general, they are inherently unordered collections of data. You should also note
that I do not need to use a
DELETE
in order to observe this effect; I could achieve the same results using
only
INSERT
s.
If I insert a small row, followed by a very large row that will not fit on the block with the small row, and then a small
row again, I may very well observe that the rows come out by default in the order “small row, small row, large row.”
They will not be retrieved in the order of insertion—Oracle will place the data where it fits, not in any order by date or
transaction.
If your query needs to retrieve data in order of insertion, you must add a column to the table that you can use
to order the data when retrieving it. This column could be a number column, for example, maintained with an
increasing sequence (using the Oracle
SEQUENCE
object). You could then
approximate
the insertion order using a
SELECT
that did an
ORDER BY
on this column. It will be an approximation because the row with sequence number
55
may very well have committed before the row with sequence
54
, therefore it was officially first in the database.
You should think of a heap organized table as a big unordered collection of rows. These rows will come out in a
seemingly random order, and depending on other options being used (parallel query, different optimizer modes, and
so on), they may come out in a different order with the same query. Do not ever count on the order of rows from a
query unless you have an
ORDER BY
statement on your query!
That aside, what is important to know about heap tables? Well, the
CREATE TABLE
syntax spans some 87 pages
in the
Oracle Database SQL Language Reference
manual provided by Oracle, so there are lots of options that go along
with them. There are so many options that getting a hold on all of them is pretty difficult. The wire diagrams (or
train
track
diagrams) alone take 20 pages to cover. One trick I use to see most of the options available to me in the
CREATE TABLE
statement for a given table is to create the table as simply as possible, for example:
EODA@ORA12CR1> create table t
2 ( x int primary key,
3 y date,
4 z clob
5 )
6 /
Table created.
