Tagged: SQL

Oracle Database

Create Oracle SQL Profile For Tuning

Looking for how to tune a SQL statement by creating a SQL Profile? The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans. Automatic SQL Tuning deals with this problem with its SQL Profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement. During SQL Profiling, the Automatic Tuning Optimizer also uses execution history information of the SQL statement to appropriately set optimizer parameter settings, such as changing the OPTIMIZER_MODE initialization...

PL/SQL Oracle

Nested table

In PL SQL exists an efficient and adaptable collection of data: Nested table. Nested table is similar to one dimensional array but with some differences : An array has declare number of element – Nested table not. The size of nested table can increase using extend method. An array has always consecutive elements – Nested table has consecutive elements initially but it can become sparse when elements are deleted. To create a nested table you have to use this syntax : type type_name is table of element_type[size element]; table_name type_name; Here below an example to creation and use of a...

PL/SQL Oracle / SQL Server

Binary Search

One of the most interesting algorithms of research is binary search algorithm. This is the fastest algorithm (algorithm of binary trees apart) to search a data inside a group of elements. In fact it runs in a worst log2(x) comparisions before to find (or not) the data. The only prerequisite is : the set of elements where to search the data must be sorted. Below an example of this algorithm. int binary_search( int arr[], int tot_el, int data ) { /* Declaration and initialization of variables */ int el = 0; int me = 0; int count = 0; /*...

Oracle Database

Using Invisible Indexes

Table created. SQL> create index ind_TEST.index_tables on TEST.tables_empty (a) Index created. OWNER                          OBJECT_NAME                    OBJECT_TYPE —————————— —————————— ———– TEST                           TABLES_EMPTY                   TABLE IND_TEST                       INDEX_TABLES                  INDEX   SQL> exec dbms_stats.gather_table_stats (ownname=>’TEST’,tabname=>’TABLES_EMPTY’,cascade=>TRUE); PL/SQL procedure successfully completed. SQL>  exec dbms_stats.gather_index_stats (ownname=>’IND_TEST’,indname=>’INDEX_TABLES’); PL/SQL procedure successfully completed. SQL> conn ind_TEST Enter password: SQL>  explain plan for 2  select * from TEST.TABLES_EMPTY; SQL> @?/rdbms/admin/utlxpls   PLAN_TABLE_OUTPUT ———————————————————————– Plan hash value: 932631555   ———————————————————————————- | Id  | Operation    | Name         | Rows  | Bytes | Cost (%CPU)| Time     | ———————————————————————————- |   0 | SELECT STATEMENT  |       |     1 |    13 |     2   (0)| 00:00:01 | |   1 |  TABLE...

Oracle Database

EXPLICIT CURSORS – modify tables

In the previous article (cursors overview) we have seen simple examples with SQL statements. You can also use cursor to modify database tables. If we want to use explicit cursor to update or delete data, we need to: -declare the cursor with FOR UPDATE clause (to avoid unwanted changes from others). -use clause WHERE CURRENT OF to modify FETCH returned row. FOR UPDATE clause determines a specific row level lock on your data, so you are sure that data don’t change before your delete or update statement. FOR UPDATE syntax is the follow: DECLARE CURSOR cur_name IS SELECT […your cols…]...

Oracle Database

Create Database Link

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services. After you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a...

Oracle Database 12 c

SQL DEVELOPER 4: NEW FEATURES FOR DBAS

The new Oracle SQL Developer 4.0 (released on december 2013) has some great new features for DBAs, as it becomes the main administration platform instead of Oracle Enterprise Manager Database Control for Oracle Database 12c. OEM Database Control is actually disappeared in Oracle Database 12c, because it is substituted by: – Oracle Enterprise Manager Express, web-based basic administration tool completely integrated into the DB – Oracle SQL Developer 4.0, java client full administration tool, automatically installed in any Oracle client and server installation Oracle SQL Developer 4.0 has a new DBA tab (Menu View -> DBA) from which you can...

PL/SQL Oracle

Create an Index By Table of Record

An index by table is an associative array. An associative array never has more than two columns: the variable being indexes and the index value. However you can combine a record with an associative array to tie multiple values to the same index.   In this case the array still has a variable and an index, but the record variable now contains multiple distinct values. In the below example you’ll see how to associate a record to an associative array.   DECLARE   TYPE r_impiegati IS RECORD (                        ...

Oracle Glossary

Oracle glossary: synonyms

A synonym is an alias to a database object. It can help to reduce sql complexity when written. A public synonym is visible to any users. A private synonym is contained and visible only to the creating user. Some example: DROP [PUBLIC] SYNONYM syn_table; CREATE PUBLIC SYNONYM syn_table FOR anotherschema.table; private synonym CREATE SYNONYM syn_table FOR schema.table;

Oracle Glossary

Oracle glossary: in, not in

In and not in are conditions. Useful for tests one or more values for membership(or not) in a list of values or subquery Some example: select * from table where col1 in (23, 45, 342) — where values of col1 match with 23, 45, 342 select * from table where col1 not in (23, 45, 342) — where values of col1 not match with 23, 45, 342 select * from table where (col1, col2) in (select val_a, val_b from testtab) — where value of col1 and col2 match with val_a and val_b of qubquery