This post covers over 200 commonly observed row source operations (, which is a unique combination of operation and options) with an example to reproduce the operation. A list of operations and options as of 11g R2 is available here, you should note that not all options are available for each operation. ALTER INDEX STATEMENT [...]
Archive of posts tagged Oracle9i
OVERLAPS predicate
OVERLAPS predicate is a useful feature, which is not available in most SQL implementations that can help to determine whether the two periods overlap each other. If two periods overlap then the predicate evaluates to TRUE otherwise FALSE. Let us create a test table with three rows – one with overlapping dates, second with non-overlapping [...]
Removing table rows using an inline view
The SQL standard allows you to use an inline view in a DELETE statement, see the pseudo form below – If a condition such as key-preserved table is not met then it will result in error, “ORA-01752: cannot delete from view without exactly one key-preserved table.” DELETE (SELECT some_column FROM table_one JOIN table_two USING (join_column)); [...]
Joins, Join Conditions, Filters and Join Types
Joins A join is a query that combines rows from two or more tables. Oracle performs a join whenever multiple tables appear in the FROM clause of the query. Below is a SQL join statement, legacy syntax and ANSI/ISO syntax (also referred as new join syntax, was first introduced in Oracle 9i release) in a [...]
Bitmap Indexes and Serialized Transactions
The advantages of using bitmap indexes are greatest for low cardinality columns: that is, columns in which the number of distinct values is small compared to the number of rows in the table. If the number of distinct values of a column is less than 1% of the number of rows in the table, or if the values in a column are repeated more than 100 times, then the column is a candidate for a bitmap index. Even columns with a lower number of repetitions and thus higher cardinality can be candidates if they tend to be involved in complex conditions in the WHERE clauses of queries.
Setting up Oracle Statistics Package (STATSPACK)
To install it, you must be connected to database as SYSDBA and execute the script spcreate.sql located under $ORACLE_HOME/rdbms/admin. To uninstall statspack, connect to database as SYSDBA and run spdrop.sql located under $ORACLE_HOME/rdbms/admin.
How to create an Oracle User to use both OS Authentication and Password Authentication (REMOTE_OS_AUTHENT and OS_AUTHENT_PREFIX)
The initialization parameter REMOTE_OS_AUTHENT offers the trusted authentication model to the network, which is users can have OS accounts on machines other than the database server and gain access to database, convenience of single sign-on through remote OS authentication
Oracle Database Undocumented Parameters List
Table x$ksppi contains all documented and undocumented (aka hidden, which status with _ [underscore]); joining with x$ksppcv table on column indx will provide parameter name, value and description.
OERR: The command line Oracle error code lookup utility
OERR stands for Oracle Error, which is a utility ships with Oracle distribution for Linux and UNIX that helps retrieve messages from message (.msg) files based on the supplied error code. This utility is not available for Windows, but there are variants freely available on the Internet.
Oracle Metadata: PLAN_TABLE
PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans. This post summarizes the metadata information of this table for Oracle8/8i, 9i, 10g, and 11g; read post, to know the methods for obtaining the formatted explain plan output from plan_table. STATEMENT_ID: VARCHAR2(30) – Value of the optional [...]