Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Information about CBO

Avatar of COMPSUPP
COMPSUPP asked on
Oracle Database
6 Comments1 Solution498 ViewsLast Modified:
I have a select query which currently has the RULE hint. It runs fast. I am trying to move to the CBO and
I have run dbms_stats on the tables in this query.
With the RULE hint, the optimiser uses indexes. Without the RULE hint ( and so presumably using the statistics) it is doing 2 full table scans.
Anyone any ideas? Or a stategy of where to go now?
I'll' try attaching the code and also the two explain plans.
select  /*+ RULE  */ 
l.nominal_region,l.nominal_county,
s.weekno,s.year,s.obs_dt,s.data_source
from sub s,location l
where s.loc_id=l.loc_id
and s.proj_id = 'GBW'
and s.weekno is not null
and l.nominal_region is not null
Plan
SELECT STATEMENT  HINT: RULE			
	5 NESTED LOOPS  		
		2 TABLE ACCESS BY INDEX ROWID BTOWATCH.SUB 	
			1 INDEX RANGE SCAN NON-UNIQUE BTOWATCH.INDX_SUB_PROJID 
		4 TABLE ACCESS BY INDEX ROWID BTOWATCH.LOCATION 	
			3 INDEX UNIQUE SCAN UNIQUE BTOWATCH.PK_LOCATION 
Without RULE hint:
Plan
SELECT STATEMENT  CHOOSECost: 12,885  Bytes: 196,571,034  Cardinality: 2,978,349  		
	3 HASH JOIN  Cost: 12,885  Bytes: 196,571,034  Cardinality: 2,978,349  	
		1 TABLE ACCESS FULL BTOWATCH.LOCATION Cost: 518  Bytes: 10,589,376  Cardinality: 378,192  
		2 TABLE ACCESS FULL BTOWATCH.SUB Cost: 9,886  Bytes: 112,586,514  Cardinality: 2,962,803
ASKER CERTIFIED SOLUTION
Avatar of Milleniumaire
MilleniumaireFlag of United Kingdom of Great Britain and Northern Ireland image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answers