The Heap™ – Consultancy ©®

1:49 AM

@SimonRigharts Not entirely sure when I'll fall into town but will definitely be in Welly overnight on the 12th

Simon Righarts

2:31 AM

@MarkStorey-Smith Evening drinks then - better put that on my calendar

5 hours later…

7:52 AM

@Phil I saw, I saw :) still din't understand though. Should be possible to reproduce?

2 hours later…

9:50 AM

@Phil Perhaps you can add an answer about how histograms are gathered and why they were different in the 2 tables (one slowly loaded and the other with a single Insert)

5 hours later…

A: Oracle is not using a unique index when querying for unique values

2:46 PM

(This answers the other question about why the histograms are different.) Histograms are created by default based on column skew and whether the column was used in a relevant predicate. Copying the DDL and the data is not enough, the workload information is also important. According to the ...

3:02 PM

@Phil I've emailed Jonathan Lewis. More in hope than expectation of a reply, but I thought it might spark an interest.

> Dear Jonathan

A question came up on dba.stackexchange.com recently that surprised me and I thought might interest you too.

It seems that the CBO (11.2) sometimes makes a histogram-based estimate of cardinality for a *unique* scan, instead of just assuming 1 row. I can’t for the life of me think why this would be and I wondered if you are familiar with this happening and have some sort of explanation?

If you are interested in taking a look, the details can be found here: http://dba.stackexchange.com/a/56143/1396 and in the question above.

4 hours later…

6:32 PM

@MarkStorey-Smith he's certainly doing something about it :D

Happy New Year, Heapers!

Happy New Year @Marian

@MarkStorey-Smith thanks, Mark! Same to you!

how did you come to the plan to visit the south pole? :)

@Marian Oz friend who was in London for 5 years is getting married in Sydney. So, doing the wedding then visiting another old pal who's now back in NZ.

Basically good excuse to finally take a holiday

@MarkStorey-Smith pretty good :).

better as it's close to Paul and Simon

you could take a picture of Paul and see if he's human :D.

@Marian I'll feed him beer to test that

6:39 PM

@MarkStorey-Smith do they drink that?

Phil

@JackDouglas He will reply. Good bloke, brilliant book

@Marian I hope so!

Phil

I'm actually shocked that the first hit for his book Cost Based Optimiser Fundamentals is a google hosted PDF of the book. WTF?

<3 my hard copy

@MarkStorey-Smith yeah, me too! Make sure you're feeding them one more :).

6:58 PM

@Phil He already has :)

> The oddity in the calculation is a consequence of the limits on character-based histograms, see particularly:
http://jonathanlewis.wordpress.com/2010/10/13/frequency-histogram-5/
http://jonathanlewis.wordpress.com/2010/10/19/frequency-histograms-6/

Looking at the example, the query is for an IN list, not for a single row, so my initial guess would be that the optimizer has used a generic strategy for calculating multi-row selectivity rather than having a special case piece of code for an IN list on a primary key. I guess it wouldn't be too hard for them to recognise this case, but the de…

1 hour later…

8:20 PM

I am doing some research how to select random records from a table. The topic stackoverflow.com/questions/848872/… talks about this. There is also an alternative way to do this as posted on msdn.microsoft.com/en-us/library/cc441928.aspx . I don't like both solutions. Any other ideas here ?

@MichaelD Why don't you like the second solution?

It doesn't require a sort - the only disadvanatge I see is that it does not return the exact number of rows you want.

It won't return enough results if other where-clauses narrow down the result list too much

You can always fine tune the condition used.

And I suppose the issue (slowness) is when there is a large number of rows.

If you have a WHERE clause that reduces the number of rows, you can use any other way, it won't matter much.

The where clause doesn't always narrow it down that much, it depends on the users selection. I could switch between the different approaches based on the amount of items returned. So then i have to count them first

8:36 PM

@MichaelD Do you want to select a fixed number of (random) rows? Say, 100 rows?

Or a percent? 2% of the rows?

Fixed amount, only 10

FreshPrinceOfSO

vote to close please

Sorry, this question is off-topic as it won't help future visitors. Questions require at least your table definition, queries and explain statements. This is something that can really be tested on your own. — FreshPrinceOfSO 1 min ago

@MichaelD Yes, I suppose you need to count the rows first then, to apply the second technique.

Interesting is, the binary approach from the article is faster then a sort on an indexed uniqueidentifier column

(not computed on the fly, but an indexed column)

FreshPrinceOfSO

Hypothetical questions such as "Does this seem like a reasonable amount of time to perform this task?" are off-topic. See the help center. — FreshPrinceOfSO 3 mins ago

9:01 PM

@MichaelD Query you used (for the sort on an indexed uniqueidentifier)?

I found the reason. The "binary" query only scans the clustered index. The sort on the indexed column does a scan on that index folowed by a key lookup as it also needs the other columns of the table. The key lookup seems to take 87% of the time

the key lookup is executed for each found record, it might explain the 87% . it's still fast though, just slower then the binary approach

If you have an indexed column which holds a permanent random value for each row (float or GUID, doesn't matter), you could use this:

SELECT TOP (10) t.*
FROM t
WHERE t.guid >=
   (SELECT RandomGUIDValue)
ORDER BY t.guid ;

Interesting, I'll give it a try

9:17 PM

This should do only 10 lookups, so it should be pretty fast.

I'm wondering, would this give priority to some records? probably not

Yes, it would. It would also lower the number of possible outcomes.

I mean, a random sample of 10 rows can be any combination of 10 rows.

With this, you can get only 10 consecutive rows (consecutive by the precomputed randomized index)

A nightly update of that column can fix this

FreshPrinceOfSO

@ypercube you're too nice