tag:blogger.com,1999:blog-8873109125023142810.post8699939583568907352..comments2024-03-29T13:16:28.902+05:30Comments on Let's Develop in Oracle: Why my distinct query is not using index?Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.comBlogger33125tag:blogger.com,1999:blog-8873109125023142810.post-79822297579705457482018-05-18T03:45:05.028+05:302018-05-18T03:45:05.028+05:30If some one needs expert view concerning blogging ...If some one needs expert view concerning blogging then i recommend him/her to pay a visit this weblog, Keep up the pleasant job.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-22162036341512217622018-02-08T00:32:51.549+05:302018-02-08T00:32:51.549+05:30Have you ever considered about including a little ...Have you ever considered about including a little bit <br />more than just your articles? I mean, what you say is valuable and everything.<br />But imagine if you added some great photos or videos <br />to give your posts more, "pop"! Your content is excellent but with images and clips, this blog could undeniably be one of the very best in its field.<br /><br />Fantastic blog!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-11669676147044604372017-12-09T13:36:08.170+05:302017-12-09T13:36:08.170+05:30Spot on with this write-up, I actually feel
this ...Spot on with this write-up, I actually feel <br />this web site needs a great deal more attention. I'll probably be back again to read through more, thanks for the info!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-42880986558531736152017-10-05T13:43:19.896+05:302017-10-05T13:43:19.896+05:30Website load time is an important factor, because ...Website load time is an important factor, because Google <br />is taking the site's loading speed into consideration in determining its ranking.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-81271837349736190762017-09-02T22:36:47.891+05:302017-09-02T22:36:47.891+05:30If you would like to take a great deal from this a...If you would like to take a great deal from this article then you have <br />to apply these strategies to your won web site.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-71114701669173728132017-08-01T08:58:39.232+05:302017-08-01T08:58:39.232+05:30We're a group of volunteers and starting a new...We're a group of volunteers and starting a new scheme in our community.<br />Your website offered us with valuable information to work on. You've done a formidable job and our whole community <br />will be grateful to you.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-26235612845652251642017-07-14T00:25:57.561+05:302017-07-14T00:25:57.561+05:30Moderate phlebitis will often resolve itself.Moderate phlebitis will often resolve itself.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-47436665860845676742017-07-02T10:03:32.627+05:302017-07-02T10:03:32.627+05:30Hello! I could have sworn I've visited this we...Hello! I could have sworn I've visited this website before <br />but after going through some of the posts I realized it's new to me.<br /><br />Regardless, I'm definitely pleased I discovered it and I'll be bookmarking it and checking back often!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-35539695269097236102016-11-04T11:41:31.777+05:302016-11-04T11:41:31.777+05:30Yeah that would be really helpful to start to lear...Yeah that would be really helpful to start to learn tuning.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-3406742475691971282016-10-19T12:10:43.246+05:302016-10-19T12:10:43.246+05:30Hi Nimish,
Can you post some tutorial for differe...Hi Nimish,<br /><br />Can you post some tutorial for different types of scan methods oracle used<br />?<br />Mohit https://www.blogger.com/profile/00328290669390820817noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-60850643078371155822016-05-29T08:27:37.487+05:302016-05-29T08:27:37.487+05:30nice yrnice yrgood and great thinkinghttps://www.blogger.com/profile/10775345957109171588noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-35347983862620289772016-05-25T07:54:17.160+05:302016-05-25T07:54:17.160+05:30Nice ExplanationNice ExplanationTuheen Mannahttps://www.blogger.com/profile/10012085418283645277noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-87216876000280757552016-05-25T07:47:48.644+05:302016-05-25T07:47:48.644+05:30Hi Nimish,
Thank you so much for your post. Hi Nimish, <br />Thank you so much for your post. sagarhttps://www.blogger.com/profile/08530093181966480546noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-89000272907520962992016-05-24T23:20:35.977+05:302016-05-24T23:20:35.977+05:30I tried the same on 11g XE (11.2.0.2). Everything ...I tried the same on 11g XE (11.2.0.2). Everything default on a Win32 bit system with the same INDEX FFS result. The only difference could be the version and number of rows. Anonymoushttps://www.blogger.com/profile/01957691321647114180noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-3965647219349904522016-05-24T19:57:51.988+05:302016-05-24T19:57:51.988+05:30I believe it´s depends upon on how statistics is u...I believe it´s depends upon on how statistics is updated , how many distincts values does the column have , how many rows does the table have , the kind of the index used etc...Paulo O D Mottanoreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-31151008189998734412016-05-24T19:57:16.822+05:302016-05-24T19:57:16.822+05:30a good blog entry - this is a common problem in or...a good blog entry - this is a common problem in oracle tuning, and explained clearly.Martin Ferriernoreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-46495847360456917952016-05-24T19:01:52.737+05:302016-05-24T19:01:52.737+05:30I executed all the things on this post in just one...I executed all the things on this post in just one flow on my Oracle 11g XE. All parameters were set to defaultNimish Garghttps://www.blogger.com/profile/15795821721808548808noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-88074148979130044312016-05-24T15:15:24.098+05:302016-05-24T15:15:24.098+05:30what an explanation sir.. Really nice to learn the...what an explanation sir.. Really nice to learn these tuning stuffs.. Will be waiting for your next postArijit Sannigrahinoreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-76867085376952594242016-05-24T02:37:15.899+05:302016-05-24T02:37:15.899+05:30Hi Nimish. First of all thanks for such demonstrat...Hi Nimish. First of all thanks for such demonstration. I was surprised that Oracle did not pick up the Index Fast Full Scan automatically and instead went for Index Full scan. I tested the scenario with similar data as yours on 11.2.0.3 and 12.1.0.2 EE Win 32 and 64 bit and found that in both cases INDEX FFS is chosen. I do not have any system stats gathered either. Then I thought of flouting with my default parameter values and changed optimizer_index_cost_adj to 1 (from default value 100) and executed the query to retrieve the cursor cache and from plan table output. In both cases that was INDEX FULL SCAN with the previous step being SORT UNIQUE NOSORT. That is exactly as yours. Can you check how are the full scan/index driving parameters shaped up in your system?Anonymoushttps://www.blogger.com/profile/01957691321647114180noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-47308002092347274102016-05-23T19:24:08.451+05:302016-05-23T19:24:08.451+05:30Great Explanation NimishGreat Explanation NimishJobgardhttp://www.jobgard.com/noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-38163767090496988182016-05-23T13:22:52.474+05:302016-05-23T13:22:52.474+05:30Yes I was also hoping that Oracle will do INDEX_FF...Yes I was also hoping that Oracle will do INDEX_FFS. And after this post I gathered system stats, afterwards it is doing INDEX_FFS itselfNimish Garghttps://www.blogger.com/profile/15795821721808548808noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-82994228027136816662016-05-22T23:25:13.569+05:302016-05-22T23:25:13.569+05:30Nimish do you know why you had to force FFS, optin...Nimish do you know why you had to force FFS, optinizer should pick it. Don't you think so.<br /><br />Regards,<br />VirendraAnonymoushttps://www.blogger.com/profile/05687373810352609332noreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-75078787945314572392016-05-21T16:55:56.583+05:302016-05-21T16:55:56.583+05:30Really excellent blog:-)Really excellent blog:-)Rajlakshmi Ghoshnoreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-768205360988808862016-05-21T08:42:44.186+05:302016-05-21T08:42:44.186+05:30This one is excellent help in SQL Tuning...thanks ...This one is excellent help in SQL Tuning...thanks much for sharingRaj Kumar Kushwahanoreply@blogger.comtag:blogger.com,1999:blog-8873109125023142810.post-44142734155449668892016-05-21T07:25:33.503+05:302016-05-21T07:25:33.503+05:30B-Tree indexes are not used also when there are to...B-Tree indexes are not used also when there are too few distinct values (see *_TAB_COLS.Num_Distinct) in the selected column; read Mark Gurry's "SQL Tuning Pocket Reference" (O'Reilly, 2001) for reference.Carlo S. Cocciolonoreply@blogger.com