OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » index problem (oracle 9i, linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

index problem [message #420300]

Fri, 28 August 2009 04:21

bhaskarao
Messages: 7
Registered: September 2005
Location: Hyderabad

Junior Member

Hi,

When I Give a query on table on indexed column database answers the that 'no rows selected'

ex :

table : Emp
indexed column : empno

query : select count(*) from emp where empno=1234

result : no rows selected

If I change query

query : select count(*) from emp where nvl(empno,1)=1234

result : 1

This is because there is some problem in index, if I bypass the index the result is given.

When I checked the user_indexes the index status is valid.

I checked the log file and found no errors reported.

My problem is database is reporting wrongly 'no rows selected'
even tough the data is present. And it is not reporting the error. I want database should report the index problem instead of 'no rows selected'. How to overcome the above problem.

Report message to a moderator

Re: index problem [message #420321 is a reply to message #420300]

Fri, 28 August 2009 05:33

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

I'm not convinced that you are seeing the results that you say you are. COUNT(*) returns a row when you use it like that:

SQL> create table test_052 (col_1 number, col_2 number);

Table created.

SQL> insert into test_052 values (1,1);

1 row created.

SQL> select count(*) from test_052 where col_1 = 2;

  COUNT(*)
----------
         0

Now, if your query had been like this, then you can get thoe results:

SQL> select col_1,count(*) from test_052 where col_1 = 2 group by col_1;

no rows selected

So, how about you show us the actual queries that you're running and the actual results that are causing you problems?

Report message to a moderator

Re: index problem [message #420341 is a reply to message #420321]

Fri, 28 August 2009 07:53

bhaskarao
Messages: 7
Registered: September 2005
Location: Hyderabad

Junior Member

Hi

here in emp table data is very much present

sql > Select * from emp

all rows displyed

when you issue query with where clause

query : select count(*) from emp where empno=1234

result : no rows selected

If I change query

query : select count(*) from emp where nvl(empno,1)=1234

result : 1

table : Emp
indexed column : empno

the problem is with index. in second query using funcion I have bypassed index and The result is displyed.

But the database does not report any index problem, not datablock corruption etc errors.

one can say rebuilding indexes. but index status is valid and no physical block error is reported.

By looking the data case to case sloving the problem is not solution.

How to identify the exact problem. is there any parameter to be set in database? or any other solution ?

Report message to a moderator

Re: index problem [message #420344 is a reply to message #420341]

Fri, 28 August 2009 08:12

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

bhaskarao wrote on Fri, 28 August 2009 08:53

Hey, thanks for giving the same bullshit faked output again. Cut and paste (look it up if you do not know what it means) your query and output. Can you not see the difference between your output and JRow's?

This is an example of real output

SQL> select count(*) from XXX where a1='x';

  COUNT(*)
----------
         0

Report message to a moderator

Re: index problem [message #420345 is a reply to message #420341]

Fri, 28 August 2009 08:14

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

when you issue query with where clause

query : select count(*) from emp where empno=1234

result : no rows selected

This is WRONG, a select count(*) ALWAYS returns ONE and only one row.

SQL> select count(*) from dual where 1=0;

  COUNT(*)
----------
         0

1 row selected.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel

[Updated on: Fri, 28 August 2009 08:15]

Report message to a moderator

Previous Topic:	tablespaces
Next Topic:	one Tablespace having two users

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 17:20:19 CST 2024