[MattR]

Hi – a bit of a nagging query here. A little background – I recently became employed at a company that uses “SQR” to do a lot of reporting on our Oracle 8i DBMS. Unfortunately, a lot of these reports were written by people with little/no SQL knowledge and so they are ugly (to say the least): they are all procedural with a SELECT * FROM user followed by different subroutines to execute queries to gather address and other data (obviously a JOIN would do!), a subroutine to determine if a user should be selected (instead of a where clause on the SQL statement!).

In any rate, I’ve been tasked to help clean some of these up – already I’ve turned some 5 hour reports into 5 minute reports by removing the procedural stuff.

I’ve come across a ‘cross-tab’ type report that I have successfully converted but I don’t know enough of 8i’s OLAP tricks (if there are any?) to help optimize the query.

The application is a packaged app so I can’t modify the tables/views (I don’t think we can add indexes or views either), nor for security reasons can I give out the DDL or exact SQL, but I can neuter the tables down to the important part, and really building the SQL doesn’t need the entire structure anyway.

In a nutshell the tables are:

User stores user information.
User{ id, name, … }

Type stores user type information. Should a user have more than one type code the priority can be used to determine which type is most significant. For example, if a user were both Admin and Guest, Admin would have a priority of 1 whereas Guest may be 10, so Admin will be chosen as their type.
Type{ code, name, priority }

Ties a user to a type. Users can be more than one type (as described above).
UserType{ id, code }

Address{ id, user_id }
Phone{ id, user_id }
Email{ id, user_id }
…

You get the point. All of the child tables relate back to the parent table. What we’re generating is something like this:

User Type Address Phone Email
------------------------------------
Admin 123,222 80,000 90,000
SomeType 22,222 12,000 12,022
Etc.

So, for each user type, how many users of that type have an address record, how many have a phone record, etc. These are not correlated (e.g. I don’t care if they have an address AND a phone, etc.). Also (here is where the priority comes into play) for the purposes of this report only count the user as being of the type considered if that type is their most significant type:

SELECT *
  FROM usertype
 WHERE user_id = 123
   AND priority = ( SELECT MIN( priority )
                      FROM usertype
                     WHERE user_id = 123 )

So the query I have formulated looks something like this:

SELECT name,
     ( SELECT COUNT( DISTINCT user_id )
         FROM address
        WHERE user_id IN ( SELECT user_id
                             FROM user     u1,
                                  usertype ut2
                            WHERE ut2.code     = ut1.code 
                              AND ut2.priority = ( SELECT MIN( priority )
                                                     FROM usertype
                                                    WHERE user_id = u1.user_id )
                         )
     ) AS Address,
     ... AS Phone, -- etc.
  FROM type

As you can see, it is nasty. The SQL to get users who are of a particular type is ugly and I don’t like running it for each additional cross-tabbed column I add (Address, Phone, Email, etc.)

So I am wondering if there is a better way?

I think I could use a temp table to insert the user_id, code combo into so I can just join to that. The table could get quite large though and I am not sure if I can create an index on a temp table (anyone?).

Anyone? Thanks!

EDIT: Fixed tablename

[andrewst]

Only a small improvement (maybe):

SELECT t.name,
     ( SELECT COUNT( DISTINCT a.user_id )
         FROM address a,
              usertype ut2
        WHERE a.user_id = ut2.user_id
          AND ut2.code  = t.code
          AND ut2.priority = ( SELECT MIN( priority )
                                 FROM usertype
                                WHERE user_id = ut2.user_id
                             )
     ) AS Address,
     ... AS Phone, -- etc.
  FROM type t

i.e.
1) no need to join to table USER
2) join to usertype (ut2) rather than subquery
3) main query is on table TYPE not USERTYPE

A more radical rewrite which may work is:

SELECT t.name,
       SUM( CASE WHEN a.user_id IS NULL THEN 0 ELSE 1 END ) AS Address,
       SUM( CASE WHEN p.user_id IS NULL THEN 0 ELSE 1 END ) AS Phone,
       ...
  FROM type t,
       user_type ut,
       address a,
       phone p,
       ...
 WHERE ut.code = t.code
   AND ut.priority = ( SELECT MIN( ut2.priority )
                         FROM usertype ut2
                        WHERE ut2.user_id = ut.user_id
                     )
   AND a.userid (+)= ut.userid                     
   AND p.userid (+)= ut.userid                     
GROUP BY t.name;

[MattR]

Thanks for the reply Tony!

3) main query on table TYPE not USERTYPE
That was a bug!

Have to run will check the query in a bit!

[MattR]

I don't know if the last query will work.. Users can have more than one email address, so I don't want them to count as more than one user... Am I making sense? I just want to check for the existance of at least one email address, and then add that to the 'has email' total.

[andrewst]

Originally posted by MattR
I don't know if the last query will work.. Users can have more than one email address, so I don't want them to count as more than one user... Am I making sense? I just want to check for the existance of at least one email address, and then add that to the 'has email' total.

You are right: as written it relies on there only being one Address, one Phone, etc. per user. Not very useful!

Perhaps if you change the FROM clause to:

  FROM type t,
       user_type ut,
       (select distinct userid from address) a,
       (select distinct userid from phone) p,

I have no idea how fast that will run!

[MattR]

You last code snippit is basically what I have that I would like to avoid. I'll make that JOIN change. I may just create another table with the joined info to avoid making it on every query.