@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
@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
@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
@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.
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
@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
@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
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)
@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
@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)
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
@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