Problem solve Get help with specific problems with your technologies, process and projects.

Oracle performs full table scan in one database

I have a SQL statement that is behaving differently in two "equal" (in schema) databases. Why the difference in optimizers?

I have a SQL statement that is behaving differently in two "equal" databases (equal in schema, but size differs by about 50%). Here's the statement:
select distinct 99, 31, b.school_id, a.provider_id, 
a.student_id, '2007', lpad('01',2,'0'), '0'
from studprovider a, student b, school c where 
a.student_id = b.student_id and
      b.school_id  = c.school_id and
      a.provider_id = 527 and
      b.t_districtid     = 31 and
      b.status = '1';
When I run Explain Plan in database A, there is a full table scan on student. But when I run the explain on database B I get no full table scan, which is what I want. Why the difference in optimizers? Do the number of data rows in the student table make the difference?
Yes, using the cost based optimizer; the number of rows will definitely make a difference on the access path Oracle chooses. The larger the table, the more likely Oracle will choose to use an index rather than a table scan to access the table. This is because the optimizer calculates the cost of retrieving the data based on the table and it indexes statistics. The more rows the more expensive it is to do a table scan.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.