How can I speed up a query if an index cannot help?
Вставка
- Опубліковано 6 вер 2024
- blog: connor-mcdonald.com
Highlights from the April DBA Office Hours session.
Office Hours is 100% free Q&A sessions held every month by Oracle experts to help you succeed with the Oracle suite of technologies.
Music: Smells Like Summer - Del (Vlog Music No Copyrighted)
Video Link: • Video
Keep these awesome tips and videos coming! Thankfully I have a nice backlog of videos. Very interesting stuff. I always thought partitioning was THE thing to go to (for huge tables in Oracle back in 9i). We just upgraded to 19c from 12.1.
Assuming big tables, other possibilities: a faster server, faster storage, more RAM, add PMEM, use RAC, move to Exadata, Advanced Compression, HCC, bitmap indexes, parallel query, DBMRC setting, LIST partitioning, OS kernel and SD device tuning. Some can have a big impact, others only minor.
My first thoughts were about partition and parallel execution.) Thanks to your clustering tip.
Nice. Hope to put this to use one day
Lets consider, a b-tree index with 3 levels. so the max number of blocks to be visited here is 4/5 to retrieve a record. The part not understood is, what data distribution (sorted/not sorted) has to do with?
The key part of your sentence is "retrieve *A* record". That is what we use unique indexes for, but often we use indexes to read LOTS of record, eg, "todays sales", "all orders for this customer" etc etc. When you are doing index RANGE scans, clustering the table data dramatically improves the performance of that index.
@@DatabaseDude
Thank you. Adding a bit context, without-exadata is fetching the entire block to memory. And exadata doing the smart scan. So if the entire block I am fetching to memory, then how does it matter? If a table got 10 blocks, and data is randomly distributed across, then all 10 blocks may have to be in the memory, but if I am simply sorting the data, I may have to fetch only two blocks. So just sorting helps here. Well, this is my take away. Kindly, your input please.
Thanks, love this one (and fact we can rebuild online).. but after the ddl add clustering and rebuilt online done, does oracle tries some magic to try keep somehow data clustered together in INSERT operations ? Probably not.. and a night job would need to simply execute the rebuild online to force data to be clustered and thats not too bad if ammount of new data in 1 day is not too much.. otherwise the only alternative would be for oracle to "reserve" data blocks after last value of some kind to allow future INSERTS taking in account the data distribution gathered from previous rebuild etc ;) Anyway thanks for sharing Connor.
Correct. Effectively the clustering is done during direct mode operations (insert-append, direct load sqldr, CTAS, alter table move etc)
hmmm ok how about what if we do insert-append when table already clustered by some column(s).. since insert-append typically only inserts about high water mark in order to use sequencial datablocks.. hmm dont see how that can work without a rebuild only ? imagine like in your example table clustered by column tag we rebuild.. so data blocks nicely clustered by tag values.. how about i do an insert /*+ append */select of source data with random order on tag ? does insert append behaves normally by insert above high mark or how does it behave ? I would say it will need a rebuild online again.. i guess your suggestion that clustering done in insert/append is only starting with a truncate table with no data ? thx
Good question - so I blogged about it
connor-mcdonald.com/2018/05/21/attribute-clustering-super-cool/
Connor McDonald just review your test and indeed looks good thanks :D
Add clustering do we have to frequently as the data gets added? Once added not worry 😊
only applies to direct load, so you might need to run "alter table move" from time to time
Is these options available in 11 g??
Not directly. You could look at using dbms_redefinition to "redefine" your table but using the same structure on the redefined table. But 12c is naturally a LOT easier
Based on your video i have applied all the same thing . while i am partitioning the table i am getting following error "ORA-14006: invalid partition name".My query " alter table t modify partition by hash(tag) partitions 16 online; " i ran the cmd in oracle 12c (12..1)kindly suggest which version it should work
12.2 is where online partition operations came into existence. Time to upgrade :-)