SQL

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 Read More

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; /* Read More

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 Read More

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…] Read More