I have a table that I need to query

[navink123]

I have a table that I need to query based on a column. The column contains two types of values. First type has the first character as alphabet and then numerals. The second type has all numerals. I need to query the table based on this first character.
I tried to use substr(column,1,2)not in('0',’1',’2’,’3’,’4’,’5’,’6’,’7’,’8’,’9’). But it is taking a long time as the table contains more than 100 MM rows.
Is there any other efficient way to do this.
Thanks,
N

 

[aryeh1010]

You should consider a function-based index on substr(column,1,2), because this is forcing your query to use full table scans.

Another alternative is to add a flag field that indicates whether the first character is a number or letter. You could then create a bitmap index on the flag field.

 

[jad]

doubt it's quicker but you could try:

ascii(column) not between 48 and 57

 

[carp]

I would go with aryeh1010's suggestion: function-based index would be the way to go here.

 

[sem]

I think that your original condition is almost always evaluated as TRUE, because substr (column,1,2) returns 2 characters, that can not be equal to a digit. Exceptions are when column contains less than 2 characters.

If I understand your task, you may query for rows starting NOT with digits as

where column <'0' or column >=':'

This may utilize an index on column if any.

Regards, Dima

http://seminihin.narod.ru

 

[sbix]

I did the following:
create table PIFO (PIFETTO CHAR(9));
insert into PIFO values ('0AAAABBBB');
insert into PIFO values ('2BBBBCCCC');
insert into PIFO VALUES ('3CCCCDDDD');
insert into PIFO VALUES ('K12345678');
commit;

select * from PIFO where PIFETTO > '9*';

PIFETTO
---------
K12345678

select * from pifo where PIFETTO > '2*' and PIFETTO < '3';

PIFETTO
---------
2BBBBCCCC

If you wish to select only the non numerical values you can execute the 1st select
If you wish only the values beginning by '2' you can issue the 2nd select

The advantage is in this case Oracle executes (if an index is present) a range scan on an index, instead a full table scan.
Furthermore an Oracle statement is very slow when there is a function in the WHERE clause

 

[sem]

Sbix, I think your solution is not &quot;pure&quot; enough:
SQL> insert into PIFO values ('9+');

1 row created.

SQL> select * from PIFO where PIFETTO > '9*';

PIFETTO
---------
9+

Regards, Dima

http://seminihin.narod.ru

 

[sbix]

Well sem, it's right ... cause 9+ is bigger than &quot;9 &quot; or &quot;9&quot;