1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Avoiding multiple subqueries

Discussion in 'SQL PL/SQL' started by beisikas, Jan 15, 2011.

  1. beisikas

    beisikas Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi all.
    I am a newbe in Oracle. I wrote an app for clients, using solutions based on Oracle 8i. The app must take some data from DB and display it. I noticed that the data taking takes much time, I guess the reason is the code writen by the last programmer.

    Suppose we have a table:
    ID date code
    ----------------
    1 20060101 1844
    1 20080101 1844
    1 20090202 1845
    1 20090303 1844
    1 20010101 1959
    1 20030303 1959
    2 20040101 1959
    2 20050202 1959
    2 20040202 1845
    3 20100202 1845

    So, I want to get into separate lines the data: for each ID - the max(date) from the each code, but the principle how to calculate the max date is different for codes. That is, the codes 1845 and 1844 should be taken as the "same", and code 1959 is taken alone. I know the GROUP BY statement, that could help fro grouping ID's, but it is not the one that could help when I need to make some "logical" grouping...
    It could be done by making several queries into that table for each code or code groups ("WHERE code in (1845,1844)...." and "WHERE code in (1959)...."), but maybe because of the table size (about 500000 records) it takes about 1 h or even more on old PCs.

    The result should be displayed in sequence "ID", "max date for codes 1845,1844", "max date for code 1959"

    smth like as follows:

    1 20090303 20030303
    2 20040202 20050202
    3 20100202 [null]

    ( [null] there means that there is no record for ID=3 with code=1959 , but the data for ID=3 should be displayed anyway, because ID=3 has at least one record with the date for the codes I am interested).

    How could I do this in the fastest way? I think it should avoid using multiple queries.
    Should I use PL/SQL?

    Thanks in advace.

    A.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This should work (written against an example table so you need to make changes for your table name/structure):

    Code (SQL):

    WITH a AS (
    SELECT id, to_char(MAX(prod_dt), 'RRRRMMDD') mdt
    FROM two_max
    WHERE code IN (1844, 1845)
    GROUP BY id),
    b AS (
    SELECT id, to_char(MAX(prod_dt), 'RRRRMMDD') m2dt
    FROM two_max
    WHERE code = 1959
    GROUP BY id
    )
    SELECT a.id, a.mdt, b.m2dt
    FROM a LEFT OUTER JOIN b ON (b.id = a.id);
     
    The output is as you desire:

    1 20090303 20030303
    2 20040202 20050202
    3 20100202 [null]
     
  3. beisikas

    beisikas Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for reply.

    But Oracle 8i doesn't support "WITH" clause... :(
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    SELECT a.id, a.mdt, b.m2dt
    FROM (
    SELECT id, to_char(max(prod_dt), 'RRRRMMDD') mdt
    FROM two_max
    WHERE code IN (1844, 1845)
    GROUP BY id) a, (
    SELECT id, to_char(max(prod_dt), 'RRRRMMDD') m2dt
    FROM two_max
    WHERE code = 1959
    GROUP BY id
    ) b where b.id (+) = a.id;

    That should work in 8i.
     
  5. beisikas

    beisikas Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi, thanks for code.
    It could be a solution.

    The main problem is, that there are two subqueries
    "SELECT id, to_char(max(prod_dt), 'RRRRMMDD') mdt
    FROM two_max..."
    and
    "SELECT id, to_char(max(prod_dt), 'RRRRMMDD') m2dt
    FROM two_max..."
    they take too much time :(, while the main table (a la two_max) has many records.

    The best solution, as far as I have found, is to use decode function both in selecting fields and in grouping function. Smth like "select id, decode (code, '1844', '1845', code) kodas from [table] group by id, decode (code, '1844', '1845', code)".
    Such query produces results such as
    1 20090303
    1 20030303
    2 20040202
    2 20050202
    ....
    the data is spooled into csv file, and it is up to me to concat the dates programmically :) in my App after reading the data from csv files. Such a solution is faster, rather then trusting only ORACLE Select queries :)
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That is a sad comment on your database configuration and schema structure as such queries should return in less time than it takes you to generate two spool files and somehow concatenate the results.

    What indexes are created against this table? Are statistics current? What does Statspack report on system activity and top wait events? There is likely much which can be done to speed up these queries; it's only a matter of knowing how the system is responding and the storage layout of the various data, index, control and log files. Possibly you could enlighten us on these matters.