Home » RDBMS Server » Server Administration » index problem (oracle 9i, linux)
index problem [message #420300] Fri, 28 August 2009 04:21 Go to next message
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.






Re: index problem [message #420321 is a reply to message #420300] Fri, 28 August 2009 05:33 Go to previous messageGo to next message
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?
Re: index problem [message #420341 is a reply to message #420321] Fri, 28 August 2009 07:53 Go to previous messageGo to next message
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 ?


Re: index problem [message #420344 is a reply to message #420341] Fri, 28 August 2009 08:12 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
bhaskarao wrote on Fri, 28 August 2009 08:53
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



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
Re: index problem [message #420345 is a reply to message #420341] Fri, 28 August 2009 08:14 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
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:
  


Current Time: Tue Dec 06 14:09:15 CST 2016

Total time taken to generate the page: 0.11393 seconds