Transform XML into Relational Table

Compare to XMLDB, can Relational Tables run faster?
Creating a master-detail table
00:41:14 SQL> create table einvoice_table
01:00:44   2  as
01:04:17   3  select m.invoice_no, m.seller_id, m.buyer_id, d.*
01:04:18   4  from einvoice,
01:04:18   5  xmltable('/A0401/Invoice' passing object_value
01:04:18   6  COLUMNS
01:04:18   7  invoice_no varchar2(10) path 'Main/InvoiceNumber',
01:04:18   8  seller_id varchar2(8) path 'Main/Seller/Identifier',
01:04:18   9  buyer_id varchar2(8) path 'Main/Buyer/Identifier',
01:04:18  10  details xmltype path 'Details'
01:04:18  11  ) m,
01:04:18  12  xmltable('/Details/ProductItem' passing m.details
01:04:18  13  COLUMNS
01:04:18  14  item_no number(38) path 'SequenceNumber',
01:04:18  15  quantuty number(38) path 'Quantity',
01:04:18  16  unit_price number(38) path 'UnitPrice',
01:04:18  17  amount varchar2(10) path 'Amount',
01:04:18  18  description varchar2(20) path 'Description'
01:04:18  19  ) d;
Table created.
04:49:28 SQL> commit;


Commit complete.
Take 03:45:10

Run testing Query
06:54:10 SQL> select *
06:54:20   2  from einvoice_table
06:54:29   3  where invoice_no = 'AA71600519';

INVOICE_NO SELLER_I BUYER_ID    ITEM_NO   QUANTUTY UNIT_PRICE AMOUNT

---------- -------- -------- ---------- ---------- ---------- ----------

DESCRIPTION

--------------------

AA71600519 80151688 77827371          1          0          0 _0

_DESCRIPTION_

AA71600519 80151688 77827371          2          0          0 _0

_DESCRIPTION_

06:56:01 SQL>

06:58:17 SQL> l

  1  select *

  2  from einvoice_table

  3* where invoice_no = 'AA71600520'

06:58:18 SQL> /

no rows selected

06:59:10 SQL>

It also take about 1 mins without index created

Creating a index to improve access performance

06:59:10 SQL> create index einvoice_table_invoice_no_ix

07:00:17   2  on einvoice_table(invoice_no)

07:00:37   3  ;

Index created.

07:28:41 SQL>

Creating index takes 28 mins

Run testing Query again

07:37:55 SQL> l
  1  select *
  2  from einvoice_table
  3  where invoice_no =  'AA71600519'
  4*
07:37:56 SQL> /


INVOICE_NO SELLER_I BUYER_ID    ITEM_NO   QUANTUTY UNIT_PRICE AMOUNT
---------- -------- -------- ---------- ---------- ---------- ----------
DESCRIPTION
--------------------
AA71600519 80151688 77827371          1          0          0 _0
_DESCRIPTION_


AA71600519 80151688 77827371          2          0          0 _0
_DESCRIPTION_




07:37:56 SQL>




07:38:55 SQL> l
  1  select *
  2  from einvoice_table
  3* where invoice_no =  'AA71600520'
07:38:56 SQL> /


no rows selected


07:38:57 SQL>

You can see it is less than 1sec

We are going to create Master and Details tables
--
-- Create einvoice_master table
--
09:47:48 SQL> l
  1  create table einvoice_master parallel
  2  as
  3  select m.invoice_no, m.seller_id, m.buyer_id
  4  from einvoice,
  5  xmltable('/A0401/Invoice' passing object_value
  6  COLUMNS
  7  invoice_no varchar2(10) path 'Main/InvoiceNumber',
  8  seller_id varchar2(8) path 'Main/Seller/Identifier',
  9* buyer_id varchar2(8) path 'Main/Buyer/Identifier') m
09:47:52 SQL> /

Table created.
10:10:21 SQL>

Takes 22.5 Minutes

--
-- Create einvoice_details table
--
10:16:07 SQL> create table einvoice_details parallel
10:17:00   2  as
10:17:00   3  select m.invoice_no, d.*
10:17:00   4  from einvoice,
10:17:00   5  xmltable('/A0401/Invoice' passing object_value
10:17:00   6  COLUMNS
10:17:00   7  invoice_no varchar2(10) path 'Main/InvoiceNumber',
10:17:00   8  details xmltype path 'Details') m,
10:17:00   9  xmltable('/Details/ProductItem' passing m.details
10:17:00  10  COLUMNS
10:17:00  11  item_no number(38) path 'SequenceNumber',
quantuty number(38) path 'Quantity',
10:17:00  12  10:17:00  13  unit_price number(38) path 'UnitPrice',
10:17:00  14  amount varchar2(10) path 'Amount',
10:17:00  15  description varchar2(20) path 'Description') d;

Table created.

10:53:54 SQL>

It takes 39.6 mins


Creating Indexes


12:25:49 SQL> l


  1  create index
  2  einvoice_master_invoice_no
  3  on einvoice_master(invoice_no)
  4*
12:25:50 SQL> /
Index created.
12:26:59 SQL>


Takes 1.1 mins

12:27:35 SQL> create index
12:27:54   2  einvoice_details_invoice_no
12:28:15   3  on einvoice_details(invoice_no)
12:28:28   4  ;
Index created.
12:54:14 SQL>

Take 25..8 mins