Migrate/Move Data to Autonomous AI Database

Assume following scenario’s:

  1. I want to refresh my UAT Database from an existing Production Data.
  2. I want to move an Oracle Database to an Autonomous AI Database. This Oracle Database could be anything – an OnPremise or on any Cloud.
  3. I want to move a Non-Oracle Database to an Autonomous AI Database

For 1 above, Autonomous AI Database provides Database Cloning option. It is just few clicks and the job is done. For 2, there are multiple methods and all of these are documented in Autonomous AI Database Documentation. Each of these methods are based on the migration scenario, database size etc. Few months back, Oracle also released a much awaited Tablespace migration option. For 3, there are multiple migration tools available that takes the data from Non-Oracle Sources, converts the data into Oracle format and then loads. All well…

But, what if the scenario is changed a bit i.e.What if, I have to refresh only a subset of Data from Production? Say, my Production is 50 TB and I just need few schema’s that are around 5 TB in size? Cloning an entire 50 TB doesn’t make sense.

One of my customers, a SaaS provider, with 20 to 30 Schema’s, had a requirement to refresh only few schema’s. They were testing few application changes and wanted to test these changes only for these schema’s. Cloning was not a solution here, as they wanted to avoid overriding other schema’s. A new clone and then dropping other schema’s means additional cost, time and efforts. The option for them was to – Take an EXPDP of these Schema’s on to Object Storage and then Import it on UAT. Manual effort and continuous monitoring.

How about a simplified way of achieving this, with an option to stop and resume from where it left? This blog is about a feature released few days back that achieves this, with just few steps. No manual efforts required. No continuous monitoring required and top of it, resilient to failures. What else do we need? Click here for the Documentation Link to know more about this feature.

In this blog, I will walk you through the steps that achieve this with just few steps. Underneath, it uses DBMS_PARALLEL_EXECUTE API to distribute an object into multiple chunks, and process these chunks individually in parallel sessions simulating parallelism. In the past, I have written many blogs about Manual Parallelism using DBMS_PARALLEL_EXECUTE. I call it manual parallelism, as I have a control on the number of threads, I can increase or reduce the number of threads at run-time and I also have an option to just process a failed thread instead of processing an entire object again. Just search on dbms_parallel_execute or manual parallelism on this blog search and you would find much of my challenging tasks accomplished using this API.

With Autonomous AI Database, you just need to run few steps and internally, all the steps needed to create multiple chunks, scheduling these are done automatically for you. So, lets get into the steps.

In this case, I want to move a Schema called MAKROOCT25 from one of my Autonomous AI Database to another Autonomous Database. The schema is in ATPAI Database (Source) and is to be moved to ATPMAY2026 Database (Target).

The steps are :

  1. On my Target, I created a Directory called ATPAI_WALLET. I will download cwallet.sso file of ATPAI Database into this directory.
  2. Then, created a credential that stores the Username and Password of a privileged user from my Source. My process (on Target) will connect to this User via Database Link, to process the chunks.
  3. Finally, we then run an API DBMS_CLOUD_IMPORT and pass the relevant parameters. Internally, this API will create a Database Link for us, connect to specified service, read the remote object(s) and then load the data into our Target.

From Oracle to Oracle, the process will create the required Indexes and Constraints as well.

create directory ATPAI_WALLET as ‘ATPAI_WALLET_DIR’;

begin
dbms_cloud.get_object(
credential_name =>’VIVEK_OBJ_CRED’,
object_uri =>'<Object_Storage_URL>/cwallet.sso’,
directory_name=>’ATPAI_WALLET’);
end;
/

SELECT
‘OBJECT_NAME=’ || OBJECT_NAME || CHR(10) ||
‘BYTES=’ || BYTES || CHR(10) ||
‘CREATED=’ || CREATED || CHR(10) ||
‘LAST_MODIFIED’ || LAST_MODIFIED OUTPUT
FROM DBMS_CLOUD.LIST_FILES(‘ATPAI_WALLET’);

OUTPUT

OBJECT_NAME=cwallet.sso
BYTES=6349
CREATED=17-MAY-26 02.35.37.000000 PM +00:00
LAST_MODIFIED17-MAY-26 02.35.37.000000 PM +00:00

Create the Credentials with the Username and Password of the Source Database.

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => ‘DB_LINK_ATPAI’,
username => ‘VIVEK’,
password => ‘<User_Password>’);
END;
/

Create an Import Task to Import the data. In this case, I am importing entire MAKROOCT25 Schema from ATPAI to ATPMAY2026.

BEGIN
DBMS_CLOUD_IMPORT.CREATE_IMPORT_TASK(
task_name => ‘MAKROOCT25_4M_ATPAI’,
hostname => ‘host_name_get_it_from_tnsnames.ora’,
port => ‘1522’,
directory_name => ‘ATPAI_WALLET’,
service_name => ‘service_name_get_it_from_tnsnames.ora’,
ssl_server_cert_dn => NULL,
credential_name => ‘DB_LINK_ATPAI’,
schema_list => ‘[“MAKROOCT25”]’
);
END;
/

HostName and ServiceName can be extracted from OCI Console (Under Connections Tab for the Source Database) or from TNSNAMES.ORA.

Once this block DBMS_CLOUD_IMPORT is executed, the API will create a database link MAKROOCT25_4M_ATPAI_DB_LINK, connect to the Username that is provided as a part of DBMS_CLOUD.CREATE_CREDENTIAL and then will schedule an Import Job on Target.

What I have observed is, even if I specify HIGH or MEDIUM Service, the data is read using LOW Service Name and this makes sense. DBMS_PARALLEL_EXECUTE itself creates multiple chunks and processes these chunks using parallel threads. This means, a table is read and loaded by these parallel threads. For each parallel thread, a database link network channel is opened. More the number of CPUs, more the number of parallel threads.

You can monitor the progress from DBA_DATA_IMPORT_TASK_STATUS. Sample output is attached.

SELECT
‘TASK_NAME=’ || TASK_NAME || CHR(10) ||
‘JOB_STATUS=’ || JOB_STATUS || CHR(10) ||
‘SUMMARY=’ || SUMMARY || CHR(10) ||
‘ELLAPSED_TIME’ ||ELLAPSED_TIME || chr(10) ||
‘CURRENTLY_LOADING’ || CURRENTLY_LOADING
FROM DBA_DATA_IMPORT_TASK_STATUS;

TASK_NAME=MAKROOCT25_4M_ATPAI
JOB_STATUS=RUNNING
SUMMARY=
ELLAPSED_TIME0d 0h 0m

TASK_NAME=MAKROOCT25_4M_ATPAI
JOB_STATUS=RUNNING
SUMMARY={“total_schemas”:1,”completed_schemas”:0,”schema_completion_percentage”:
0,”total_tables”:64,”completed_tables”:17,”table_completion_percentage”:26.56,”l
oading_tables”:1,”failed_tables”:0,”chunking_tables”:0,”fetching_stats_tables”:0
}
ELLAPSED_TIME0d 0h 5m 59.963216s
CURRENTLY_LOADING[{“schema”:”MAKROOCT25″,”table”:”GLP_HIST_ACTIVE_SESS_HISTORY”,
“task_name”:”629331387″,”total_chunks”:118,”completed_chunks”:95,”completion_per
centage”:80.51}]

TASK_NAME=MAKROOCT25_4M_ATPAI
JOB_STATUS=RUNNING
SUMMARY={“total_schemas”:1,”completed_schemas”:0,”schema_completion_percentage”:
0,”total_tables”:64,”completed_tables”:28,”table_completion_percentage”:43.75,”l
oading_tables”:1,”failed_tables”:0,”chunking_tables”:0,”fetching_stats_tables”:0
}
ELLAPSED_TIME0d 0h 7m 2.779188s
CURRENTLY_LOADING[{“schema”:”MAKROOCT25″,”table”:”GLP_HIST_SQLTEXT”,”task_name”:
“3497203576”,”total_chunks”:24,”completed_chunks”:0,”completion_percentage”:0}]

TASK_NAME=MAKROOCT25_4M_ATPAI
JOB_STATUS=SUCCEEDED
SUMMARY={“total_schemas”:1,”completed_schemas”:1,”schema_completion_percentage”:
100,”total_tables”:64,”completed_tables”:64,”table_completion_percentage”:100,”l
oading_tables”:0,”failed_tables”:0,”chunking_tables”:0,”fetching_stats_tables”:0
}
ELLAPSED_TIME0d 0h 12m 5.7349288s
CURRENTLY_LOADING[]

In case the job fails or doesn’t start, you can also validate whether the Database Link that was created implicitly by the API is working as expected. For this, you can run the following query. The DBLink is created as <TASKNAME>_DB_LINK.

select sysdate from dual@MAKROOCT25_4M_ATPAI_DB_LINK;

If the job fails in-between, you can check it from


SELECT CHUNK_ID, TASK_OWNER, TASK_NAME, STATUS, START_ROWID, END_ROWID, ERROR_MESSAGE FROM DBA_PARALLEL_EXECUTE_CHUNKS;

The best part of this approach is, you can suspend the job and resume it later. For example, if you are moving some large data and it coincides with some critical batch job, you can suspend it temporarily and resume it later. The job will resume from where it left. It is also resilient to failures, meaning, by any chance if the instance crashes or is terminated, the failed job can be restarted and it will load the data from the point it failed.

I don’t want to make this blog too long, hence would conclude here. The idea here was to post about this brilliant feature and the underneath logic that it uses. Is it restricted to only Autonomous AI Database? Answer is NO. You can very well use this logic on Non-Autonomous Databases as well. I wrote about DBMS_PARALLEL_EXECUTE in the past. You can use it for processing large amount of data, in a controlled, efficient manner and with same resiliency.

As a final note, please note that this feature does more than why I posted. You can Migrate/Move data from Non-Oracle Databases to Autonomous AI Databases as well. Autonomous Database will do all the Data-type conversion. You do not need any migration tool to accomplish this. At the time of writing this blog, this feature supports MySQL, PostgreSQL and Amazon Redshift Databases. I am sure, more Non-Oracle Databases would be added to this list. Read the Documentation for more information about this feature.

This is a personal blog. The thoughts and opinions expressed here are entirely my own and do not necessarily represent those of my employer, team, or any organization associated with me. Any code, scripts, or technical examples are shared for learning purposes and should be used with appropriate caution.

Oracle Community Yatra 2024 ! AIOUG

Happy to announce that I will be traveling across 6 cities in India speaking on my favorite topic around Database and Application Performance. This event is organized by “All India Oracle User Group” and is scheduled from 20th July 2024. Prominent Speakers from across the globe will be traveling as well. A great opportunity to Learn and to build a Network. For registration, click on the following link.

https://www.aioug.org/ocyatra/2024

External Partition ! Oracle Autonomous Database

Autonomous Database was released around March 2018. The first was a Data Warehouse Implementation that supports business Intelligence and Analytical Workload. It was Autonomous Data Warehouse. Next, the transaction workload was added to this family, called as Autonomous Transaction Processing followed by Autonomous JSON and Autonomous APEX.

One of the coolest feature of Autonomous Data Warehouse (ADW) is easy integration with Data Lake, which means, while we can store our data natively within our Autonomous Database – for faster processing, this feature allows us to store and access our data from an external storage. On Cloud, when we talk about external storage, we usually refer to an Object Storage. This is by way of creating an External Table. In this, the Table Metadata is stored in the Data Dictionary and the actual data is stored externally. This allows us to save on Storage cost. Refer to Oracle Documentation for more details on this feature.

External Table can be Non-Partitioned, Partitioned and Hybrid Partitioned. As with Oracle Partitioning that improves Query performance by pruning the partitions that are not needed for our Query. This same improvement applies to External Tables as well. With Partitioned External Table, you get the benefit of Partition Pruning thus scanning only required external data. Remember, Queries on External Data will not be as fast as queries on database tables. However, if I have an Archived Table that is in-frequently accessed and is not performance sensitive, I can always move these to External Storage. And, to improve the query performance on External Storage, External Partitioning is highly recommended. As mentioned earlier, the other benefit of this feature is COST. Hybrid Partitioning is basically mix of both i.e. I can have few historical partitions on an External Storage and latest performance sensitive data within the database.

In this blog, I will talk about External Partition Tables, and subsequent blogs would take this topic further.

Assuming you have a large table with around 5 year of Data. Users frequently query latest data. The table is currently non-partitioned. As the data is growing, I want to ensure consistent performance. How do I do that?

There are multiple strategies, like :

  • Archive the Data to a New Table and Purge it from the Original Table. Reports requiring latest data would run on the Original Table and Reports on the Historical Data would access the Archived Table.
  • Partition the Table and leverage the benefits of Partition Pruning. This is the most common strategy.

My topic for today is on Strategy#1 i.e. Archiving to a New Table. This solution can also be applied if a table has only historical data. Anyways, many customers do implement strategy#1 and move the table to some other database, called as Archival Database. With Autonomous Database, We don’t need to do that and we will see this in action. My subsequent blogs will cover both the strategies that can be optimized further on Autonomous Databases.

For this demonstration, I will use LINEORDER table which is under SSB (Sample Star Benchmark) Schema. This schema is created by-default when you provision an Autonomous Database. It’s a huge table. Therefore, I will create another table as Sample 2% and will use it as my Source table. The very first step would be to identify the partition strategy. In this case, monthly partition on LO_ORDERDATE column is a first choice. However, the other requirement is to have sub-partition it on LO_TAX column, based on Tax Codes. So, what we need here is an External Table with Monthly partitions and Sub-Partitions on LO_TAX.

OT  OWNER		 NAME				  NUM_ROWS     BLOCKS 
--- -------------------- ------------------------------ ---------- ----------
1T  ADMIN		 LINEORDER			 119993317     644744

Next step is to move the data on to the External Storage (an Object Storage). This data will be moved keeping our partitioning strategy in mind. Before I move to the next step, it is important to know that Autonomous Database support many different file formats and structure. On Autonomous Database you can create partitioned external tables from HIVE style partitioned data or from simple folder partitioned data stored on your Cloud Object Store. For example, a Hive format partitioned file will look like :

table/partition1=partition1_value/partition2=partition2_value/data_file.csv

The advantage of this approach is the partitioned information is available in the data file path name itself and therefore, the partitioned columns need not be part of the actual data. Partition pruning is done directly based on the path name. So, in our case the path name would be :

LINEORDER/MONTH_YEAR=JUN1993/TAX=2/filename.gz

In this case, the files will be compressed so that these occupy less space. MONTH_YEAR is a derived column, but TAX is LO_TAX column of LINEORDER Table. Since these columns and column values are part of my file path name, my CSV files need not contain these columns and column values. With Simple Folder partitioned data, the columns and column values of the partitioned columns have to be in the data files. So, in this case since I am using HIVE style partitioned data, I will dynamically create folders with column name and column values.

In order to access my Object Storage, the very first mandatory requirements is to create a credentials. Alternatively, you can use OCI Resource Principals. I am assuming you have all the required credentials. If not, please follow the documentation.

Next, I will use dbms_cloud.export_data api to export the data into compressed CSV files on to my Object Storage. To speed up the process, I created a CONTROL_TABLE with distinct MONYYYY values from LINEORDER and then scheduled a pl/sql block from three different sessions. This pl/sql block will export the data for a specified Month and a Tax Code. The data inserted into the control table has a condition to_char(lo_orderdate,’MONYYYY’)!=’AUG1998′. I will explain this later.

create table control_table (
    partition_name varchar2(100),
    partition_number  number,
    status char(1)
);

insert into control_table(partition_name, status)
SELECT DISTINCT TO_CHAR(LO_ORDERDATE,'MONYYYY') MONTH_YEAR, 'N' FROM LINEORDER WHERE to_char(lo_orderdate,'MONYYYY')!='AUG1998';
COMMIT;
UPDATE control_table set partition_number=rownum;
commit;


DECLARE
  FILE_URI_O	VARCHAR2(1000);
  FILE_URI_F	VARCHAR2(128);
  FILE_URI	VARCHAR2(1000);
  L_STATEMENT	VARCHAR2(1000);
BEGIN
   FOR OUTER_LOOP IN (SELECT PARTITION_NAME MONTH_YEAR, PARTITION_NUMBER FROM CONTROL_TABLE WHERE PARTITION_NUMBER>=1 AND PARTITION_NUMBER<=25 AND STATUS='N' ORDER BY PARTITION_NUMBER)
   LOOP
      FOR INNER_LOOP IN (SELECT DISTINCT LO_TAX TAX FROM LINEORDER WHERE TO_CHAR(LO_ORDERDATE,'MONYYYY')=OUTER_LOOP.MONTH_YEAR)
      LOOP
            L_STATEMENT:='select  lo_orderkey, lo_linenumber, lo_custkey, lo_partkey, lo_suppkey, lo_orderdate, lo_orderpriority, lo_shippriority, lo_quantity, lo_extendedprice, lo_ordtotalprice, lo_discount, lo_revenue, lo_supplycost, lo_commitdate, lo_shipmode from lineorder where to_char(lo_orderdate,''MONYYYY'')='||''''||OUTER_LOOP.MONTH_YEAR||''''||' and lo_tax='||INNER_LOOP.TAX||'';
            FILE_URI_F:='MONTH_YEAR='||OUTER_LOOP.MONTH_YEAR||'/TAX='||INNER_LOOP.TAX||'/';
    	    FILE_URI_O:='https://objectstorage.us-ashburn-1.oraclecloud.com/n/my_tenancy/b/External_Partitions/o/LINEORDER/'||FILE_URI_F;
    	    FILE_URI:=FILE_URI_O||OUTER_LOOP.MONTH_YEAR||'_'||INNER_LOOP.TAX;
    	    DBMS_CLOUD.EXPORT_DATA(CREDENTIAL_NAME => 'MY_CREDENTIAL', FILE_URI_LIST => FILE_URI, QUERY=>L_STATEMENT, FORMAT=> JSON_OBJECT('TYPE' VALUE 'CSV', 'quote' value '"', 'COMPRESSION' VALUE 'GZIP', 'MAXFILESIZE' VALUE '20485760'));
      END LOOP;
      UPDATE CONTROL_TABLE SET STATUS='Y' WHERE PARTITION_NUMBER=OUTER_LOOP.PARTITION_NUMBER;
      COMMIT;
   END LOOP;
END;
/

L_STATEMENT variable has the query that is used to export the data. I have the required columns in the SELECT Clause alongwith the relevant WHERE predicate. Each run of the statement will create a CSV file for a required partition and sub-partition. Further, I dynamically create the folders. If the folders doesn’t exist, the api will create it for us else it will create the relevant file within it’s folder. The folder name has the partition column name and it’s value. For example, I will have folder names as:

HIVE Format Folders and Files

Now, I have all the data on to my Object Storage and these are HIVE Format. I have compressed CSV files for each Month and Tax. I will now create an External Partition Table. This is done using dbms_cloud.create_external_part_table api.

BEGIN
    DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE (
        table_name => 'LINEORDER_EXT_PART',
        credential_name => 'MY_CREDENTIAL',
        file_uri_list => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/my_tenancy/b/External_Partitions/o/LINEORDER/*.gz',
        column_list =>
            'LO_ORDERKEY NUMBER,
             LO_LINENUMBER number,
             LO_CUSTKEY NUMBER,
             LO_PARTKEY NUMBER,
             LO_SUPPKEY NUMBER,
             LO_ORDERDATE DATE,
             LO_ORDERPRIORITY CHAR(15),
             LO_SHIPPRIORITY CHAR(1),
             LO_QUANTITY NUMBER,
             LO_EXTENDEDPRICE NUMBER,
             LO_ORDTOTALPRICE NUMBER,
             LO_DISCOUNT NUMBER,
             LO_REVENUE NUMBER,
             LO_SUPPLYCOST NUMBER,
             LO_COMMITDATE NUMBER,
             LO_SHIPMODE CHAR(10),
             MONTH_YEAR VARCHAR2(12),
             TAX NUMBER',
        format => '{"type":"csv", "skipheaders":0, "dateformat":"DD-MON-YYYY HH24:MI:SS", "compression":"gzip", "partition_columns":["MONTH_YEAR","TAX"]}');
END;
/

This creates our external partition table. The data resides on our Object Storage and the metadata is stored in the data dictionary. Now, you can run your queries against this table and the data will be retrieved from the Object Storage.

Time to run some queries and check the run time plan. Run time pan will help us validate whether the queries are able to leverage the benefits of partition pruning. We will join this external partitioned table to another table with data stored natively and from the run time plan, we see that Adaptive Optimization kicks in as well.

# Query 1
select lo_custkey, lo_orderdate, lo_shipmode, lo_quantity from lineorder_ext_part where month_year='AUG1993' and tax=2 and lo_orderkey=3517992324;

LO_CUSTKEY LO_ORDERDATE 	LO_SHIPMOD LO_QUANTITY
---------- -------------------- ---------- -----------
  29663183 10-AUG-1993 00:00:00 MAIL		     9

Elapsed: 00:00:01.75

SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST -bytes +predicate +note +cost +adaptive'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	dmak3yzpw71a2, child number 0
-------------------------------------
select lo_custkey, lo_orderdate, lo_shipmode, lo_quantity from
lineorder_ext_part where month_year='AUG1993' and tax=2 and
lo_orderkey=3517992324

Plan hash value: 1982497926

--------------------------------------------------------------------------------
| Id  | Operation		    | Name		 | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |			 |	  |	3 (100)|
|   1 |  PARTITION LIST SINGLE	    |			 |	1 |	3   (0)|
|*  2 |   EXTERNAL TABLE ACCESS FULL| LINEORDER_EXT_PART |	1 |	3   (0)|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("MONTH_YEAR"='AUG1993' AND "TAX"=2 AND
	      "LO_ORDERKEY"=3517992324))
	 
     
# Query 2
select lo_custkey, c_name, lo_orderdate, lo_shipmode, lo_quantity 
from 	lineorder_ext_part,
	vivek.customer 
where 	month_year='DEC1997' 
and 	tax=0
and 	lo_orderkey=3187415872
and	c_custkey = lo_custkey;

LO_CUSTKEY C_NAME		     LO_ORDERDATE	  LO_SHIPMOD LO_QUANTITY
---------- ------------------------- -------------------- ---------- -----------
  11640322 Customer#011640322	     04-DEC-1997 00:00:00 FOB		       6

Elapsed: 00:00:02.27
SQL> SQL> @apc
SQL> set echo on
SQL> set lines 200
SQL> set pages 1000
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST -bytes +predicate +note +cost +adaptive'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	6pn53c37c2dfb, child number 0
-------------------------------------
select lo_custkey, c_name, lo_orderdate, lo_shipmode, lo_quantity from
lineorder_ext_part,  vivek.customer where  month_year='DEC1997' and
tax=0 and  lo_orderkey=3187415872 and c_custkey = lo_custkey

Plan hash value: 1739419959

--------------------------------------------------------------------------------------
|   Id	| Operation			  | Name	       | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT		  |		       |	|     5 (100)|
|- *  1 |  HASH JOIN			  |		       |      1 |     5   (0)|
|     2 |   NESTED LOOPS		  |		       |      1 |     5   (0)|
|     3 |    NESTED LOOPS		  |		       |      1 |     5   (0)|
|-    4 |     STATISTICS COLLECTOR	  |		       |	|	     |
|     5 |      PARTITION LIST SINGLE	  |		       |      1 |     3   (0)|
|  *  6 |	EXTERNAL TABLE ACCESS FULL| LINEORDER_EXT_PART |      1 |     3   (0)|
|  *  7 |     INDEX UNIQUE SCAN 	  | CUSTOMER_PK        |      1 |     1   (0)|
|     8 |    TABLE ACCESS BY INDEX ROWID  | CUSTOMER	       |      1 |     2   (0)|
|-    9 |   TABLE ACCESS FULL		  | CUSTOMER	       |      1 |     2   (0)|
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C_CUSTKEY"="LO_CUSTKEY")
   6 - filter(("MONTH_YEAR"='DEC1997' AND "TAX"=0 AND
	      "LO_ORDERKEY"=3187415872))
   7 - access("C_CUSTKEY"="LO_CUSTKEY")

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
   - this is an adaptive plan (rows marked '-' are inactive)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

A query against all the sub-partitions or partitions works as well.

SQL> select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from lineorder_ext_part where month_year='AUG1993' and lo_custkey=29663183;

LO_ORDERKEY LO_ORDERDATE	 LO_SHIPMOD LO_QUANTITY
----------- -------------------- ---------- -----------
 3517992324 10-AUG-1993 00:00:00 MAIL		      9
 2251158885 21-AUG-1993 00:00:00 REG AIR	     29

Elapsed: 00:00:12.28
SQL> @apc
SQL> set echo on
SQL> set lines 200
SQL> set pages 1000
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST -bytes +predicate +note +cost +adaptive'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	5m709tc22ua7u, child number 0
-------------------------------------
select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from
lineorder_ext_part where month_year='AUG1993' and lo_custkey=29663183

Plan hash value: 146054153

--------------------------------------------------------------------------------
| Id  | Operation		    | Name		 | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |			 |	  |    14 (100)|
|   1 |  PARTITION LIST ITERATOR    |			 |	7 |    14  (15)|
|*  2 |   EXTERNAL TABLE ACCESS FULL| LINEORDER_EXT_PART |	7 |    14  (15)|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("MONTH_YEAR"='AUG1993' AND "LO_CUSTKEY"=29663183))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


27 rows selected.

From the queries that we executed against this External Partitioned Table, we can clearly see that for the application it doesn’t matter whether the data is fetched from within the database storage or from an External Storage. It is completely transparent to the application. One important point to note here is that these External Tables are READ ONLY and the Data is not managed by the Database. Now, lets add some more data to this External Partition Table. While generating thh CSV files, I excluded the data for AUGUST 1998. I will not generate the CSV files for AUG1998 and we will see whether I am able to query the data.

DECLARE
  FILE_URI_O	VARCHAR2(1000);
  FILE_URI_F	VARCHAR2(128);
  FILE_URI	VARCHAR2(1000);
  L_STATEMENT	VARCHAR2(1000);
BEGIN
      FOR INNER_LOOP IN (SELECT DISTINCT LO_TAX TAX FROM LINEORDER WHERE TO_CHAR(LO_ORDERDATE,'MONYYYY')='AUG1998')
      LOOP
            L_STATEMENT:='select  lo_orderkey, lo_linenumber, lo_custkey, lo_partkey, lo_suppkey, lo_orderdate, lo_orderpriority, lo_shippriority, lo_quantity, lo_extendedprice, lo_ordtotalprice, lo_discount, lo_revenue, lo_supplycost, lo_commitdate, lo_shipmode from lineorder where to_char(lo_orderdate,''MONYYYY'')='||''''||'AUG1998'||''''||' and lo_tax='||INNER_LOOP.TAX||'';
            FILE_URI_F:='MONTH_YEAR=AUG1998/TAX='||INNER_LOOP.TAX||'/';
    	    FILE_URI_O:='https://objectstorage.us-ashburn-1.oraclecloud.com/n/my_tenancy/b/External_Partitions/o/LINEORDER/'||FILE_URI_F;
    	    FILE_URI:=FILE_URI_O||'AUG1998_'||INNER_LOOP.TAX;
    	    DBMS_CLOUD.EXPORT_DATA(CREDENTIAL_NAME => 'MY_CREDENTIAL', FILE_URI_LIST => FILE_URI, QUERY=>L_STATEMENT, FORMAT=> JSON_OBJECT('TYPE' VALUE 'CSV', 'quote' value '"', 'COMPRESSION' VALUE 'GZIP', 'MAXFILESIZE' VALUE '20485760'));
      END LOOP;
END;
/

Now, let me run a query against this newly added CSV file. While the files are uploaded and available in the correct HIVE format folders, you can see that Oracle could not access the data and the run time plan says PARTITION LIST EMPTY.

SQL> select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from lineorder_ext_part where month_year='AUG1998' and tax=6 and lo_custkey=16287940;

no rows selected

Elapsed: 00:00:00.11
SQL> @apc
SQL> set lines 200
SQL> set pages 1000
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST -bytes +predicate +note +cost +adaptive'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	5s7wagh8za9bx, child number 1
-------------------------------------
select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from
lineorder_ext_part where month_year='AUG1998' and tax=6 and
lo_custkey=16287940

Plan hash value: 3670407871

--------------------------------------------------------------------------------
| Id  | Operation		    | Name		 | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |			 |	  |	2 (100)|
|   1 |  PARTITION LIST EMPTY	    |			 |	1 |	2   (0)|
|*  2 |   EXTERNAL TABLE ACCESS FULL| LINEORDER_EXT_PART |	1 |	2   (0)|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("MONTH_YEAR"='AUG1998' AND "TAX"=6 AND
	      "LO_CUSTKEY"=16287940))

SQL> select count(*) from lineorder_ext_part where month_year='AUG1998';

  COUNT(*)
----------
	 0

As I said earlier, External Tables are READ Only and the Data is not managed by Oracle Database. For this, we need to Refresh the External Table using SYNC_EXTERNAL_PART_TABLE procedure of dbms_cloud. Once done, the newly added CSV files would be considered as a new partition and the data can then be queried.

BEGIN
   DBMS_CLOUD.SYNC_EXTERNAL_PART_TABLE(table_name => 'LINEORDER_EXT_PART');
END;
/

SQL> select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from lineorder_ext_part where month_year='AUG1998' and tax=6 and lo_custkey=16287940;

LO_ORDERKEY LO_ORDERDATE	 LO_SHIPMOD LO_QUANTITY
----------- -------------------- ---------- -----------
 3004306725 02-AUG-1998 00:00:00 REG AIR	     14

Elapsed: 00:00:42.34
SQL> @apc
SQL> set echo on
SQL> set lines 200
SQL> set pages 1000
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST -bytes +predicate +note +cost +adaptive'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	5s7wagh8za9bx, child number 1
-------------------------------------
select lo_orderkey, lo_orderdate, lo_shipmode, lo_quantity from
lineorder_ext_part where month_year='AUG1998' and tax=6 and
lo_custkey=16287940

Plan hash value: 917046205

----------------------------------------------------------------------------------
| Id  | Operation		      | Name		   | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 		   |	    |	  2 (100)|
|   1 |  PX COORDINATOR 	      | 		   |	    |		 |
|   2 |   PX SEND QC (RANDOM)	      | :TQ10000	   |	  1 |	  2   (0)|
|   3 |    PX BLOCK ITERATOR	      | 		   |	  1 |	  2   (0)|
|*  4 |     EXTERNAL TABLE ACCESS FULL| LINEORDER_EXT_PART |	  1 |	  2   (0)|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(("MONTH_YEAR"='AUG1998' AND "TAX"=6 AND
	      "LO_CUSTKEY"=16287940))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 16 because of degree limit
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


31 rows selected.

Elapsed: 00:00:00.08
SQL> select count(*) from lineorder_ext_part where month_year='AUG1998';

  COUNT(*)
----------
     99688

Elapsed: 00:00:01.44
SQL> 

Wow! a cool feature and easy to use. Isn’t it? Last, with the latest enhancement to this feature, two new hidden columns are added to the External Table. These are file$path and file$name. These columns can be queried to check the Source File Path and Name.

SQL> select lo_orderkey, file$path, file$name from lineorder_ext_part where month_year='AUG1993' and tax=2 and lo_custkey=29663183;

LO_ORDERKEY FILE$PATH															     FILE$NAME
----------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------
 3517992324 https://objectstorage.us-ashburn-1.oraclecloud.com/n/my_tenancy/b/External_Partitions/o/LINEORDER/MONTH_YEAR=AUG1993/TAX=2	     AUG1993_2_10_20220704T111509Z.csv.gz

This is very easy to implement on Autonomous Database and is a Cost Saving option. Performance insensitive and historical data can be easily moved to an external storage. Accessing this data is as simple as accessing natively stored data. Do try it out and let me know how it works.

DBMS_CLOUD : Loading a TEXT file without any DELIMITER

Recently I was working on an issue related to loading data from a flat file on to Autonomous Data Warehouse using dbms_cloud api. While lot has been written on this subject, I thought of writing on this as the challenge here was that the file in this case was without any delimiter. The Original data to be loaded was around 90 Million, but for testing purpose customer was loading around 200k rows.

What is a Delimiter? Delimiter is one or more character that separates two strings. These characters are used to identify different columns.

One of the best blog on this topic is this blog from Nilay Panchal.

Coming back to my topic of this blog. In this case, customer was trying to load 200k rows into a table and the way they were using it, the time it took to load these 200k rows was around 25 minutes which is too huge considering that actual production number of rows would be 90 Million.

As mentioned, the challenge here was that the content in this flat file had no delimiter or any character to identify column break. The original flat file had around 500 columns, but for testing the file was truncated to 5 columns. The idea here is to demonstrate an efficient way of loading the data into a table when the data in the flat file is without any delimiter. The sample data is as under:

CCODEDEBUTRUNSHUNDREDSPLAYER_NAME
IND151119891592151SACHIN TENDULKAR
AUS081219951337841   RICKY PONTING
 SA141219951328945  JACQUES KALLIS
 SL200720001240038 KUMAR SANGAKARA
 WI061219901195334      BRIAN LARA
IND20062011 720227     VIRAT KOHLI

Our very first step will be to create cloud object storage credentials using dbms_cloud.create_credentials. If you have already created a credential, then you can skip this. Check dba_credentials for the list of Credentials.

begin
  dbms_cloud.create_credential(
    credential_name=>'VIVEK_CREDENTIAL',
    username=>'VIVEKS',
    password=>'paste_auth_token_here'
  );
end;
/

SQL> column owner for a20
SQL> column credential_name for a30
SQL> column username for a20
SQL> select owner, credential_name, username from dba_credentials;

OWNER		     CREDENTIAL_NAME		    USERNAME
-------------------- ------------------------------ --------------------
ADMIN		     VIVEK_CREDENTIAL		    VIVEKS

I have uploaded by data.txt file that contains the required data to be loaded on the Object Storage. Next Step will be create a table in which the data is to be loaded. For COPY_DATA, you need to ensure the Target Table is already created as the procedure would fail.

CREATE TABLE CRICKETER_DATA
   (CCODE VARCHAR2(3) ,
   DEBUT DATE,
   RUNS  NUMBER(5),
   HUNDREDS NUMBER(2),
   PLAYER_NAME VARCHAR2(16));

Now, I will create by COPY_DATA procedure with the required inputs. It is important to note the format and field_list option. In my case, the date was in DDMMYYYY format and hence I had to specify the dateformat explicitly to allow copy_data to parse the date accurately. Without the dateformat value, my copy_data failed with an error. The other parameter is field_list, which ensures that in the absence of delimiter, the copy_data parses the column values based on the start and end position. You need to ensure that these position are accurate.

begin
dbms_cloud.copy_data(
    table_name =>'CRICKETER_DATA',
    credential_name =>'VIVEK_CREDENTIAL',
    file_uri_list =>'&MY_OBJECT_STORAGE_URL/data.txt',
    format => json_object('skipheaders' value '1', 'dateformat' value 'ddmmyyyy'),
    field_list =>'CCODE POSITION(1:3), DEBUT POSITION(4:11), RUNS POSITION(12:16), HUNDREDS POSITION(17:18), PLAYER_NAME POSITION(19:34)');
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.50
SQL> SQL> select * from cricketer_data;

CCO DEBUT		       RUNS   HUNDREDS PLAYER_NAME
--- -------------------- ---------- ---------- ----------------
IND 15-NOV-1989 00:00:00      15921	    51 SACHIN TENDULKAR
AUS 08-DEC-1995 00:00:00      13378	    41	  RICKY PONTING
 SA 14-DEC-1995 00:00:00      13289	    45	 JACQUES KALLIS
 SL 20-JUL-2000 00:00:00      12400	    38	KUMAR SANGAKARA
 WI 06-DEC-1990 00:00:00      11953	    34	     BRIAN LARA
IND 20-JUN-2011 00:00:00       7202	    27	    VIRAT KOHLI

6 rows selected.

If for some reason the copy_data procedure fails, it creates a error log table, which is reported immediately along with the error number. To drill down to the actual error, you need to query the log table. For example, when I omitted FORMAT option from my COPY_DATA procedure, it failed with following error. I queried the COPY$22_LOG to extract the actual error.


select * from COPY$21_LOG;

RECORD
------------------------------------------------------------------------------------------------------------------------------------
 LOG file opened at 01/28/20 05:15:18

Total Number of Files=1

Data File: &MY_OBJECT_STORAGE_URL/data.txt

Log File: COPY$21_331103.log

 LOG file opened at 01/28/20 05:15:18

Bad File: COPY$21_331103.bad

Field Definitions for table COPY$Q18IZ07MUUMYRU3IG6MU
  Record format DELIMITED BY
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    CCODE			    CHAR (3)
      Record position (1, 3)
      Terminated by "|"
    DEBUT			    CHAR (8)
      Record position (4, 11)
      Terminated by "|"
    RUNS			    CHAR (5)
      Record position (12, 16)
      Terminated by "|"
    HUNDREDS			    CHAR (2)
      Record position (17, 18)
      Terminated by "|"
    PLAYER_NAME 		    CHAR (16)
      Record position (19, 34)
      Terminated by "|"
error processing column DEBUT in row 1 for datafile &MY_OBJECT_STORAGE_URL/data.txt

ORA-01858: a non-numeric character was found where a numeric was expected

40 rows selected.

Oracle Groundbreaker Yatra ! July 2019..

After Sangem 2018, now it’s time for another OTN Yatra, now renamed to Oracle Groundbreaker Yatra. Sangam is hosted every year and had been either in Bangalore or Hyderabad. It is a One City Event. Oracle Groundbreaker Yatra on the other hand is a Multi-City Tour. I assume, last year it was a 6 City Tour. This year, it is scheduled to be hosted in Mumbai, Chennai, Delhi, Bengaluru, Hyderabad, Kolkata, Pune, Ahmedabad, Visakhapatnam and Thiruvananthapuram.

Registration for the Groundbreaker would open soon. Many prominent speakers would be travelling and it should be a never-miss event for the Oracle Database Community (DBA’s, Developers, Architects, Data Scientists etc). Sandesh Rao, Connor Mcdonald, Roy Swonger and Gurmeet Goindi would be speaking on some interesting topic (as always). Roy Swonger from Oracle US would be visiting India for the first time. Every time you attend the sessions from these experts, you tend to learn something new and I am sure, this time as well, you will come out with bags full of Knowledge.

I am also one of the speakers for the Groundbreaker Yatra and would be travelling to 4 cities, apart from Mumbai (as it is my base location). I have opted for the locations, where I am either travelling for the first time for an User Group event or have visited only once or twice. Now, you can guess it :). I will be in Ahmedabad, Kolkata, Visakhapatnam, Thiruvananthapuram and Mumbai. I assume the agenda for some of the locations is already published and for the other locations, is in the final stage.

Keep a tab on this link Groundbreaker Yatra 2019, so that you do not miss on the registration.

Now, on my session. I am presenting a Session on “Database and Application Performance ! Then and Now.” It is a 1 hour session. I would be covering some of the Performance Challenges the DBA’s or Developers use to face and still face and how these are automatically taken care in Autonomous Databases. I will cover Optimizer, Parallelism, Oracle 19c and walk through some of the topics around Reinforcement Learning and Data Science. Usually, my sessions are supported by live demo’s. But for a 1 hour session, I am not sure whether I will be able to, but shall try to make it more interesting. See you all in July 2019.

All India Oracle User Group Events for 2018

Sangam 2018 is scheduled for 7th and 8th December 2018. I am presenting a session on “SQL Tuning ! Tips, Tools and Techniques” which is on 7th December 2018 and is immediately after Lunch. A 45 minute session doesn’t justify the topic, but will try my level best to cover few interesting real life examples. On 8th December, I will be co-hosting a Fire side chat on Performance along side Tirthankar Lahiri (VP of Product Management, Oracle US), Arup Nanda, GP Gongloor and Karan Dodwal. This is scheduled for 2.55 pm. The only issue is that I may have to leave early to catch my flight back to Mumbai.

North India Chapter – Chandigarh. Mark your calendar for 24th November 2018 as I will be speaking for the entire day on Performance Optimization covering some interesting topics around SQL Optimization, Optimizer, Indexes, Autonomous Databases and many more. This is a Full day event which gives me good amount of time to demonstrate some of the interesting facts. Registration for this event is open. Use the following link to register. See you soon.

https://www.meraevents.com/us/previewevent?view=preview&eventId=183767

For 2019, will publish the calendar, once it is freezed 🙂

Importance of Constraints ! Know your Reporting Tools

Recently, I was working on a customer issue. The issue was a performance comparison between a Competition and an Oracle Database. The performance of Competition was reported to be better than Oracle. Now, this is a classic case of Bad Schema Design and a Badly Written Query. Working on this issue reminded me of a great learning that I had after reading “Expert Oracle Database Architecture” by Thomas Kyte. He wrote about a classic issue with a Pl/SQL Block running in SQL Server and generating wrong results when ported to Oracle Database due to the way these 2 databases compare NULL values. Each of these databases are different. It also reminded me of one of my response to a query from a customer on “A count(*) from a Table is doing a Full Table Scan, even though it has a Unique Index on it”.

As Tom mentioned in his book, every database is different and implement the features differently. Now, the third party tools that connect to each of these different data sources generate queries that syntactically work on all but may not run optimally. Therefore, it is important to understand our Data and design the Schema with all the required constraints and indexes in place.

In this case, customer was running few analytical reports from a Third Party Reporting Tool (Tableau). Customer selected few columns with couple of predicates with Date Range and some product name. The Queries were around 40-50 Lines. Surprisingly, for each of the Predicates, Tableau added few additional predicates and due to these additional predicates, the run time plan was around 2700+ lines long. As an example, I have a table T1 and have to generate a report selecting object id’s and names for any of the 2 TEMPORARY values (Y or N). My query will look like :

select object_id, object_name from t1 where temporary=:b1;

If I have an Index on TEMPORARY Column, Optimizer will come out with an optimal plan based on it’s cost calculation. However, when run from Tableau, it came out with the following query:

select object_id, object_name from t1
where ((temporary=:b1) or (temporary is NULL and :b1 is NULL));

What will be the implication of this added OR conditions ? Let’s see.

SQL> create table t1 as
select * from all_objects;  2

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for all columns size auto for columns temporary size 100');

PL/SQL procedure successfully completed.

SQL> create index t1_idx on t1(temporary);

Index created.

SQL> select num_rows, blocks from dba_tables where table_name='T1';

  NUM_ROWS     BLOCKS
---------- ----------
     68417       1375

SQL> variable l_temporary varchar2(1);
SQL> exec :l_temporary:='Y';

PL/SQL procedure successfully completed.

SQL> select object_id, object_name from t1
where ((temporary=:l_temporary) or (temporary is NULL and :l_temporary is NULL)); 

161 rows selected.

SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID  7xsahyu4q594y, child number 0
-------------------------------------
select object_id, object_name from t1 where ((temporary=:l_temporary)
or (temporary is NULL and :l_temporary is NULL))

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   375 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |   161 |  6923 |   375   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("TEMPORARY"=:L_TEMPORARY OR ("TEMPORARY" IS NULL AND
              :L_TEMPORARY IS NULL)))

161 Rows out of 68000, which is less than 1%. Index Access would have been a better option. At customer end, the table was huge with Billions of Rows and with multiple such OR Predicates, the very first observation was the amount of time the Optimizer took to Parse the Query. The Query took around 184 Seconds to run and the observation was that out of 184 Seconds, around 175 Seconds were spent on Parsing. This was identified as a BUG in 18c (BUG#28725660) and the primary cause identified as the change in OR Expansion behaviour in 18c. This BUG is fixed in 19c. Backporting it to 18c would have taken some time, so the other fix that we applied was to add NOT NULL Constraints to some of the columns. From the Data, we could see that none of the columns had NULL values. We checked with the developers and they mentioned that NULL values are not stored in this column. Therefore, it was safe to add these constraints. Continuing with our example above, let’s add a NOT NULL constraint to our Table T1.

SQL> alter table t1 modify temporary not null;

Table altered.

select object_id, object_name from t1
where ((temporary=:l_temporary) or (temporary is NULL and :l_temporary is NULL));

select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 1775246573

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |       |       |     5 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1     |   161 |  6923 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1_IDX |   161 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TEMPORARY"=:L_TEMPORARY)

The run time plan of the same query is now an Index Scan and is much better than previous. This additional input to the Optimizer was enough to transform the query. You can generate 10053 trace to see the transformation.

NOT NULL constraint combined with Unique Index is required to answer a COUNT(*) query from an Index, which one of my customer asked long time back. This is another case, where NOT NULL constraint helped optimizer come up with an optimal plan.

It is important to optimize the Schema and provide all the critical inputs to the Optimizer. Generic reporting tools would come out with queries that work on all the databases. These generated queries may work on some database and may not work on other. Therefore, the title of this blog “Know your Reporting Tool” :).

On the real customer issue, post adding the constraints to few of the columns, the query response time reduced drastically from 184 Seconds to less than 10 seconds far better than the competition.

Autonomous Database Tech Day ! Gurgaon

 

#Autonomous #AIOUG Presenting a Full Day Event on 8th September 2018 for North India Oracle User Group in Gurgaon. This is on Oracle Autonomous Database. Would be covering some interesting technical capabilities of Autonomous Databases. I am covering the 2 offerings i.e. Autonomous Data Warehouse and Autonomous Transaction Processing. For Registration, click on the following link :

Meraevents Link

This being an Oracle User Group Session, focus would be on the Technical Capabilities of ADW / ATP, like Parallel Processing, Concurrency, Optimizer Enhancements and Behaviour and most importantly, Competition.

So, North India Folks : See you all on 8th September 2018.

Sangam 2017 ! Largest Annual Event of All India Oracle User Group

Sangam, India’s largest All India Oracle User Group Event is back and this time in Hyderabad. Oracle experts across the globe would be speaking and therefore, this is the best opportunity to learn from them and collaborate with other Oracle professionals.

I will be presenting 2 sessions and will be hosting a “Fireside Chat with Performance Tuning Experts” round table discussions. Along side me, there would be Tirthankar Lahiri, VP-Product Management for In-Memory Technologies, Oracle, Karan Dodwal and Kishore Surve.

Oracle Autonomous Database is one of the most talked about technology and many of you would be eager to know more about it. I will be speaking on Autonomous Database on 9th December 2017. At present, it is scheduled for 2 pm India Time. Please do check the schedule, as it can change. Seats are limited. Therefore, to avoid disappointment, kindly book your seats in advance.

Another session of mine is on “Indexing : Facts and Myths”. This session will walk you through some of the common myths and few facts. A must attend session for Technical Folks. Again, due to the limited capacity, it is better to book your seat in advance. This is scheduled for 8th December 2017.

On 9th December 2017, join me and other Oracle Experts for a fireside chat on performance. Bring your questions and get response from the experts. Performance queries are difficult to address without some data or artifacts. If you can carry some of these, that would help us further and the chat would me more meaningful.

See you in Hyderabad on 8th December.

Index – Ordering of Columns

Post my previous Blog on Consistent Gets for an Index Scan, there were few other queries that I received over the chat. The queries were related to Clustering Factor of an Index and therefore, the recommendation or comment was that the Columns should be ordered to keep the value of a Clustering Factor as low as possible. The other comment was that High Cardinality or Low Cardinality plays a critical role with an exception for Unique or Primary Key Indexes. Again, Clustering Factor was cited as the reason behind this comment. Thought of clarifying this as well. In my opinion, this is again a MYTH.

The myth that I am going to address here is – Indexes and Column Ordering should be designed such that the Clustering Factor is as low as possible. This means, an Index on (B,A) should be preferred than (A,B) if the clustering factor of (A,B) is higher than (B,A).

Assuming, I have application queries, like :

select C, D from TABLE_A where A=:b1;

select C, D from TABLE_A where B=:b1 and A=:b2;

If I go by the above myth, shall I then create an Index on (B,A) ? Will this index be used for Query#1 ? Remember, Index Skip Scan is an Optimizer decision and is chosen if the leading column is a low cardinality column. The definition of low cardinality would be an Optimizer decision based on the costing. If an index on (B,A) is not used by Query#1, then shall I go ahead and create another Index on A ? Indexes improve query performance but are overhead for DML’s and therefore, less the number of Indexes, better the DML performance would be. Further, if a column is badly clustered, whether it is a leading column or a trailing column, it doesn’t matter and we will see this with an example. Therefore, as I mentioned in earlier and in my previous blog, Column Ordering of an Index should be basis your application queries and should not be designed basis any other table or column level statistics.

In this case, I am creating a table VIVEK_TEST with 100K rows. Column BC stands for BAD_CLUSTERED and will dominate the clustering factor. There are other 2 columns of interest – Object_ID which is a non unique column and RNUM_UNQ which is a Unique Column.


exec dbms_random.seed(0);

create table vivek_test as
with test as
(select * from all_objects where rownum between 1 and 10000)
select  trunc((rownum-1)/1000) object_id,
	rownum	rnum_unq,
        round(dbms_random.value(1,100),0) bc,
        a.object_name,
        a.temporary,
        a.created
from    test a,
        test b
where  rownum between 1 and 10000;

exec dbms_stats.gather_table_stats(user,'VIVEK_TEST');

column owner for a30
select owner, num_rows, blocks from dba_tables where table_name='VIVEK_TEST';

OWNER                  NUM_ROWS     BLOCKS
-------------------- ---------- ----------
SCOTT                     10000         62

First, I will create 4 Indexes – 2 of these are Single Column Indexes and 2 are Multi-Column.

create index vivek_test_oid on vivek_test(object_id);
create index vivek_test_oid_bc on vivek_test(object_id, bc);
create index vivek_test_bc on vivek_test(bc);
create index vivek_test_bc_oid on vivek_test(bc,object_id);

column index_name for a30
select index_name, num_rows, blevel, leaf_blocks, distinct_keys, clustering_factor
from    dba_indexes
where   table_name='VIVEK_TEST'
order by 1;

INDEX_NAME                       NUM_ROWS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- ------------- -----------------
VIVEK_TEST_BC                       10000          1          20           100              4642 <-- Single Column on BC (Bad_Clustered)
VIVEK_TEST_BC_OID                   10000          1          24          1000              4642 <-- BC is the Leading Column
VIVEK_TEST_OID                      10000          1          20            10                56
VIVEK_TEST_OID_BC                   10000          1          24          1000              4856 <-- BC is the Trailing Column

As seen in the example above, column ordering doesn’t matter when it comes to the Clustering Factor. My choice of Indexing would be :

VIVEK_TEST_BC_OID if most of my queries are on 
	BC=:b1;
	BC =:b1 and object_id=:b2;

VIVEK_TEST_OID_BC if most of my queries are on 
	object_id=:b1;
	BC=:b1 and object_id=:b2;

Now, I will drop these indexes and create new indexes on the combination of BC and RNUM_UNQ. RNUM_UNQ is 100% Unique Column.

create index vivek_test_rnum on vivek_test(rnum_unq);
create index vivek_test_rnum_bc on vivek_test(rnum_unq, bc);
create index vivek_test_bc on vivek_test(bc);
create index vivek_test_bc_rnum on vivek_test(bc, rnum_unq);


column index_name for a30
select index_name, num_rows, blevel, leaf_blocks, distinct_keys, clustering_factor
from    dba_indexes
where   table_name='VIVEK_TEST'
order by 1;

INDEX_NAME                       NUM_ROWS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- ------------- -----------------
VIVEK_TEST_BC                       10000          1          20           100              4642 <-- Single Column on BC (Bad_Clustered)
VIVEK_TEST_BC_RNUM                  10000          1          26         10000              4642 <-- Leading Column dominates CF
VIVEK_TEST_RNUM                     10000          1          21         10000                56
VIVEK_TEST_RNUM_BC                  10000          1          26         10000                56


For an Index with a Column, either leading or trailing, with 100% Unique values, the leading column would dictate the calculation of Clustering Factor. This is obvious as for the Clustering factor calculation, the values are sorted on the first_column then on Second_Column. For a better idea of how is it calculated, you may run the following queries :

## CF Calculation for BC, RNUM_UNQ (it matches the value as per DBA_INDEXES)

select  sum(block_change) from (
select  block_fno, bc, RNUM_UNQ, prev_bfno,
        (case when nvl(prev_bfno,0)!=block_fno then 1 else 0 end) block_change from (
        select  block_fno, bc, RNUM_UNQ, lag(block_fno) over (order by bc, rnum_unq) prev_bfno from (
                select  dbms_rowid.rowid_block_number(rowid)||'.'||
                        dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,'&Schema','&table_name') block_fno,
                        bc, RNUM_UNQ
                from  VIVEK_TEST
                order by bc, RNUM_UNQ, block_fno)
        )
);

SUM(BLOCK_CHANGE)
-----------------
             4642


## CF Calculation for RNUM_UNQ, BC (it matches the value as per DBA_INDEXES)

select  sum(block_change) from (
select  block_fno, RNUM_UNQ, bc, prev_bfno,
        (case when nvl(prev_bfno,0)!=block_fno then 1 else 0 end) block_change from (
        select  block_fno, RNUM_UNQ, bc, lag(block_fno) over (order by rnum_unq, bc) prev_bfno from (
                select  dbms_rowid.rowid_block_number(rowid)||'.'||
                        dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,'&Schema','&table_name') block_fno,
                        bc, RNUM_UNQ
                from  VIVEK_TEST
                order by RNUM_UNQ, bc, block_fno)
        )
);

SUM(BLOCK_CHANGE)
-----------------
               56

This query can be further modified to get the calculation. Remove the SUM(BLOCK_CHANGE) SELECT from the query and you could generate the output with the calculation. For every Index Entry, if the table block is changed, the BLOCK_CHANGE value is set to 1. This query will help you understand the calculation. This holds true for a combination of Non-Unique columns as well. It is not that for column involving Unique Column, the calculation is different and this will be clear if you deep dive into the queries pasted above.

During my session, I also mentioned a FLAW in Clustering Factor. It is that it doesn’t take into consideration the caching effect and to address this, Oracle introduced TABLE_CACHED_BLOCKS preference. I am against setting manually setting clustering_factor to a lower value as it will be overwritten by the next statistics gathering process. Also, I am STRONGLY against CTAS using ORDER BY to improve the clustering_factor. By using CTAS, you can improve the clustering_factor of one index, but it will impact this for other indexes. The best way is to use the TABLE_CACHED_BLOCKS preference as depicted below.

I will use the queries pasted above to come out with the actual clustering_factor and will then set the TABLE_CACHED_BLOCKS preference. For demonstration, I will work on an Index on BC, RNUM_UNQ. This process can be used for any of the indexes.

## I will create a temporary table to hold the clustering_factor calculation


create table cluf_factor as
select bc, blkno,
        lag(blkno,1,blkno) over(order by bc) prev_blkno,
        case when blkno!=lag(blkno,1,blkno) over(order by bc) or rownum=1
           then 1 else null end cluf_ft from
(select bc, rnum_unq, dbms_rowid.rowid_block_number(rowid) blkno
from    VIVEK_TEST
where   BC is not null
order by bc);

compute sum of cnt on report
break on report
select blkno, count(*) cnt from cluf_factor group by blkno order by 1;

     BLKNO        CNT
---------- ----------
    255883        188
    255884        185
    255885        185
    255886        185
    255887        185
    255936        182
    255937        180
    255938        180
    255939        180
    255940        180
    255941        180
    255942        180
    255943        180
    255945        180
    255946        180
    255947        180
    255948        180
    255949        180
    255950        180
    255951        180
    258896        180
    258897        180
    258898        180
    258899        180
    258900        180
    258901        180
    258902        180
    258903        180
    258905        180
    258906        180
    258907        180
    258908        180
    258909        180
    258910        180
    258911        180
    260480        180
    260481        180
    260482        180
    260483        180
    260484        180
    260485        180
    260486        180
    260487        180
    260489        180
    260490        180
    260491        180
    260492        180
    260493        180
    260494        180
    260495        180
    260496        180
    260497        180
    260498        180
    260499        180
    260500        180
    260501         70
           ----------
sum             10000

56 rows selected.

As per the output, each of the block was touched around 180 times. Each of these touch were considered as it these were read from the Disk. However, Oracle purely works in Memory (Buffer_Cache, Shared_Pool etc) and once the block is read from disk, it is cached in the Buffer_cache thus avoiding the disk i/o. This flaw causes unwanted high clustering_factor and therefore, is addressed by way of TABLE_CACHED_BLOCKS.

TABLE_CACHED_BLOCKS value defaults to 1. Increasing this value (to, say 250) means, clustering_factor for an Index will not be incremented, if the table block being referenced by the current Index entry has been referenced by any of the previous 250 Index Entries. In our case, each of the table blocks were referenced by previous 180 index entries. Therefore, I will change this value to 180.

select index_name, leaf_blocks, clustering_factor from dba_indexes
where table_name = 'VIVEK_TEST';

INDEX_NAME                     LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ----------- -----------------
VIVEK_TEST_RNUM                         21                56
VIVEK_TEST_RNUM_BC                      26                56
VIVEK_TEST_BC                           20              4642
VIVEK_TEST_BC_RNUM                      26              4642

select dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS',ownname=>'SCOTT',tabname=>'VIVEK_TEST') preference from dual;

PREFERENCE
-----------------------------------------------------------
1

## Setting TABLE_CACHED_BLOCKS to 180
exec dbms_stats.set_table_prefs(ownname=>'SCOTT',tabname=>'VIVEK_TEST',pname=>'TABLE_CACHED_BLOCKS',PVALUE=>180);

select dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS',ownname=>'SCOTT',tabname=>'VIVEK_TEST') preference from dual;

PREFERENCE
------------------------------------------------------------
180

exec dbms_stats.gather_table_stats(user,'VIVEK_TEST',method_opt=>'for all columns size 1', no_invalidate=>false);

select index_name, leaf_blocks, clustering_factor from dba_indexes
where table_name = 'VIVEK_TEST';

INDEX_NAME                     LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ----------- -----------------
VIVEK_TEST_RNUM                         21                56
VIVEK_TEST_RNUM_BC                      26                56
VIVEK_TEST_BC                           20                56
VIVEK_TEST_BC_RNUM                      26                56

Clustering Factor is calculated for Unique Indexes as well and is critical for costing. The only time when clustering_factos is not considered or not relevant for Unique or Primary Keys is when querying for all the columns part of the Unique or Primary Key i.e. INDEX UNIQUE SCAN. However, clustering_factor will play a critical role when accessing the Unique or Primary Key using INDEX RANGE SCAN.

With this blog, I tried addressing following Myth’s –

- 	Indexes and Column Ordering should be designed such that the Clustering Factor is as low as possible. 
- 	Clustering_Factor is irrelevant for Unique and Primary Key Indexes.
-	High Cardinality or Low Cardinality play important roles with an exception for Unique & Primary Key.

Be it a Unique Index / Primary Key Index or any other Index, column ordering has to be basis your application queries.

One interesting fact on TABLE_CACHED_BLOCKS : While testing this, I actually created a table with 100k rows. The number of blocks in the table were around 469 and the table block were referenced by around 230 previous index entries. Therefore, I modified this preference to 230 and regathered the statistics. It didn’t work. The same table creation script with 10k rows, everything worked as expected. I am investigating this and will update the blog, once I have something to share.