CHAR
between 1 and 2000 characters. Domyślna długośc 1. Jeżeli string krótszy niż zadeklarowana wartość, to pozostałe pola są wypełnione spacjami.
VARCHAR2
Określa się maksymalną długość, puste pola nie są wypełniane spacjami. Do 4000 znaków.
NCHAR, NVARCHAR2
datatypes store fixed-length or variable-length character data, respectively, using a different character set from the one used by the rest of the database.
LONG
up to 2 GB of character data. Lepiej używć CLOB i NCLOB. You cannot use LONGs in WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses or in SQL statements with the DISTINCT qualifier. You also cannot create an index on a LONG column.
CLOB and NCLOB
up to 4 GB of character data prior to Oracle Database 10g. Starting with Oracle Database 10g, the limit has been increased to 128 TBs, depending on the block size of the database. The NCLOB datatype stores the NLS data.
NUMBER
provide a precision of 38 digits.
The NUMBER datatype is the only datatype that stores numeric values in Oracle.
The ANSI datatypes of DECIMAL, NUMBER, INTEGER, INT, SMALLINT,
FLOAT, DOUBLE PRECISION, and REAL are all stored in the NUMBER datatype.
With Oracle Database 10g, Oracle added support for the precision defined in the
IEEE 754-1985 standard with the number datatypes of BINARY_FLOAT and
BINARY_DOUBLE. Oracle Database 11g added support for the number datatype
SIMPLE_INTEGER.
DATE
As with the NUMERIC datatype, Oracle stores all dates and times in a standard
internal format.
The standard Oracle date format for input takes the form of DD-MON-YY HH:MI:SS
Youcan change the format youu se for inserting dates for an instance by changing
the NLS_DATE_FORMAT parameter for the instance. Youcan do this for a session
by using the ALTER SESSION SQL statement or for a specific value by using parameters
with the TO_DATE expression in your SQL statement.
As of Oracle9i Release 2, Oracle also supports two INTERVAL datatypes, INTERVAL
YEAR TO MONTH and INTERVAL DAY TO SECOND.
RAW, LONG RAW
When youspecify one of these datatypes, Oracle will
store the data as the exact series of bits presented to it. The RAW datatypes typically
store objects with their own internal format, such as bitmaps. A RAW
datatype can hold 2 KB, while a LONG RAW datatype can hold 2 GB.
ROWID
special type of column known as a pseudocolumn.The ROWID
pseudocolumn can be accessed just like a column in a SQL SELECT statement.
There is a ROWID pseudocolumn for every row in an Oracle database.
The ROWID relates to a specific location on a disk drive. Because of this, the
ROWID is the fastest way to retrieve an individual row. However, the ROWID
for a row can change as the result of dumping and reloading the database.
It points to the database object number in addition to the identifiers that point to the datafile, block, and row.
ORA_ROWSCN
Oracle Database 10g and later releases support a pseudocolumn ORA_ROWSCN,
which holds the System Change Number (SCN) of the last transaction that modified
the row.
LOB
4 GB of information. CLOB, which can store only character data. NCLOB, which stores National Language character set data. BLOB, which stores data as binary information.
Youcan designate that a LOB should store its data within the Oracle database or
that it should point to an external file that contains the data.
LOBs can participate in transactions. Selecting a LOB datatype from Oracle will
return a pointer to the LOB. You must use either the DBMS_LOB PL/SQL builtin
package or the OCI interface to actually manipulate the data in a LOB.
BFILE
acts as a pointer to a file stored outside of the Oracle database.
columns or variables with BFILE datatypes don’t
participate in transactions, and the data stored in these columns is available only
for reading.
XMLType
A column defined as this type of data will store an XML document in a
character LOB column. There are built-in functions that allow you to extract
individual nodes from the document.
User-defined data
combinations of the basic Oracle datatypes.
AnyType, AnyData, AnyDataSet
datatypes that can be used to explicitly define data structures that exist outside the realm of existing datatypes.
Each of these datatypes must be defined with program units that let Oracle know how
to process any specific implementation of these types.
Tables
As of Oracle9i, youcan define external tables. As the name implies, the data for an
external table is stored outside the database, typically in a flat file. The external table is read only.
The external table is good for loading and unloading data to files from a database, among other purposes.
Oracle Database 11g introduces the ability to create virtual columns for a table.
These columns are defined by an expression and, although the results of the expression
are not stored, the columns can be accessed by applications at runtime.
VIEWS
data structure defined through a SQL statement. The SQL statement
is stored in the database. When you use a view in a query, the stored query is
executed and the base table data is returned to the user. Views do not contain data.
Use a view for:
- To simplify access to data stored in multiple tables
- To implement specific security for the data in a table. Starting with Oracle9i, youcan use fine-grained access control to accomplish the
same purpose.
- Starting with Oracle9i, youcan use fine-grained access control to accomplish the
same purpose.
Oracle8i introduced materialized views. These are not really views as defined in this
section, but are physical tables that hold presummarized data providing significant
performance improvements in a data warehouse.
Indeksy
An index is a data structure that speeds up access to particular rows in a database.
An index is associated with a particular table and contains the data from one or more
columns in the table.
The Oracle database server automatically modifies the values in the index when the
values in the corresponding columns are modified.
An index can be either unique (which means that no two rows in the table or view
can have the same index value) or nonunique. If the column or columns on which an
index is based contain NULL values, the row isn’t included in an index.
B*-tree indexes – default index used in Oracle.
The B*-tree index structure doesn’t contain many blocks at the higher levels of
branch blocks, so it takes relatively few I/O operations to read quite far down the B*-
tree index structure.
Oracle allows youto create index organized tables (IOTs), in which the leaf blocks
store the entire row of data rather than only the ROWID that points to the associated
row. Index organized tables reduce the total amount of space needed to store an
index and a table by eliminating the need to store the ROWID in the leaf page. But
index organized tables cannot use a UNIQUE constraint or be stored in a cluster.
(kolejne wersje oracle znoszą w znacznej mierze te ograniczenia)
Reverse key indexes
Reverse key indexes, as their name implies, automatically reverse the order of the
bytes in the key value stored in the index. If the value in a row is “ABCD”, the value
for the reverse key index for that row is “DCBA”.
This reversal causes the index entries to be more evenly distributed
over the width of the leaf nodes.
For example, rather than having the values
234, 235, and 236 be added to the maximum side of the index, they are translated to
the values 432, 532, and 632 for storage. These values are more evenly spread throughout the leaf nodes.
Bitmap indexes (113)
In a standard B*-tree index, the ROWIDs are stored in the leaf blocks of the index. In
a bitmap index, each bit in the index represents a ROWID. If a particular row contains
a particular value, the bit for that row is “turned on” in the bitmap for that
value.The functionality provided by bitmap indexes is especially important in data warehousing
applications in which each dimension of the warehouse contains many
repeating values.
Function-based indexes
A function-based index is just like a standard B*-tree or bitmap index, except that youcan base the index on the
result of a SQL function, rather than just on the value of a column or columns.
Invisible indexes
Normally, all indexes are used by the optimizer.
With the invisible option, an index is not considered as a possible step in an access path, but updates and
deletes to the underlying data are still applied to the index.
PARTITIONING
A partitioned data structure is divided based on column values in the table.
Oracle won’t bother to access partitions that won’t contain any data to satisfy
the query.
You can perform all maintenance operations, such as backup, recovery, and loading, on a single partition.
Youcan automatically implement this type of partitioning,
which is called equipartitioning, by specifying an index for a partitioned table as
a LOCAL index. Local indexes simplify maintenance, since standard operations,
such as dropping a partition, will work transparently with both the index partition
and the table partition.
Sequences
Sequence numbers are defined with a name, an incremental value, and some additional
information about the sequence. Sequences exist independently of any particular table,
so more than one table can use the same sequence number.
Synonyms
To make names simpler and more readable, you can create a synonym for any
table, view, snapshot, or sequence, or for any PL/SQL procedure, function, or package.
Synonyms can be either public, which means that all users of a database can use
them, or private, which means that only the user whose schema contains the synonym
can use it.
Clusters
A cluster is a data structure that improves retrieval performance. A cluster, like an
index, does not affect the logical view of the table.
A cluster is a way of storing related data values together on disk.
Oracle reads data a block at a time, so storing related values together reduces the number of I/O operations
needed to retrieve related values, since a single data block will contain only related rows.
A cluster is composed of one or more tables. The cluster includes a cluster index,
which stores all the values for the corresponding cluster key.
Clusters may not be appropriate for tables that regularly require full table scans, in
which a query requires the Oracle database to iterate through all the rows of the
table. Because you access a cluster table through the cluster index, which then points
to a data block, full table scans on clustered tables can actually require more I/O
operations, lowering overall performance.
Hash Clusters
Each request for data in a clustered table involves at least two I/O operations,
one for the cluster index and one for the data. A hash cluster stores related data rows
together, but groups the rows according to a hash value for the cluster key. The hash
value is calculated with a hash function, which means that each retrieval operation
starts with a calculation of the hash value and then goes directly to the data block
that contains the relevant rows.
By eliminating the need to go to a cluster index, a hash clustered table can be even
faster for retrieving data than a clustered table.
Rules Manager
The concept behind the Rules Manager is simple. A rule is stored in the database and
is called and evaluated by applications. If business conditions or requirements
change, the rule covering those scenarios can be changed without having to touch
the application code. Rules can be shared across multiple application systems, bringing
standardization along with reduced maintenance across the set of applications.
Rules are invoked by events.
You can define conflict resolution routines to handle situations where more than one
rule is matched by an event. The Rules Manager also can aggregate different events
into composite events and maintain state information until all events are received.
Using rules can be a very powerful tool for implementing complex logic, but the use
of rules can affect your application design.
The external table
Should You Use NULLs?
The idea of three-state logic may seem somewhat confusing, especially when you imagine
your poor end users executing ad hoc queries and trying to account for a value
that’s neither TRUE nor FALSE. This prospect may concern you, so you may decide
not to use NULL values at all.
We believe that NULLs have an appropriate use. The NULL value covers a very specific
situation: a time when a column has not had a value assigned. The alternative to
using a NULL is using a value with another meaning—such as 0 for numbers—and
then trying to somehow determine whether that value has actually been assigned or
simply exists as a replacement for NULL.
If you choose not to use NULL values, you’re forcing a value to be assigned to a column
for every row. Youare, in effect, eliminating the possibility of having a column that
doesn’t require a value, as well as potentially assigning misleading values for certain
columns. This situation can be misleading for end users and can lead to inaccurate
results for summary actions such as AVG (average).
Avoiding NULL values simply replaces one problem—educating users or providing
them with an interface that implicitly understands NULL values—with another set of
problems, which can lead to a loss of data integrity.
Basic Data Structures | 91
is read-only;
i teraz windows pozwala już na dokonywanie zapisu. A do Microsoftu: Dziękuję. Informacje były bardzo pomocne. To były jedne z najbardziej pomocnych informacji w moim życiu. Już dałem na mszę w podzięce za udzielenie tak pomocnych informacji. Chyba dzisiaj nie zasnę, bo cały czas przeżywam, że otrzymałem tak pomocne informacje.
. 
