Andy Sayer

I enjoy unhealthy food and databases

[Andy Sayer @AndrewSayer_]

@eduardo_claro Only case your date first index will be better is if you’re not including the other equality filter - in that case you’d be better off with the index just being on the date column

[Andy Sayer @AndrewSayer_]

@eduardo_claro I would check the low/high values for your columns in stats. Also, follow the golden rule of indexing rather than rules of thumb https://t.co/fHpxvxklFM it’s about what you’re using equality comparisons for, which is why you’d get a performance impact when you use the wrong one

[Andy Sayer @AndrewSayer_]

@thatjeffsmith @freonpeon Presumably talking about commit_wait parameter in Oracle. https://t.co/ms5zEmQu8q it can give real performance boosts if you are spending time doing log file syncs (etc) and are not worried about loosing a few minutes of data if you have certain failures

[Andy Sayer @AndrewSayer_]

@Loic__Lefevre Just going by what’s documented

[Andy Sayer @AndrewSayer_]

Just spotted in the docs: Oracle 23c increases the maximum size of an inline lob to 8000 bytes

[Andy Sayer @AndrewSayer_]

@dbms_xtender @martinberx @ToonKoppelaars @rickythomas888 I do like the bouncy scan. I think that even when ratios mean it make things worse, the overall execution time is going to be pretty good.

[Andy Sayer @AndrewSayer_]

Oracle performance quiz for the long weekend. How would you make this fast: count unique clients with unpaid invoices, stop counting at 99, expect 1m unpaid invoices. Basic sql: select count(*) from (select distinct client_id from invoice where status='unpaid') where rownum<=99

[Andy Sayer @AndrewSayer_]

@martinberx @ToonKoppelaars Although as @rickythomas888 pointed out, the same is possible without the fiddling!

[Andy Sayer @AndrewSayer_]

@martinberx @ToonKoppelaars Looks promising with the right indexes https://t.co/7sh5NB608m

[Andy Sayer @AndrewSayer_]

@rickythomas888 Good spot, looks like I was overthinking this!

[Andy Sayer @AndrewSayer_]

@FranckPachot @Yugabyte Pretty cool! Do you have a demo? Is this “basically” an index skip scan? There’s a recursive CTE way to do this in Oracle, might be worth a try

[Andy Sayer @AndrewSayer_]

@Mautro Cus=clients? 1 client will have 10 invoices on average. Most will have no unpaid invoices (so you shouldn’t loop through each client until you find the 99th) I think that’s the right track, I am thinking it would involve stopping the read when 99 distinct are found

[Andy Sayer @AndrewSayer_]

@martinberx @Mautro You can start with https://t.co/Sr07jojsJI

[Andy Sayer @AndrewSayer_]

@martinberx @Mautro Nothing relevant, a client will have multiple invoices of different statuses

[Andy Sayer @AndrewSayer_]

There will be about 100m total invoices, I’ve put together a DB fiddle with mostly random data https://t.co/Sr07jojsJI (ratios are slightly different to advertised but shouldn’t matter too much)

[Andy Sayer @AndrewSayer_]

@martinberx If there are 1000 clients with unpaid invoices, I only want to report 99. Order by doesn’t matter as I just want to report the count, not the IDs

[Andy Sayer @AndrewSayer_]

@Mautro 100 mil invoices total. Complete freedom to change things as long as the data doesn’t change.

[Andy Sayer @AndrewSayer_]

@nlitchfield @myoraclesupport Tier 1 have replicated the issue and agreed it *could* be a bug. Hopefully it can get resolved sooner than our data corrupting bug that took 5 years to fix (and annoyingly is in this patch set)

[Andy Sayer @AndrewSayer_]

Has anyone on Windows been able to install 19.18 oracle home from a gold image? Our process has worked for every other patch but this time it’s complaining about not being able to restore cloned files. @myoraclesupport is waiting for other users to raise before treating as a bug

[Andy Sayer @AndrewSayer_]

@nlitchfield @myoraclesupport Agreed, I’ve just spoken to a support manager and we’ve agreed some steps forward. Fingers crossed. We know we’re in the minority being on windows but it does seem like there’s no capacity to support us unless we make a fuss

[Andy Sayer @AndrewSayer_]

@ExaGridDba Seems reasonable to me, until there is a new requirement of course