ram_0218
asked on
order by causing issues with str2tbl..
this query:
select * from table where col in (select * from table(str2tbl('something,s omething2' ))) and rownum < 50
--> str2tbl gets called once
select * from table where col in (select * from table(str2tbl('something,s omething2' ))) and rownum < 50
order by somecolumn
--> executes as many as records getting retrieved..in this case str2tbl gets called 50 times..
what's wrong?
select * from table where col in (select * from table(str2tbl('something,s
--> str2tbl gets called once
select * from table where col in (select * from table(str2tbl('something,s
order by somecolumn
--> executes as many as records getting retrieved..in this case str2tbl gets called 50 times..
what's wrong?
ASKER
sorry.. the query is like how you have written.. i was actually moving around order by to inside removing outer query..
the query you posted is executing str2tbl function 50 times..
if i remove the order by, then the str2tbl function gets called only once..
the query you posted is executing str2tbl function 50 times..
if i remove the order by, then the str2tbl function gets called only once..
s elect * from table, table(str2tbl('something,s omething2' )) x
where table.col = x.column_value
and rownum < 50
order by somecolumn
where table.col = x.column_value
and rownum < 50
order by somecolumn
http:#37032631 will return the first 50 rows that happen to match then will sort them (so, essentially a semi-random set, then ordered)
if you meant to sort first then return the first 50 rows try this...
select * from (
select t.* from table t, table(str2tbl('something,s omething2' )) x
where t.col = x.column_value
order by somecolumn)
where rownum < 50
if you meant to sort first then return the first 50 rows try this...
select * from (
select t.* from table t, table(str2tbl('something,s
where t.col = x.column_value
order by somecolumn)
where rownum < 50
ASKER
x.column_value
where's the column_value specified?
where's the column_value specified?
column_value is the name of the value returned by the TABLE function
literally "column_value" that's not pseudocode
you can test by doing the TABLE function by itself
select * from table(str2tbl('a,b,c'))
literally "column_value" that's not pseudocode
you can test by doing the TABLE function by itself
select * from table(str2tbl('a,b,c'))
ASKER
nope.. it's calling the function same number of times..
s elect * from table, table(str2tbl('something,s omething2' )) x
where table.col = x.column_value
and rownum < 50
order by somecolumn
calling str2tbl 50 times..
s elect * from table, table(str2tbl('something,s
where table.col = x.column_value
and rownum < 50
order by somecolumn
calling str2tbl 50 times..
how did you determine that it was executing more than once?
my explain plan shows the "pickler" called once and then hash joined
it's not in a nested loop
it's not in a nested loop
1 Plan hash value: 1644228069
2
3 ---------------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
5 ---------------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 49 | 8967 | | 10731 (1)| 00:02:09 |
7 | 1 | SORT ORDER BY | | 49 | 8967 | 45M| 10731 (1)| 00:02:09 |
8 |* 2 | COUNT STOPKEY | | | | | | |
9 |* 3 | HASH JOIN | | 243K| 42M| | 965 (1)| 00:00:12 |
10 | 4 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 8168 | 16336 | | 29 (0)| 00:00:01 |
11 |* 5 | HASH JOIN RIGHT OUTER | | 3461 | 611K| | 935 (1)| 00:00:12 |
12 | 6 | TABLE ACCESS FULL | SEG$ | 6939 | 76329 | | 56 (0)| 00:00:01 |
13 |* 7 | HASH JOIN RIGHT OUTER | | 3061 | 508K| | 878 (1)| 00:00:11 |
14 | 8 | INDEX FULL SCAN | I_USER2 | 116 | 464 | | 1 (0)| 00:00:01 |
15 |* 9 | HASH JOIN OUTER | | 3061 | 496K| | 877 (1)| 00:00:11 |
16 |* 10 | HASH JOIN | | 3061 | 472K| | 791 (1)| 00:00:10 |
17 | 11 | TABLE ACCESS FULL | USER$ | 116 | 1856 | | 4 (0)| 00:00:01 |
18 |* 12 | HASH JOIN | | 3061 | 424K| | 786 (1)| 00:00:10 |
19 |* 13 | HASH JOIN OUTER | | 3061 | 316K| | 514 (1)| 00:00:07 |
20 |* 14 | HASH JOIN | | 3061 | 301K| | 427 (1)| 00:00:06 |
21 | 15 | NESTED LOOPS | | 13 | 923 | | 7 (15)| 00:00:01 |
22 |* 16 | HASH JOIN | | 1 | 68 | | 1 (100)| 00:00:01 |
23 |* 17 | FIXED TABLE FULL | X$KSPPI | 1 | 55 | | 0 (0)| 00:00:01 |
24 | 18 | FIXED TABLE FULL | X$KSPPCV | 100 | 1300 | | 0 (0)| 00:00:01 |
25 | 19 | TABLE ACCESS FULL | TS$ | 13 | 39 | | 6 (0)| 00:00:01 |
26 |* 20 | TABLE ACCESS FULL | TAB$ | 3061 | 91830 | | 420 (0)| 00:00:06 |
27 | 21 | INDEX FAST FULL SCAN | I_OBJ1 | 79444 | 387K| | 85 (0)| 00:00:02 |
28 |* 22 | TABLE ACCESS FULL | OBJ$ | 79444 | 2792K| | 272 (1)| 00:00:04 |
29 | 23 | INDEX FAST FULL SCAN | I_OBJ1 | 79444 | 620K| | 85 (0)| 00:00:02 |
30 ---------------------------------------------------------------------------------------------------------
31
32 Predicate Information (identified by operation id):
33 ---------------------------------------------------
34
35 2 - filter(ROWNUM<50)
36 3 - access("U"."NAME"=VALUE(KOKBF$))
37 5 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND
38 "T"."TS#"="S"."TS#"(+))
39 7 - access("CX"."OWNER#"="CU"."USER#"(+))
40 9 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
41 10 - access("O"."OWNER#"="U"."USER#")
42 12 - access("O"."OBJ#"="T"."OBJ#")
43 13 - access("T"."BOBJ#"="CO"."OBJ#"(+))
44 14 - access("T"."TS#"="TS"."TS#")
45 16 - access("KSPPI"."INDX"="KSPPCV"."INDX")
46 17 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
47 20 - filter(BITAND("T"."PROPERTY",1)=0)
48 22 - filter(BITAND("O"."FLAGS",128)=0)
I also added dbms_output.put_line('here '); as the first line of the function.
note, the "here" only prints once at the end
ee.txt
note, the "here" only prints once at the end
ee.txt
ASKER
i just put a dbms_output.put_line in the str2tbl function and it's printing 50 times if i use rownum < 50
interestingly i found this so far:
1. if i don't have rownum check and just have order by - called one time
2. if i remove order by, keeping rownum- called one time
confusing :-)
interestingly i found this so far:
1. if i don't have rownum check and just have order by - called one time
2. if i remove order by, keeping rownum- called one time
confusing :-)
what version of oracle?
also, there are multiple str2tbl versions around. all do pretty much the same thing, but there are minor variances
can you post your version?
this is the one I used....
also, there are multiple str2tbl versions around. all do pretty much the same thing, but there are minor variances
can you post your version?
this is the one I used....
CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
RETURN vcarray
PIPELINED
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE (v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start);
IF v_index = 0
THEN
PIPE ROW (SUBSTR(p_string, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW (SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP;
RETURN;
EXCEPTION
WHEN no_data_needed
THEN
NULL;
END str2tbl;
/
obviously, that's mine with the debug line removed.
dbms_output.put_line('here ');
was inserted between lines 8 and 9 in mine.
dbms_output.put_line('here
was inserted between lines 8 and 9 in mine.
ASKER
version 11.2.0.2
having same exact code as yours for str2tbl..
it's printing as many number of times as in rownum.. if i use rownum < 20 then it displays 20 times here..
having same exact code as yours for str2tbl..
it's printing as many number of times as in rownum.. if i use rownum < 20 then it displays 20 times here..
can you post your real query and table structures?
I've got the same setup
I've got the same setup
ASKER
ok.. this is very interesting:
select table_name from all_tables t, table(str2tbl('someschema' )) x
where t.owner = x.column_value
and rownum < 5
order by table_name;
--- prints only once..
i copy the exact query, change the table name to my table..
select col_name from mytable t, table(str2tbl('someschema' )) x
where t.col_name= x.column_value
and rownum < 5
order by col_name;
--prints 5 times :o
select table_name from all_tables t, table(str2tbl('someschema'
where t.owner = x.column_value
and rownum < 5
order by table_name;
--- prints only once..
i copy the exact query, change the table name to my table..
select col_name from mytable t, table(str2tbl('someschema'
where t.col_name= x.column_value
and rownum < 5
order by col_name;
--prints 5 times :o
ASKER
sdstuber, can you do the same query on a big table other than dba_tables?
on a user created table please?
i checked with lot of other tables and i think the function gets called infact more than rownum..
select col_name from mytable t, table(str2tbl('someschema' )) x
where t.col_name= x.column_value
and rownum < 5
order by col_name;
--> on a different table this prints str2tbl here more than 2000 times..
on a user created table please?
i checked with lot of other tables and i think the function gets called infact more than rownum..
select col_name from mytable t, table(str2tbl('someschema'
where t.col_name= x.column_value
and rownum < 5
order by col_name;
--> on a different table this prints str2tbl here more than 2000 times..
I just tried it against one of my own tables of 214,081 rows, I can go bigger if you want.
it ran just once.
it ran just once.
ASKER
does it have anything to do with cursor sharing or anything else?
cause it looks like on system table it runs only once.. but on user created table (doesnt matter which table i run) it always run like so many times..
cause it looks like on system table it runs only once.. but on user created table (doesnt matter which table i run) it always run like so many times..
ASKER
ok i don't know what's going on.. i'm just trying to tweak this little bit so i can use a temp table? something like this:
procedure(inputlist) {
populate temp table using input list;
select * from table where id in (select * from temptable)
}
comments?
procedure(inputlist) {
populate temp table using input list;
select * from table where id in (select * from temptable)
}
comments?
so you're not really trying to do a join at all?
select * from table
where id in (select * from table(str2tbl('something,s omething2' )) )
and rownum < 50
order by somecolumn
select * from table
where id in (select * from table(str2tbl('something,s
and rownum < 50
order by somecolumn
ASKER
yes.. no other tables.. on the user table same query .. i mean literally same except table name id fields are different..
it's calling str2tbl like million times sometimes..
it's calling str2tbl like million times sometimes..
ASKER
i'm at the lost of words here, may be i'm not explaining well :-)
select t.* from table t, table(str2tbl('something') ) x
where t.col = x.column_value
--> executes str2tbl just once
select * from (
select t.* from table t, table(str2tbl('something') ) x
where t.col = x.column_value)
where rownum < 10
order by id
--> executes like million times..
select t.* from table t, table(str2tbl('something')
where t.col = x.column_value
--> executes str2tbl just once
select * from (
select t.* from table t, table(str2tbl('something')
where t.col = x.column_value)
where rownum < 10
order by id
--> executes like million times..
did you try the "in" version I posted above?
ASKER
yes.. but looks like i figured out the reason here..
select * from (
select t.* from table t, table(str2tbl('something') ) x
where t.col = x.column_value)
where rownum < 10
order by id
-- million times
select * from (
select * from (
select t.* from table t, table(str2tbl('something') ) x
where t.col = x.column_value)
where rownum < 10)
order by id
i think putting rownum and order by in the same level causing the loop.. if i nest them differently it works good..
select * from (
select t.* from table t, table(str2tbl('something')
where t.col = x.column_value)
where rownum < 10
order by id
-- million times
select * from (
select * from (
select t.* from table t, table(str2tbl('something')
where t.col = x.column_value)
where rownum < 10)
order by id
i think putting rownum and order by in the same level causing the loop.. if i nest them differently it works good..
that was discussed above putting rownum and order by on the same level doesn't make sense usually because it's effectively random selection then ordered.
http:#37032668
http:#37032668
To clarify: rownum is a pseudo column applied to the resultset BEFORE the order by.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hey sdstuber.. sorry for being the pain.. you're being so helpful, thanks..
i did something and seemed to work sometime before but not anymore..
trying this:
select * from (
select e.* from table e,table(str2tbl('something ') t where e.col=t.column_value order by 1)
where rownum < 10
this time it's not executing million times but exactly 10 times..
i did something and seemed to work sometime before but not anymore..
trying this:
select * from (
select e.* from table e,table(str2tbl('something
where rownum < 10
this time it's not executing million times but exactly 10 times..
your rownum is outside the results.
so you are ordering and filtering.
if you look at the plan, you should see a "STOP KEY" operation
if that has changed for the same query, I don't know why
so you are ordering and filtering.
if you look at the plan, you should see a "STOP KEY" operation
if that has changed for the same query, I don't know why
ASKER
yes i see the stop key.. how to remove this?
all i want to see is just calling that function once..
all i want to see is just calling that function once..
you don't want to remove it, that's what is keeping you from processing millions of times.
i was mentioning it simply to show where/how your rownum filter was being applied
Can you post ddl for your tables and some sample data?
I'll see if I can mock up a million rows based on your sample and try to replicate your results
i was mentioning it simply to show where/how your rownum filter was being applied
Can you post ddl for your tables and some sample data?
I'll see if I can mock up a million rows based on your sample and try to replicate your results
ASKER
ok.. i just created a sample student table in my db now to repro and provide you the details.. looks like the str2tbl function gets called only once.. on the new table..
but all the existing tables in the application schema right now calls the functino more than once.. strange, i checked the table differences (between the new one i created and the one's that existing) don't see any difference at all..any ideas?
but all the existing tables in the application schema right now calls the functino more than once.. strange, i checked the table differences (between the new one i created and the one's that existing) don't see any difference at all..any ideas?
can you post ddl for your original?
ASKER
13:34:45 SQL> desc xxxxyyy
Name Null? Type
-------------------------- ---------- ----- -------- -------------------
xx NOT NULL NUMBER(20)
xx NOT NULL VARCHAR2(20)
YYY NOT NULL VARCHAR2(255)
xx NOT NULL DATE
xx NOT NULL VARCHAR2(20)
xx NOT NULL DATE
xx VARCHAR2(100)
xx VARCHAR2(50)
xx VARCHAR2(4000)
xx VARCHAR2(200 CHAR)
xx VARCHAR2(20)
YYY is the field i'm trying to map.. sorry edited all column names.. i've character semantics for one of the column, will that be an issue?
Name Null? Type
--------------------------
xx NOT NULL NUMBER(20)
xx NOT NULL VARCHAR2(20)
YYY NOT NULL VARCHAR2(255)
xx NOT NULL DATE
xx NOT NULL VARCHAR2(20)
xx NOT NULL DATE
xx VARCHAR2(100)
xx VARCHAR2(50)
xx VARCHAR2(4000)
xx VARCHAR2(200 CHAR)
xx VARCHAR2(20)
YYY is the field i'm trying to map.. sorry edited all column names.. i've character semantics for one of the column, will that be an issue?
probably not, particularly if it isn't YYY
can you post some sample data?
even if masked, just so I can get an idea of how big each row is.
are there any indexes?
can you post some sample data?
even if masked, just so I can get an idea of how big each row is.
are there any indexes?
ASKER
ok.. i extracted table def.. these are some of the probperties used for the table..
ORGANIZATION HEAP
TABLESPACE xxxyyyy
LOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
NOROWDEPENDENCIES
ORGANIZATION HEAP
TABLESPACE xxxyyyy
LOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
NOROWDEPENDENCIES
did you get your answer?
While I wait, not where I can test this but try:
select * from
(
select * from table where col in (select * from table(str2tbl('something,s
)
order by somecolumn