Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Location
    Cochin, INDIA
    Posts
    9

    Unanswered: Query Optimisation

    Hi gurus,

    Select orchid_id, Chrom_start
    From ALL_SNP
    Where chrom = '22' and (chrom_start between 22000000 and 22001000) and rownum= 1
    Union
    Select orchid_id, Chrom_start
    From ALL_SNP
    Where chrom = '22' and (chrom_start between 22001000 and 22002000)and rownum= 1
    Union
    Select orchid_id, Chrom_start
    From ALL_SNP
    Where chrom = '22' and (chrom_start between 22002000 and 22003000)and rownum= 1
    Union
    Select orchid_id, Chrom_start
    From ALL_SNP
    Where chrom = '22' and (chrom_start between 22003000 and 22004000)and rownum= 1
    Union
    Select orchid_id, Chrom_start
    From ALL_SNP
    Where chrom = '22' and (chrom_start between 22004000 and 22005000)and rownum= 1
    Union .....

    I have a query similar to the one shown above. I expect help from any of you to optimise the same to have a faster execution.
    Regards,
    Saju Joseph

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    why rownum=1 as this will pick a record at random and wont get the first record necessarily.

    If you do want the first record in each chrom start group (ordered by chrom start) then this might work though I havent tried it out.

    Select
    min(orchid_id) KEEP (DENSE_RANK FIRST ORDER BY chrom_stamp asc) as min_orchid_id,
    min(Chrom_start) min_chrom_start
    From ALL_SNP
    Where chrom = '22'
    group by trunc(chrom_start/1000)

    Alan

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    I expect at least an explain plan or statistics or a tkprof output or something.

    Plus, since we do not know what you are trying to accomplish then
    you make it even more difficult. How about explaining what you
    are trying to do??
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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