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 Go to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 21623
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 Go to previous messageGo to next message
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 Razz

[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 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
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
Cool 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 #501277 is a reply to message #501274] Mon, 28 March 2011 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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....

What about changing the name of the file each day?

Regards
Michel
Re: SQLLOADER: views linked to External tables [message #501286 is a reply to message #501277] Mon, 28 March 2011 07:12 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #501294 is a reply to message #501290] Mon, 28 March 2011 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it possible to index a materialized view?

Yes, but a materialized view is... materialized and so it is the underlying table that you actually index.

Quote:
If you compare those 3 solutions:

To achieve what?

Regards
Michel
Re: SQLLOADER: views linked to External tables [message #501295 is a reply to message #501294] Mon, 28 March 2011 07:39 Go to previous messageGo to next message
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
Junior Member
So you can't index a materialized view built from external tables right?

The final 2 tables or views are used to obtain user data for an authentification on an application.

[Updated on: Mon, 28 March 2011 09:30]

Report message to a moderator

Re: SQLLOADER: views linked to External tables [message #501332 is a reply to message #501295] Mon, 28 March 2011 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So you can't index a materialized view built from external tables right?

Yes you can.

Regards
Michel
Re: SQLLOADER: views linked to External tables [message #501387 is a reply to message #501332] Mon, 28 March 2011 13:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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>

Re: SQLLOADER: views linked to External tables [message #501392 is a reply to message #501387] Mon, 28 March 2011 14:29 Go to previous message
Michel Cadot
Messages: 67546
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes I prefer the old name of "snapshot", I don't knw why Oracle changed it to the misleading "materialized view".
"Snapshot" was clear.

Regards
Michel
Previous Topic: Probl improving conventional path load, direct=false, with number of commits and bindsize/readsize
Next Topic: Transferring changed data from Database A to B by Data pump
Goto Forum:
  


Current Time: Sun Nov 29 05:24:52 CST 2020