I have a doubt that whether External tables(Oracle) can be used in my application without actually creating a real table in Database. What I intend to do is that I have a .txt(around 25 GB) file and I want to run SQL queries directly on my .txt file without creating and loading the data in an actual Database table. So, my question is whether this is possible and if yes, then what are the drawbacks and benefits in doing so. Any help is highly appreciated.
Add a comment
|
2 Answers
It's possible, but the main drawback of external tables is that you can't create any index on such tables, so selecting data can be rather slow. Read this.
4 Comments
Najzero
rather slow is a really large understatement. Accessing 25gb of .txt is... madness
user2002522
@Mikhail Thanks for your input, as per my requirement I won't create indexes, also the contents of the table needs to be deleted and loaded with new data very frequently. So, I thought of saving the data load time, so is it still a good approach to use external table? And can you please share that how it is different from accessing a regular database table in terms of code syntax.
Mikhail
@user2002522 Therw would be no differences in query syntax, wherever you are quering external table or not. The only difference is in CREATE TABLE statement. I think, you should just test two cases: the time it takes to load data into regular table and quering it, and the time it would take to query external table. And, considering times you receive, decide, if it will be faster to load data or just query it as it is.
user2002522
@Mikhail Thanks again, and yes I would try out both the cases and would choose the better one.
You can apply parallel query, but of course cannot create indexes or modify the data, or gather a full set of statistics on it (you can collect some statistics using DBMS_STATS.GATHER_TABLE_STATS, which may help Oracle determine the best join strategy).
If the file was logically split into multiple files such that you could tell that data for January was in a particular file, then you could certainly have multiple external tables and possibly something fancy to emulate partitioned views, but it would be a pain.
Really you should load the data. If performance of that is a concern then use a direct path nologging load with indexes already defined on the table -- in case of media failure you can still reload the data so the logging is optional.
Comments
