----------------------------- -- all columns ----------------------------- alter table bigthing NO INMEMORY; alter table smallthing NO INMEMORY; set timing on; select count(*) from bigthing b, smallthing s where s.object_id=b.object_id and s.object_id <100; select count(*) from bigthing b, smallthing s where s.object_id=b.object_id and s.object_id <100; set timing off; alter table bigthing inmemory PRIORITY CRITICAL; alter table smallthing inmemory PRIORITY CRITICAL; set timing on; select count(*) from bigthing b, smallthing s where s.object_id=b.object_id and s.object_id <100; select count(*) from bigthing b, smallthing s where s.object_id=b.object_id and s.object_id <100; set timing off; -- wait for population to finish set timing on; select count(*) from bigthing b, smallthing s where s.object_id=b.object_id and s.object_id <100; select count(*) from bigthing b, smallthing s where s.object_id=b.object_id and s.object_id <100; set timing off; ----------------------------- -- selective columns ----------------------------- alter table bigthing NO INMEMORY; alter table smallthing NO INMEMORY; set timing on; select count(*) from bigthing b, smallthing s where s.object_id=b.object_id and s.object_id <100; select count(*) from bigthing b, smallthing s where s.object_id=b.object_id and s.object_id <100; set timing off; alter table bigthing inmemory PRIORITY CRITICAL no inmemory (OWNER,OBJECT_NAME,SUBOBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME,SHARING,EDITIONABLE,ORACLE_MAINTAINED); alter table smallthing inmemory PRIORITY CRITICAL no inmemory (OWNER,OBJECT_NAME,SUBOBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME,SHARING,EDITIONABLE,ORACLE_MAINTAINED); set timing on; select count(*) from bigthing b, smallthing s where s.object_id=b.object_id and s.object_id <100; select count(*) from bigthing b, smallthing s where s.object_id=b.object_id and s.object_id <100; set timing off; -- wait for population to finish set timing on; select count(*) from bigthing b, smallthing s where s.object_id=b.object_id and s.object_id <100; select count(*) from bigthing b, smallthing s where s.object_id=b.object_id and s.object_id <100; set timing off;