ITPub博客

Because a view is defined by a query that references other objects (tables, materializedviews, or other views), a view depends on the referenced objects. Oracle automaticallyhandles the dependencies for views. For example, if you drop a base table of a viewand then create it again, Oracle determines whether the new base table is acceptable tothe existing definition of the view.视图的依赖性1. 由于视图的定义是一个引用了其他对象的查询,因此视图依赖于其所引用的对象 2. Oracle 会自动地处理视图的依

Oracle determines whether to use indexes for a query against a view by transformingthe original query when merging it with the view’s defining query.In all possible cases, Oracle merges a query against a view with the view’s definingquery and those of any underlying views. Oracle optimizes the merged query as if youissued the query without referencing the views. Therefore, Oracle can use indexes onany referenced base table columns, whether the columns are referenced in the viewdefinition or in

When Oracle evaluates views containing string literals or SQL functions that haveglobalization support parameters as arguments (such as TO_CHAR, TO_DATE, and TO_NUMBER), Oracle takes default values for these parameters from the globalizationsupport parameters for the session. You can override these default values byspecifying globalization support parameters explicitly in the view definition.视图中的国际化支持参数当 Oracle 发现视图中存在使用国际化支持参数的 SQL 函数或字符串型符号时,Oracle将使用当

Oracle stores a view’s definition in the data dictionary as the text of the query thatdefines the view. When you reference a view in a SQL statement, Oracle:1. Merges the statement that references the view with the query that defines the view2. Parses the merged statement in a shared SQL area3. Executes the statementOracle parses a statement that references a view in a new shared SQL area only if noexisting shared SQL area contains a similar statement. Therefore, you get the benefit ofreduced m

Views provide a means to present a different representation of the data that resideswithin the base tables. Views are very powerful because they let you tailor thepresentation of data to different types of users. Views are often used to:■ Provide an additional level of table security by restricting access to apredetermined set of rows or columns of a table■ Hide data complexity■ Simplify statements for the user■ Present the data in a different perspective from that of the base table■ Isolat

Unlike a table, a view is not allocated any storage space, nor does a view actuallycontain data. Rather, a view is defined by a query that extracts or derives data from thetables that the view references. These tables are called base tables. Base tables can inturn be actual tables or can be views themselves (including materialized views).Because a view is based on other objects, a view requires no storage other than storagefor the definition of the view (the stored query) in the data dictionary.

A view is a tailored presentation of the data contained in one or more tables or otherviews. A view takes the output of a query and treats it as a table. Therefore, a view canbe thought of as a stored query or a virtual table. You can use views in most placeswhere a table can be used.Because views are derived from tables, they have many similarities. For example, youcan define views with up to 1000 columns, just like a table. You can query views, andwith some restrictions you can update, insert

After the metadata for an external table is created, you can query the external datadirectly and in parallel, using SQL. As a result, the external table acts as a view, whichlets you run any SQL query against external data without loading the external datainto the database.The degree of parallel access to an external table is specified using standard parallelhints and with the PARALLEL clause. Using parallelism on an external table allows forconcurrent access to the datafiles that comprise an ex

The main use for external tables is to use them as a row source for loading data into anactual table in the database. After you create an external table, you can then use aCREATE TABLE AS SELECT or INSERT INTO ... AS SELECT statement, using theexternal table as the source of the SELECT clause.When you access the external table through a SQL statement, the fields of the externaltable can be used just like any other field in a regular table. In particular, you can usethe fields as arguments for an

When you create an external table, you specify its type. Each type of external table hasits own access driver that provides access parameters unique to that type of externaltable. The access driver ensures that data from the data source is processed so that itmatches the definition of the external table.In the context of external tables, loading data refers to the act of reading data from anexternal table and loading it into a table in the database. Unloading data refers to theact of reading dat

External tables access data in external sources as if it were in a table in the database.You can connect to the database and create metadata for the external table using DDL.The DDL for an external table consists of two parts: one part that describes the Oraclecolumn types, and another part (the access parameters) that describes the mapping ofthe external data to the Oracle data columns.An external table does not describe any data that is stored in the database. Nor does itdescribe how data is s

Transaction-specific temporary tables are accessible by user transactions and theirchild transactions. However, a given transaction-specific temporary table cannot beused concurrently by two transactions in the same session, although it can be used bytransactions in different sessions.If a user transaction does an INSERT into the temporary table, then none of its childtransactions can use the temporary table afterward.If a child transaction does an INSERT into the temporary table, then at the en

Temporary tables use temporary segments. Unlike permanent tables, temporary tablesand their indexes do not automatically allocate a segment when they are created.Instead, segments are allocated when the first INSERT (or CREATE TABLE ASSELECT) is performed. This means that if a SELECT, UPDATE, or DELETE is performedbefore the first INSERT, then the table appears to be empty.You can perform DDL statements (ALTER TABLE, DROP TABLE, CREATE INDEX, andso on) on a temporary table only when no session i

In addition to permanent tables, Oracle can create temporary tables to holdsession-private data that exists only for the duration of a transaction or session.The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table that canbe transaction-specific or session-specific. For transaction-specific temporary tables,data exists for the duration of the transaction. For session-specific temporary tables,data exists for the duration of the session. Data in a temporary table is private to these

You can create a table with a column whose datatype is another table. That is, tablescan be nested within other tables as values in a column. The Oracle database serverstores nested table data out of line from the rows of the parent table, using a storetable that is associated with the nested table column. The parent row contains aunique set identifier value associated with a nested table instance.嵌套表1. 一个表的某列的数据类型可以为另一个表.即一个表可以作为一个列值嵌套在表中.2. Oracle

Partitioned tables allow your data to be broken down into smaller, more manageablepieces called partitions, or even subpartitions. Indexes can be partitioned in similarfashion. Each partition can be managed individually, and can operate independentlyof the other partitions, thus providing a structure that can be better tuned foravailability and performance.分区表1. 用户可以使用分区表将数据划分为更小 , 更易管理的单位 , 这种单位被称为分区.2. 分区还可以被继续划分为子分区 .3. 索引也可以采取

Integrity constraint checking occurs after the row with a default value is inserted. Forexample, a row is inserted into the emp table that does not include avalue for the employee's department number. Because no value is supplied for thedepartment number, Oracle inserts the deptno column's default value of 20. Afterinserting the default value, Oracle checks the FOREIGN KEY integrity constraintdefined on the deptno column.默认值的插入及完整性约束检查1. 当一个包含默认值的行被插入之后将发生完整性

You can assign a default value to a column of a table so that when a new row isinserted and a value for the column is omitted or keyword DEFAULT is supplied, adefault value is supplied automatically. Default column values work as though anINSERT statement actually specifies the default value.The datatype of the default literal or expression must match or be convertible to thecolumn datatype.If a default value is not explicitly defined for a column, then the default for the columnis implicitly se

A null is the absence of a value in a column of a row. Nulls indicate missing,unknown, or inapplicable data. A null should not be used to imply any other value,such as zero. A column allows nulls unless a NOT NULL or PRIMARY KEY integrityconstraint has been defined for the column, in which case no row can be insertedwithout a value for that column.Nulls are stored in the database if they fall between columns with data values. In thesecases they require 1 byte to store the length of the column (z

Compression occurs while data is being bulk inserted or bulk loaded. These operationsinclude:■ Direct path SQL*Loader■ CREATE TABLE and AS SELECT statements■ Parallel INSERT (or serial INSERT with an APPEND hint) statementsExisting data in the database can also be compressed by moving it into compressedform through ALTER TABLE and MOVE statements. This operation takes an exclusivelock on the table, and therefore prevents any updates and loads until it completes. Ifthis is not acceptable, then