Link to home
Start Free TrialLog in
Avatar of ram_0218
ram_0218Flag for United States of America

asked on

order by causing issues with str2tbl..

this query:

select * from table where col in (select * from table(str2tbl('something,something2'))) and rownum < 50

--> str2tbl gets called once

select * from table where col in (select * from table(str2tbl('something,something2'))) and rownum < 50
order by somecolumn

--> executes as many as records getting retrieved..in this case str2tbl gets called 50 times..

what's wrong?

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

What are you wanting sorted, the first 50 rows returned of the top 50 after the sort?


While I wait, not where I can test this but try:

select * from
(
select * from table where col in (select * from table(str2tbl('something,something2'))) and rownum < 50
)
order by somecolumn
Avatar of ram_0218

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..
s elect * from table, table(str2tbl('something,something2')) x
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,something2')) x
where t.col =  x.column_value
order by somecolumn)
where rownum < 50
x.column_value

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'))
nope.. it's calling the function same number of times..

s elect * from table, table(str2tbl('something,something2')) x
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
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)

Open in new window

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
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 :-)
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....
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;
/

Open in new window

obviously, that's mine with the debug line removed.

dbms_output.put_line('here');


was inserted between lines 8 and 9 in mine.
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..
can you post your real query and table structures?

I've got the same setup
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
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..
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.
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..
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?
so you're not really trying to do a join at all?

select * from table
where id in  (select * from table(str2tbl('something,something2')) )
and rownum < 50
order by somecolumn
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..
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..
did you try the "in" version I posted above?
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..
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




To clarify:  rownum is a pseudo column applied to the resultset BEFORE the order by.
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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..
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
yes i see the stop key.. how to remove this?

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
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?
can you post ddl for your original?
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?
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?
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
did you get your answer?