Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 620
  • Last Modified:

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?

0
ram_0218
Asked:
ram_0218
  • 19
  • 17
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
0
 
ram_0218Author Commented:
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..
0
 
sdstuberCommented:
s elect * from table, table(str2tbl('something,something2')) x
where table.col =  x.column_value
and rownum < 50
order by somecolumn
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sdstuberCommented:
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
0
 
ram_0218Author Commented:
x.column_value

where's the column_value specified?
0
 
sdstuberCommented:
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'))
0
 
ram_0218Author Commented:
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..
0
 
sdstuberCommented:
how did you determine that it was executing more than once?
0
 
sdstuberCommented:
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

0
 
sdstuberCommented:
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
0
 
ram_0218Author Commented:
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 :-)
0
 
sdstuberCommented:
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

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

dbms_output.put_line('here');


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

I've got the same setup
0
 
ram_0218Author Commented:
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
0
 
ram_0218Author Commented:
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..
0
 
sdstuberCommented:
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.
0
 
ram_0218Author Commented:
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..
0
 
ram_0218Author Commented:
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?
0
 
sdstuberCommented:
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
0
 
ram_0218Author Commented:
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..
0
 
ram_0218Author Commented:
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..
0
 
sdstuberCommented:
did you try the "in" version I posted above?
0
 
ram_0218Author Commented:
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..
0
 
sdstuberCommented:
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




0
 
slightwv (䄆 Netminder) Commented:
To clarify:  rownum is a pseudo column applied to the resultset BEFORE the order by.
0
 
sdstuberCommented:
to clarify the clarification:  rownum is special because it only applies after a result is generated.  

i.e. you don't know what row 1 is until you have a first row.   So,  when rownum is included in a query,  one of the side effects of it is "materialization" of the query.  That is, the entire result set will be generated so that it can be numbered.


order by is always applied after all other operations.  

So, if you put rownum and order by on the same level ...

first you generate a million rows,  then you number them, then you filter them down to 50, then your order the 50.

I call that "random" because there are no conditions that ensure which rows will be the first 50
0
 
ram_0218Author Commented:
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..
0
 
sdstuberCommented:
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
0
 
ram_0218Author Commented:
yes i see the stop key.. how to remove this?

all i want to see is just calling that function once..
0
 
sdstuberCommented:
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
0
 
ram_0218Author Commented:
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?
0
 
sdstuberCommented:
can you post ddl for your original?
0
 
ram_0218Author Commented:
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?
0
 
sdstuberCommented:
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?
0
 
ram_0218Author Commented:
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
0
 
sdstuberCommented:
did you get your answer?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 19
  • 17
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now