Home » RDBMS Server » Server Utilities » Creating Physical table by using External tables (Oracle 10g on Linux (Debian))
Creating Physical table by using External tables [message #338263] Mon, 04 August 2008 06:10 Go to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Hi,
I want to use External table, but also need to populate physical table on the basis of External tables, but after creation of Physical table on the basis of external table when we change file of external table and do select * from external_table,contents are being chenged but what about physical table, do i need to drop physical table and recreate it by using external table again or is there any other way that Physical table change itself according to External table.

[Updated on: Mon, 04 August 2008 06:16]

Report message to a moderator

Re: Creating Physical table by using External tables [message #338272 is a reply to message #338263] Mon, 04 August 2008 06:56 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How did you populate "physical" table? I guess that it was something like
INSERT INTO physical_table SELECT * FROM external_table

Now, if external table contents changed (i.e. there's been a new file created), what would modify contents of an Oracle table, unless you explicitly do it? (OK, you could do that using a job, but - someone, somehow, has to do that).
Re: Creating Physical table by using External tables [message #338287 is a reply to message #338272] Mon, 04 August 2008 08:00 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
So i need to drop table and then recreate it again with indexes and primary key on daily basis.

Re: Creating Physical table by using External tables [message #338299 is a reply to message #338287] Mon, 04 August 2008 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Drop and recreate which table?
There is no index on external table.
Executing the query Littlefoot posted does not require to drop the table.

Regards
Michel
Re: Creating Physical table by using External tables [message #338310 is a reply to message #338287] Mon, 04 August 2008 09:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
When you change the data file for the external table, that automatically changes what you get when you select from the external table, as the external table is only a pointer, a method of access to that data file.

If you want to change the data in a "physical" table, a regular database table that actually stores data that you have inserted, then you will need to truncate that "physical" table and insert into it again by selecting from the external table again. You just need to truncate and insert. You do not need to drop and recreate.
Re: Creating Physical table by using External tables [message #338317 is a reply to message #338310] Mon, 04 August 2008 10:01 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Thank you Barbara,

Is there any other solution?

Truncating a 10 large table (Having more than 200,000 records Average) on daily basis is feasable.

Re: Creating Physical table by using External tables [message #338319 is a reply to message #338317] Mon, 04 August 2008 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course it is feasible. 200,000 is tiny, 200,000,000 is large... for the moment.

Regards
Michel

[Updated on: Mon, 04 August 2008 10:08]

Report message to a moderator

Re: Creating Physical table by using External tables [message #338320 is a reply to message #338263] Mon, 04 August 2008 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Creating Physical table by using External tables [message #338376 is a reply to message #338317] Mon, 04 August 2008 15:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Why do you need a "physical" table at all? Why can't you just select directly from the external table? What exactly are the requirements? Does the location change at predictable times? If so, you can use a materialized view which can be set to automatically refresh at certain times. I have provided a brief demo using a materialized view below.

-- directory object and external table:
SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY my_dir as 'C:\oracle11g'
  2  /

Directory created.

SCOTT@orcl_11g> CREATE TABLE external_table
  2    (col1   VARCHAR2(8),
  3  	col2   NUMBER)
  4  ORGANIZATION EXTERNAL
  5    (TYPE ORACLE_LOADER
  6  	DEFAULT DIRECTORY my_dir
  7  	ACCESS PARAMETERS
  8  	  (RECORDS DELIMITED BY NEWLINE
  9  	   LOGFILE 'ext_tab.log'
 10  	   FIELDS TERMINATED BY ','
 11  	   LDRTRIM
 12  	   MISSING FIELD VALUES ARE NULL)
 13  	LOCATION ('old_file.txt'))
 14  REJECT LIMIT UNLIMITED
 15  /

Table created.


-- materialized view with initial data:
SCOTT@orcl_11g> CREATE MATERIALIZED VIEW physical_mview AS
  2  SELECT col1, col2 FROM external_table
  3  /

Materialized view created.

SCOTT@orcl_11g> SELECT * FROM physical_mview
  2  /

COL1           COL2
-------- ----------
old data         10
old data         20
old data         30
old data         40


-- alter file location and refresh materialized view (could be scheduled):
SCOTT@orcl_11g> ALTER TABLE external_table LOCATION ('new_file.txt')
  2  /

Table altered.

SCOTT@orcl_11g> EXEC DBMS_MVIEW.REFRESH ('physical_mview')

PL/SQL procedure successfully completed.


-- new data in materialized view:
SCOTT@orcl_11g> SELECT * FROM physical_mview
  2  /

COL1           COL2
-------- ----------
new data         11
new data         21
new data         31
new data         41

SCOTT@orcl_11g> 


Re: Creating Physical table by using External tables [message #338427 is a reply to message #338376] Tue, 05 August 2008 01:00 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice trick. ./fa/2115/0/

Regards
Michel
Previous Topic: sql *loader
Next Topic: using 8i dump to import into 9i
Goto Forum:
  


Current Time: Sat Dec 10 03:01:20 CST 2016

Total time taken to generate the page: 0.06275 seconds