Yesterday we looked at partitioning. Today we are going to continue this evaluation but actually execute code rather than talk in abstracts. If we want to create a partition, this is easily done by appending partitioning to a table create. It is important to remember that this option cost money when done on-premise and is typically done either to improve performance by having a smaller table to bring into memory or done to split storage so that higher speed disk can be assigned to more relevant data and lower speed and lower cost disk can be assigned to data we typically don't need to read regularly. If we are looking at using partitioning in the cloud, tiering storage is not an option. We get one disk, one type of disk, and can't assign higher speed storage to that disk partition with PaaS or DBaaS. We pay $50/TB/month to attach a disk to a compute engine and that stores our data. The tablespaces are stored in either the USER tablespace or the SYSTEM tablespace based on who creates the tablespace. To quickly review we have tables that contain our data. This data is stored in a tablespace. The tablespace might contain multiple tables or parts of tables if partitioning is used. We can assign tablespaces to different directories and typically do with on-premise systems. This allows us to put data that we need fast access to in flash memory and historic data that we might read once a year in lower cost network storage and not have to backup the historic data on a daily basis. With DBaaS we get a /u02 directory that contains the oradata folder. All tablespaces are created in this area by default. Theoretically we could mount an nfs file share if we ran the storage cloud appliance on a compute instance and pay $30/TB/month for this storage. We would have to install the nfs client on our database instance, install OSCSA on a compute instance and share the nfs directory, create a cloud storage container to hold our historic tablespaces, and point our historic partitions to our nfs mounted directories. We are not going to do this in this blog but it is an interesting thought on how to reduce the cost of storage as well as expand the amount of data that you can support with a DBaaS instance.
Let's create a few tablespaces and a partitioned table to see how it works. Most of these examples are liberally hijacked from other blogs and tutorials on the internet.
We need to note that the DBaaS that we provisioned needs to be High Performance Edition or Extreme Performance Edition. This option does not work with Standard Edition or Enterprise Edition and will fail when you try to create the table. We begin by creating a few tablespaces as well as a partitioned table that stores data into these tablespaces. It is important to note that we can easily do this because consuming storage only happens when we insert data and not create a table. We can play with creation all we want at very little cost. First, let's look at our layout using SQL Developer. If we connect to our database as a sys user we can see that by default we have the following tablespaces defined in our PDB1 pluggable container. The same is true for an 11g instance or container database. We are going to look at pluggable because it is easy to make sure that what we are creating is for this instance and not someone else playing with the system. If we add our database instance to the DBA view in SQL Developer we notice that Tablespaces appears as one of the line entries under our database. We can click on this and look at the tablespaces and files associated with them provisioned in our instance. To see the file allocation and which file system the tablespace is allocated in we need to scroll across the screen to see the information on the right.
We are going to create a few tablespaces then create a table and allocate provisions into these tablespaces. Note that these commands might not work on Amazon RDS because you need to have system level access to the database to create a tablespace and assign the file name. If we let the system use the default oradata area the create works fine. If we want to create the tablespace in /nfs/historic_oradata then the create will fail and is not allowed with RDS. Let's look at a simple example
CREATE TABLESPACE T1;
CREATE TABLESPACE T2;
CREATE TABLESPACE T3;
CREATE TABLESPACE T4;
CREATE TABLE credential_evaluations
( eval_id VARCHAR2(16) primary key
, grad_id VARCHAR2(12)
, grad_date DATE
, degree_granted VARCHAR2(12)
, degree_major VARCHAR2(64)
, school_id VARCHAR2(32)
, final_gpa NUMBER(4,2))
PARTITION BY RANGE (grad_date)
( PARTITION grad_date_70s
VALUES LESS THAN (TO_DATE('01-JAN-1980','DD-MON-YYYY')) TABLESPACE T1
, PARTITION grad_date_80s
VALUES LESS THAN (TO_DATE('01-JAN-1990','DD-MON-YYYY')) TABLESPACE T2
, PARTITION grad_date_90s
VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')) TABLESPACE T3
, PARTITION grad_date_00s
VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')) TABLESPACE T4 )
ENABLE ROW MOVEMENT;
The create tablespace t1 is needed prior to creating the partition that stores data in the tablespace t1 or the create table command will fail. We have to have the tablespace created before we allocate a partition into it. After we create the tablespace, we can look at the tablespace allocation with SQL Developer by going to the DBA view and looking at PDB1, tablespaces.
Note that the file /u02/app/oracle/oradata/ORCL/339C06AF452F1EB6E0531635C40AD41B/datafile/o1_mf_t1_co5fjnr3_.dbf was created for us. If we change our tablespace create command to
CREATE TABLESPACE T1 datafile '/u02/app/oracle/oradata/ORCL/PDB1/t1.dbf' size 2G;
CREATE TABLESPACE T2 datafile '/u02/app/oracle/oradata/ORCL/PDB1/t2.dbf' size 2G;
CREATE TABLESPACE T3 datafile '/u02/app/oracle/oradata/ORCL/PDB1/t3.dbf' size 2G;
CREATE TABLESPACE T4 datafile '/u02/app/oracle/oradata/ORCL/PDB1/t4.dbf' size 2G;
we drop the files into the directory that we want and have control over the file name and location. It is important to note that this will fail on Amazon RDS because we do not have access to the filesystem and can't specify the filename or location.
When we execute this command it takes significantly longer than our first execution because the system creates a 2 GB file before creating our tablespace and table. We would typically want to add other options like how to grow our partitions, limits on the size, and other dynamic commands. We are primarily concerned with where the file is created and not post maintenance at this point.
We need to make sure that we are running on High Performance Edition or Extreme Performance Edition because Standard Edition and Enterprise Edition fail during the create table command.
In summary, we looked a little deeper at partitioning by looking at the create tablespace and where it creates the files in the file system. We also looked at how we can control the naming as well as location with the create statement options. We briefly touch on two of the advantages that partitioning brings, speed and cost and talked about how to reduce cost by using an nfs share to store more data than a typical DBaaS provides as well as using $30/TB/month storage rather than $50/TB/month storage in the cloud. Hopefully this code example will allow you to play with partitioning and speed up select statements using the High Performance Edition of DBaaS.