Stagnation

Well it's been quite a while since I posted anything here and quite frankly it's been quite a while since I found anything worth posting. My work occupies most of my time and it seems like I have lost that zeal to learn and try out new stuff. So,as part of this year's resolution, I have decided to hit the books again! Hopefully you will see a new post coming from me soon.

Meanwhile, I just thought of sharing this link with all of you. I hit this link during my inital training days and have found this quite useful.

http://learndatamodeling.com/

Regards,
Chandan
'Knowledge should not be speculative, it should be certainty'

Union woes

Silly Me!

I always thought a Union should and would accept a Select clause that is exactly identical w.r.t number of columns, data types and column names. i.e if I want to say display the list of all employees who are in Salary Grade 'A' and the list of employees who are in HR Department in the same result set (for some strange reason) I was expecting this to be the correct query:

SELECT
E.EMPLOYEE_NAME,
S.SAL_GRADE AS GRADE_DEPT
FROM EMPLOYEE E , SALARY_GRADE S
WHERE E.SAL > S.LOW_SAL
AND E.SAL < S.HIGH_SAL
AND S.SAL_GRADE = 'A'

UNION

SELECT
E.EMPLOYEE_NAME,
D.DEPT_NAME AS GRADE_DEPT
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT_CODE = D.DEPT_CODE
AND D.DEPT_CODE = 'HR'


But apparently my assumption made using my "profound knowledge" was wrong. All a Union requires is for the number of columns and the data type of the columns in the Select list to be the same (refer Wiki). So even if I interchange the columns in the second query above, the query will still run and give results, but not necessarily the results you would want!

I learn this the hard way after discovering a stange bug in one of the reports that I had developed. After looking into the SQL I realized that I had interchanged the column names in one of the SQLs in the Union. The SQL still worked fine because each query was still having equal number of columns and same data types for each column!

Felt like pulling my hair out after discovering the bug!

Correlated Update : A tricky Situation

Hi Folks,

Long time no see, any post.
Last day I saw a bewildering issue. It may not strike some of you if you already know it.
But i thought this should be shared.

In a correlated update :
eg:
UPDATE MASTER_ORDERS X
SET QTY=(SELECT Y.QTY
FROM ORDERS Y
WHERE X.ORDER_NUM = Y.ORDER_NUM);


We know one thing ; that is it will scan the whole table and consider each row for updating.
But in this case if the inner query returns not result, still the update will complete. It will update even the row for which the inner query did not give any data with NULL. Some kind of force update .Hence resulting in loss of data.
Tom Kyte says this not the right way to do such an update.

To avoid this there are two methods:

- Use an updatable join (updatable view), if the row to be updated is key preserved (i.e; only one row is fetched for the join condition).

- Use an exists clause in the outer where condition, so that only records with change are picked for update. It also makes the query faster as the update is done on a smaller subset.

UPDATE MASTER_ORDERS X
SET QTY=(SELECT Y.QTY
FROM ORDERS Y
WHERE X.ORDER_NUM = Y.ORDER_NUM)
WHERE EXISTS (SELECT 1
FROM ORDERS Y
WHERE X.ORDER_NUM = Y.ORDER_NUM);

Insufficient Privileges in Stored Procedures

Hello All,
I came across an intersting problem.
There are two roles CONNECT and RESOURCE given to user xxx.
The problem is while creating a view dynamically through named procedures,i get insufficient privileges error
ex:create or replace procedure stg_proc as
begin
execute immediate 'create or replace view stg_view as select * from stg_dummy';
end;
SQL> /
Procedure created.
SQL> exec stg_proc;
BEGIN stg_proc; END;
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "STB_STAGING.STG_PROC", line 3
ORA-06512: at line 1
but the strange thing was, there was no error with anonymous procedure block while creatng the view dynamically
ex:begin
execute immediate 'create or replace view stg_view as select * from stg_dummy';
end;
SQL> /
PL/SQL procedure successfully completed.
The reason why this happens is
PL/SQL Blocks and Roles
The use of roles in a PL/SQL block depends on whether it is an anonymous block or a named block (stored procedure, function, or trigger), and whether it executes with definer's rights or invoker's rights.
Named Blocks with Definer's Rights
All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.
The SESSION_ROLES view shows all roles that are currently enabled. If a named PL/SQL block that executes with definer's rights queries SESSION_ROLES, the query does not return any rows.
Anonymous Blocks with Invoker's Rights
Named PL/SQL blocks that execute with invoker's rights and anonymous PL/SQL blocks are executed based on privileges granted through enabled roles. Current roles are used for privilege checking within an invoker's rights PL/SQL block, and you can use dynamic SQL to set a role in the session.
There are 2 solutions to the above problem
1)AUTHID as CURRENT_USER
2)Grant CREATE VIEW permission to xxx.
ex:create or replace procedure stg_proc AUTHID CURRENT_USER as
begin
execute immediate 'create or replace view stg_view as select * from stg_dummy';
end;
Procedure created.
SQL> exec stg_proc;
PL/SQL procedure successfully completed.
For further information can be found at http://stanford.edu/dept/itss/docs/oracle/10g/network.101/b10773/authoriz.htm#1007305
Hope it helps....

Difference between a Primary Key and a Unique Index

The differences between a Primary Key and a Unique Index are:
1) Column(s) that make the Primary Key of a table cannot be NULL since by definition, the Primary Key cannot be NULL since it helps uniquely identify the record in the table. The column(s) that make up the unique index can be nullable. A note worth mentioning over here is that different RDBMS treat this differently –> while SQL Server and DB2 do not allow more than one NULL value in a unique index column, Oracle allows multiple NULL values. That is one of the things to look out for when designing/developing/porting applications across RDBMS.
2) There can be only one Primary Key defined on the table where as you can have many unique indexes defined on the table (if needed).
3) Also, in the case of SQL Server, if you go with the default options then a Primary Key is created as a clustered index while the unique index (constraint) is created as a non-clustered index.
This is just the default behavior though and can be changed at creation time, if needed.
So, if the unique index is defined on not null column(s), then it is essentially the same as the Primary Key and can be treated as an alternate key meaning it can also serve the purpose of identifying a record uniquely in the table.

I have directly picked the above information from http://decipherinfosys.wordpress.com/2007/07/04/back-to-the-basics-difference-between-primary-key-and-unique-index/ link.

Selectivity Factor - Block Selectivity

Hello everyone.

I was just reading through an article on Indexes, and as I was reading it I realized that I had a mistake in my earlier post where I had claimed that Row selectivity is an important factor to consider while deciding on Indexes. As you know , Indexes are only helpful if they help in reducing the number of IO(physical or logical). Lets consider the case of Oracle. In Oracle, data in a table is stored in blocks. Lets assume a case where each block hold only 100 rows of a table. If there 1000 rows in a table, then assume that these are stored in 10 different blocks. In this case, even if a filter has a low selectivity (say 10%), having an index will result in 10 IOs per block(for 10 rows in each ). If Oracle had decided to do a Full Table scan, then it would have resulted in just 10 IOs as a whole. My point here is, instead of taking Row selectivity as a decision making parameter, Indexing decisions have to be based on something known as Block selectivity. A simple definition of Block selectivity is :

Block selectivity = P/Ptotal
where P = Number of blocks which have atleast 1 row which qualifies the filter condition.
Ptotal = Total number of blocks below the high water mark of the table.

The lesser the block selectivity for a field, the better the performance will be if the field is indexed. One way to ensure a good block selectivity is to physically cluster the data along this field. A good clustering factor will ensure that the rows with same value for this field are physically close and in a lesser number of blocks. On the other hand , if these rows were spread across almost the entire set of blocks, then an Index may prove as an overhead as each block would have to be visited only once. In fact a full table scan would ensure that each block is read once and only once. So people, next time you are planning to index a field make sure to physically cluster the data in the table and find out the block selectivity for this field. For more information refer this link:

http://www.hotsos.com/e-library/abstract.php?id=5

P.S: You will have to register to read this article. Registration is very simple and will take only 1 min of your time.

Moving To New Tablespace.

Hi Guys,

This is a small thing. But may be useful.
When we move tables and indexes to another tablespace, what all needs to be done (Oracle queries are used).
1) move the table.
alter table tablename move tablespace tablespacename;
2) now the indexes are of no use. You have to rebuild them in the new table space.
alter index indexname rebuild tablespacename;
3) Finally the packages need to be recompile. There is no need for explicit compilation as during execution it will be recompiled.
alter package packagename compile package;

Thats it!!