Skip to Main Content
  • Questions

  • Create table with partition to test cell offloading exadata.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Pinky.

Asked: January 09, 2020 - 9:39 pm UTC

Last updated: January 13, 2020 - 3:11 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hello Tom,

I need to test if Basic compression make any difference in cell loading (As per me it will not but we need to test it or prove it)

so i need to create a large table with partition of atleast 10GB with basic compression( as we are working on datawarehouse env)

I need help here to create a dummy table of huge size having partitions large(10gb) with basic compression.
can you give me the scripts to create large dummy table(compression) and partitions, also to check the % of cell offloading happening.

Thanks

and Connor said...

Creating a large table is easy - just take any existing table and multiply it as many times as you like, eg

create table MY_BIG_TABLE as
select d.*
from dba_objects d,
  ( select 1 from dual connect by level <= 1000 )


That will create a table that is 1000 copies of DBA_OBJECTS (typically ~100k rows), so thats 100 million rows. Add a zero for 1 billion etc.

SQL Monitoring is the easy way to check for cell offload, because it prints it out in the GUI report.

Maria has a nice blog post on this

https://sqlmaria.com/2017/08/01/getting-the-most-out-of-oracle-sql-monitor/

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

required more insight

A reader, January 10, 2020 - 2:40 pm UTC

Thanks Tom,

But what is require here is to table with huge partitions with basic compression as we need to test how basic compression impact cell offloading.

dummy table of huge size having partitions large(10gb) with basic compression.
Connor McDonald
January 13, 2020 - 3:11 am UTC

Just add the necessary partition clauses....the same concept still works fine

create table MY_BIG_TABLE 
partition by range ( blah )
interval ( 1000000)
( partition p1 values less than (0))
as
select rownum blah, d.*
from dba_objects d,
  ( select 1 from dual connect by level <= 1000 )


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.