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…
Leggi tutto
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…
Leggi tutto
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>…
Leggi tutto
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…
Leggi tutto
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…
Leggi tutto
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,…
Leggi tutto
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…
Leggi tutto
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…
Leggi tutto
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,…
Leggi tutto
Oracle subquery is a sql query within another sql query. Some example: select tab.name, tab.surname from (select name, surname from customer where surname like ‘S%’) tab select id from myusers where (name, surname) in (select name, surname from customer where surname like ‘S%’) tab select (select ‘1’ from dual) from table.
Commenti recenti