Create Table Oracle SQL having a function call in a select statment

Hello,

I have an oracle SQL query that basically calls a function. When I just run the select statement I get results but when I try to do CREATE TABLE AS the function doesn't work. The dataset is huge about 3 million rows. How can I run this query and save the data in a temporary table? Please assist

drop table Ind purge;
create table Ind
as
select business_id,
, indicator(fk_pc_id) as ind
from table1 t1 inner join table2 t2  on trim(t1.business_id) = trim(t2.appl_id)

Open in new window

angel7170Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
When you say you get results:  Are you using a GUI like SQL Developer and you get results by seeing some data come back?

If so, SQL Developer buffers the data and returns it as you ask for it.  The first 50 rows may come back really "fast" but the query is far from done.  If you try to go to the end of the data, it will probably take a while.

The CREATE TABLE must return after it gets ALL THE DATA.
0
slightwv (䄆 Netminder) Commented:
Also using TRIM on the id columns will do a full table scan if there are indexes on just the id columns.  You would need a Function-Based Index using the TRIM to get index usage.
0
angel7170Author Commented:
I am using TOAD Oracle. Yes, it is returning the first 500 results with no issues but when I did CTAS, it created the table within few minutes but the column with the function doesn't have the data. Not sure why it would create the table but not populate the data for the IND column.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

angel7170Author Commented:
Not sure how I should do a Function-Based index. Any help please
0
slightwv (䄆 Netminder) Commented:
I misunderstood the question.  If you are happy with creating the table in a few minutes, no need to create an index for this process.

There are a LOT of examples out there on them.  If you would like to experiment with them, I would read up on them.

Here is a decent post on them:
https://oracle-base.com/articles/8i/function-based-indexes

>> Not sure why it would create the table but not populate the data for the IND column.

I cannot think of a reason where a select using the function would work and running the exact same select as the exact same user wouldn't work in a CTAS statement.

You could try pre-creating the table and insert into as select?  I would expect that to fail as well but it's a test.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
angel7170Author Commented:
Thank you. I am trying the INSERT statement. Hope that works.
0
slightwv (䄆 Netminder) Commented:
If you aren't sure, why close the question?  I really don't think it will do anything any different.

If it doesn't do anything, post back and I'll re-open the question and we can continue.
0
angel7170Author Commented:
Sure. It worked for 1000 records. Not sure what the difference in CTAS and INSERT. But not sure whether this is the appropriate method to run for million of records....
0
slightwv (䄆 Netminder) Commented:
>> Not sure what the difference in CTAS and INSERT

I cannot think of anything.  I would triple check to make sure you were running the CTAS as the user you were thinking you were running it as and make sure you were running the function in the schema you thought you were running it as?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.