SQL behaves differently in two identical Oracle 10g db’s

5 pts.
Tags:
Oracle
Oracle 10g
SQL
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 a database A, there is a full table scan on student: Plan SELECT STATEMENT ALL_ROWSCost: 297 Bytes: 41,383 Cardinality: 1,427 6 SORT UNIQUE Cost: 297 Bytes: 41,383 Cardinality: 1,427 5 NESTED LOOPS Cost: 296 Bytes: 41,383 Cardinality: 1,427 3 NESTED LOOPS Cost: 295 Bytes: 35,675 Cardinality: 1,427 1 TABLE ACCESS FULL TABLE SEBS.DDATASTUDENT Cost: 289 Bytes: 121,620 Cardinality: 8,108 2 INDEX UNIQUE SCAN INDEX (UNIQUE) SEBS.DDATASTUDPROVIDER_PK Cost: 0 Bytes: 10 Cardinality: 1 4 INDEX UNIQUE SCAN INDEX (UNIQUE) SEBS.SCHOOL_IC Cost: 0 Bytes: 4 Cardinality: 1 But when i run the explain on database B I get no full table scan: Plan SELECT STATEMENT ALL_ROWSCost: 89 Bytes: 2,408 Cardinality: 86 7 SORT UNIQUE Cost: 89 Bytes: 2,408 Cardinality: 86 6 NESTED LOOPS Cost: 88 Bytes: 2,408 Cardinality: 86 4 NESTED LOOPS Cost: 88 Bytes: 2,064 Cardinality: 86 1 INDEX RANGE SCAN INDEX (UNIQUE) SEBS.DDATASTUDPROVIDER_PK Cost: 2 Bytes: 860 Cardinality: 86 3 TABLE ACCESS BY INDEX ROWID TABLE SEBS.DDATASTUDENT Cost: 1 Bytes: 14 Cardinality: 1 2 INDEX UNIQUE SCAN INDEX (UNIQUE) SEBS.DDATASTUDENT_PK Cost: 0 Cardinality: 1 5 INDEX UNIQUE SCAN INDEX (UNIQUE) SEBS.SCHOOL_IC Cost: 0 Bytes: 4 Cardinality: 1 which is what i want. Why the difference? Do the number of data rows in student table make the difference? thanks in advance.
ASKED: May 15, 2007  1:33 PM
UPDATED: May 15, 2007  3:17 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Yes, the size of certain files DOES drive the optimizer’s plan. You can weigh in on the plan as follows (code shown is fully optimized):

SELECT /*+ INDEX(a) INDEX(b) INDEX(c) */
______99,
______31,
______b.school_id,
______a.provider_id,
______a.student_id,
______’2007′,
______’01′,
______’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′
___GROUP BY b.school_id, a.provider_id, a.student_id;

You can specify a specific index using INDEX(alias indexName).

Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following