Home » SQL & PL/SQL » SQL & PL/SQL » Pulling hair out trying to use table function (Oracle 11)
Pulling hair out trying to use table function [message #672377] |
Fri, 12 October 2018 15:38 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/69fecc1b258e55cd2dcb55d99a9fcb5e?s=64&d=mm&r=g) |
jbarker36
Messages: 7 Registered: October 2018
|
Junior Member |
|
|
Hello All. I am newer to using PL/SQL so forgive me if some of what I am describing is not the right terminology.
I was able to speed up some queries from run 30-45 minutes to less than 5 by narrowing down data to search through to a table I create in our database and then running the various queries against it and then dropping the created table each time. I know this is not good to do in the system each time so I'm trying to change this and use the table function to hold the temporary table data to run queries against when it is needed. Here is what I have done so far (work with samples collected):
CREATE OR REPLACE TYPE samp_obj AS OBJECT
( sample_id NUMBER (10),
user_sampleid VARCHAR2 (40),
sample_type VARCHAR2 (40),
condition VARCHAR2 (20)
);
/
Type SAMP_OBJ compiled
CREATE OR REPLACE TYPE samp_list AS TABLE OF samp_obj;
/
Type SAMP_LIST compiled
When I try to create the funtion is where I can't get past the Oracle - ORA-00947: not enough values
Once the data is there then I want to call the function. (The in_time_value will be like this '%-AUG-18')
CREATE OR REPLACE FUNCTION GET_SAMPLES_FOR_THE_MONTH(in_time_value varchar2)
RETURN samp_list
IS
samp_rec samp_list;
BEGIN
SELECT
s.sample_id,
s.user_sampleid,
s.sample_type,
s.condition
INTO samp_rec FROM nais_samples s
WHERE sample_id in (
SELECT ssa.sample_id
FROM scwas_sample_attributes ssa
WHERE name = 'COLLECTION DATE'
and time_value like in_time_value)
and s.condition not in ('REJECTED')
and s.sample_type not like ('NY STATE PROF%')
and s.sample_type not like ('ABSOLUTE%')
and s.sample_type not like ('WPS%ROOM%');
RETURN samp_rec;
END;
/
Function GET_SAMPLES_FOR_THE_MONTH compiled
LINE/COL ERROR
--------- -------------------------------------------------------------
6/3 PL/SQL: SQL Statement ignored
11/17 PL/SQL: ORA-00947: not enough values
Errors: check compiler log
Thank you for your help. Any suggestions on better ways to do this is also accepted. I was looking at packages and really was confused so I figured I would start with using a function.
[mod-edit: code tags added by bb; next time please add them yourself]
[Updated on: Fri, 12 October 2018 18:14] by Moderator Report message to a moderator
|
|
|
|
Re: Pulling hair out trying to use table function [message #672381 is a reply to message #672377] |
Fri, 12 October 2018 19:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9096 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
In the following demonstration I have added tables and data for testing and demonstration and suggested indexes. I have made minimal corrections to your function by adding samp_obj and bulk collect where indicated. I have also demonstrated the usage of the table function, timing, and autotrace. Since I only used two rows of data it is not a good test of index usage or speed. I just demonstrated the process of doing so, so that you can use the same methods on your larger tables.
-- added tables and data for testing:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE nais_samples
2 (sample_id NUMBER (10),
3 user_sampleid VARCHAR2 (40),
4 sample_type VARCHAR2 (40),
5 condition VARCHAR2 (20))
6 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT ALL
2 INTO nais_samples VALUES (1, 'A', 'sample_type1', 'condition1')
3 INTO nais_samples VALUES (2, 'B', 'sample_type2', 'condition2')
4 SELECT * FROM DUAL
5 /
2 rows created.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE scwas_sample_attributes
2 (sample_id NUMBER (10),
3 name VARCHAR2 (15),
4 time_value VARCHAR2 ( 9))
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT ALL
2 INTO scwas_sample_attributes VALUES (1, 'COLLECTION DATE', '01-AUG-18')
3 INTO scwas_sample_attributes VALUES (2, 'COLLECTION DATE', '02-AUG-18')
4 SELECT * FROM DUAL
5 /
2 rows created.
-- suggested indexes:
SCOTT@orcl_12.1.0.2.0> CREATE INDEX ns_idx ON nais_samples (sample_id, condition, sample_type, user_sampleid)
2 /
Index created.
SCOTT@orcl_12.1.0.2.0> CREATE INDEX ssa_idx ON scwas_sample_attributes (sample_id, name, time_value)
2 /
Index created.
-- types you provided:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TYPE samp_obj AS OBJECT
2 (sample_id NUMBER (10),
3 user_sampleid VARCHAR2 (40),
4 sample_type VARCHAR2 (40),
5 condition VARCHAR2 (20));
6 /
Type created.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TYPE samp_list AS TABLE OF samp_obj;
2 /
Type created.
-- modified function (added samp_obj and bulk collect to query):
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION get_samples_for_the_month
2 (in_time_value IN VARCHAR2)
3 RETURN samp_list
4 AS
5 samp_rec samp_list;
6 BEGIN
7 -- added samp_obj and bulk collect to query:
8 SELECT samp_obj (s.sample_id, s.user_sampleid, s.sample_type, s.condition)
9 BULK COLLECT
10 INTO samp_rec
11 FROM nais_samples s
12 WHERE sample_id in
13 (SELECT ssa.sample_id
14 FROM scwas_sample_attributes ssa
15 WHERE name = 'COLLECTION DATE'
16 and time_value like in_time_value)
17 and s.condition not in ('REJECTED')
18 and s.sample_type not like ('NY STATE PROF%')
19 and s.sample_type not like ('ABSOLUTE%')
20 and s.sample_type not like ('WPS%ROOM%');
21 RETURN samp_rec;
22 END get_samples_for_the_month;
23 /
Function created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
-- query using table function (column formats added to shorten line length for easier reading)
SCOTT@orcl_12.1.0.2.0> COLUMN user_sampleid FORMAT A13
SCOTT@orcl_12.1.0.2.0> COLUMN sample_type FORMAT A12
SCOTT@orcl_12.1.0.2.0> SET TIMING ON
SCOTT@orcl_12.1.0.2.0> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (get_samples_for_the_month ('%-AUG-18'))
2 /
SAMPLE_ID USER_SAMPLEID SAMPLE_TYPE CONDITION
---------- ------------- ------------ --------------------
1 A sample_type1 condition1
2 B sample_type2 condition2
2 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 554399883
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| GET_SAMPLES_FOR_THE_MONTH | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
SCOTT@orcl_12.1.0.2.0> SET AUTOTRACE OFF
SCOTT@orcl_12.1.0.2.0> SET TIMING OFF
|
|
|
Re: Pulling hair out trying to use table function [message #672382 is a reply to message #672381] |
Fri, 12 October 2018 19:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9096 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I forgot to mention that you should test the query used in the function separately, so that you can see the index usage, as shown below.
Also, I assume '%-AUG-18' is a date value stored as varchar2. It would be better to store it as a date and compare it as a date, including the full year.
SCOTT@orcl_12.1.0.2.0> SET TIMING ON
SCOTT@orcl_12.1.0.2.0> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_12.1.0.2.0> SELECT samp_obj (s.sample_id, s.user_sampleid, s.sample_type, s.condition)
2 FROM nais_samples s
3 WHERE sample_id in
4 (SELECT ssa.sample_id
5 FROM scwas_sample_attributes ssa
6 WHERE name = 'COLLECTION DATE'
7 and time_value like '%-AUG-18')
8 and s.condition not in ('REJECTED')
9 and s.sample_type not like ('NY STATE PROF%')
10 and s.sample_type not like ('ABSOLUTE%')
11 and s.sample_type not like ('WPS%ROOM%');
SAMP_OBJ(S.SAMPLE_ID,S.USER_SAMPLEID,S.SAMPLE_TYPE,S.CONDITION)(SAMPLE_ID, USER_SAMPLEID, SAMPLE_TYPE, CONDITION)
----------------------------------------------------------------------------------------------------------------------------------
SAMP_OBJ(1, 'A', 'sample_type1', 'condition1')
SAMP_OBJ(2, 'B', 'sample_type2', 'condition2')
2 rows selected.
Elapsed: 00:00:00.22
Execution Plan
----------------------------------------------------------
Plan hash value: 3708426559
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 194 | 2 (0)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 2 | 194 | 2 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN| NS_IDX | 2 | 138 | 1 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN| SSA_IDX | 2 | 56 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SAMPLE_ID"="SSA"."SAMPLE_ID")
2 - filter("S"."CONDITION"<>'REJECTED' AND "S"."SAMPLE_TYPE" NOT
LIKE 'NY STATE PROF%' AND "S"."SAMPLE_TYPE" NOT LIKE 'ABSOLUTE%' AND
"S"."SAMPLE_TYPE" NOT LIKE 'WPS%ROOM%')
3 - access("NAME"='COLLECTION DATE')
filter("TIME_VALUE" IS NOT NULL AND "NAME"='COLLECTION DATE' AND
"TIME_VALUE" LIKE '%-AUG-18')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SCOTT@orcl_12.1.0.2.0> SET AUTOTRACE OFF
SCOTT@orcl_12.1.0.2.0> SET TIMING OFF
|
|
|
|
|
Re: Pulling hair out trying to use table function [message #672385 is a reply to message #672383] |
Fri, 12 October 2018 20:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9096 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
jbarker36 wrote on Fri, 12 October 2018 18:30Hello Barbara,
Thank you for the detailed PL/SQL but I am a little confused. The nais_samples table and the scwas_sample_attributes tables already exist and have millions of records in them. I normally create a table holding the 4 columns of data output from the select statement that I have in the function. I was attempting to fill the samp_list type table instead of creating a table and then dropping it.
Joleen
I understand that you already have the nais_samples table and the scwas_sample_attributes table, but we don't, as you did not provide the DDL for them and it is not possible to test the code or demonstrate it without them, so I create some and entered some data, just so that I could test and demonstrate the code. As long as your tables have the same columns referenced in your function, along with any other columns, then the same code should work on your huge tables. I was not suggesting that you should create additional tables or types. I don't know what indexes you already have on those tables, so I offered some suggested indexes. The indexes can have the most drastic affect on your query time.
The function that I used is just a slight correction of what you seemed to be trying to do. It selects the data using bulk collect, which is faster than just a standard select, into the same samp_rec variable of samp_list type that you were selecting into. You can then select from the function using the table function, which is what your post title and comments seemed to suggest that you were trying to do.
|
|
|
|
|
|
|
Re: Pulling hair out trying to use table function [message #672435 is a reply to message #672434] |
Mon, 15 October 2018 08:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
You keep ignoring forum rules.
1. Post CREATE TABLLE nais_samples statement
2. Post INSERT statements to populate sample data into nais_samples table
3. Post CREATE objects and CREATE function code.
4. Post SQL statement you used to call table function along with errors.
SY.
|
|
|
Re: Pulling hair out trying to use table function [message #672436 is a reply to message #672434] |
Mon, 15 October 2018 09:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9096 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
jbarker36 wrote on Mon, 15 October 2018 06:12I have a new issue with this. I'm not sure if I should start a new question. I am still getting the same error:
ORA-00932: inconsistent datatypes: expected UDT got CHAR
But now it is from a different spot. My function was crested successfully but I need to pass the value '%-MAR-18' to it. I need the tics to pass as well.
SELECT *
FROM TABLE (samp_list('%-MAR-18') )
Normally when I created a table in the system the code looked like this:
and time_value like '%-MAR-18')
But I am trying to pass this in and used a variable:
and time_value like in_time_value)
When you use the function, it inserts rows into the variable samp_rec of the type samp_list, not into samp_list itself.
You need to use the function name get_samples_for_the_month in your call, not the table type samp_list.
SELECT * FROM TABLE (get_samples_for_the_month ('%-AUG-18'));
As others have said, in order for us to see exactly what you are doing, you need to post a complete run of everything from SQL*Plus as I originally did, including the tables, a few rows of sample data, the types, the function, and how you are calling the function.
|
|
|
|
Re: Pulling hair out trying to use table function [message #672440 is a reply to message #672435] |
Mon, 15 October 2018 10:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
typically the only thing you have to do is ad an index on the data you want to search on. I have tables with hundreds of millions of rows and by using the correct index it returns the data that I want in less then 1 second. Don't set up a new table or memory table when there is no need. Also you query is not using joining or function based indexes
SELECT S.Sample_id,
S.User_sampleid,
S.Sample_type,
S.Condition
FROM Nais_samples S, Scwas_sample_attributes Ssa
WHERE Sample_id = Ssa.Sample_id
AND Ssa.Name = 'COLLECTION DATE'
AND TRUNC(Ssa.Time_value, 'MONTH') = TO_DATE(In_time_value, 'MON-RR')
AND S.Condition NOT IN ('REJECTED')
AND S.Sample_type NOT LIKE ('NY STATE PROF%')
AND S.Sample_type NOT LIKE ('ABSOLUTE%')
AND S.Sample_type NOT LIKE ('WPS%ROOM%');
THE SUGGESTED INDEXS ARE
CREATE INDEX scwas_sample_attributes_I1 ON scwas_sample_attributes(SAMPLE_ID,NAME,TRUNC(TIME_VALUE,'MONTH'));
CREATE INDEX Nais_samples_I1 ON Nais_samples(SAMPLE_ID,CONDITION,SAMPLE_TYPE);
|
|
|
|
Re: Pulling hair out trying to use table function [message #672499 is a reply to message #672496] |
Tue, 16 October 2018 13:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/06f726c8b1b832b32c066e2ddee7d54e?s=64&d=mm&r=g) |
JPBoileau
Messages: 88 Registered: September 2017
|
Member |
|
|
There's really nothing wrong with creating a temporary table if an identical subset of data is going to be used multiple times. For instance, many companies will require reports to be run for a their business activity for one month. Of course, you must take into account the amount of space that will be used.
Unrelated code used only as an example:
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SELECT
PD.PRINT_IND,
COUNT(*)
FROM
SERVICE_TRANSACTION ST,
PRINT_DETAIL PD
WHERE
ST.STRANS_DATE >= TO_DATE('01-AUG-2018', 'DD-MON-YYYY') AND
ST.STRANS_DATE < TO_DATE('01-SEP-2018', 'DD-MON-YYYY') AND
PD.STRANS_NO = ST.STRANS_NO
GROUP BY
PD.TITLE_PRINT_IND;
T COUNT(*)
- ----------
4921
B 121464
O 27313
Elapsed: 00:00:31.21
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
CREATE GLOBAL TEMPORARY TABLE STRANS_AUG2018 (
STRANS_NO NUMBER(9)
)
ON COMMIT PRESERVE ROWS;
INSERT INTO STRANS_AUG2018 (
SELECT
STRANS_NO
FROM
SERVICE_TRANSACTION ST
WHERE
ST.STRANS_DATE >= TO_DATE('01-AUG-2018', 'DD-MON-YYYY') AND
ST.STRANS_DATE < TO_DATE('01-SEP-2018', 'DD-MON-YYYY') );
COMMIT;
Elapsed: 00:00:16.24
DEV1> COMMIT;
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SELECT
PD.PRINT_IND,
COUNT(*)
FROM
STRANS_AUG2018 ST,
PRINT_DETAIL PD
WHERE
PD.STRANS_NO = ST.STRANS_NO
GROUP BY
PD.PRINT_IND;
T COUNT(*)
- ----------
4921
B 121464
O 27313
Elapsed: 00:00:12.12
JP
|
|
|
Goto Forum:
Current Time: Mon May 20 07:46:01 CDT 2024
|