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));
I would [...]
Archive of posts filed under the Oracle11g category.
Removing table rows using an inline view
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 simple [...]
ORION: Oracle I/O Numbers Calibration Tool in Oracle 11g R2
ORION (Oracle I/O Calibration Tool) is a standalone tool for calibrating the I/O performance for storage systems that are intended to be used for Oracle databases. The calibration results are useful for understanding the performance capabilities of a storage system, either to uncover issues that would impact the performance of an Oracle database or to [...]
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 [...]
Oracle Database 11g Architecture and Background Processes
This poster, supplied by Oracle University contains a comprehensive list that categorizes the database background processes and a single diagram that illustrates the relationships between key database memory, processes and strorage structures.
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.
Upgrade path for Oracle Database 11g Release 1
Still quite a few remain in 7.3, 8.1.7 and 9.2.0.8 (ofcourse, without a support on very old versions and happy with what they have) – Others are gearing towards to upgrade to Oracle 11g Release 1. The recommended direct and indirect path upgrade listed below:
Oracle Database Statistics Names
Oracle database statistic names in this post is from Oracle Database 11g Release 11.1.0.6.0. Many of these statistics are tied to the internal implementation of Oracle and therefore are subject to change or deletion without notice, even between patch releases. Application developers should be aware of this and write their code to tolerate missing or extra statistics.
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.