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

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

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.

only applies to direct load, so you might need to run "alter table move" from time to time

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

12.2 is where online partition operations came into existence. Time to upgrade :-)