OraFAQ Forum: SQL & PL/SQL » why cbo select FULL TABLE SCAN

Home » SQL & PL/SQL » SQL & PL/SQL » why cbo select FULL TABLE SCAN

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

why cbo select FULL TABLE SCAN [message #660965]

Thu, 02 March 2017 14:16

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???

Report message to a moderator

Re: why cbo select FULL TABLE SCAN [message #660969 is a reply to message #660965]

Thu, 02 March 2017 14:27

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: why cbo select FULL TABLE SCAN [message #660971 is a reply to message #660969]

Thu, 02 March 2017 14:55

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?

Report message to a moderator

Re: why cbo select FULL TABLE SCAN [message #660973 is a reply to message #660971]

Thu, 02 March 2017 15:02

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

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?

Report message to a moderator

Re: why cbo select FULL TABLE SCAN [message #660974 is a reply to message #660973]

Thu, 02 March 2017 15:05

meganmegan
Messages: 7
Registered: August 2013
Location: IRAQ

Junior Member

what should i do exactly and thanks for your mentioned?

Report message to a moderator

Re: why cbo select FULL TABLE SCAN [message #660975 is a reply to message #660974]

Thu, 02 March 2017 15:13

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?

Report message to a moderator

Re: why cbo select FULL TABLE SCAN [message #660976 is a reply to message #660975]

Thu, 02 March 2017 15:19

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

Report message to a moderator

Re: why cbo select FULL TABLE SCAN [message #660979 is a reply to message #660976]

Thu, 02 March 2017 15:30

Bill B
Messages: 1971
Registered: December 2004

Senior Member

When was the last time statistics were gathered on the table?

Report message to a moderator

Re: why cbo select FULL TABLE SCAN [message #660991 is a reply to message #660965]

Fri, 03 March 2017 01:37

John Watson
Messages: 9000
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.

Report message to a moderator

Re: why cbo select FULL TABLE SCAN [message #661015 is a reply to message #660991]

Sun, 05 March 2017 04:21

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

Attachment: information.txt
(Size: 2.06KB, Downloaded 1603 times)

Report message to a moderator

Re: why cbo select FULL TABLE SCAN [message #661017 is a reply to message #661015]

Sun, 05 March 2017 04:29

John Watson
Messages: 9000
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?

Report message to a moderator

Re: why cbo select FULL TABLE SCAN [message #661018 is a reply to message #660965]

Sun, 05 March 2017 04:36

John Watson
Messages: 9000
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.

Report message to a moderator

Re: why cbo select FULL TABLE SCAN [message #661019 is a reply to message #661018]

Sun, 05 March 2017 04:41

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

Report message to a moderator