Supressing Repeating Values

SELECT DECODE((LAG(DEPTNO) OVER(ORDER BY DEPTNO)),DEPTNO ,NULL,DEPTNO) DEPTNO , ENAME
FROM EMP;

how to assign data of the deleted row to variables?
we can assign data of the deleted row into variables using returning ... Into .
Similar to select ... Into, returning ... Into will not work if multiple rows are deleted.
Ex:-
create or replace procedure test_proc as
fname varchar2(30);
lname varchar2(30);
begin
delete from emp
where empno = 7369
returning ename, job into fname, lname;
dbms_output.put_line('name deleted = ' || fname || ' ' || lname);
end;

Need others to post here also.

This is getting a typical Oracle / SQL thread.

Packages

You can write a procedure in package body without defining it in package specs.It considers the procedure as a private procedure for package and can be used only inside that package. But you can't define a procedure in the package specs without code in the package body , At the time of compilation it will throw error message.

Indexes - 1
Creating the right indexes can improve the search performance in database applications
In general, integer fields make more efficient indexes than character fields and fixed-length data types are more efficient than variable types.

What to Index
Columns used frequently in Where clauses , Group by clauses or Order by clauses
Columns used in joins, usually primary and foreign keys
Tables where the average row length is high. An index can avoid table fetch in favor of an index scan

What Not to Index
Tables with a small number of rows
Tables with heavy transaction-based I/O
Columns not used in Where clause
Columns with greater than 5 percent selectivity
Wide columns (greater than 25 bytes in width)

Some useful Data Dictionary Views related to indexes
USER_INDEXES
USER_IND_COLUMNS
USER_IND_EXPRESSIONS

When Indexes are not used
Index will not be used if the column reference in the where clause is part of the function or expression.
Ex:- upper(Ename) =’ADAM’ or where hiredate +7 =’10-JAN-89’

Indexes - II

Query to display table name along with their indexes
select user_tables.table_name, user_indexes.index_name
from user_tables join user_indexes on user_indexes.table_name = user_tables.table_name
order by user_tables.table_name,user_indexes.index_name;

Query to display table name s, index names and column names
select user_tables.table_name, user_indexes.index_name, user_ind_columns.column_name
from user_tables join user_indexes on user_indexes.table_name = user_tables.table_name join user_ind_columns on user_indexes.index_name = user_ind_columns.index_name
order by user_tables.table_name,user_indexes.index_name;

Indexes -III

Query To view function based index details

select i.index_name, i.uniqueness, c.column_name, f.column_expression
from all_ind_columns c, all_indexes i, all_IND_EXPRESSIONS f
where i.table_owner = 'TABLE_OWNER_HERE'
and i.table_name = 'TABLE_NAME_HERE'
and i.index_name = c.index_name
and i.owner = c.indeX_owner
and c.index_owner = f.index_owner(+)
and c.index_name = f.index_name(+)
and c.table_owner = f.table_owner(+)
and c.table_name = f.table_name(+)
and c.column_position = f.column_position(+)
order by i.index_name, c.column_position

To rename a column in a table
alter table tablename rename column oldcolumnname to newcolumnname

Indexes -IV
How to monitor whether existing indexes are used by the queries or not?
You can use the view v$object_usage to monitor index usage. The view displays statistics about index usage gathered from the database. Monitor for sufficient number of days. All indexes that have been used at least once can be displayed in this view.

We can monitor the usage of particular index using the following syntax
ALTER INDEX INDEX_NAME MONITORING USAGE;

To see if the index has been used by a query or not
SELECT INDEX_NAME, USED FROM V$OBJECT_USAGE WHERE OWNER = 'XXX’;

To disable monitoring option
ALTER INDEX INDEX_NAME NOMONITORING USAGE;

To generate monitoring scripts for all indexes
SELECT 'ALTER INDEX '||INDEX_NAME||' MONITORING USAGE;' FROM USER_INDEXES WHERE INDEX_TYPE='NORMAL';

To turn off monitoring for all indexes
SELECT 'ALTER INDEX '||INDEX_NAME||' NOMONITORING;' FROM USER_INDEXES WHERE INDEX_TYPE='NORMAL';

Find unused indexes
SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED FROM V$OBJECT_USAGE WHERE MONITORING='YES' AND USED='NO';

Indexes -V
When to rebuild indexes?

when there are lots of dml operations on the table, the indexes can get fragmented. The height of the btree index can increase. In this situation it is required to rebuild index.
Or else find the value of (number of deleted rows/total number of rows). If the result's value >=20%, then go ahead with the rebuild index statement.
Use the following syntax to rebuilt the index.

Alter index indexname rebuild;

if the table is in use it will through error message ora-00054: resource busy and acquire with nowait specified

you can do online rebuild using the following syntax

alter index indexname rebuild online;

Indexes –VI

When to disable indexes?

The presence of many indexes on a table decreases the performance of updates, deletes, and inserts, because Oracle must also update the indexes associated with the table. So While inserting large volume of data you may temperorly disable index as follows.

SQL> alter index indexname unusable;
Index altered.

An unusable index must be rebuilt, or dropped and re-created, before it can be used.

Indexes -VII
What are different approaches used for creating indexes

Proactive approach:- Creating indexes based on anticipation which columns will be most often used for joining , selection, groping and ordering is called proactive approach.

Reactive approach :- Indexes are created based on optimizer feedback, query implementation plan, and system performance measurements.

What is the difference between strcpy and memcpy?

Query will return true if user is authenticated as SYSDBA.

SELECT USERENV('ISDBA') FROM DUAL

export / Import specific objects
To export only procedures/fuction from database

expdp scott/tiger directory=DPDATA1 dumpfile=expprocs.dmp include=PROCEDURE --(or FUNCTION)

impdp scott/tiger directory=DPDATA1 dumpfile=expprocs.dmp sqlfile=procs.sql

For this we need to create directory to hold procs.sql

Collections –1
A collection is an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection.

When to use Collections?
When you need to retrieve large volume of data from table, you declare a cursor, retrieve the values one at a time from the database, and process them sequentially. Instead of process them sequentially, using collections concepts you may retrieve a load of values all in one go, and load them into a collection type , so that you can carry out some sort of operation on the group of values as a whole.

Collection Types
PL/SQL offers three collection types.
Index-by tables (Introduced in oracle 7 version)
Nested tables -( Sets and bags in other languages)
VARRAY (short for variable-size arrays). – (Arrays in other languages)

Collections –1I
Index by table
Syntax is
Type <typename> is table of <col_name>
Index by binary_integer

<variable_name> <type_name>

· Index-by tables cannot be stored in the database
· We can’t perform DML operations on index by tables
· Index-by tables can have negative subscripts
· Data does not have to be stored in consecutive rows
· To extend an index-by table, you just specify larger subscripts.

Ex:- Declare
Type enametype is table of emp.ename%type not null index by binary_integer;
Type dnametype is table of dept.dname%type index by binary_integer;
Enamelist enametype;
Dnamelist dnametype;
subscript binary_integer :=1;
Begin
For var in ( select ename, dname from emp , dept where emp.deptno = dept.deptno)
Loop
enamelist(subscript) := var.ename;
dnamelist(subscript) := var.dname;
dbms_output.put_line('Employee name is ' || enamelist(subscript) || 'Department name is' ||dnamelist(subscript));
subscript := subscript + 1;
end loop;
end;
/

Collections –1II
Nested Tables
- It is a collection of rows represented as column within a main table. Column represents another table by itself.
- Cannot be indexed
- Nested tables data stored outside the main table (out of line storage)
- Main table's column keeps a pointer to the nested table
- No limitations for nesting in terms of number of rows
- No restrictions on the number of columns having nested tables
- Oracle stores the rows of a nested table in no particular order
- While retrieving data into PL/Sql Variable, the rows are given consecutive subsripts starts with 1
- Is always dense initially, but can become sparse after deletes.

Collections –1V
Varray
- It is a set of objects each with the same data type.
- Enable storage of multiple values for a single column per row
- Number of multiple values are fixed by definition
- Limited to one column per table
- Array values are stored in the table itself (in line storage)
- Indexing a varray column is not possible
- Selection of array values is complex
- DML operations require use of constructor methods
- NULL values need to be specified explicitly
- Is always dense; you can only remove elements from the end of a varray.

Collections –V
How to choose your collection type
Use nested table when you need to...
–Work within PL/SQL code only
–Sparsely fill and manipulate the collection
–Take advantage of negative index values
Use nested tables when you need to...
–Access the collection inside SQL (table functions, columns in tables)
–Want to perform set operations
Use varrays when you need to...
–If you need to specify a maximum size to your collection
–Access the collection inside SQL (table functions, columns in tables).