CBO not using the index ... Here is what I did

fwiw,

The CBO is not choosing the fastest path for whatever reason on this query. When you run the various explain plans with timing on using the hints versus without the hint, it is clear which way is the faster
way, but its not going that way using
the CBO with a straight query.

I turned the statistics back on and
the apps programmer has used the index
hint to force its use. Personally,
I dont like this, but it works for now
as we have a policy to avoid hints since Oracle 8i if at all possible.

As for the question as to why the apps
programmer wrote the query the way they
did ? I do not have an answer as to why apps programmers think the way they do.
I recommended they put some boundries on that query through a where clause and they went round and round about why they needed to return all the data.

Thanks for your time and Jonathan your
book is most excellent.

Have a good weekend.

Mike

-----Original Message-----
Sent: Friday, January 24, 2003 2:15 PM
To: Multiple recipients of list ORACLE-L hinted

Yes ... I can force the hint, but why wont it take the RBO path ? Its a difference of 7 seconds versus a split second. So why would the CBO take that path ?

-----Original Message-----
Sent: Friday, January 24, 2003 1:14 PM
To: Multiple recipients of list ORACLE-L hinted

Well, if it can do it under rule-based, then it shouldn't be able to ignore the hint
under CBO - I don't suppose there's
any chance that you have a typo in
the hint ?

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March
____USA_(FL)_May

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 24 January 2003 21:06
hinted

>All the columns in the table are not null ...
>
>here is an interesting test I ran ...
>
>several Direct_Path_Write and Read waits with large elasped times
>are showing up in the 10046 trace data on this query due to the
>full table scan.
>
>Just deleted statistics on this and fwiw ...
>
>With Statistics ....
>=====================
>
>SELECT Statement Optimizer=Choose (Cost=225 ...)
> SORT (Order By) (Cost=225 ...)
> Table Access (Full) of 'table1'
>
>Physical Reads = 433
>
>Without Statistics ..
>======================
>
>SELECT Statement Optimizer=Choose
> Table Access (By Index Row) of 'table1
> Index ( Full Scan ) of 'concatenated index' (Non-Unique)
>
>Physical Reads = 0
>
>Mike

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Johnson, Michael 
  INET: Michael.Johnson_at_oln-afmc.af.mil

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Johnson, Michael 
  INET: Michael.Johnson_at_oln-afmc.af.mil

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).