Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2006
    Posts
    11

    Post Unanswered: Minimize Search Time

    Hi,
    My name is nouman working in patni computer. This problem is regarding the cursor which cointains a select query. This select query is selecting some records form the database tables (say 1000) records. Now out of these 1000 records if i want to select only first 50 records i will use the FETCH FIRST clause. But even though if i am using the select query with FETCH FIRST the search time will be equal as compare to query without FETCH FIRST clause, as each record will be searched and only first 50 records will be pushed into the active area for the cursor to retrive. Now the probelm is that i want to minimize the search time so that only first 50 records will be searched and the active area sould cointain only 50 records.
    Could you please help me in this field and provied me with some solutins.

    (i guess in oracle SQL we have some concept of psudo column for row_num, is there any subsitute psudo column in DB2)

    Thanks for your time

    Nouman Memon
    Patni Computers

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There probably is not of a difference in response time in retrieving 50 rows or the entire 1000 rows.

    But assuming your example was different and there is actuall 1,000,000 rows in the table and you only want the first 100. If you have an order by, DB2 will have look at all 1,000,000 rows to determine which are the first 50. If you do not have an order by (or something that causes a sort like group by, or a join) then DB2 will usually (if it can) only read the first 50 rows.

    You try your test with rows in the table, or post your DDL here for us to look at.

    There is a rownum function in DB2, but it has little meaning if there is no order by.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2006
    Posts
    11
    Hi Feldman,
    The query is given below

    SELECT A.xxxxxxxxxxxx, A.xxxxxxxxxxxx,
    A.xxxxxxxxxxxx,
    A.xxxxxxxx,
    A.xxxxxxxxxxxxx, A.xxxxxxxxxxxxxx,
    A.xxxxxxxxxxxxxxx
    FROM zzzz.yyyyy A, zzzz.yyyyy B
    WHERE (A.xxxxxxxxxxxx = B.xxxxxxxxxxxx AND
    A.xxxxxxxxxxxxx BETWEEN 'aaaaa' AND 'aaaaa '
    AND
    B.xxxxxxxxxxxxx BETWEEN 'aaaaa' AND 'aaaaa'
    AND
    A.xxxxxxxxxxxxxx BETWEEN 'bbbbb' AND 'bbbbb'
    AND
    B.xxxxxxxxxxxxxx BETWEEN 'bbbbb' AND 'bbbbb'
    AND
    A.xxxxxxxxxxxx IN ('A','F',' ') AND
    B.xxxxxxxxxxxx IN ('A','F',' '))
    UNION
    SELECT B.xxxxxxxxxxxx, B.xxxxxxxxxxxx,
    B.xxxxxxxxxxxx,
    B.xxxxxxxx,
    B.xxxxxxxxxxxxx, B.xxxxxxxxxxxxxx,
    B.xxxxxxxxxxxxxxx
    FROM zzzz.yyyyy A, zzzz.yyyyy B
    WHERE (A.xxxxxxxxxxxx = B.xxxxxxxxxxxx AND
    A.xxxxxxxxxxxxx BETWEEN 'aaaaa' AND 'aaaaa '
    AND
    B.xxxxxxxxxxxxx BETWEEN 'aaaaa' AND 'aaaaa'
    AND
    A.xxxxxxxxxxxxxx BETWEEN 'bbbbb' AND 'bbbbb'
    AND
    B.xxxxxxxxxxxxxx BETWEEN 'bbbbb' AND 'bbbbb'
    AND
    A.xxxxxxxxxxxx IN ('A','F',' ') AND
    B.xxxxxxxxxxxx IN ('A','F',' '))
    ORDER BY 3 DESC, 1
    FETCH FIRST 600 ROW ONLY WITH UR;

    Here can we modify this query such that only 600 records will be processed and returned.

    Thanks for your time

    Nouman Memon
    Patni Computer's

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Try putting the FETCH FIRST 600 ROWS ONLY at the end of each query (not at the end of the both queries). The ORDER BY (which applies to the end result after the UNION) would be last.

    If you use UNION, DB2 will have to go through and eliminate the duplicate rows in both queries, so if you have no duplicate rows from each part of the UNION, then use UNION ALL.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Feb 2006
    Posts
    11
    Hi Feldman,
    Is there any Pseudo column concept in DB2 as we have in oracle SQL for row_num
    select * from emp where row_num < 10;

    Thanks for your previous reply, i will try that on my mainframe terminal and update you ASAP.

    Thanks,
    Nouman Memon
    Patni Computer's

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There is a row_number function on DB2 for Linux, UNIX, and Windows. Not sure about DB2 for z/OS (or what release is required if it is available).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Feb 2006
    Posts
    11
    Hi Feldman,
    I have tried providing FETCH FIRST clause for each select query seperated by a UNION but it is giving me an Error saying that illegal use of keywork UNION. The test query which i had used is given below

    (SELECT XXXXXX FORM XXXXX
    FETCH FIRST 10 ROWS ONLY)
    UNION
    (SELECT XXXXXX FORM XXXXX
    FETCH FIRST 10 ROWS ONLY)
    WITH UR;

    Now here if we omit the FETCH FIRST clause the query runs fine. Is there any other way to include FETCH FIRST clause in a select query seperated by union.

    Thanks for your time.

    Nouman Memon
    Patni Computer's

Posting Permissions

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