Oracle SQL Summary 3: DDL statements

Oracle SQL Summary 3: DDL Statement 5. DDL statement 5.1 Enable/Disable/Validate/Novalidate combination features: whether www.2cto.com meets the constraint that Validate Novalidate has been record added/modified record existing record added/modification record Enable Yes No Yes Disable Yes No Validate ensure that the existing data meets the constraints; novalidate does not have to consider whether the existing data meets the constraints. Enable is Validate by default unless Novalidate is specified. Disable is Novalidate by default unless Validate is specified. Validate and Novalidate have no default implication for Enable and Disable. Enable Validate is the same as Enable. Check existing records and new records to make sure they all comply with the constraints. Enable Novalidate allows existing records not to meet the constraints, but new/modified records must meet; disable Validate: the constraint is disabled, and the indexes on the constraint are deleted. No bound records can be modified. Disable Novalidate is the same as Disable. Disable the constraint and delete the indexes on the constraint, the bound record can be modified. For more information, see SQL references <constraint> 5.2 about partition table Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition. each partition has its own name, and may optionally have its own storage characteristics. partitioning allows a table, index, or index to partition a table into smaller partitions. Each partition is called a partition. Each partition has its own name and has its own storage features. In the oracle manual www.2cto.com, the advantage of partition is that partitions can be accessed in parallel (because each partition table can be placed on a different hard disk), which improves performance. So my question is, if I only have one hard disk, all the partition tables can only be placed on this hard disk. Isn't it possible to improve performance even if the partition table is made? 5.3 index and index-types of organization table indexes: (1) B-TREE indexes, also divided into: Index-organization table reverse key index reverse index B-TREE Cluster Index (2) Bitmap index (3) function-based index (4) The application domain index www.2cto.com oracle concept describes the index in great detail, * An index is an optional structure, associated with a table or table cluster, that can be sometimes speed data access. by creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small se T of randomly distributed rows from the table. indexes are one of your means of disk I/O. an index is an optional data structure related to tables and table clusters. It can accelerate data access. By generating an index on one or more columns, you can improve the ability to access data stored in random mode. indexing is one of the several ways to reduce hard disk I/O operations. If a heap-organized table has no indexes, then the database must perform a full table scan to find a value. for example, without an index, a query of location 2700 in the hr. specified ments table requires the database to search every row in every table block for this value. this approach does not scale well as data volumes increase. if a heap-organized table (this method is used by default when oracle Creates table), the database must perform a full table scan to find Data. For example, if no index exists and the location = 2700 is queried in the hr. Orders ments table, you must search for each block in the table. This method causes worse performance as data increases. Www.2cto.com For an analogy, suppose an HR manager has a shelf of cardboard boxes. folders containing employee information are inserted randomly in the boxes. the folder for employee Whalen (ID 200) is 10 folders up from the bottom of box 1, whereas the folder for King (ID 100) is at the bottom of box 3. to locate a folder, the manager looks at every folder in box 1 from bottom to top, and then mo Ves from box to box until the folder is found. to speed access, the manager cocould create an index that sequentially lists every employee ID with its folder location: ID 100: Box 3, position 1 (bottom) ID 101: Box 7, position 8 ID 200: Box 1, position 10. assume that an HR manager has a shelf full of card boxes, and folders containing employee information are randomly placed in the box. Each box contains 10 folders, the employee Whalen (ID 200) folder is placed at the top of the 1st boxes, and the employee King (ID 100) folder is placed at the bottom of the 1st boxes, the HR manager must query the folders in each box from the top to the bottom, Query the boxes one by one until you find the desired folder. To speed up the query, the manager can create a table listing the positions of each employee ID and his/her folder in sequence. Bitmap Indexes bitmap index www.2cto.com In a bitmap index, the database stores a Bitmap for each index key. in a conventional B-tree index, one index entry points to a single row. in a bitmap index, each index key stores pointers to multiple rows. in bitmap indexes, the database stores a bitmap for each index key ). In a general B-tree index, an index object (entry) points to a record (a single row), but in a bitmap index, each index key points to multiple records (multiple rows) Bitmap indexes are primarily designed for data warehousing or environments in which queries reference specified columns in an ad hoc fashion. situations that may call for a bitmap index include: The indexed columns have low cardinality, that is, the number of distinct values is small compared to the number of table Rows. the indexed table is either read-only or not subject to significant modification by DML statements. bitmap indexes are mainly designed for Data Environments of data warehouses or similar data environments with a large number of columns distributed randomly. Bitmap indexes can be used in the following scenarios:-The index column has a low similarity, this means that the number of data records with the same value is relatively small. -The indexed table or read-only or non-DML statement focuses on www.2cto.com For a data warehouse example, the sh. customer table has a cust_gender column with only two possible values: M and F. suppose that queries for the number of MERs of a particle gender are common. in this case, the customer. cust_gender column wocould be a candidate for a bitmap index. for example, sh. the customer table has a cust_gender column, which only contains two values: M or F. If there is no other gender (neutral ?), In this example, the customer. cust_gender column has a relatively small similarity and can be used as a bitmap index. Each bit in the bitmap corresponds to a possible rowid. if the bit is set, then the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a B-tree index although it uses a different internal representation. in-Place graphs, each (bit) corresponds to a possible rowid. If a bit is specified, the record (row) corresponding to the rowid corresponds to the value. A ing function converts the location of a bit into The exact rowid so that Bitmap indexes provide the same functionality in different ways of B-TREE indexing. If the indexed column in a single row is updated, then the database locks the index key entry (for example, M or F) and not the individual bit mapped to the updated row. because a key points to define rows, DML on indexed data typically locks all of these rows. for this reason, bitmap indexes are not appropriate for your OLTP applications. if one of the indexed columns is modified, the database locks the index value (such as M or F) because one index value corresponds to many records, that is, the database does not lock only The modified records will be locked together with those records with the same index value as the record. For this reason, bitmap indexes are not suitable for OLTP applications. Index-Organized Tables index An index-organized table is a table stored in a variation of a B-tree Index structure. in a heap-organized table, rows are inserted where they fit. in an index-organized table, rows are stored in an index defined on the primary key for the table. each index entry in the B-tree also stores the non-key column values. thus, the index is the data, and the data is the index. Applications manipulate index-organized tables just like heap-organized tables, using SQL statements. www.2cto.com index organization table (index-organized table) is one of the variants of the table stored in the B-TREE index structure, in a stack organization table (heap-organized table, where there is extra space, the record (row) will be inserted to where (that is, the record is not stored in order, physically random ). However, in index-organized table, records are stored in the index and arranged in the order of primary keys. Each index entity in the B-TREE also stores non-primary key columns, so the index is the table, the table is the index. However, the index-organized table operation is not different from the heap-organized tables operation. SQL statements are also used. For an analogy of an index-organized table, suppose a human resources manager has a book case of cardboard boxes. each box is labeled with a number-1, 2, 3, 4, and so on-but the boxes do not sit on the shelves in sequential order. instead, each box contains a pointer to the shelf location of the next box in the sequence. folders containing employee records are stored in each box. the folders are s Orted by employee ID. employee King has ID 100, which is the lowest ID, so his folder is at the bottom of box 1. the folder for employee 101 is on top of 100,102 is on top of 101, and so on until box 1 is full. the next folder in the sequence is at the bottom of box 2. in this analogy, ordering folders by employee ID makes it possible to search efficiently for folders without having to maintain Separate index. suppose a user requests the records for employees 107,120, and 122. instead of searching an index in one step and retrieving the folders in a separate step, the manager can search the folders in sequential order and retrieve each folder as found. www.2cto.com Index-organized tables provide faster access to table rows by primary key or a valid prefix of the key. the presence of no N-key columns of a row in the leaf block avoids an additional data block I/O. for example, the salary of employee 100 is stored in the index row itself. also, because rows are stored in primary key order, range access by the primary key or prefix involves minimal block I/OS. another benefit is the avoidance of the space overhead of a separate primary key index. index-organized tables are useful wh En related pieces of data must be stored together or data must be physically stored in a specific order. this type of table is often used for information retrieval, spatial (see "Overview of Oracle Spatial"), and OLAP applications (see "OLAP "). 5.4 the role of CASCADE <oracle SQL REFERENCES> is: Specify CASCADE to disable any integrity constraints that depend on the specified integrity constraint. to disa Ble a primary or unique key that is part of a referential integrity constraint, you must specify this clause. use CASCADE to cancel (disable) any integrity constraints that depend on other integrity constraints. For example, to cancel (disable) The primary key constraint or uniqueness constraint related to a foreign key constraint, you must use CASCADE. 5.6 Restrictions of restrictions on Check Constraints check constraints Check Constraints are subject to the following Restrictions: check constraints have the following restrictions: You cannot specify a check constraint for a view. however, you can define the view using the with check OPTIONclause, which is equivalent to specifying a check constraint for the view. you cannot specify a check constraint for a view. However, you can use the with check option clause to define a view. This is equivalent to specifying the check constraint for the view. Www.2cto.com The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other tables. the check constraint can point to any column in a table, but it cannot point to columns in other tables. Conditions of check constraints cannot contain the following constructs: the Conditions of check constraints cannot include Subqueries and scalar subquery expressions Subqueries and scalar subquery CILS to the functions that are not deterministic (CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP, SESSIONTIMEZONE, SYSDATE, region, UID, USER, and USERENV) the following functions cannot be called: CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP, SESSIONTIMEZONE, USER, USERENV callto user-defined functions Call USER-defined function Dereferencing of REF columns (for example, using the DEREF function) www.2cto.com Nested table columns or attributes The pseudo columns CURRVAL, NEXTVAL, LEVEL, or ROWNUM pseudo columns CURRVAL, NEXTVAL, LEVEL, ROWNUM Date constants that are not fully specified the author of static data that is not completely specified (or defined) yeyelei