How can I speed up a query if an index cannot help?

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)

[GregMorphis]

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.

[stevo]

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.

[mikhaill]

My first thoughts were about partition and parallel execution.) Thanks to your clustering tip.

[sandeeepkumarmishra]

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?

[sureshethiswaran]

Add clustering do we have to frequently as the data gets added? Once added not worry 😊

[emanueol]

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.

[ATOMIC_UNIVERSE-SS]

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