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 Oracle10g
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 [...]
Optimizer Index Access Paths: Why Oracle is not using my Index?
Access paths are ways in which data is retrieved from the database. In general, index access paths should be used for statements that retrieve a small subset of table rows, while full scans are more efficient when accessing a large portion of the table. Online transaction processing (OLTP) applications, which consist of short-running SQL statements [...]
Get a complete CREATE TABLE definition using DBMS_METADATA package
CREATE TABLE has lot of option to go with it; it is hard to remember all of them. Most people will get lost while reading the rail road diagrams. It is fairly quick to create the table in its simple form and obtain all the parameters associated with it. ops$rperumal@PDB10> CREATE TABLE t 2 (n [...]
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.
DBMS_XPLAN.DISPLAY_CURSOR results in error cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
Some of you received the below error while your tried to lookup the execution plan using DBMS_XPLAN.DISPLAY_CURSOR procedure.
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
Reading, Interpreting, and Components of Oracle STATSPACK Report
Statspack is a set of SQL, PL/SQL and SQL*Plus scripts which allow the collection, automation, storage and viewing of performance data. A user, PERFSTAT, is automatically created by the installation; owns all objects needed by this package. This user is granted limited query-only privileges on the V$views required for performance tuning. A Statspack report is generated from two snapshots, which is nothing but a collection of contents from various dynamic performance tables. In general, it is recommended to get the snapshots intervals be 15 minutes in length. This allows fine-grained reporting when hangs are suspected/detected. Following is the compilation from metalink doc id 228913.1; and the statspack report shown here generated on a low end desktop system to see how the advisors work in 10gr2 (10.2.0.4)
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.