A Silent Story of DQLs

This post is nothing but few observations that I made in my Documentum repository. Some of you may find it obvious but some others may find it interesting. The observations are specific to a particular repository. These results had messed up my mind and I expect the same for at least a few of you. Please don’t make any conclusion out of this post. Any comments from the readers are welcome.

*****************************************************************************************************************

SELECT COUNT(*) FROM dm_user
Result  >>  435820

SELECT COUNT(*) FROM dm_group
Result  >>  267375

SELECT COUNT(*) FROM dm_user WHERE r_is_group = true
Result  >>  263559

SELECT COUNT(*) FROM dm_user WHERE r_is_group = false
Result  >>  172261

SELECT COUNT(*) FROM dm_group
WHERE group_name
IN (SELECT user_name FROM dm_user WHERE r_is_group = true)
Result  >>  263559

SELECT COUNT(*) FROM dm_group
WHERE group_name
NOT IN (SELECT user_name FROM dm_user WHERE r_is_group = true)
Result  >>  3816

SELECT COUNT(*) FROM dm_group
WHERE r_object_id
IN (SELECT r_object_id FROM dm_user WHERE r_is_group = true)
Result  >>  0

SELECT COUNT(*) FROM dm_group, dm_user
WHERE dm_group.group_name = dm_user.user_name
Result  >>  465587

SELECT dm_user.user_name,dm_group.group_name
FROM dm_group, dm_user
WHERE dm_group.group_name = dm_user.user_name
Total Results  >>  465587

SELECT DISTINCT dm_user.user_name, dm_group.group_name
FROM dm_group, dm_user
WHERE dm_group.group_name = dm_user.user_name
Total Results  >>  198518

SELECT COUNT(*), dm_group.group_name
FROM dm_group, dm_user
WHERE dm_group.group_name = dm_user.user_name
GROUP BY dm_group.group_name
HAVING count(*) > 1
Total Results  >>  38608

SELECT group_name, COUNT(*) FROM dm_group
GROUP BY group_name HAVING COUNT(*) > 1
Total Results  >>  38608

SELECT user_name, COUNT(*) FROM dm_user
GROUP BY user_name HAVING COUNT(*) > 1
Total Results  >>  39468

*********************************************************************************************************

SELECT COUNT(DISTINCT user_name) FROM dm_user
Result  >>  366103

SELECT COUNT(DISTINCT group_name) FROM dm_group
Result  >>  198518

*********************************************************************************************************

SELECT COUNT(DISTINCT user_name) FROM dm_user WHERE r_is_group = true
Result  >>  195107

SELECT COUNT(DISTINCT user_name) FROM dm_user WHERE r_is_group = false
Result  >>  170996

SELECT COUNT(DISTINCT group_name)
FROM dm_group
WHERE group_name
IN (SELECT user_name FROM dm_user WHERE r_is_group = true)
Result  >>  195107

SELECT COUNT(DISTINCT group_name)
FROM dm_group
WHERE group_name
NOT IN (SELECT user_name FROM dm_user WHERE r_is_group = true)
Result  >>  3411

SELECT DISTINCT dm_user.user_name, dm_group.group_name
FROM dm_group, dm_user
WHERE dm_group.group_name = dm_user.user_name
Total Results  >>  198518

SELECT DISTINCT dm_user.user_name, dm_group.group_name
FROM dm_group, dm_user
WHERE dm_user.r_is_group = true and dm_group.group_name = dm_user.user_name
Total Results  >>  195107

SELECT DISTINCT dm_user.user_name, dm_group.group_name
FROM dm_group, dm_user
WHERE dm_user.r_is_group = false and dm_group.group_name = dm_user.user_name
Total Results  >>  3411

SELECT COUNT(DISTINCT group_name)
FROM dm_group
WHERE group_name
IN (SELECT user_name FROM dm_user WHERE r_is_group = false)
Result  >>  3411

SELECT COUNT(DISTINCT group_name)
FROM dm_group
WHERE group_name
IN (SELECT user_name FROM dm_user)
Result  >>  198518

***************************************************************************************************************

***************************************************************************************************************

It’s just an observation and I don’t have much to comment.

Advertisement