Need Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Need Query

  1. #1
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159
    I am having a table called location_address where i have a column named country with type varchar2.

    I have millions of rows in that.The immediate need is we should take all the rows where the country value is between 'A' and 'Z'.We tried with some query but its taking too much time to return the records.

    Our query is

    select distinct country from location_address where
    substr(country,1,1) between 'A' and 'Z' and
    substr(country,2,1) between 'A' and 'Z';

    Can anyone reply me with a fastest script for this need.

    thanks

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    If it is a varchar2 column and you have valid data in this column, the query should return all the rows.

    Or, are you trying to filter out invalid data ?

    What are you trying to accomplish ?
    svk

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Is your country column indexed? IS your table and Indices analyzed? and finally, as the previous poster ponders, what are you trying to accomplish, by going for first two characters to be of alphabet in nature?

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159
    I need to pullout all the rows which are between A..Z.

    for example.

    i have data like below.

    a1
    22
    1a
    bb
    aa
    **
    $$

    i need to extract only bb,aa.This is my requirement.

    thanks

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    If you have good_records >> bad_records then you can use:

    select distinct country from location_address
    minus
    select distinct country from location_address where
    upper(substr(country,1,1)) < 'A'
    and
    upper(substr(country,1,1)) > 'Z'
    and
    upper(substr(country,2,1)) < 'A'
    and
    upper(substr(country,2,1)) > 'Z'

    in this case you should create and analize 3 indexes:

    1) create index aaa on location_address(country) ....:
    2) create index bbb on location_address(upper(substr(country,1,1))) ....:
    3) create index ccc on location_address(upper(substr(country,2,1))) ....:

    execution plan probably will be:

    1 - FULL fast scan on index aaa
    2,3 - RANGE scan on indexes bbb, ccc

    Try.

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