[Oracle] "The table has data, but the result of select COUNT (*) is 0" solution to the problem

Source: Internet
Author: User

First, the question

Today there is a magical problem-there is data in the table, but the result of select COUNT (*) is 0.

The initial manifestation of the problem is that the query report has no paging.
At first, I thought it was a Java-side problem. It was later discovered that the SQL statement that looked up the paging was returned with 0.
The SQL statement is then put into the plsql to run, and the discovery also returns 0.

The database version is Oracle 11.1.0.6.

I tried several search engines, but I couldn't find a similar situation on the Internet.

Second, there is no way to the rope

First check if the table has data--

select * from mytable

You can see that the table (mytable) does have data.

Then query the number of bars and find that the statement returns 0.

select count(*) from mytable

Change the Count method, or return 0.

select count(1) from mytableselect count(id) from mytable
Third, Vista

The number of bars can be found when the condition is added.

select count(*) from mytable where id<10

Do you want to write where you can?
What do I do if I want to check the full list of records?

So try to do this kind of writing to judge the truth, but found still return 0--

select count(*) from mytable where 1=1

It seems to be the database automatically optimized query statements.

Think again, simply instead of the primary key non-empty judgment, this is also true.

select count(*) from mytable where not id is null

This will normally find the number of records.

[Oracle] "The table has data, but the result of select COUNT (*) is 0" solution to the problem

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.