Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise.
All legitimate Oracle experts publish their Oracle qualifications. 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.
The index scan statistics do not reflect 63 single block IOs that you mentioned. It just shows 57 logical IOs. Doesn't a single block IO count as a logical IO? Given the organization of your index it is frankly "backwards" for the question , it would be highly unlikely to be used. Suubu, August 16, - pm UTC. Tom, I have the same queries, one without index hint and another one with index hint.
Previously, the index did not exist on login2customer. Just for the performance comparison, i forced the index hint, surprisingly optimized chose the index and did less logical io than the other.
Do you have any suggestions on tuning this query itself 2. I do not want to use index hint. How to optimize this query. August 17, - am UTC. Your data must be unusually "well clustered" in this case. Tom, To my knowledge position of the column in index is very important.
My question is looking very dry April 26, - pm UTC. If you are going to create an index on the columns C1 and C2 in a table with , rows, and you find C1 has , distinct values and C2 has 25, distinct values, you would want to create the index on T C1,C2.
Consider the following example. There is an argument for arranging the columns in the index in order from the least discriminating to the most discriminating. These queries read every single row in the table by means of the index. There are no inherent efficiencies to be gained by placing the columns in order of how discriminating they are, and as stated previously, with index key compression there is an argument for putting the least selective first.
However, the fact is that the decision to put column C1 before C2 must be driven by how the index is used. This single index could be used by either of the queries. Additionally, using index key compression which we looked at with regard to IOTs and will examine further later , we can build a smaller index if C2 is first. This is because each value of C2 repeats itself on average four times in the index.
In Oracle 5 yes, version 5! It had to do with the way version 5 implemented index compression not the same as index key compression. This feature was removed in version 6 with the addition of row-level locking. Since then, it is not true that putting the most discriminating entries first in the index will make the index smaller or more efficient.
It seems like it will, but it will not. With index key compression, there is a compelling argument to go the other way since it can make the index smaller. However, it should be driven by how you use the index, as previously stated. I did this in the following version Oracle9i Enterprise Edition Release 9. I created these index I am inserted following value The problem is when i have just the name condition alone in my select then no index is considered.
It always gose for a full scan. But the index has very less blocks but even then it goes for the full scan. Why is it so? June 13, - am UTC. Hi Tom, I have one question in the index which is not created in the same schema where table is available. I have created the table in the Demo1 schema and inserted the some sample records. I have given grant to schema Demo2 on the table. I have check the owner details in the data dictionary table.
It is showing demo2 as a owner. I have executed the explain plan of the below query. Still it is going for the index. I have tried to insert the data and it is inserting with out any error. Could you guide me how it is inserting the data in index entry which is available in the Demo2 schema and since we have removed the access from Demo1.
October 29, - am UTC. They no longer have permission to do something But they used to And when they had this permission - they did something You cannot change that fact. The user demo2 cannot query that index - they do not have access to the data. Hi Tom, Good Day.
The following query returns rows, but statistics is huge. I am not much bothered about full table scan, as it is not an evil and CBO would have selected the best plan. The statistics I am getting is consistent gets physical reads Please note that the statistics are updated. I think, in Oracle, the create index statement updates the index statistics. Now, my questions are: a Is this the behaviour of the query? Is it because the number of records are high? Thanks Hari. November 24, - am UTC.
Look at the plan the optimizer is guessing , records. Are the statistics up to date How do you gather column statistics - if you have skewed data - as you must - you might need to get histograms in place. Scofield, December 20, - am UTC. Hi sir Regarding the index range scan, suppose "where x betwen 20 and 30" From your book I understand that: "Oracle finds the first index block contains 20 and then just walks horizantally thorugh the linked list of leaf nodes" What I want to ask is; Does oracle perform like this: 1- go to index block that contain 20, get the rowid 2- go to block, read the data 3- go to index block that contain 21, get the rowid 4- go to block, read the data 5- go to index block that contain 22, get the rowid..
December 20, - am UTC. It might. It depends on the rest of the plan. But usually, yes, an index range scan would be followed by a table access by index rowid - back and forth, back and forth between the two.
To Hari A reader, December 22, - pm UTC. As Tom has suggested you might want to create histograms. A reader, January 11, - am UTC. Sir, in above you confirmed that below is generally true: "where x betwen 20 and 30" 1- go to index block that contain 20, get the rowid 2- go to block, read the data 3- go to index block that contain 21, get the rowid 4- go to block, read the data 5- go to index block that contain 22, get the rowid Suppose it will take 3 phyical or logical IO to reach the index block that contain January 18, - am UTC.
A reader, January 23, - am UTC. Respected Sir; How does this happen? Does oracle store some information about this so that traversing up the tree again is not required? January 25, - am UTC. There is a doubly linked list of leaf blocks. Dear Tom, I am facing a peculiar problem. We recently partitioned a big table on a date column. Created local index on the partitioned column and global partitioned index on another column which will be the leading column in the where clause of most of the queries.
Just e-mail: and include the URL for the page. All rights reserved by Burleson. Search BC Oracle Sites. Learn more. Oracle is not using the index Ask Question. Asked 4 years, 8 months ago. Active 4 years, 8 months ago. Viewed 4k times. Improve this question. If poliespa has only two values, then using it probably does not sufficiently reduce the number of rows that need to be scanned, to make the index worthwhile.
Full scans are not evil; indexes are not all goodness I really trust the Oracle Optimizer : What I'm trying to understand is why he doesn't use the index.
Isn't enough? I know I can use a hint. But is not the purpose this question. You have not marked an answer on this. Review the answers.
0コメント