Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606

    Talking Unanswered: Cleaning up bad Oracle 8i SQL; made it worse?

    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, Ive been tasked to help clean some of these up already Ive turned some 5 hour reports into 5 minute reports by removing the procedural stuff.

    Ive come across a cross-tab type report that I have successfully converted but I dont know enough of 8is OLAP tricks (if there are any?) to help optimize the query.

    The application is a packaged app so I cant modify the tables/views (I dont 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 doesnt 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 were 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 dont 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:
    Code:
    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:

    Code:
    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 dont 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
    Thanks,

    Matt

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Cleaning up bad Oracle 8i SQL; made it worse?

    Only a small improvement (maybe):

    Code:
    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:

    Code:
    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;

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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!
    Thanks,

    Matt

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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.
    Thanks,

    Matt

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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:

    Code:
      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!

  6. #6
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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.
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •