DBAsupport.com Forums - Powered by vBulletin
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 33

Thread: Collection error!!

  1. #21
    Join Date
    Jun 2004
    Posts
    125
    So, which would be faster performance wise. I mean, bulk collect or what I have. Thanks.

  2. #22
    Join Date
    May 2005
    Posts
    31
    So, which would be faster performance wise. I mean, bulk collect or what I have
    that you have to benchmark in your system and test and use the one which is performant.

    I, however based on your report query that you have posted, fail to see why do you really need the table type object and function to return such SQLTYPE.

    Your report query would simply look like this



    SELECT trim(substr(sp.name, 1, decode(instr(sp.name, ')'), 0, length(sp.name), instr(sp.name, ')')-4))), sp.acct_num, count(s.reg_num), count(c.claim_num), count(c.claim_num)/count(s.reg_num) as Ratio,
    SUM(s.act_line_haul_amt)
    FROM shipment_people sp, shipment s, claims c
    WHERE sp.s_reg_num = c.reg_num(+)
    AND sp.acct_num IN


    (select driver_account_number
    from driver
    where contract_date < sysdate
    and contract_expires > sysdate

    union all

    select account_num
    from f01names2
    where (account_num between 1 and 1900 OR account_num between 4000 and 6700)

    )



    AND sp.s_reg_num = s.reg_num
    AND sp.acct_num between :P_From_Acctnum and :P_To_Acctnum
    AND s.act_load_date between :P_From_Date and :P_To_Date
    GROUP BY trim(substr(sp.name, 1, decode(instr(sp.name, ')'), 0, length(sp.name), instr(sp.name, ')')-4))), sp.acct_num
    ORDER BY sp.acct_num

    i.e no need to write function return table type etc.....
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  3. #23
    Join Date
    Jun 2004
    Posts
    125
    After all this, u have knocked me out man. You are right! When would you use something like this. My other reason for doing all this is to know this, so that when I move somewhere else, I, at least, know these basic things. I would appreciate if you would recommend a book or something. But I know these things u learn from experience and working with a good bunch of people. I'll use union because that should be the fastest. Thanks a lot.

  4. #24
    Join Date
    May 2005
    Posts
    31
    I'll use union because that should be the fastest
    No do not do that. THe Union will infact run slower because Union will remove duplicate records and just give distinct rowsets from the two queries. If you really want to have distinct records then use UNION but for perfomance wise no.... It will be slower compared to Union all....

    The books you can read is Expert one on one by Tom Kyte or Practical Oracle 8i by Jonathan Lewis. Both these books are excellent books to Know how Oracle Works....
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  5. #25
    Join Date
    Jun 2004
    Posts
    125
    Hi Bonker,

    If it's not much of a trouble to you, would u give me your yahoo ID or something? May be, once in a while, when I'm stuck with something or want to learn something I may ask for you. By the way, I don't chat a lot but I can, at least, keep in touch with you, u know. Thanks.

    My yahoo id: lynx_2050

    If u add me, I'll know. thanks

  6. #26
    Join Date
    May 2005
    Posts
    31
    I also do not Chat often. My mail id is raj_iyen@myway.com
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  7. #27
    Join Date
    Jun 2004
    Posts
    125
    Okay. I ran the union on SQL PLUS. It only runs for the second query and not for the first. I have not change the report yet. I am just trying.

    EDITED:
    I don'nt know, why union does not work? Both columns are the same data type and the same size too.
    END.
    Last edited by see_one; 06-16-2005 at 09:54 AM.

  8. #28
    Join Date
    May 2005
    Posts
    31
    Okay. I ran the union on SQL PLUS. It only runs for the second query and not for the first. I have not change the report yet. I am just trying
    so if use Union all what happens?
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  9. #29
    Join Date
    Jun 2004
    Posts
    125
    Sorry, man. I am using a login sript for my sql plus and, I think, my UNION ALL was hanging in the middle with some space in between of my two selects. So, it was not running my first query. Now it's fine. As you said, UNION all will be slower right?

  10. #30
    Join Date
    May 2005
    Posts
    31
    Nope I said UNION will be slower compared to UNION ALL
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

Posting Permissions

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


Click Here to Expand Forum to Full Width