Re: Inmemory table on Exadata and indexes

 Did some More testing
I had tried to use the INMEMORY Hint but still it was using External Index I then Tried to using FULL Hint and found that Exection plan is doing Full Inmemory Scan and as the table has 7000K Blocks and so saw was doing long operation and doing 100K blocks a minute. Which make me believe that something is not good in the Setup. Forgot to Mentioned that this is 12.1.0.2 with Jan Patch Sanjay

    On Tuesday, April 10, 2018, 9:15:36 PM EDT, Sanjay Mishra <dmarc-noreply_at_freelists.org> wrote:  

 Hi
I had strange issue and not sure if I am doing wrongly as I had populated table in memory and using 200G for In Memory. Table is Compressed on Exadata with size of 30G. It has only three column  Eckacct( scnid number(20), Sqrno Number(10), BoxId nuber (20)) and it is compress for query low Tables is completely populated in Memory Table has index (Eckacct_idx1 (scnid,boxid)Partiioned with Boxid  and Another index eckacct_idx2(boxid)

Running Queryselect distinct sqrno from exkacct where boxId in (around 100 values); Tables has aorund 10Mill plus Records

It is using Inlist Iterator ==> Index Range Scan ==> eckacct_idx2  ==Elapsed: 00:00:00:18 I made this index invisible and ran again It is then using Inlist Iterator ==> Partion Hash Iterator ==> Index Eckacct_idx1  ==Elapsed: 00:00:00:12

If I also made this index invisible so as to use In memory data, It never returned the data and had to cancel the query after few min  Can someone point as why it is not using Oracle inmemory as this query is called 10K time in 10min and traget is reduce the time to meet the SLA. Query is supposed to be using inmemory or if I missing anything Thanks for your help and suggestionSanjay   

--
http://www.freelists.org/webpage/oracle-l