Create eInvoice Partitioning Table

Create eInvoice Partition Table

21:39:34 SQL> CREATE TABLE einvoice_part OF XMLType
21:39:34   2  XMLTYPE STORE AS SECUREFILE BINARY XML
21:39:35   3  ( TABLESPACE "FSMS_DATA" STORAGE ( INITIAL 1 M NEXT 1M))
21:39:35   4  VIRTUAL COLUMNS
21:39:35   5  (INVOICENUM AS (XMLCast(XMLQuery('/A0401/Invoice/Main/InvoiceNumber' PASSING OBJECT_VALUE RETURNING CONTENT)
21:39:35   6  AS varchar2(12))))
21:39:35   7  STORAGE(INITIAL 8K NEXT 8K)
21:39:35   8  TABLESPACE "FSMS_DATA"
21:39:35   9  PARALLEL
21:39:35  10  PARTITION BY HASH (INVOICENUM)
21:39:35  11  PARTITIONS 256
21:39:35  12

21:39:38 SQL> /

Table created.

Elapsed: 00:00:16.27

21:39:55 SQL> 

Testing running sqlldr to eInvoice_part

Control file

load data

infile 'filelist.dat'

APPEND

into table einvoice_part

xmltype(XMLDATA)

(

filename filler char(120),

XMLDATA lobfile(filename) terminated by eof

)

Execution SQL Loader Command line

$ sqlldr userid=fsms/password@orcl control=filelist.ctl direct=y log=20110312.log

SQL*Loader: Release 11.2.0.1.0 - Production on Mon Mar 21 21:44:58 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Load completed - logical record count 14959.

View 20110321.log

Total logical records skipped:          0

Total logical records read:         14959

Total logical records rejected:         0

Total logical records discarded:        0

Total stream buffers loaded by SQL*Loader main thread:    88408

Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Mon Mar 21 21:44:58 2011

Run ended on Tue Mar 22 00:03:19 2011

Elapsed time was:     02:18:19.84

CPU time was:         01:59:16.81

Create XML View

10:31:29 SQL> @scripts.sql
10:31:37 SQL> set echo on
10:31:37 SQL> set time on
10:31:37 SQL> alter session enable parallel query;


Session altered.


10:31:37 SQL> alter session enable parallel ddl;


Session altered.


10:31:37 SQL> alter session enable parallel dml;


Session altered.


10:31:37 SQL> DROP INDEX invoice_ix;
DROP INDEX invoice_ix
           *
ERROR at line 1:
ORA-01418: specified index does not exist 




10:31:37 SQL> CREATE INDEX invoice_ix
10:31:37   2  ON einvoice(object_value)
10:31:37   3  INDEXTYPE IS XDB.XMLINDEX PARALLEL
10:31:37   4  PARAMETERS ('
10:31:37   5  GROUP einvoiceparam XMLTable einvoice_idx_tab
10:31:37   6  ''/A0401/Invoice/Main''
10:31:37   7  COLUMNS invoicenumber   VARCHAR2(10)  PATH ''InvoiceNumber'',
10:31:37   8        SellerID VARCHAR2(8)  PATH ''Seller/Identifier'',
10:31:37   9        BuyerID VARCHAR2(8)  PATH ''Buyer/Identifier''
10:31:37  10  ');


Index created.


11:31:14 SQL> create index einvoice_idx_tab_invoicenumber
11:31:14   2  on einvoice_idx_tab (invoicenumber) parallel;


Index created.


11:32:09 SQL> create index einvoice_idx_tab_sellerid
11:32:09   2  on einvoice_idx_tab (sellerid) parallel;


Index created.


11:32:41 SQL> create index einvoice_idx_tab_buyerid
11:32:41   2  on einvoice_idx_tab (buyerid) parallel;


Index created.


11:33:13 SQL> 
11:33:13 SQL> --
11:33:13 SQL> -- Create einvoice_master view
11:33:13 SQL> --
11:33:13 SQL> create or replace view einvoice_master
11:33:13   2  as
11:33:13   3  select m.invoice_no, m.seller_id, m.buyer_id, m.amount
11:33:13   4  from einvoice,
11:33:13   5  xmltable('/A0401/Invoice' passing object_value
11:33:13   6  COLUMNS
11:33:13   7  invoice_no varchar2(10) path 'Main/InvoiceNumber',
11:33:13   8  seller_id varchar2(8) path 'Main/Seller/Identifier',
11:33:13   9  buyer_id varchar2(8) path 'Main/Buyer/Identifier',
11:33:13  10  amount number path 'Amount/SalesAmount') m;


View created.


11:33:13 SQL> 
11:33:13 SQL> --
11:33:13 SQL> -- Create einvoice_details view
11:33:13 SQL> --

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


View created.


20:48:48 SQL>

11:33:13 SQL> spool off

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

Creating XMLIndex and Run Testing Query

The following is how to create a XMLIndex
BEGIN
  DBMS_XMLINDEX.modifyParameter(
    'einvoiceparam',
    '
       XMLTable einvoice_idx_tab (TABLESPACE "FSMS_IDEX" ''/A0401/Invoice/Main''
         COLUMNS invoicenumber   VARCHAR2(10)  PATH ''InvoiceNumber'',
                 SellerID   VARCHAR2(8)  PATH ''Seller/Identifier'',
                 BuyerID    VARCHAR2(8)  PATH ''Buyer/Identifier''
');
END;
/


CREATE INDEX invoice_ix
ON einvoice(object_value)
INDEXTYPE IS XDB.XMLINDEX
PARAMETERS('PARAM einvoiceparam')

After XMLIndex Created,wanna to know what's the performance impact to sqlloader 
(Note: Now sqlloader cannot use direct path load only conventional path)
The command:
$ sqlldr userid=fsms/fsms@orcl control=filelist.ctl parallel=y

From filelist.log
Run began on Mon Mar 14 19:36:42 2011
Run ended on Tue Mar 15 00:06:38 2011


Elapsed time was:     04:29:53.42
CPU time was:         00:14:09.60

Run Query

00:40:05 SQL> l
  1  SELECT
  2  XMLQuery('/A0401/Invoice/Main' passing object_value returning content)
  3  from einvoice p
  4  where
  5* XMLExists('/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE)
00:40:06 SQL>
00:40:07 SQL>
00:40:09 SQL>
00:40:09 SQL> /


XMLQUERY('/A0401/INVOICE/MAIN'PASSINGOBJECT_VALUERETURNINGCONTENT)
--------------------------------------------------------------------------------
<Main>
  <InvoiceNumber>AA71600519</InvoiceNumber>
  <InvoiceDate>
    <Year>100




00:41:14 SQL>

It still takes around 1 mins.

Execution Plan

寄件者 Oracle Spatial Working Notes

Create B-tree on XMLIndex table can boost query performance

create index einvoice_idx_tab_ix

on einvoice_idx_tab (invoicenumber);

And run the same query, you will check the cost is reduced.

Compare the cost 88 vs 7 and the query run less 1 sec.

eInvoice Simple Testing

A simple testing on a quad-cores CPU with 8GB memory running Oracle Enterprise Linux. Storage is a SATA disk.

1. The size of 15M XML eInvoice
[oracle@gis data]$ du -h /home/oracle/data
71G     /home/oracle/data

2. Content of sqlloader control file: filelist.ctl

load data

infile 'filelist.dat'

APPEND

into table einvoice

xmltype(XMLDATA)

(

filename filler char(120),

XMLDATA lobfile(filename) terminated by eof

)

3. Content of filelist.dat (part of, actually is file listing) 

test-000000000-0986.xml

test-000000986-0814.xml

test-000001800-0811.xml

test-000002611-1464.xml

....

Total: 14959 files (15M of invoice number)

4. sqlloader Command

[oracle@gis data]$ sqlldr userid=fsms/password@orcl control=filelist.ctl direct=y
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Mar 11 20:54:13 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Load completed - logical record count 14959.

4.1 To find how long does sqlloader take, check the log file
this is a single process sqlloader
Content of log file: filelist.log
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Mar 11 20:54:13 2011


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Control File:   filelist.ctl
Data File:      filelist.dat
  Bad File:     filelist.bad
  Discard File:  none specified


 (Allow all discards)


Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      Direct


Table EINVOICE, loaded from every logical record.
Insert option in effect for this table: APPEND


   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FILENAME                            FIRST   120           CHARACTER
  (FILLER FIELD)
XMLDATA                           DERIVED     *  EOF      CHARACTER
    Dynamic LOBFILE.  Filename in field FILENAME


The following index(es) on table EINVOICE were processed:

index FSMS.SYS_C0014048 loaded successfully with 14959 keys

Table EINVOICE:

  14959 Rows successfully loaded.

  0 Rows not loaded due to data errors.

  0 Rows not loaded because all WHEN clauses were failed.

  0 Rows not loaded because all fields were null.

Bind array size not used in direct path.

Column array  rows :       1

Stream buffer bytes:  256000

Read   buffer bytes: 1048576

Total logical records skipped:          0

Total logical records read:         14959

Total logical records rejected:         0

Total logical records discarded:        0

Total stream buffers loaded by SQL*Loader main thread:    88408

Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Fri Mar 11 20:54:13 2011

Run ended on Fri Mar 11 23:04:57 2011

Elapsed time was:     02:10:42.70

CPU time was:         02:03:32.10

5. Count The Total Imported Invoice Number

SQL> l

  1  select count(*)

  2  from einvoice,

  3  xmltable('/A0401/Invoice' passing object_value

  4  COLUMNS

  5* invoice_no varchar2(10) path 'Main/InvoiceNumber')

SQL> /

  COUNT(*)

----------

  15000000

6. A Testing Invoice Query Without XMLType Index Created

23:50:00 SQL> l

  1  SELECT

  2  XMLQuery('/A0401/Invoice/Main' passing object_value returning content)

  3  from einvoice p

  4  where

  5* XMLExists('/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE)

23:50:07 SQL> /

XMLQUERY('/A0401/INVOICE/MAIN'PASSINGOBJECT_VALUERETURNINGCONTENT)
--------------------------------------------------------------------------------
<Main>
  <InvoiceNumber>AA71600519</InvoiceNumber>
  <InvoiceDate>
    <Year>100


00:15:02 SQL>

Takes 25mins

6. Create a XML Index to improve performance

09:49:49 SQL> CREATE INDEX invoice_ix

10:06:57   2  ON einvoice(object_value)

10:06:57   3  INDEXTYPE IS XDB.XMLINDEX

10:06:57   4  PARAMETERS('PATH TABLE einvoice_path_table

10:06:57   5  PATHS (INCLUDE (/A0401/Invoice/Main/InvoiceNumber /A0401/Invoice/Main/Seller/Identifier /A0401/Invoice/Main/Buyer/Identifier))

10:06:57   6  PIKEY INDEX einvoice_pikey_ix

10:06:57   7  VALUE INDEX einvoice_value_ix')

10:06:58   8  /

Index created.

11:31:30 SQL>

It takes around 01:30:00 

7. Run Testing Invoice Query With XML Index Created

11:39:19 SQL> l

  1  SELECT

  2  XMLQuery('/A0401/Invoice/Main' passing object_value returning content)

  3  from einvoice p

  4  where

  5* XMLExists('/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE)

11:39:21 SQL> /

XMLQUERY('/A0401/INVOICE/MAIN'PASSINGOBJECT_VALUERETURNINGCONTENT)

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

<Main>

  <InvoiceNumber>AA71600519</InvoiceNumber>

  <InvoiceDate>

    <Year>100

11:40:22 SQL>

Takes 1 (00:01:09) minutes. (Compares with previous SQL query without index created, it taked 25 minutes)


7. I am interesting in load data with index already created.

12:08:13 SQL> truncate table einvoice;


Table truncated.


12:08:26 SQL>

Note: Cannot use direct path load cause index is already created.

[oracle@gis data]$ sqlldr userid=fsms/fsms@orcl control=filelist.ctl direct=n

SQL*Loader: Release 11.2.0.1.0 - Production on Mon Mar 14 12:09:25 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

p.s. The load performance is bad compared to previous on which has no index created. It might be improved by pre-allocated table and index space by setting "miniextend"

Use Spatial Functions Examples

1.  Function get_point_high
SQL> select get_point_high(121, 23) from dual;

GET_POINT_HIGH(121,23)
----------------------
                  2292

2. Function get_line_high
SQL> select get_line_high(120,22, 121,24) from dual

GET_LINE_HIGH(120,22,121,24)
----------------------------
                  284.309862

3. procedure get_line_every_high_points
SQL> EXECUTE get_line_every_high_points(120,22,121,24)
SQL>
SQL> select * from dem_result;

         X          Y          Z
---------- ---------- ----------
120.999612 23.9991496       1088
120.999612  23.999322       1101
120.999784 23.9996668       1105
120.999957 23.9998392       1107
120.999957 24.0000116       1110


3998 rows selected.

Get Every High Points

CREATE GLOBAL TEMPORARY TABLE DEM_RESULT
   ( X NUMBER,
Y NUMBER,
Z NUMBER
   ) ON COMMIT DELETE ROWS ;


create or replace
PROCEDURE GET_LINE_EVERY_HIGH_POINTS
(
  first_x NUMBER
, first_y NUMBER
, next_x NUMBER
, next_y NUMBER
, SRID IN NUMBER DEFAULT 4326
) AS
BUFFER_GEOM SDO_GEOMETRY;
n_High NUMBER;
BEGIN
  BUFFER_GEOM := sdo_geom.sdo_buffer(line(first_x, first_y, next_x, next_y), 10, 1 );
  INSERT INTO dem_result(X, Y, Z) SELECT T.X, T.Y, T.Z
  FROM (
    SELECT X, Y, Z, Z - LAG(Z) over (order by Y)  AS P, LAG(Z) over (order by Y)- Z AS F FROM dem_part WHERE SDO_INSIDE(location, BUFFER_GEOM) = 'TRUE') T
  WHERE T.P > 0 AND T.F < 0;
END GET_LINE_EVERY_HIGH_POINTS;

Create a GET_LINE_HIGH function

Following "Create a GET_POINT_HIGH function" concept, I am going to create a GET_LINE_HIGH function.

Before to get this function done, I need a LINE constructor

create or replace
FUNCTION line(
first_x NUMBER, first_y NUMBER, next_x NUMBER, next_y NUMBER, srid NUMBER DEFAULT 4326)
RETURN SDO_GEOMETRY
DETERMINISTIC
IS
BEGIN
  RETURN SDO_GEOMETRY(2002, srid, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(first_x, first_y, next_x, next_y));
END;


Then

create or replace
FUNCTION GET_LINE_HIGH 
(
  first_x NUMBER
, first_y NUMBER
, next_x NUMBER
, next_y NUMBER  
, SRID IN NUMBER DEFAULT 4326 
) RETURN NUMBER AS 
BUFFER_GEOM SDO_GEOMETRY;
n_High NUMBER;
BEGIN
  BUFFER_GEOM := sdo_geom.sdo_buffer(line(first_x, first_y, next_x, next_y), 10, 1 );
  SELECT AVG(z) INTO n_high FROM dem_part WHERE SDO_INSIDE(location, BUFFER_GEOM) = 'TRUE';
  RETURN n_High;
END GET_LINE_HIGH;


Eliminate top and button 10%

create or replace
FUNCTION GET_LINE_HIGH 
(
  first_x NUMBER
, first_y NUMBER
, next_x NUMBER
, next_y NUMBER  
, SRID IN NUMBER DEFAULT 4326 
) RETURN NUMBER AS 
BUFFER_GEOM SDO_GEOMETRY;
n_High NUMBER;
BEGIN
  BUFFER_GEOM := sdo_geom.sdo_buffer(line(first_x, first_y, next_x, next_y), 10, 1 );
  SELECT AVG(T.Z) INTO n_high
  FROM (
    SELECT Z, CUME_DIST() over (order by Z) P FROM dem_part WHERE SDO_INSIDE(location, BUFFER_GEOM) = 'TRUE') T
  WHERE T.P > 0.1 AND T.P < 0.9;
  RETURN n_High;
END GET_LINE_HIGH;

Create a GET_POINT_HIGH function

To return a point high, basically is not possible cause it is very rate to get exactly the same point. I try to approach by
1. Get a point buffer
2. Get the MBR from the buffer returned by the point
3. Average all returned points from a SDO_INSIDE operator interact with the MBR


create or replace
FUNCTION GET_POINT_HIGH 
(
  X IN NUMBER  
, Y IN NUMBER  
, SRID IN NUMBER DEFAULT 4326 
) RETURN NUMBER AS 
MBR_GEOM SDO_GEOMETRY;
n_High NUMBER;
BEGIN
  MBR_GEOM := sdo_geom.sdo_MBR(sdo_geom.sdo_buffer(point(X,Y), 10, 1 ));
  SELECT AVG(z) INTO n_high FROM dem_part WHERE SDO_INSIDE(location, mbr_geom) = 'TRUE';
  RETURN n_High;
END GET_POINT_HIGH;

Create DEM with partition

For improve performance, try to create table DEM_PART with RANGE-RANGE partition

SQL> CREATE TABLE DEM_PART
  2  (X NUMBER
  3  ,Y NUMBER
  4  ,Z NUMBER
  5  ,LOCATION MDSYS.SDO_GEOMETRY
  6  )
  7  PARTITION BY RANGE (X)
  8  (PARTITION X_119 VALUES LESS THAN (120)
  9  ,PARTITION X_120 VALUES LESS THAN (121)
 10  ,PARTITION X_121 VALUES LESS THAN (122)
 11  ,PARTITION X_122 VALUES LESS THAN (123)
 12  );

Table created.

Load point into DEM_PART

SQL>

INSERT INTO dem_part
SELECT x, y, z, point(x, y)
FROM points;
COMMIT;

Use this information to update USER_SDO_GEOM_METADATA

SQL>
INSERT INTO USER_SDO_GEOM_METADATA
VALUES ('DEM_PART','LOCATION',
SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',119.982565,122.02683,10),
SDO_DIM_ELEMENT('Y',21.8673002,25.3523955,10)),4326);

COMMIT;

Create Spatial Index
A better way to create Spatial Index for saving working space and time is listed as following:

CREATE INDEX sdo_dem_part_location ON dem_part (location)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS ('tablespace=fsms_idex')
LOCAL UNUSABLE;

ALTER INDEX sdo_dem_part_location REBUILD PARTITION X_119;
ALTER INDEX sdo_dem_part_location REBUILD PARTITION X_120;
ALTER INDEX sdo_dem_part_location REBUILD PARTITION X_121;
ALTER INDEX sdo_dem_part_location REBUILD PARTITION X_122;

eInvoice XMLTYPE tables and query

1. XMLTYPE Table
CREATE TABLE EINVOICE OF SYS.XMLTYPE XMLTYPE STORE AS BINARY XML ( TABLESPACE "FSMS_DATA" CHUNK 8192 ) LOGGING NOCOMPRESS 

2. Using sqlloader to load xml
The best way to load large amount of XML files is through SQL Loader with direct path load.
SQL Loader control file:
load data
infile 'filelist.dat'
APPEND
into table einvoice
xmltype(XMLDATA)
(
filename filler char(120),
XMLDATA lobfile(filename) terminated by eof
)

filelist.dat:
test-000010203-0725.xml
test-000010928-0302.xml
test-000011230-0786.xml
test-000012016-0741.xml
test-000012757-1794.xml
test-000014551-1677.xml

... ...

3. Query sample
The we can query XML content  by using XQuery
SQL> select m.invoice_no
     2  from einvoice,
     3  xmltable('/A0401/Invoice/Main' passing object_value
     4  COLUMNS
     5  invoice_no varchar2(10) path 'InvoiceNumber') m;


Master-Detail Query sample
SQL>

create or replace view einvoice_master_detail_view
as
select m.invoice_no, m.seller_id, m.buyer_id, d.*
from einvoice,
xmltable('/A0401/Invoice' passing object_value
COLUMNS
invoice_no varchar2(10) path 'Main/InvoiceNumber',
seller_id varchar2(8) path 'Main/Seller/Identifier',
buyer_id varchar2(8) path 'Main/Buyer/Identifier',
details xmltype path 'Details'
) m,
xmltable('/Details/ProductItem' passing m.details
COLUMNS
item_no number(38) path 'SequenceNumber',
quantuty number(38) path 'Quantity',
unit_price number(38) path 'UnitPrice',
amount varchar2(10) path 'Amount',
description varchar2(20) path 'Description'
) d;


SQL>

SELECT
XMLQuery('/A0401/Invoice/Main' passing object_value returning content)
from einvoice p
where
XMLExists('/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE)


Choose invoice master where invoice number = "AA66279380"
SQL>

SELECT
XMLQuery('/A0401/Invoice/Main[InvoiceNumber="AA66279380"]'  passing object_value returning content)
from einvoice
where
XMLExists('/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE)

Choose invoice datails where invoice number = "AA66279380"
SQL>

select
XMLQuery('/A0401/Invoice[Main/InvoiceNumber="AA66279380"]/Details'  passing object_value returning content)
from einvoice
where
XMLExists('/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE)





SQL>

select count(*)
from einvoice,
xmltable('/A0401/Invoice' passing object_value
COLUMNS
invoice_no varchar2(10) path 'Main/InvoiceNumber')



SELECT count(*) FROM einvoice
WHERE XMLExists('$p/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE AS "p");



SELECT XMLQuery('$p/A0401/Invoice/Main' PASSING OBJECT_VALUE AS "p" RETURNING CONTENT)
FROM einvoice p
WHERE XMLExists('$p/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE AS "p");


SELECT XMLQuery('$p/A0401/Invoice/Main/InvoiceNumber' PASSING OBJECT_VALUE AS "p" RETURNING CONTENT)
FROM einvoice p
WHERE XMLExists('$p/A0401/Invoice/Main[InvoiceNumber="AA66279380"]' PASSING OBJECT_VALUE AS "p")



4. XML Index
Create index to improve query performance

SQL> create index invoice_ix on einvoice(object_value) indextype is XDB.XMLIndex;
Index created.