Need Query

[msuresh]

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

[svk]

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 ?

[sambavan]

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

[msuresh]

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

[Shestakov]

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.