Test 2 Oracle 10g DB Admin Ch 6 7,8 and 9
BINARY_FLOAT
32-bit binary precision floating number including values for infinity and NaN. (Not a Number)
BINARY_DOUBLE
64-bit binary precision floating number including values for infinity and NaN. (Not a Number)
DATE
7 byte numbers including hours, minutes, and seconds.
NULL
A NULL valued column, in a row, in a table, has not been set to anything, or has been set to NULL,
Cluster
A cluster is not a table. It is a group of tables stored together as if they were one table.
NEXT
Added Fixed Amount
PCTINCREASE
Added as %Allocated
Group (aggregate) functions
Applies specific functionality to grouping and summary queries. (MIN, MAX, AVG, SUM, COUNT)
CLOB
Character data up to 4GB. Used for high volume text data
Combination sorting
Combine various sorting parameters.
Temporary table
Contain data that is privately retained for a connected user and disappears when the user commits a transaction or when a commit is forced.
Datatype conversion functions
Converts values such as numbers to strings. Ie. TO_CHAR
SMALLINT
Creates datatype Number(38)
Aggregated query
Creates groupings or summaries of larger row sets. (select count(col1),col2 from table group by col1;)
Create new table or view
Creates new table or view based on results of query. (create view as select * from table;)
User-defined functions
Custom written functions for specific tasks.
NESTED TABLE
Dynamic array or pointer to a variable number of array elements.
HAVING
Filters GROUP BY clause
VARRAY
Fixed length array or reserved chunk of memory for a fixed number of array elements.
FLOAT(p)
Floating point or real number.
Physical ROWID
Identifies a row by its physical location in a datafile. Never changes unless the table is reorganized.
Sorting NULL values
In ascending order,null values are returned last.
ASSOCIATIVE ARRAY
Index dynamic array
INITIAL
Initial Extents
INITRANS
Initial Trans Slots
VARRAY
Limited Number of Rows at creation, Cannot be indexed, Ordered, Storage Inline Single Column Built-in or Collection Type, Fixed array length
COMMIT
Makes the change permanent
MAXEXTENTS
Maximum Extents
MAXTRANS
Maximum Trans Slots
Join query
Merges rows form one or more tables, using links based on related values.
MINEXTENTS
Minimum Extents
MINTRANS
Minimum Trans Slots
An object type contains one or more attributes, which can be made up of another _____
Object Type
Object Tables
Object tables are created with one column that has a datatype that is a user-defined object type. TRUE
Single row functions
Operate one row at a time.
PCTFREE
Percent Free (10)
PCTUSED
Percent Used (40)
Extended ROWID
Physical ROWID that contains 4 values. Data object#, Datafile#, Data Block#, Row#
Restricted ROWID
Physical ROWID that contains only 3 values. Datafile#, Data Block#, Row#
ROWID
Physical locator string or logical pointer for row of data.
NUMBER(p,s)
Precision, number of digits. Scale, number of digits to the right of the decimal. INTEGER Creates datatype Number(38)
ROWNUM
Pseudocolumn allows Top-N Query
Subqueries
Querries executed within other queries. (select * from table where (select * from table); )
Individual tables and indexes can be divided into separate physical chunks in a number of ways:
Range partitioning ,List partitioning, Hash partitioning, Composite partitioning
REF
Reference Object identifier.
Index-Organized table
Relational table with rows stored in the physical order of the primary key values.
DISTINCT Clause
Retrieves the first value of each group in a set of duplications It can operate on single or multiple columns.
Basic query
Returns all rows in a table (select * from table;)
Filtered query
Returns rows based no stipulation (select * from table where column=X;)
Sorted query
Returns rows in a specified order (select * from table order by column)
TIMESTAMP(p)
Same as date except contains fractions of seconds.
Sort by expression
Sort by one or more expressions and the expression does not have to be in the selected column list.
Positional sort
Sort by the position of a column, in any order, within the specified list of columns.
Sort by one or more columns
Sorts by one or more columns
Ascending and descending sorts
Sorts query in ascending or descending order. ASC is the default.
MEDIA
Special multimedia datatypes of which there are numerous different datatypes.
SPATIAL
Special spatially oriented datatypes allowing for multiple dimensions such as for graphical information systems and architectural and construction design.
Flashback or version queries
Specialized query that allows access to data at a previous point in time.
Parallel queries
Specialized query that executes SQL statements in parallel.
Composite queries
Specialized query that uses set operators (UNION) to concatenate rows of 2 different queries together.
Hierarchical queries
Specialized query used to build tree-like hierarchical output row structures from hierarchical data.
Relational table
Standard table traditionally used in a relational database. Stores data of all types and is the most common form of storage in the database.
NCLOB
Stores large data in Unicode or national character set.
BFILE
Stores pointers to an external file.
BLOB
Stores unstructured data in binary format up to 4GB.
An expression containing a NULL returns a NULL NVL({value}, {replace}) replaces NULL values
TRUE
Comparing NULL to a value evaluates to false
TRUE
It can be tested for using IS [ NOT ] NULL
TRUE
Most built-in functions return NULL when passed NULL
TRUE
NULL values sort as the highest value by default
TRUE
Null Represents nothing, not a space, not a zero
TRUE
A table is contained in a single segment.
TRUE (Segments > Extents > Data Blocks => Disk Blocks)
XML table
Table created with one column of the XML type datatype.
Partitions
Table data being physically split into separate sections.
External table
Table that contains data in a file outside the database.
Nested table
Table that contains data that is stored within a single column of another table.
Object tables
Table that holds objects and attributes of those objects.
CHAR(n)
Test string with variable length up to 2000 bytes.
VARCHAR2(n)
Test string with variable length up to 4000 bytes.
NCHAR(n)
Text String with variable length up to 2000 bytes that depend on a national character set.
NVARCHAR2(n)
Text string with variable length up to 4000 bytes that stores characters in any language.
INTERVAL YEAR
Time interval as years and months only
ROLLBACK
Undoes the change
UROWID
Universal ROWID, Hexadecimal string containing ROWID values for an index organized table, object table, or non-Oracle entry.
ANY
Unknown or Generic Datatype
NESTED BLOCKSNESTED
Unlimited Number of Rows, Can be indexed, Unordered, Storage Out of line, Single Column Table Type, Dynamic length array or collection
DELETE
Use the DELETE command to remove zero or more rows from a table.
MERGE
Use the MERGE command to perform both INSERT and UPDATE in a single command.
UPDATE
Use the UPDATE command to change zero or more rows of a table.
Aliases
Used to refert o a column and can be used to sort with.
XML
XML documents can be stored, retrieved and manipulated as XML documents.
Logical ROWID
identifies a row by its primary key in an index organized table.
An object table row is also called an object table _____.
instance
Range partitioning
method of table partitioning in which a table is stored in partitions according to a specified set of ranges of values defined by the partitioning key.
List partitioning
method of table partitioning in which distinct lists of partitioning key values are set up, and values are defined to go into each partition.
Column data
one to three byte section containing the length of the columns data followed by the actual data for the column.
Segment
set of extents that make up one schema object within a table space.
Row header
stores the number of columns contained in the column data are, some overhead, and the ROWID pointing to the chained or migrated row.
Object _____ can be built with layers of object types in their definitions.
tables
You must create an object _____ that has the design you want to use for your object table before actually creating the object table
type
