ITPub博客

引用自http://developer.mimer.com/documentation/html_92/Mimer_SQL_Mobile_DocSet/Defining_Database9.htmlCreate a secondary index called ITM_RELEASE_DATE on the RELEASE_DATE column in the ITEMS table: CREATE INDEX itm_release_date ON items(release_date);Primary key columns may also be included in a secondary index. If a table has the primary keycolumns A, B, and C, the primary index would cover all three columns of the primary key.The following combinations of the columns in the primary key are aut

引用自:http://developer.mimer.com/documentation/html_92/Mimer_SQL_Mobile_DocSet/Defining_Database9.htmlA secondary index is automatically used during searching when it improves the efficiency of thesearch.Secondary indexes are maintained by the system and are invisible to the user.Any column(s) may be specified as a secondary index, except columns declared using a LOB data type.Columns in the PRIMARY KEY, the columns of a FOREIGN KEY and columns defined as UNIQUE areautomatically indexed, (in th

Oracle database applications can be run against non-Oracle database servers using SQL*Connect or theOracle Transparent Gateway. In such cases, the format of rowids varies according to thecharacteristics of the non-Oracle system. Furthermore, no standard translation toVARCHAR2/hexadecimal format is available. Programs can still use the ROWID datatype. However, theymust use a nonstandard translation to hexadecimal format of length up to 256 bytes.Rowids of a non-Oracle database can be stored in a

When a row's physical location changes, the logical rowid remains valid even if it contains a guess,although the guess could become stale and slow down access to the row. Guess information cannot beupdated dynamically. For secondary indexes on index-organized tables, however, you can rebuild theindex to obtain fresh guesses. Note that rebuilding a secondary index on an index-organized tableinvolves reading the base table, unlike rebuilding an index on an ordinary table.Collect index statistics w

Logical rowids are similar to the physical rowids in the following ways:Logical rowids are accessible through the ROWID pseudocolumn.You can use the ROWID pseudocolumn to select logical rowids from an index-organized table. TheSELECT ROWID statement returns an opaque structure, which internally consists of the table's primarykey and the physical guess (if any) for the row, along with some control information.You can access a row using predicates of the form WHERE ROWID = value, where value is th

Rows in index-organized tables do not have permanent physical addresses , they are stored in theindex leaves and can move within the block or to a different block as a result of insertions.Therefore their row identifiers cannot be based on physical addresses. Instead, Oracle providesindex-organized tables with logical row identifiers, called logical rowids, that are based on thetable's primary key. Oracle uses these logical rowids for the construction of secondary indexes onindex-organized table

Oracle uses rowids internally for the construction of indexes. Each key in an index is associatedwith a rowid that points to the associated row's address for fast access. End users and applicationdevelopers can also use rowids for several important functions:Rowids are the fastest means of accessing particular rows.Rowids can be used to see how a table is organized.Rowids are unique identifiers for rows in a given table.Before you use rowids in DML statements, they should be verified and guarant

Restricted rowids use a binary representation of the physical address for each row selected. Whenqueried using SQL*Plus, the binary representation is converted to a VARCHAR2/hexadecimalrepresentation. The following query:SELECT ROWID, last_name FROM employees WHERE department_id = 30;can return the following row information:ROWID ENAME------------------ ----------00000DD5.0000.0001 KRISHNAN00000DD5.0001.0001 ARBUCKLE00000DD5.0002.0001 NGUYENAs shown, a restricted rowid's VARCHAR2

You can use the function SUBSTR to break the data in a rowid into its components. For example, youcan use SUBSTR to break an extended rowid into its four components (database object, file, block,and row):SELECT ROWID, SUBSTR(ROWID,1,6) "OBJECT", SUBSTR(ROWID,7,3) "FIL", SUBSTR(ROWID,10,6) "BLOCK", SUBSTR(ROWID,16,3) "ROW" FROM products;ROWID OBJECT FIL BLOCK ROW------------------ ------ --- ------ ----AAA

Extended rowids use a base 64 encoding of the physical address for each row selected. The encodingcharacters are A-Z, a-z, 0-9, +, and /. For example, the following query:SELECT ROWID, last_name FROM employees WHERE department_id = 20;can return the following row information:ROWID LAST_NAME------------------ ----------AAAAaoAATAAABrXAAA BORTINSAAAAaoAATAAABrXAAE RUGGLESAAAAaoAATAAABrXAAG CHENAAAAaoAATAAABrXAAN BLUMBERGAn extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:OOO

Physical rowids provide the fastest possible access to a row of a given table. They contain thephysical address of a row (down to the specific block) and allow you to retrieve the row in a singleblock access. Oracle guarantees that as long as the row exists, its rowid does not change. Theseperformance and stability qualities make rowids useful for applications that select a set of rows,perform some operations on them, and then access some of the selected rows again, perhaps with thepurpose of up

Each table in an Oracle database internally has a pseudocolumn named ROWID. This pseudocolumn is notevident when listing the structure of a table by executing a SELECT * FROM ... statement, or aDESCRIBE ... statement using SQL*Plus, nor does the pseudocolumn take up space in the table.However, each row's address can be retrieved with a SQL query using the reserved word ROWID as acolumn name, for example:SELECT ROWID, last_name FROM employees;You cannot set the value of the pseudocolumn ROWID in

Oracle uses a ROWID datatype to store the address (rowid) of every row in the database.Physical rowids store the addresses of rows in ordinary tables (excluding index-organized tables),clustered tables, table partitions and subpartitions, indexes, and index partitions andsubpartitions.Logical rowids store the addresses of rows in index-organized tables.A single datatype called the universal rowid, or UROWID, supports both logical and physical rowids,as well as rowids of foreign tables such as no

摘自http://www.itpub.net/viewthread.php?tid=114023&extra=&page=21. How do we store a bitmap index?2. Example3. Bitmapped index blockdump4. Miscellaneous1. How do we store a bitmap index? Essentially this is a three-step operation: I. A bitmap is created for every distinct key column value. II. The bitmaps are then compressed. III. The compressed bitmaps are stored in a normal B-TREE index structure along with the keyvalue, and a ROWID range. The ROWID range is th

/*+ USE_CONCAT */Causes all ORs in the statement to be transferred to UNION ALLs.Use the USE_CONCAT hint to change OR conditions into a UNION ALL. We have found that this hint does not always seem to work as expected. The OR EXISTS in the script below is one case where the USE_CONCAT would speed up the processing considerably.SELECT /*+ USE_CONCAT */ a.acct_name FROM accts aWHERE a.acct_no between (1000 and 2000) OR EXISTS (SELECT 'x' FROM trans t WHERE a.acct_no = t.acct

Hash clusters group table data in a manner similar to regular index clusters (clusterskeyed with an index rather than a hash function). However, a row is stored in a hashcluster based on the result of applying a hash function to the row’s cluster key value.All rows with the same key value are stored together on disk.Hash clusters are a better choice than using an indexed table or index cluster when atable is queried frequently with equality queries (for example, return all rows fordepartment 10

Clusters are an optional method of storing table data. A cluster is a group of tablesthat share the same data blocks because they share common columns and are oftenused together. For example, the employees and departments table share thedepartment_id column. When you cluster the employees and departmentstables, Oracle physically stores all rows for each department from both theemployees and departments tables in the same data blocks.Because clusters store related rows of different tables togethe

Oracle provides extensible indexing to accommodate indexes on customized complexdatatypes such as documents, spatial data, images, and video clips and to make use ofspecialized indexing techniques. With extensible indexing, you can encapsulateapplication-specific index management routines as an indextype schema object anddefine a domain index (an application-specific index) on table columns or attributes ofan object type. Extensible indexing also provides efficient processing ofapplication-speci

UROWID datatype columns can hold logical primary key-based rowids identifyingrows of index-organized tables. Oracle supports indexes on UROWID datatypes of aheap- or index-organized table. The index supports equality predicates on UROWIDcolumns. For predicates other than equality or for ordering on UROWID datatypecolumns, the index is not used.在堆表及索引表的 UROWID 列上建立B树索引1. 数据类型为 UROWID 的列可以用于存储根据主键生成的唯一标示索引组织表行的rowid2. Oracle 能够在堆表及索引表的

You can partition an index-organized table by RANGE, HASH, or LIST on columnvalues. The partitioning columns must form a subset of the primary key columns. Justlike ordinary tables, local partitioned (prefixed and non-prefixed) index as well asglobal partitioned (prefixed) indexes are supported for partitioned index-organizedtables.分区索引组织表1. 用户可以采用 RANGE，HASH，或 LIST 的方式对索引表进行分区2. 分区列必须是属于主键列3. 可在分区索引表上建立本地全局索引[@more@]