Querying Microsoft Office Access 2007 data from an Oracle 11g Database Part 3 B
Notes:
a) In the above, you may notice that in the definition of the external table, we’ve amended the ADDRESS, WEB_PAGE, NOTES and ATTACHMENTS to be CLOB columns, instead of their original LONG column data type.
b) Ensure that the value for “location” is set to be the database directory and text file created previously
(i.e.(NWIND_DIR:’customers.txt’))
c) Check that the following parameters reference the same oracle directory:
default directory, badfile, discardfile and logfile
d) skip 1 allows you to skip the very first line of the text file which will contain the commands run for your original select statement:
“select * from “Customers”@Northwind;”
e) load when (COMPANY != “off”) is required, to exclude the line containing the “spool off” command, which will be at the end of the customers.txt file. If you don’t add this command, then this line will be added as a COMPANY within your new external table.
STEP N: Check that your new external table functions correctly.
——-
44) As the nwind_access user, first run the commands below to re-set your SQLPLUS* environment. Otherwise it will look as though you have ‘~’ values separating the data items in the table.
set head on feedback on trimspool off lines 132 buffer 132 pages 1000 space 1 colsep ” “
45) Then run a query, to check that the data can be accessed:
Select * from nwind_customers;
This should return the data from the table.
46) Although you can see the data, there are various performance-related issues with using external tables that you should be aware of.
a) By default, any queries will select all the data from the table. (i.e. if you look at an explain plan for your queries this will
show “EXTERNAL TABLE ACCESS FULL| NWIND_CUSTOMERS”.
b) You can’t create indexes on the external table. (ORA-30657: operation not supported on external organized table).
c) You can’t gather statistics on the external table.
(ORA-20000: Unable to analyze TABLE “NWIND_ACCESS”.”NWIND_CUSTOMERS”, sampling on external table is not supported).
If we just left things as they are, any reports that you ran against the data could take a long time to complete. A method of resolving this situation would be to create a materialized view based on the external table.
STEP O: Creating a Materialized view based on the external table
——-
47) As the nwind_access user, run the statement below:
create materialized view mv_nwind_customers
tablespace users
refresh complete
as select *
from nwind_customers;
If you’re thinking - “why can’t I just create the materialized view just based on a select * from “customers”@northwind? - if you try this, then you just see the error “ORA-00942: table or view does not exist”. You aren’t able to select from the table across the database link, as the table contains LONG columns.
48) Check that the materialized view can be refreshed correctly:
exec dbms_mview.refresh(’MV_NWIND_CUSTOMERS’,'C’);
Note: You need to perform a complete refresh, as you won’t be able to create a materialized view log on the external table. If you try, you’ll just see the error: “ORA-12014: table ‘NWIND_CUSTOMERS’ does not contain a primary key constraint.” Trying to add a primary key constraint just returns the error: “ORA-30657: operation not supported on external organized table.”
STEP P: Testing a refresh of data in the customers.txt file.
——-
49) We’ll now check that our configuration will pick up any changes to the data in the customers.txt file.
Open up the file C:\app\Administrator\admin\ORCL11\nwind_dump\customers.txt in Notepad.
50) You’ll notice that the first ID and COMPANY in the file is listed in the file as “1~Company A”
Edit this value, so that it appears as “1~Company TEST”
Save and close the file.
51) Manually refresh the materialized view again, as nwind_access:
exec dbms_mview.refresh(’MV_NWIND_CUSTOMERS’,'C’);
52) Then run a query to check that the data value has been changed in your materialized view:
select * from mv_nwind_customers where id=1;
53) In order to enhance the performance of queries against the materialized view, we’ll also gather some statistics:
exec dbms_stats.gather_table_stats(’NWIND_ACCESS’,'MV_NWIND_CUSTOMERS’);
Note: You could also create a primary key constraint and add indexes if you wanted to.
STEP Q: Creating external tables with DATE columns.
——-
Please see tomorrow for step 54 onwards …
We will release this as a full document if you would like to follow the series from start to finish 