A reader, November 18, - pm UTC. My e-mail should have reached you by now, since it did not bounce back to me. A reader, November 22, - am UTC. Since, most of the rows have the value of 'Y', the CBO, decides to do a full index scan maybe?
Could you please interpret the query results above and help me draw a more logical conclusion? Thanks so much for your valuable suggestions and time,. November 22, - pm UTC. A reader, November 23, - am UTC. I gathered histograms for that table on all indexed columns size skew only. But still I get the exact same results. No clue as to why no index is picked up in the fist place. I tried creating an index only on the columns that are involved in the query.
That index does get picked up without any hints. I was trying to avoid that index overhead and rely on the index skip scan capability, since there are 2 indexes which already have those columns along with other columns.
Anything else that I could try? November 23, - am UTC. The above gave no change in the execution plan. November 23, - pm UTC.
A reader, November 23, - pm UTC. No change there. I did size , but still got the same plan. A reader, December 01, - am UTC. In other words, 1,2,3,4,5 now becomes 1,2,4,5,3. How would queries using columns 1,2,3 behave? December 01, - am UTC. Hi Tom, Please explain why index is not used in 2nd statement. December 03, - am UTC. Each partition cover 3 months. Statistics updated till date. Table has data from - till date.
I'm using a query where this table is joined with couple of more tables. Following is the explain plan for the query. When I wrote this query, it use to pick up the PK index. But since last couple of weeks it has stopped using it.
As a result, query is taking mins. Earlier it use to take couple of mins. What could be wrong? Please advice. February 09, - pm UTC. Tom, In one of the responses to the discussion threads above, you mentioned the following : thank goodness no indexes were used. From the above query plan, how did you figure out that using indexes would make the query run longer? What are the things that you typically look for in a query plan to arrive at this conclusion. Tom, From the discussions that i have been reading about execution plans, I understand that you say that : 1.
Leave the decision to the optimizer and do not use hints that could instruct the optimizer to do things in a different way and only use hints that provide extra info to the optimizer My questions are : 1.
How do I determine that the optimizer has made the correct plan? Assuming that the optimizer has done the correct plan, if my query takes longer to complete, what are the key things I need to look for. In this case, I did not put any hint to force the optimizer and it used its own plan.
The entire select completes in about 1 minute. But when I use this select to do a join update, the update runs for more than 30 minutes. Please let me know what I need to look for in the autotrace. The select returns rows. February 24, - pm UTC. Which index to use?
Sujit, February 25, - am UTC. Hi, I have a table with around 1 million rows and there is a column in this table with around distinct values. There is a bitmap index created on this column and we are concerned whether it is correct to ok bitmap index in this case. The original desingers of the system are not with us now to answer the logic behind creating bitmap index on this column. Could you please advise what could be the best solution in this case.
Whether we can leave it as it as or should we go on changing it to b-tree index. February 25, - pm UTC. A reader, February 25, - pm UTC. It all depends on the type of query, you plan to run against this table. If yours is a datawarehouse and you want star transformation, you will probably need to keep the bitmap index. In addition to the performance benefit, it is faster to create the bitmap index and it occupies less storage space when compared to btree index.
On the downside,DML operations will slow down if the table has bitmap indexes rather than bree indexes. On what basis optimizer decides to make FTS? March 02, - pm UTC. Do CBO always need statistics to make any decision? I am sorry if this question is already answered. March 13, - am UTC. And don't forget, in 10g -- dynamic sampling will kick in. Ok, I apologize, I am backing off from my words "under no circumstance full table scan will be better than cover query".
I was thinking of possibilities when FTS will be better than index scan for this example and found 1 possibility when there is heavy DML on the table. Very heavy DML may result in index block to split causing IO of atleast 3 data blocks [ 1 root and 2 leaves ] via index fetch but the table data is in just one block. I am sure this can be proved. Say for eg. Data block of table can store upto rows and say index can store upto rows. I am not proving it with actual numbers but can definitely be done by above mentioned way.
Hope in such incident CBO will fetch values from table rather than from index after analyze. I would hope the index would "survive" such modifications rather unscathed actually. Index space is most certainly reused over time.
Vodoo magic :- hrishy, April 30, - am UTC. How did you come to this conclusion. April 30, - am UTC. I set up tests, loaded data, simulated the environment. Attached is the explain plan for your advice. I have gathered the schema before executing the Query. Do we need to tune the query or it is behave in this manner? Thanks in advance. ONPN, M. Elapsed: May 05, - pm UTC. Please do reply. May 27, - pm UTC. Alex, June 08, - pm UTC. Tom, I have a query that uses a temporary table.
I have a feeling the optimizer is coming up with a sub-optimal plan because the temp table isn't filled in until run time. Using the ordered hint makes it run much faster. Do you have any suggestions for a problem like this? Is a hint ok in this case? ID AND jt. June 08, - pm UTC. You select nothing from them.
Hi Tom i have not been able to understand this and all that is written related to this defferrable constraint in your book "beginning oracle programming".. Thanks Sachin. Please explain this with a little example to make it more clear.. Excellent thank you. A reader, June 08, - pm UTC. After you do that, optimize this query to return the rows as soon as possible -- optimize for query initial response time, NOT total throughput.
Alex, June 09, - am UTC. All tables and indexes are analyzed and using choose optimizer How can i make use of indexes other than using hints? December 14, - pm UTC. A reader, March 29, - am UTC. Hi Tom, I have a procedure which generates a query like the following and when I run it, it takes more than one min.
Can you shed some light on how this is to be handled? March 29, - am UTC. Not using the right index. I have a XYZ non-partitioned table with 70 million rows. I am suspecting that it was using PK before the analyze. June 13, - pm UTC. RE: Not using the right index.
Here's the autotrace you asked. June 14, - pm UTC. Followup: are you using in real life? Yes this date is used for records that are not expired i. June 15, - am UTC. It is confused. I'll buy the book. In the meantime.. June 16, - pm UTC. Allow me to repeat, your use of this fake date - it is the root cause.
It messed up the cardinality estimate, entirely. Such a bad idea. Such a common idea irrational FEAR of null. Such a horrible bad idea. Sean, June 15, - pm UTC. Hi Tom, We have a few tables with millions of rows and we would like to know when a session issue a query or dml which does full table scan on these tables. SID : SOFAR : However I still didn't get your point. May be it made my problem more muddy for you. I have made my problem simpler here..
XYZ has following indexes 1. Its not in the query. The column is not indexed either. We recently analyzed the table as a part of regularly scheduled job. June 21, - am UTC. That is the root cause, period.
The guess of 12 is "wrong" right -- way way way wrong Tom, I am having trouble understanding why the optomizer chooses the execution plan it does with the following two queries. I have included both queries and their execution times and plans.
CONNE 5 ports. June 24, - am UTC. Getting confused once again Narendra, August 01, - am UTC. Tom, It seems this is one thing I keep stumbling upon every time I try it myself. But why does CBO "thinks" that cost of full scan 12 is less than cost of index scan 48 even when index scan requires quite less logical IOs compared to full scan?
August 01, - am UTC. The costing of the full scan single block IO's all - as opposed to a fast FULL scan plus the 63 single block IO's needed to fetch the rows outweighed the cost of the simple full scan of a relatively small table. Not quite understood Tom, Thanks for the reply. But I did not understand the following: The costing of the full scan single block IO's all - as opposed to a fast FULL scan plus the 63 single block IO's needed to fetch the rows outweighed the cost of the simple full scan of a relatively small table.
Buy on Amazon paperback only. Markus offers SQL training and consulting for developers working at companies of any size. Learn more ». Learn why. Visit my sibling! It would do so only if you explicitly set this parameter to a large value" I'm not questioning the explanation, in fact I'm just delving into the subject because I'm experiencing a similar situation as the original question: a 18 million rows table that refuses to use an optimal index when returning more than K rows.
When hinting the index for a more than k rows predicate I see the cost higher than a FTS. But it's curious to see it using the index as expected if the query returns less than or about k rows. I just tried copying the table to another schema with a CTAS and adding the index; a fresh table to avoid the "one row per block" situation, updated statistics and all. Still getting the same problem, when running the query for more than K rows the index is skipped in favor of a FTS.
What else could explain the bad performance for what is, in theory, an optimal index for my query? May 17, - pm UTC. A friend Wolfgang did a great writeup on the basics of how the costing will be done. I'm experiencing a similar situation as the original question: a 18 million rows table that refuses to use an optimal index when returning more than K rows. Perhaps that could be due to Clustering Factor of the index. As a rule of thumb it's not beneficial to use an index if the subset is a significant part of the whole table.
Think about what happens when you access this data through index - you access blocks with index one-at-a-time and given ratio it can be as much as whole index plus blocks of the table. Given how data may be stored on the table the clustering factor , the index access may end up reading the entire table anyway. Table scan can be better and faster as you can just sequentially go through all the blocks using multi-block reads instead of accessing data one-at-a-time.
Oracle uses a cost based optimizer. If Oracle thinks the cost of a full table scan is less than the cost of an index lookup, then it will choose a full table scan. So you provided the explain plan for you query; thats good.
Lets go one step further and get runtime diagnostics:. Now add a hint to you query to force the use of the index. Run the query again, and get the execution plan for the index plan, using the statement above. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more.
0コメント