Tag Archives: DBMS

Generating ER diagram from database -2

Sometime back I wrote about DBvisualizer to generate schema ER design from database.

Here is another way by using schemaspy.

http://schemaspy.sourceforge.net/

This is a simple java based tool/ jar file. As per example given in link above, all you need to run the jar file providing database access details.

java -jar schemaSpy.jar -t dbType -db dbName [-s schema] -u user [-p password] -o outputDir 

You might want to give database drivers jar file path. For example, for Postgres

java -jar /home/kamal/pathto/schemaSpy_5.0.0.jar -t pgsql -db dbnamehere -s public -u dhusername -p dbpassword -host localhost -port 5432  -o /home/kamal/outputdir -dp /home/kamal/pathto/postgresql-9.3-1104.jdbc4.jar

Find and delete blocking sessions- Oracle

Following query would give you all the session running in your oracle database with query details

select sesion.sid,
sesion.serial#,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null;

Now you might want to kill an unwanted session

ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;

Union vs Union All

The simple difference between using Union and Union All is that Union selects only distinct records from both the resultsets, whereas Union All will return everything.

But today I figured out one more use of union All today. In a ms sql query, I was using union to add up resultset of multiple queries, and got this error

“The text data type cannot be selected as DISTINCT because it is not comparable.”

As I knew none of my queries will return duplicate records, I did not need distinct clause. Replacing Union with “Union All” did the trick in this case.

concatenate strings in different rows of a recordset

How to concatenate strings in different rows of a recordset, using a common id?

Example

I have a table (test123) like this

IdValue DataValue
———————
1 aaaaa
2 bbbbb
1 ccccc
2 ddddd

Desired output

IdValue Data_Concatenated
———————-
1 aaaaa,ccccc
2 bbbbb,ddddd

Solution 1: Using sys_connect_by_path

SELECT idvalue ,
SUBSTR(MAX(sys_connect_by_path(datavalue, ‘,’ )),2) data_concatenated
FROM
(SELECT idvalue ,
datavalue ,
row_number() over (partition BY idvalue order by datavalue) row#
FROM test123
)
START WITH row# = 1
CONNECT BY prior row# = row#-1
AND prior idvalue = idvalue
GROUP BY idvalue
ORDER BY idvalue;

Solution 2:

SELECT idvalue, RTRIM (EXTRACT (XMLAGG (XMLELEMENT (“X”, dataVALUE || ‘,’)), ‘/X/text()’),’,’) VALUE
FROM test123
group by IDvalue;

Stored procedure Vs SQL in code

Originally Posted December 24, 2007

The debate is old. But every now and then, we have to make a decision between using stored procedures and writing sql queries in code. When you are implementing sql queries for your database, you can either write them down in your code (again you can have different layers in your code and you can decide where to implement your sql queries, but that is different topic) or you can store them in database itself as stored procedures.

There are two schools of thoughts, one that will recommend going for stored procedure and the second will propose no stored procedures approach. I tried to understand the difference between the two approaches.

What is a stored procedure for a database? It is same as any procedure you create in your coding language, that takes some arguments, apply some logic, execute and returns back a result (if required).

As I already mentioned the debate is really old, so instead of reinventing the wheel, I will borrow the pointers from different sources and then try to add my thoughts to that

Stored procedures are secure: Stored procedure are secured against SQL injections. Are they totally safe? Isn’t it again the responsibility of person creating the procedure to make sure it is safe. Again, if I am writing queries in application layer (code), I can make sure the code is protected against the attacks.

Separation of data logic from business logic: All your queries which are interacting with database are residing over the database engine itself. This clearly separates all database interactions and hence make design more cleaner. One alternative will be to divide yourapplication layer into different parts, and keep data handling part completely separate from the rest.

A Disadvantage: Moving stored procedure from one platform to another can be tough, eg you decided to move from MSSQL to MYSQL, you may have recode all your stored porcedures. But this has a related advantage that if the front end language is changing, say you are moving from php to java, and your database is not changing then you don???t need to change the stored procedures.

Testing the app: Testing application with stored procedures can be a pain. Yes, as the changes are to be made in multiple places, your code as well as stored procedure. Now if database is handled by a different team, every time for a small change you have to communicate the changes and then test. This will take longer time than making changes just in your code as multiple teams are involved.

Stored procedure is faster: now that is debatable statement. I found this thing mentioned at many places that stored procedures as faster as the procedure is part of database engine itself and hence have faster access to database. Also you don’t need to send the complete query but just the procedure name. But as per my exp, I never faced any differences in terms of execution time with stored procedure.

Having considered all the points above, my personal opinion would be not to use the stored procedures especially in big application and if the same database is being used by multiple applications, because you don’t have complete control over the database and every time a change is made to a procedure it has to be communicated to multiple parties. In small applications and small teams one can use stored procedures as mostly same team has control of both database engine and code layer, so it is easier to coordinate and implement the changes.

http://codebetter.com/blogs/eric.wise/archive/2006/05/24/145393.aspx
http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html
http://codebetter.com/blogs/jeremy.miller/archive/2006/05/25/145450.aspx

Test your SQL

SQL Fiddle is the place to test your queries and database design. You can simply share the links with other team members to let them know what you are thinking. It provided a number of database platforms like Oracle, Mysql, MS Sql, Postgres etc.

How does database (oracle) joins handle filter condition

I was wondering how does database interpret join queries. When we create a join between 2 tables in Oracle, with some additional filter condition on one or both tables, will oracle join the tables first and then filter or will it filter the conditions first and then join.

For example I have 2 simple queries

Query 1:

select e.name, d.name from employee e, department d
where e.dept_id=d.id and e.salary>50000;

Query 2:

select e.name, d.name from
(select * from employee where salary>50000) e, department d
where e.dept_id=d.id;

Now which is a better option?

I posted my doubts on a couple of forums and did some googling, and this is what I found

“Both are same”

An easy way to check this out id to use “explain plan for” clause, which tells you how actually oracle interprets your query after optimization.

explain plan for
select e.name, d.name from employee e, department d
where e.dept_id=d.id and e.salary>50000;
select * from table(dbms_xplan.display());

output was same as

explain plan for
elect e.name, d.name from
(select * from employee where salary>50000) e, department d
where e.dept_id=d.id;

Abusing Foreign Keys

Recently I came across a database design which made me think that upto what extent one should be using Foreign keys. A colleague created a new table in an existing schema and used maximum number of foreign key constraints possible to make sure no junk data gets entered into this table. On one hand it is a good idea to use foreign key constraints to make sure your data is clean all the time, on the other hand it might introduce some unwanted conditions on your code.

Let me take an example. You have 2 tables employee and salary. Employee has a primary key employee_id which you use as foreign key in your salary table. Sounds smooth, if we have salary entry, we must have the employee, and if employee is moving out(record getting deleted), delete the salary entry. Lets say I have another table called print_history which stores who all has printed salary slips for an employee. Now this table has 2 employee Ids, one for the manager who printed the slip (say Manager_id) and second is the employee whose slip was printed (say emp_id). Now we know that logically these 2 Ids should be in employee table, but is it necessary to add the constraint. It will work fine while insertion of the records, but say there is a case when an employee record is getting deleted, should we enforce deletion of print_history record? should we stop employee record from being deleted if there is an print_history entry? (I know soft deletes might help, but still, why to add unnecessary  constraints?