Home » SQL & PL/SQL » SQL & PL/SQL » why cbo select FULL TABLE SCAN
why cbo select FULL TABLE SCAN [message #660965] Thu, 02 March 2017 14:16 Go to next message
meganmegan
Messages: 7
Registered: August 2013
Location: IRAQ
Junior Member
hi
i have a query with a column in where clause that using the index perfectly but when i add a non-indexed column to where clause(within pervious indexed column),cbo donot use the index and prefers to use FULL TABLE SCAN,WHY???
Re: why cbo select FULL TABLE SCAN [message #660969 is a reply to message #660965] Thu, 02 March 2017 14:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Quote:
cbo donot use the index and prefers to use FULL TABLE SCAN,WHY???

Because it thinks it is better.

Re: why cbo select FULL TABLE SCAN [message #660971 is a reply to message #660969] Thu, 02 March 2017 14:55 Go to previous messageGo to next message
meganmegan
Messages: 7
Registered: August 2013
Location: IRAQ
Junior Member
but when i hint for using the index ,speed of execution my query increase very much! and i have good performance,why?
Re: why cbo select FULL TABLE SCAN [message #660973 is a reply to message #660971] Thu, 02 March 2017 15:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

To explain it we need information, the information that are listed in the link I gave you, do you think we gave you links just to bother you?

Re: why cbo select FULL TABLE SCAN [message #660974 is a reply to message #660973] Thu, 02 March 2017 15:05 Go to previous messageGo to next message
meganmegan
Messages: 7
Registered: August 2013
Location: IRAQ
Junior Member
what should i do exactly and thanks for your mentioned?
Re: why cbo select FULL TABLE SCAN [message #660975 is a reply to message #660974] Thu, 02 March 2017 15:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
We don't have your table.
We don't have your data.
We don't have your query.
We don't have your results.

specific answer depends upon all of the above.
since we can't see what you see, we can provide better response.

You have a mystery & we have no clues.

How can Oracle return non-indexed column, except by doing full table scan?
Re: why cbo select FULL TABLE SCAN [message #660976 is a reply to message #660975] Thu, 02 March 2017 15:19 Go to previous messageGo to next message
meganmegan
Messages: 7
Registered: August 2013
Location: IRAQ
Junior Member
thanks for your reply
is it enough getting statistics of my table and its index?
if yes,i provide them for you soon .of course query will be involved
again thanks
Re: why cbo select FULL TABLE SCAN [message #660979 is a reply to message #660976] Thu, 02 March 2017 15:30 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
When was the last time statistics were gathered on the table?
Re: why cbo select FULL TABLE SCAN [message #660991 is a reply to message #660965] Fri, 03 March 2017 01:37 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You need to show the query, the exec plans, they actual and estimated cardinalities. If you do not know how to do this, the links you have been given explain how.
Re: why cbo select FULL TABLE SCAN [message #661015 is a reply to message #660991] Sun, 05 March 2017 04:21 Go to previous messageGo to next message
meganmegan
Messages: 7
Registered: August 2013
Location: IRAQ
Junior Member
hi everyone
sorry for my delay
os version:windows 2000 server
oracle:9i (9.2.0.5)
I create index named:PK_HIS_ANALOGS on the column named:TIME_STAMP
This volumn has type:DATE
Whan i run below query ,it is executed very soon and rapidly
:
select * from HISU.HIS_MESSAGES where TIME_STAMP between TO_DATE('2017-01-19','YYYY-MM-DD') and TO_DATE('2017-01-25','YYYY-MM-DD')

explain plan use my index but when i add another column from the same table(my mean is:PATH1) explain plan prefers to select FULL TABLE SCAN instead of INDEX(RANGE SCAN)!!!
When i force to select the index by using hint in the query ,it is executed very soon and performance of my vision is very good.
my question is:
why after adding PATH1column to where clause of my query ,CBO ignores using index while using


using the index improve the performance vividly?
you should consider CLUSTERING_FACTOR of my index is good and i provide histogram information for both of columns:TIME_STAMP that is indexed and PATH1 without having index.
thank you everybody
i 'm waiting for your reply.
some information maybe you need attached


please convert extention of txt to rar for using attachment
Re: why cbo select FULL TABLE SCAN [message #661017 is a reply to message #661015] Sun, 05 March 2017 04:29 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Note that very few people will be prepared to download and open your attachment. I actually did so. You have provided virtually none of the information requested. Are you serious about asking for help, or are you merely trying to irritate people?
Re: why cbo select FULL TABLE SCAN [message #661018 is a reply to message #660965] Sun, 05 March 2017 04:36 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
It has come to my attention that while your registered profile states that you are in Iraq, your messages appear to be from Iran. Use of Oracle products in Iran is illegal. It is not the purpose of this board to enforce US law and some members may be prepared to assist you further, but I cannot do so. Goodbye.
Re: why cbo select FULL TABLE SCAN [message #661019 is a reply to message #661018] Sun, 05 March 2017 04:41 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Is your 9i Oracle running as rule based or cost based? Also have you gathered recent statistics if your running cost ? Try putting the test for time_stamp as the last clause in your query
Re: why cbo select FULL TABLE SCAN [message #661021 is a reply to message #661019] Sun, 05 March 2017 04:50 Go to previous messageGo to next message
meganmegan
Messages: 7
Registered: August 2013
Location: IRAQ
Junior Member

every night i am gathering all statistics in 2:00 AM and there is not stale or empty statistics

statistics are Ok
optimizer mode:CBO(CHOOSE)
Re: why cbo select FULL TABLE SCAN [message #661022 is a reply to message #661021] Sun, 05 March 2017 04:57 Go to previous message
meganmegan
Messages: 7
Registered: August 2013
Location: IRAQ
Junior Member
changing order of entry in where clause has not any fluence
i tried it before
Previous Topic: Bitmap index for nullable timestamp column ?
Next Topic: Delays between two dates excluding holidays/weekends
Goto Forum:
  


Current Time: Fri Mar 29 04:18:31 CDT 2024