Home » RDBMS Server » Server Utilities » SQLLOADER: views linked to External tables (10G)
| SQLLOADER: views linked to External tables [message #501269] |
Mon, 28 March 2011 05:13  |
 |
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
|
Junior Member |
|
|
Hello again everyone,
i just posted another topic where i heard about external table and i had a few questions concerning them. I thought it was best to create a new topic than to continue on the other one...
I noticed that to create an external table the CTL is like this:
CREATE TABLE emp_load (FIELDS description)
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (RECORDS FIXED 62 FIELDS (employee_number CHAR(2),
employee_dob CHAR(20),
employee_last_name CHAR(18),
employee_first_name CHAR(11),
employee_middle_name CHAR(11)))
LOCATION ('info.dat'));
1) This creates an external table, but, is it possible to Create a normal table in a CTL file?
For physical tables, the table has to exist right?
2) if you create a view linked to 2 external tables and if the CSV files are updated each day, the external tables will be updated automatically, and the view will be updated as well?
3) Can't there be any synchronisation problems?
4) What happens if a select request (or someone requests on the view) while the CSV file is being updated?
5) Is there anyway you can protect the accesses from those tables/views when the CSVs are being updated?
6) Is it possible to create an index on these sort of tables?
7) Is it possible to index a view?
8) Are external tables visible on a tool like sql developper?
Thanks a lot to everyone!
[Updated on: Mon, 28 March 2011 05:29] by Moderator Report message to a moderator |
|
|
|
| Re: SQLLOADER: views linked to External tables [message #501272 is a reply to message #501269] |
Mon, 28 March 2011 05:24  |
 |
Littlefoot
Messages: 21782
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
1) What do you mean by a "normal table in a CTL file"? An external table is capable of "reading" contents of a file stored on file system (i.e. in a directory). "Normal" tables contain data themselves, and can't read files.
2) A view is a stored query. It will display information available to its SELECT statement, it doesn't contain any data. So, when source changes, view changes as well.
3 - 5) Simply don't "update" a file when it is on a location described by the "directory". Prepare the file elsewhere, and - once you're done - copy it to a directory.
6 - 8) It is easy to try. So - try it!
[Updated on: Mon, 28 March 2011 05:25] Report message to a moderator |
|
|
|
| Re: SQLLOADER: views linked to External tables [message #501274 is a reply to message #501272] |
Mon, 28 March 2011 05:28 |
|
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
|
Junior Member |
|
|
i meant:
Can you create a table in a database from a CTL file (a table not external)?
I think that from a STL, you can just describe how to insert data in an existing table, and that's it right?
What do you mean by "once i'm done"?
The file is delivered somewhere each day, probably in the same directory as the one linked to an external table.
So there will be a moment where the table will be unavailable i guess.
even if i copy it elsewhere, there will be a moment where i'll have to replace the existing one by the new one....
Thanks a lot 
[Updated on: Mon, 28 March 2011 05:28] Report message to a moderator |
|
|
|
| Re: SQLLOADER: views linked to External tables [message #501276 is a reply to message #501272] |
Mon, 28 March 2011 05:34 |
 |
Michel Cadot
Messages: 68501
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
1) No and it is meaningless.
2) Yes a view is just a stored query that is a way to see other things (tables, views...)
3) No, as there is no synchronisation in the same way what you see through your window is "synchronized" with what is in the other side.
4) It sees what it is when it reads it (in the same way what you see through your window)
5) No (think about your window)
6) No
7) No
 Yes, with any SQL tool as you query the external table like any other one.
Regards
Michel
[Updated on: Mon, 28 March 2011 05:35] Report message to a moderator |
|
|
|
|
|
| Re: SQLLOADER: views linked to External tables [message #501286 is a reply to message #501277] |
Mon, 28 March 2011 07:12 |
|
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
|
Junior Member |
|
|
Thanks everyone (Merci pour le cadot - and sorry for the bad joke :S)
Quote:1) No and it is meaningless.
What's meaningless? It would be interesting to create the table at the same time as you load the data from the csv....
Concerning the change of name each day...it's possible.
I guess a batch can change the name of the file linked to a CSV file. that would be great actually...
I was just affraid because, if the file is delivered in the same directory, it would probably erase and replace the existing file; so i thought that if a query was executed on the view while the new file is downloading there would be a problem.
Is it possible to index a materialized view?
Quote:6 - 8) It is easy to try. So - try it!
It probably is, and i would if i could.... Sorry for the trouble
Thanks a lot everyone!
[Updated on: Mon, 28 March 2011 07:13] Report message to a moderator |
|
|
|
| Re: SQLLOADER: views linked to External tables [message #501290 is a reply to message #501286] |
Mon, 28 March 2011 07:30 |
|
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
|
Junior Member |
|
|
If you compare those 3 solutions:
1) You have 3 CSVs, from which you create 3 external tables. From those 3 external tables, you create 2 views.
2) You have 3 CSVs, from which you create 3 external tables. From those 3 external tables you create 2 tables.
3) You have 3 CSVs, from which you create 3 tables. From those 3 tables you create 2 other tables. Then, you drop the first 3 tables created from the CSVs.
Which solution seems to be a most effective? (fast....)
[Updated on: Mon, 28 March 2011 07:31] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: SQLLOADER: views linked to External tables [message #501387 is a reply to message #501332] |
Mon, 28 March 2011 13:56 |
 |
Barbara Boehmer
Messages: 9058
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I have provided an example below for clarification. With external tables, there is no control (.ctl) file. The external table is created using sql commands. An external table is just a pointer, through which you access the data in a text file. In the example below, I have provided two text files (test1.dat and test2.dat) with different data. I have created an external table (list_ext) using test1.dat, then created a materialized view (list) on the external_table list_ext, then created an index on the materialized view. Then I have used alter table ... location ... to change the data accessed by list_ext from test1.dat to test2.dat, then refreshed the view, so that the view then reflects the new data. If you want an index on the view, then it needs to be a materialized view. In general, the more intermediary steps that you can eliminate the better, so I see no point in creating and dropping intermediary tables. However, tables have fewer restrictions and can be easier to work with than views, so I might be inclined to create tables instead of views. It is important to note that each time you change the contents or location of the data file used by an external table, it will replace, not add to, existing data. If you want to add to existing data, then you should use tables instead of views.
-- test1.dat:
1;FRANCE ;2
3;UNITED STATES ;4
-- test2.dat:
5;FRANCE ;6
7;UNITED STATES ;8
SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl_11gR2> CREATE TABLE list_ext
2 (col1 NUMBER,
3 country VARCHAR2 (15),
4 col3 NUMBER)
5 ORGANIZATION external
6 (TYPE oracle_loader
7 DEFAULT DIRECTORY my_dir
8 ACCESS PARAMETERS
9 (FIELDS TERMINATED BY ";"
10 ("COL1", "COUNTRY", "COL3"))
11 location ('test1.dat'))
12 /
Table created.
SCOTT@orcl_11gR2> SELECT * FROM list_ext
2 /
COL1 COUNTRY COL3
---------- --------------- ----------
1 FRANCE 2
3 UNITED STATES 4
2 rows selected.
SCOTT@orcl_11gR2> CREATE MATERIALIZED VIEW list
2 AS
3 SELECT col1,
4 SUBSTR (country, 1, 6) country,
5 col3
6 FROM list_ext
7 WHERE country LIKE 'FRANCE%'
8 /
Materialized view created.
SCOTT@orcl_11gR2> SELECT * FROM list
2 /
COL1 COUNTRY COL3
---------- ------------------------ ----------
1 FRANCE 2
1 row selected.
SCOTT@orcl_11gR2> CREATE INDEX list_country
2 ON list (country)
3 /
Index created.
SCOTT@orcl_11gR2> ALTER TABLE list_ext LOCATION ('test2.dat')
2 /
Table altered.
SCOTT@orcl_11gR2> SELECT * FROM list_ext
2 /
COL1 COUNTRY COL3
---------- --------------- ----------
5 FRANCE 6
7 UNITED STATES 8
2 rows selected.
SCOTT@orcl_11gR2> SELECT * FROM list
2 /
COL1 COUNTRY COL3
---------- ------------------------ ----------
1 FRANCE 2
1 row selected.
SCOTT@orcl_11gR2> EXEC DBMS_MVIEW.REFRESH ('list')
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> SELECT * FROM list
2 /
COL1 COUNTRY COL3
---------- ------------------------ ----------
5 FRANCE 6
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Sep 25 22:06:19 CDT 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
