Taggato: SQL

Oracle Database

Create Oracle SQL Profile For Tuning

Stai cercando come ottimizzare un’istruzione SQL creando un profilo SQL? Query Optimizer a volte può produrre stime imprecise su un attributo di un’istruzione a causa della mancanza di informazioni, portando a piani di esecuzione scadenti. L’ottimizzazione SQL automatica risolve questo problema con la sua capacità di profiling SQL. L’ottimizzazione della sintonizzazione automatica crea un profilo dell’istruzione SQL denominato profilo SQL, costituito da statistiche ausiliarie specifiche per tale istruzione. Durante la creazione di profili SQL, l’ottimizzazione della sintonizzazione automatica utilizza anche le informazioni sulla cronologia di esecuzione dell’istruzione SQL per impostare in modo appropriato le impostazioni dei parametri dell’ottimizzatore, ad esempio...

PL/SQL Oracle

Nested table

In PL SQL esiste una raccolta di dati efficiente e adattabile: la tabella annidata. La tabella annidata è simile a una matrice unidimensionale ma con alcune differenze: Un array ha il numero di dichiarazioni di elementi, la tabella nidificata no. La dimensione della tabella nidificata può aumentare utilizzando il metodo di estensione. Un array ha sempre elementi consecutivi: la tabella annidata ha inizialmente elementi consecutivi ma può diventare sparsa quando gli elementi vengono eliminati. Per creare una tabella nidificata devi usare questa sintassi: type type_name is table of element_type[size element]; table_name type_name; Di seguito un esempio di creazione e utilizzo...

PL/SQL Oracle / SQL Server

Binary Search

Uno degli algoritmi di ricerca più interessanti è l’algoritmo di ricerca binaria. Questo è l’algoritmo più veloce (algoritmo di alberi binari a parte) per cercare dati all’interno di un gruppo di elementi. In effetti viene eseguito in un peggior confronto log2 (x) prima di trovare (o meno) i dati. L’unico prerequisito è: l’insieme di elementi in cui cercare i dati deve essere ordinato. Di seguito un esempio di questo algoritmo.int binary_search( int arr[], int tot_el, int data ){/* Declaration and initialization of variables */int el = 0;int me = 0;int count = 0;/* Start program */el = tot_el-1;while (count <=...

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