Skip to Main Content
  • Questions

  • In list question with bind variables.

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Question and Answer

Tom Kyte

Thanks for the question, Mark .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: February 17, 2010 - 11:04 am UTC

Version:

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I am using Pro*C and I need to send an array of strings
in a "where <value> in :<value>" is this possible?
And if not what should I do?



and Tom said...


In Oracle7, this is rather difficult. You cannot bind in a SINGLE value and expect it to be treated as MANY values (think about how hard it would be to find something in an IN LIST that had a "comma" in it for example).

In Oracle8.0 and up, this is easy with object types and nested tables. Just to see what that will look like, I'll show that - then I'll show the v7 implementation.

ops$tkyte@8i> create or replace type myTableType
as table of number;
2 /
Type created.

ops$tkyte@8i> create or replace function in_list
( p_string in varchar2 ) return myTableType
2 as
3 l_data myTableType := myTableType();
4 l_string long default p_string || ',';
5 l_n number;
6 begin
7
8 loop
9 exit when l_string is null;
10 l_data.extend;
11 l_n := instr( l_string, ',' );
12 l_data( l_data.count ) := substr( l_string, 1, l_n-1 );
13 l_string := substr( l_string, l_n+1 );
14 end loop;
15 return l_data;
16 end;
17 /

Function created.


So, in Oracle8 and up we can "select * from PLSQL_FUNCTION" when the function returns a SQL Table type as follows:

ops$tkyte@8i>
ops$tkyte@8i> select *
2 from THE ( select cast( in_list('1,2,3,5,12')
as mytableType ) from dual ) a
3 /

COLUMN_VALUE
------------
1
2
3
5
12

Which means we can do that in a subquery:

ops$tkyte@8i> select *
2 from all_users
3 where user_id in
4 ( select * from THE (
select cast( in_list('1,2,3,5,12')
as mytableType ) from dual ) a )
5 /

USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM 5 20-APR-99




In v7, we can do something very similar (works in 8.0 and up as well) with plsql tables. Its a little more work but not too much. It would look like this:


ops$tkyte@8i> create or replace package my_pkg
2 as
3 function in_list( p_string in varchar2,
p_idx in number ) return varchar2;
4 pragma restrict_references( in_list,
wnds, rnds, wnps, rnps );
5
6 function get_list_count(p_string in varchar2)
return number;
7 pragma restrict_references( get_list_count,
wnds, rnds, wnps, rnps );
8
9 pragma restrict_references( my_pkg,
wnds, rnds, wnps, rnps );
10 end;
11 /

Package created.

ops$tkyte@8i>
ops$tkyte@8i> create or replace package body my_pkg
2 as
3 function in_list( p_string in varchar2,
p_idx in number ) return varchar2
4 is
5 l_start number;
6 l_stop number;
7 begin
8 if ( p_idx = 1 ) then
9 l_start := 1;
10 else
11 l_start := instr( p_string, ',', 1, p_idx-1 )+1;
12 end if;
13
14 l_stop := instr( p_string ||',', ',', 1, p_idx );
15
16 return ltrim( rtrim( substr( p_string, l_start,
l_stop-l_start ) ) );
17 end;
18
19 function get_list_count( p_string in varchar2 )
return number
20 is
21 l_cnt number default 0;
22 begin
23 for i in 1 .. 1000 loop
24 exit when nvl( instr( p_string, ',', 1, i ), 0 ) = 0;
25 l_cnt := i+1;
26 end loop;
27
28 return l_cnt;
29 end;
30
31 end;
32 /

Package body created.



So, now I can code a query like:

ops$tkyte@8i> variable bindVar varchar2(255)
ops$tkyte@8i>
ops$tkyte@8i> exec :bindVar := '1, 3, 44, 1000'

PL/SQL procedure successfully completed.

ops$tkyte@8i>
ops$tkyte@8i> select my_pkg.in_list( :bindVar, rownum )
2 from all_objects
3 where rownum <=
( select my_pkg.get_list_count( :bindVar ) from dual )
4 /

MY_PKG.IN_LIST(:BINDVAR,ROWNUM)
---------------------------------------
1
3
44
1000


All we need for that to work is a table with MORE rows then we have IN LIST items -- all_objects is generally a good candidate. It works by using rownum as an "index" into the plsql table.

So, now we can simply:


ops$tkyte@8i> select *
2 from all_users
3 where user_id in (select my_pkg.in_list(:bindVar,rownum)
4 from all_objects
5 where rownum <=
( select my_pkg.get_list_count( :bindVar ) from dual )
6 )
7 /

USERNAME USER_ID CREATED
------------------------------ ---------- ---------
WEB$AZEILMAN 1000 24-JUN-99



Rating

  (23 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Followup

Tim Glasgow, March 19, 2002 - 6:11 am UTC 

Tom, thanks for re-directing me to this section. Is it correct to pass the attribute name (as apposed to the value)to the in_list function and is the function visible to the query at run time?

This is the relevant section from my code:
-----------------------------------------------------
dbms_session.set_context( 'MY_CTX', 'estateagentid', pc_estate_agent_id_in);

IF li_estateagentid_count = 0 THEN

sql_stmt := sql_stmt || ' and ea.estateagentid =  sys_context( ''MY_CTX'',''estateagentid'')';

ELSE

dbms_output.put_line('hi1');

sql_stmt := sql_stmt || ' and ea.estateagentid IN ( select * from THE ( select cast( in_list(''estateagentid'') as mytableType ) from dual ) a )';

END IF;
---------------------------------------------------
The line of code after the ELSE is giving me the following error:

SQL> create or replace type myTableType as table of number;
  2  /

Type created.

SQL> @unit_tests/properties/test_all_nulls;
Input truncated to 1 characters
hi1
BEGIN
*
ERROR at line 1:
ORA-00904: invalid column name
ORA-06512: at "PROP.AGENTPROPERTYREPORTS", line 362
ORA-06512: at line 3

I've copied the in_list function like for like and inserted it into my package body as a private function.

It's obviously something in the sql_stmt string at run time but I'm not sure what. Any help would be greatly appreciated.
Thanks,
Tim

Tom Kyte
March 19, 2002 - 8:58 am UTC

in_list must be VISIBLE outside of the package and qualified. Otherwise SQL cannot "see it".



Followup

Tim Glasgow, March 20, 2002 - 7:29 am UTC

Tom, I have made the in_list function public and qualified it in the dynamic sql as follows:
----------------------------------
sql_stmt := sql_stmt || ' and ea.estateagentid IN ( select * from THE ( select cast( AgentPropertyReports.in_list(''estateagentid'') as mytableType ) from dual ) a )';
----------------------------------
Unfortunately something seems to be wrong with this as my sql*plus session is not responding when I pass my procedure two estate agentid's in the format '139,44'

Please help, I really need to find a way of making this work. Would it be possible to send you an attachment of the code?
Thanks,
Tim


Tom Kyte
March 20, 2002 - 2:21 pm UTC

just put the code here, in a followup. A cut and paste from sqlplus would be great.

Followup

Tim Glasgow, March 21, 2002 - 4:46 am UTC

Tom,thanks for your message.. here's the code. I couldn't send sql*plus outputs as sql is locking up when I pass multiple estate agents. So I've pasted in the entire package and the 2 relevant procedures. Not that it makes much difference, but I'm passing the following inputs:

Thanks very much for your help. Tim

AgentPropertyReports.PropertyReport
(pc_start_date_in => '01-OCT-2001'
,pc_end_date_in => '31-OCT-2001'
,pc_period_type_in => 'MONTH'
,pc_estate_agent_id_in => '139,44'
,pi_property_type_id_in => null
,pc_type_in => null
,outRS => rout);

/*----------------------*/
CREATE OR REPLACE PACKAGE AgentPropertyReports IS

TYPE RecordSet_cursor IS REF CURSOR;

PROCEDURE PropertyReport
(pc_start_date_in IN VARCHAR
,pc_end_date_in IN VARCHAR
,pc_period_type_in IN VARCHAR DEFAULT 'MONTH'
,pc_estate_agent_id_in IN VARCHAR2
,pi_property_type_id_in IN property.propertytypeid%TYPE
,pc_type_in IN property.type%TYPE
,outRS OUT RecordSet_cursor);

FUNCTION in_list ( p_string IN VARCHAR2 ) RETURN myTableType;

CREATE OR REPLACE PACKAGE BODY AgentPropertyReports IS

FUNCTION in_list ( p_string IN VARCHAR2 )

RETURN myTableType AS

l_data myTableType := myTableType();
l_string LONG DEFAULT p_string || ',';
l_n NUMBER;

BEGIN
LOOP
EXIT WHEN l_string IS NULL;
l_data.extend;
l_n := instr( l_string, ',' );
l_data( l_data.count ) := substr( l_string, 1, l_n-1 );
l_string := substr( l_string, l_n+1 );
END LOOP;

RETURN l_data;

END in_list;

PROCEDURE PropertyReport
(pc_start_date_in IN VARCHAR
,pc_end_date_in IN VARCHAR
,pc_period_type_in IN VARCHAR DEFAULT 'MONTH'
,pc_estate_agent_id_in IN VARCHAR2
,pi_property_type_id_in IN property.propertytypeid%TYPE
,pc_type_in IN property.type%TYPE
,outRS OUT RecordSet_cursor)AS

/*=====================================================
* declare a variable to hold a dynamic sql statement
*====================================================*/
sql_stmt VARCHAR2(3000);

/*=======================================================
* declare variables to hold the new start and end dates
*======================================================*/

ld_new_start_date property.dateadded%TYPE := null;
ld_new_end_date property.dateadded%TYPE := null;
li_estateagentid_count NUMBER(1) := 0;

BEGIN
--clear out the property_report table
EXECUTE IMMEDIATE 'truncate table property_report';

/*======================================================
* call function set_date to get the correct start_date
*=====================================================*/
if pc_start_date_in IS NULL
then
ld_new_start_date := set_date(pd_sd_date_in => NULL
,pc_range_type_in => gc_start);
else
ld_new_start_date := set_date(pd_sd_date_in => to_date(pc_start_date_in,'DD-MON-YYYY')
,pc_range_type_in => gc_start);
end if;

/*====================================================
* call function set_date to get the correct end_date
*===================================================*/
if pc_end_date_in is NULL
then
ld_new_end_date := set_date(pd_sd_date_in => NULL
,pc_range_type_in => gc_end); else
ld_new_end_date := set_date(pd_sd_date_in => to_date(pc_end_date_in,'DD-MON-YYYY')
,pc_range_type_in => gc_end);
end if;

/*======================================================= * --has more than one estate_agent_id has been passed in
*========================================================*/
IF pc_estate_agent_id_in IS NOT NULL
THEN
select instr(pc_estate_agent_id_in,',',1,1)
into li_estateagentid_count
from dual;

if li_estateagentid_count >0
then
li_estateagentid_count := 1;
else
li_estateagentid_count := 0;
end if;
END IF;

/*==============================================
* create a namespace for the context MY_CTX
* and associate the namespace with the package
*=============================================*/
EXECUTE IMMEDIATE 'create or replace context MY_CTX using prop.AgentPropertyReports';

/*========================================================
* build up a dynamic sql statement depending on the *parameters
*========================================================*/
sql_stmt := 'INSERT INTO PROPERTY_REPORT ';
--monthly or weekly
IF pc_period_type_in = gc_period --'MONTH'
THEN
sql_stmt := sql_stmt || ' select ''01-''||to_char(p.dateadded,''MON-YYYY''),count(p.propertyid),round(avg(decode(p.askingprice,0,null,p.askingprice))),0,0,0,0,0,0,0 ';
ELSE
sql_stmt := sql_stmt || ' select TRUNC(p.dateadded,''DAY''),count(p.propertyid),round(avg(decode(p.askingprice,0,null,p.askingprice))),0,0,0,0,0,0,0 ';
END IF;

--estate_agent_id
IF pc_estate_agent_id_in IS NOT NULL
THEN
--set the context
dbms_session.set_context( 'MY_CTX', 'estateagentid', pc_estate_agent_id_in);

sql_stmt := sql_stmt || ' from property p ';
sql_stmt := sql_stmt || ' , estateagentlink eal ';
sql_stmt := sql_stmt || ' , estateagent ea ';
sql_stmt := sql_stmt || ' where eal.propertyid = p.propertyid ';
sql_stmt := sql_stmt || ' and eal.estateagentid = ea.estateagentid ';
sql_stmt := sql_stmt || ' and p.dateadded between :start_date and :end_date ';

--check if there is more than one estate agent id
IF li_estateagentid_count = 0
THEN
sql_stmt := sql_stmt || ' and ea.estateagentid = sys_context( ''MY_CTX'',''estateagentid'')';
ELSE
--I'VE PASSED IN MORE THAN ONE ESTATE_AGENT_ID
sql_stmt := sql_stmt || ' and ea.estateagentid IN ( select * from THE ( select cast( AgentPropertyReports.in_list(''estateagentid'') as mytableType ) from dual ) a )';
END IF;
ELSE
sql_stmt := sql_stmt || ' from property p ';
sql_stmt := sql_stmt || ' where p.dateadded between :start_date and :end_date ';
END IF;

--property_id
IF pi_property_type_id_in IS NOT NULL
THEN
dbms_session.set_context( 'MY_CTX', 'propertytypeid', pi_property_type_id_in);
sql_stmt := sql_stmt || ' and p.propertytypeid = sys_context( ''MY_CTX'',''propertytypeid'')';
END IF;

--sale or rental
IF pc_type_in IS NOT NULL
THEN
dbms_session.set_context( 'MY_CTX', 'type', upper(pc_type_in));
sql_stmt := sql_stmt || ' and upper(p.type) = sys_context( ''MY_CTX'',''type'')';
END IF;

--monthly or weekly
IF pc_period_type_in = gc_period --'MONTH'
THEN
sql_stmt := sql_stmt || ' group by to_char(p.dateadded,''MON-YYYY'')';
ELSE
--'WEEK'
sql_stmt := sql_stmt || ' group by TRUNC(p.dateadded,''DAY'')';
END IF;

/*=====================================================
* execute the sql statement depending on the input *parameters
*========================================================*/
EXECUTE IMMEDIATE sql_stmt using ld_new_start_date,ld_new_end_date;
commit;

Tom Kyte
March 21, 2002 - 4:05 pm UTC

#1 -- get that execute immediate 'create or replace context' out of there. You want to avoid doing DDL, not do it as often as possible. do the create or replace as part of your install, not runtime.

#2 -- hopefully you are using temporary tables, you don't need to truncate them, you could use a transaction level temporary table and just commit when you are done.

do this to debug.

Instead of RUNNING the query, print the query out using dbms_output:

create or replace procedure p ( p_str in varchar2 )
is
l_str long := p_str;
begin
loop
exit when l_str is null;
dbms_output.put_line( substr( l_str, 1, 250 ) );
l_str := substr( l_str, 251 );
end loop;
end;
/

so, collect a query without the IN and with the IN and use

set autotrace traceonly explain

to generate the query plans. Lets see what the difference between them is.

Also, search this site for showsql and run that script sometime when this "hangs" -- see if it is running the SQL and the sql is taking really long or if its "hung" elsewhere.



Followup

Tim Glasgow, March 22, 2002 - 9:28 am UTC

Tom, I got it working ..no prizes for spotting the difference between these two:

---------------------------------------------------
sql_stmt := sql_stmt || ' and ea.estateagentid IN ( select * from THE ( select cast( AgentPropertyReports.in_list(sys_context( ''NEW_CTX'',''estateagentid'')) as mytableType ) from dual ) a )';

sql_stmt := sql_stmt || ' and ea.estateagentid IN ( select * from THE (
select cast( AgentPropertyReports.in_list(''estateagentid'') as mytableType )
from dual ) a )';
---------------------------------------------------

Your reply put me on the right track and particularly your debug proc which was extremely useful, thanks for posting it as I had been trying for ages to get my sql_stmt output.

Also, you'll be pleased to hear I have removed my DDL!!

Another happy customer!
Thanks again,
Tim

oracle 8i pl/sql

senthil kumaran, March 11, 2005 - 1:59 am UTC 

SQL> drop table senthil;
drop table senthil
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

tom, how can i understand this one

Tom Kyte
March 11, 2005 - 6:12 am UTC

means you have another session on your system that is modifying senthil and has not yet committed its transaction.

Just what I needed

A reader, March 16, 2005 - 4:32 pm UTC

Thanks Tom.

Somewhat related

Mark, April 12, 2005 - 11:05 am UTC

My requirement is somewhat relaed here.

I have a need to take a function

F(in_num, in_where, in_group_by)

and attempt to parse the in_where string into bind variables. The in_where clause could be anything, and looks like 'COL1 IN(1,2,3) AND col2 BETWEEN SYSDATE AND SYSDATE - 1 AND col3 = 123 and col4 = 456' and on and on. I need to do this within the function, as it represents a column in an outer SQL statement.

Even a partial conversion to bind variables of just col = value pairs would help on the hard parsing going on. Being a row-level call to this function, the values in the in_where clause change per row. This is causing a tremendous amount of hard parse.

Hope i explained this well....

Right now, the function looks like this:

(
in_parent_company_id IN NUMBER DEFAULT NULL
,in_where IN VARCHAR2 DEFAULT NULL
,in_group_by IN VARCHAR2 DEFAULT NULL
)
RETURN NUMBER
IS
v_return NUMBER;
v_sql VARCHAR2 (3000);
BEGIN
v_sql :=
'select sum(count(distinct entity_id)) from MPPRU_' ||
in_parent_company_id;

IF in_where IS NOT NULL
THEN

v_sql := v_sql || ' where ' || in_where;
v_sql := v_sql || ' and units > 0 ';
ELSE
v_sql := v_sql || ' where units > 0 ';
END IF;

v_sql := v_sql || in_group_by || ', SERVICE_DATE_FROM';
EXECUTE IMMEDIATE v_sql
INTO v_return;
RETURN v_return;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
-- raise_application_error (-20001, v_SQL);
END;

Regards,
Mark

Tom Kyte
April 12, 2005 - 9:02 pm UTC

if they are sending you predicates basically, you would need to parse it and bind it. too hard.

just alter session set cursor_sharing = force and let us do it (and rethink this approach, forget performance killing attributes of it, consider the sql injection possiblilities)

Lists of one item (Oracle 7)

Tim, October 17, 2005 - 10:48 am UTC 

I found this very useful; however, the v7 example only works for lists of 2 or more items:

SQL> exec :bindVar := '1'

PL/SQL procedure successfully completed.

SQL> select my_pkg.in_list( :bindVar, rownum )
  2      from all_objects
  3     where rownum <=
  4           ( select my_pkg.get_list_count( :bindVar ) from dual )
  5  /

no rows selected


I think the easiest fix is to change the default for get_list_count.l_cnt from 0 to 1. (But note that this means get_list_count(null) returns 1.)

Using bind variables with IN clause

HM, September 14, 2006 - 2:24 pm UTC

Tom
I have a dynamic sql as such:
sqlstmt:='select * from cat where table_name in '||tab_list||';
execute immediate sqlstmt using :x;

I want to use a bind varible for tab_list but I can not do that. What are the alternatives? I dont believe I can use your suggestion above.

Tom Kyte
September 14, 2006 - 4:33 pm UTC

I firmly believe AND KNOW that you in fact can.

so, go for it.

'THE' is such a hard word to search for.

A reader, October 18, 2006 - 4:58 pm UTC

You used the 'THE' operator but I can't find the documentation on it and googling 'THE' is impossible.

Can you tell me what other keyword would get me to the docs or the url there?

Tom Kyte
October 18, 2006 - 5:44 pm UTC

"the" is deprecated. no need for it today.

TABLE is it's replacement

select * from TABLE( function );

In-list values Vs GTT

Oraboy, October 18, 2006 - 10:31 pm UTC

Tom
I recently noticed that a query with huge in-list (say it varies from 50 to 200 elements depending on customer)

The column against which it was checked was highly skewed and exceeded more than 254 distinct values ( and dbms_stats generated height based histograms on that column(SKEWONLY))

Now when I check this query, the cardinality is completely false and CBO doesnt consider using the index at all..On the other hand, when I try to push the in-list elements into an Global temporay table(gtt) and use it as a subquery

select * from table
where colin (select id from gtt)

the dynamic sampling kicks in and picks correct cardinality and uses index.

Just wanted to know
- is there any option I could try without having to reconstruct the SQL to use temporary table?


(sorry I dont have the table or test case handy at this time..got remainded of this question when I was reading through this thread)

Thanks


Tom Kyte
October 19, 2006 - 8:09 am UTC

but that would mean sometimes you want an index, sometimes you don't right?

or do you always want "first_rows(nn)" type of optimization (that is a hint... in more ways than one)

in-list vs Gtt

Oraboy, October 19, 2006 - 12:24 pm UTC

thanks first for not ignoring my question (which had no test scripts to reproduce).

well, more things come to my mind as you ask.(it has been a month or so since I worked on this)

The actual query structure looked like this..

Select * from outertab o /*Outertab*/
where req_id in
/*Unionized subquery*/
(select id from table_1 where <cond>>
union
select id from table_2 where <<cond>>
union
select id from table_3 where <<some other cond>>
union
select id from table_4 where <<cond>>
)
and date_col between :date1 and :date2
and exists ( /*Subquery2*/
select * from table_bigger_then_outertab x
where x.col1 = o.col1
and x.refid in (1,2,3..<<200 elements>>)
)


Stats:
Outertab : table with 200M rows
index available on col1 (consider this as PK)
index " " " req_id
index " " " date_col

Unionized Subquery: (CBO treated as a view - VW_NSO_1)
first two queries returned less than 10 rows
third query returned more than 300 rows
fourth query returned one or two rows

Subquery2 : the table used is bigger than main/outer table
correlated subquery (referring to Pk of outer tab)
IN-list had more 200+ elements

This was the problem:
---------------------
- The date column wasnt helpful to filter anything out
- Since the req_id uses the unionized query, CBO (SOMEHOW) calculated wrong estimate (it estimates more than 1700 rows coming out of that subquery whereas in reality it was just 300).
- I took each individual query (unionized subquery) and autotraced it..works fine on isolation (with correct estimation and execution)..but the issue was when it became a single query CBO lost the cardinality

Note: in 8.1.7 it used the index for this subquery..but in 10.1.0.4 since the card was off, it full scanned outertab and hashjoins with view
- To fix, I tried two options..

a)req_id in (
Select distinct id from -- added this
( /* unionized query*/)
)
b) req_id in (select id from global_temp_table)
(this one solved the issue ) <--


-- also there was another query with similar structure, where CBO preferred to do a Full Table scan of *Subquery2* and then hashjoined with outertab.

I rewrote the query to push all ids into global temp table and it solved it.

FYI: optimizer_mode was ALL_ROWS (Now after reading your reply, I am thinking may it was the root cause for leaning towards HASH_JOINS.)

and the query returned more than 1500 rows (yes, they said its OLTP,but ..;-) )

>>but that would mean sometimes you want an index, >>sometimes you don't right?

- to answer that, I wanted the index to be used all the time. because outer tab is million row table..and probing via Index (RBO) is working faster and lesser LIOs.



Tom Kyte
October 19, 2006 - 2:17 pm UTC

did you try first_rows(nn)?

GTT vs Unionized subquery - IN Clause

Oraboy, October 19, 2006 - 9:55 pm UTC

Tom-
Thanks again for your valuable hints.

I havent gotten a chance to retrieve the old query with huge in-list elements..but instead got this where a subquery (unionizied) performs worse than the same query when replaced by global temporary table.

fyi:

Query:
-----
## With First Rows
#######################

SELECT /*+ FIRST_ROWS_100 */
* from TAB_PRO A
where A.REQ_ID in (
SELECT GRP_ID FROM GMTI_TAB WHERE MEMBER_ID=98
UNION
SELECT USR_ID FROM USR_TAB WHERE USER_ID = 98
UNION
SELECT MBR_ID FROM GMTI_TAB WHERE GROUP_ID IN (98)
UNION
SELECT USR_ID FROM USR_TAB WHERE USER_ID IN (98)
)

Elapsed: 00:00:13.95

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 294K| 26M| 2365 (2)| 00:00:29 |
|* 1 | HASH JOIN | | 294K| 26M| 2365 (2)| 00:00:29 |
| 2 | VIEW | VW_NSO_1 | 243 | 3159 | 17 (24)| 00:00:01 |
| 3 | SORT UNIQUE | | 243 | 2420 | 17 (65)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| GMTI_TAB | 4 | 40 | 6 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | GMTI_IDX | 4 | | 3 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | USR_IDX | 1 | 5 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | GMTI_GP_IDX | 237 | 2370 | 3 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | USR_IDX | 1 | 5 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | TAB_PRO | 773K| 59M| 2336 (2)| 00:00:29 |
----------------------------------------------------------------------------------------------------

<<predicate info truncated>>

Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
12195 consistent gets
0 physical reads
0 redo size
237690 bytes sent via SQL*Net to client
1706 bytes received via SQL*Net from client
212 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3162 rows processed

## With ALL_ROWS hint
## #####################
3162 rows selected.

Elapsed: 00:00:14.21

same plan and same stats as FIRST_ROWS above

## RULE
#########################
3162 rows selected.

Elapsed: 00:00:12.31
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | TAB_PRO | | | |
| 2 | NESTED LOOPS | | | | |
| 3 | VIEW | VW_NSO_1 | | | |
| 4 | SORT UNIQUE | | | | |
| 5 | UNION-ALL | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| GMTI_TAB | | | |
|* 7 | INDEX RANGE SCAN | GMTI_IDX | | | |
|* 8 | INDEX UNIQUE SCAN | USR_IDX | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| GMTI_TAB | | | |
|* 10 | INDEX RANGE SCAN | GMTI_GP_IDX | | | |
|* 11 | INDEX UNIQUE SCAN | USR_IDX | | | |
|* 12 | INDEX RANGE SCAN | REQ_IDX | | |
----------------------------------------------------------------------------------------------

<<truncated>>
Note
-----
- rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1036 consistent gets
0 physical reads
0 redo size
237608 bytes sent via SQL*Net to client
1706 bytes received via SQL*Net from client
212 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3162 rows processed



Now ,Moving the Unionized query to GTT
=======================================

SELECT *
from TAB_PRO A
where a.req_id in (
select grp_id from gtt_temp_tab)
/

3162 rows selected.

Elapsed: 00:00:13.39

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2560 | 232K| 443 (1)| 00:00:06 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_PRO | 160 | 12800 | 55 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2560 | 232K| 443 (1)| 00:00:06 |
| 3 | SORT UNIQUE | | 16 | 208 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | GTT_TEMP_TAB | 16 | 208 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | REQ_IDX | 160 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Statistics
------------------------------------------------------
7 recursive calls
0 db block gets
1027 consistent gets
0 physical reads
0 redo size
237608 bytes sent via SQL*Net to client
1706 bytes received via SQL*Net from client
212 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3162 rows processed

Lets ignore for a minute what the stats for REQ_IDX is.

My question is how/why the optimizer gets the right execution only when things gets moved into Global temp table?

also, I tried using dynamic sampling (9) hint on the unionized query, and the cardinality out of subquery was correct ,but still the plan for doing hash-join (and fts on tab_pro.

By the way this is just part of bigger query..(so please ignore elapsed time above..just the LIOs are my issue)


Thanks Much



Tom Kyte
October 20, 2006 - 4:09 am UTC

anytime the optimizer guesses the cardinality wrong, we can definitely get the wrong plan.

show us the plan with the right cardinalities (from dynamic sampling) but that is "wrong"

Subquery with Union

Oraboy, October 24, 2006 - 6:44 pm UTC 

>>anytime the optimizer guesses the cardinality wrong, we 
>>can definitely get the wrong plan.

You are right..I worked on the query and saw that even if I force to get the correct cardinality (with dynamic sampling set to >4) , but that doesnt help fix the join method. (still prefers hash join)

Turns out it has *something to do*  with the subquery with union.

This time, I have a testcase to reproduce too.

***** Setup
***** ------

Create table Ven_big_table as select * from all_objects;

Create table ven_lookup as select * from dba_users where username='VENKAT';

Insert into ven_lookup     select * from dba_users where username like 'S%' and rownum<2;

create index Idx_bigtab_owner on ven_big_table(owner);


-- generate stats
-- ---------------
begin
     dbms_stats.gather_table_stats(tabname=>'VEN_BIG_TABLE',
                                   estimate_percent=>20,
                                   method_opt=>'FOR COLUMNS OWNER SIZE SKEWONLY',
                                   CASCADE=> TRUE);

     dbms_stats.gather_table_stats(tabname=>'VEN_LOOKUP',
                                   estimate_percent=>20,
                                   method_opt=>'FOR COLUMNS USERNAME SIZE SKEWONLY',
                                   CASCADE=> TRUE);
end;

-- checking
-- ----------
select table_name,num_rows from user_tables where table_name like 'VEN%';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
VEN_BIG_TABLE                       52550
VEN_LOOKUP                              2


select table_name,num_rows from user_indexes where table_name like 'VEN%';
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
VEN_BIG_TABLE                       52991


select username from ven_lookup;

USERNAME
------------------------------
VENKAT
SENDIL


<b>-- Testing the inner query estimates in isolation </b>
<b>-- -----------------------------------------------</b>


SQL>select username from ven_lookup where username = 'VENKAT';


Execution Plan
----------------------------------------------------------
Plan hash value: 2886351085

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| VEN_LOOKUP |     1 |     7 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("USERNAME"='VENKAT')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        231  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          

SQL>select username from ven_lookup where username = 'VENKAT'
  2         union
  3         select username from ven_lookup where username like 'VEN%';


Execution Plan
----------------------------------------------------------
Plan hash value: 3518217026

-----------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |     2 |    14 |     6  (67)|
|   1 |  SORT UNIQUE        |            |     2 |    14 |     6  (67)|
|   2 |   UNION-ALL         |            |       |       |            |
|*  3 |    TABLE ACCESS FULL| VEN_LOOKUP |     1 |     7 |     2   (0)|
|*  4 |    TABLE ACCESS FULL| VEN_LOOKUP |     1 |     7 |     2   (0)|
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("USERNAME"='VENKAT')
   4 - filter("USERNAME" LIKE 'VEN%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        229  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed          

select username from ven_lookup where username = 'VENKAT'
       union
select username from ven_lookup where username like 'VEN%'
       union
select 'NOUSER' from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 3248711011

-----------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |     3 |    14 |     9  (78)|
|   1 |  SORT UNIQUE        |            |     3 |    14 |     9  (78)|
|   2 |   UNION-ALL         |            |       |       |            |
|*  3 |    TABLE ACCESS FULL| VEN_LOOKUP |     1 |     7 |     2   (0)|
|*  4 |    TABLE ACCESS FULL| VEN_LOOKUP |     1 |     7 |     2   (0)|
|   5 |    FAST DUAL        |            |     1 |       |     2   (0)|
-----------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("USERNAME"='VENKAT')
   4 - filter("USERNAME" LIKE 'VEN%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        250  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed
          
<b>-- Testing against the big table</b>
<b>-- ------------------------------</b>

select * from ven_big_table b
 where owner in (select username from ven_lookup where username='VENKAT');
 
-------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost 
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     1 |   100 |     5
|*  1 |  HASH JOIN SEMI              |                  |     1 |   100 |     5
|   2 |   TABLE ACCESS BY INDEX ROWID| VEN_BIG_TABLE    |    10 |   930 |     2
|*  3 |    INDEX RANGE SCAN          | IDX_BIGTAB_OWNER |    10 |       |     1
|*  4 |   TABLE ACCESS FULL          | VEN_LOOKUP       |     1 |     7 |     2
-------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OWNER"="USERNAME")
   3 - access("OWNER"='VENKAT')
   4 - filter("USERNAME"='VENKAT')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
       1349  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

<b> query2 </b>

SQL>select * from ven_big_table b where owner like 'VEN%'

12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4166374032

-------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     5 |   465 |     3 
|   1 |  TABLE ACCESS BY INDEX ROWID| VEN_BIG_TABLE    |     5 |   465 |     3 
|*  2 |   INDEX RANGE SCAN          | IDX_BIGTAB_OWNER |     5 |       |     2 
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER" LIKE 'VEN%')
       filter("OWNER" LIKE 'VEN%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1702  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

<b> subquery has one union </b> (even if it uses the index,note that estimated Rows is way off)
<b> ---------------------- </b>         
select * from ven_big_table b
 where owner in
 (
 select username from ven_lookup where username = 'VENKAT'
        union
 select username from ven_lookup where username like 'VEN%');
 
 12 rows selected.
 
 
 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 438939357
 
-------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |  2841 |   305K|   102 
|   1 |  TABLE ACCESS BY INDEX ROWID| VEN_BIG_TABLE    |  1420 |   128K|    48 
|   2 |   NESTED LOOPS              |                  |  2841 |   305K|   102 
|   3 |    VIEW                     | VW_NSO_1         |     2 |    34 |     6 
|   4 |     SORT UNIQUE             |                  |     2 |    14 |     6 
|   5 |      UNION-ALL              |                  |       |       |       
|*  6 |       TABLE ACCESS FULL     | VEN_LOOKUP       |     1 |     7 |     2 
|*  7 |       TABLE ACCESS FULL     | VEN_LOOKUP       |     1 |     7 |     2 
|*  8 |    INDEX RANGE SCAN         | IDX_BIGTAB_OWNER |  1420 |       |     4 
-------------------------------------------------------------------------------

 
 Predicate Information (identified by operation id):
 ---------------------------------------------------
 
    6 - filter("USERNAME"='VENKAT')
    7 - filter("USERNAME" LIKE 'VEN%')
    8 - access("OWNER"="$nso_col_1")
 
 
 Statistics
 ----------------------------------------------------------
           1  recursive calls
           0  db block gets
          19  consistent gets
           0  physical reads
           0  redo size
        1349  bytes sent via SQL*Net to client
         236  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           1  sorts (memory)
           0  sorts (disk)
         12  rows processed
         
<b> this is the actual query which is having performance issues </b>         
<b> -----------------------------------------------------</b>

select * from ven_big_table b
where owner in
(
select username from ven_lookup where username = 'VENKAT'
       union
select username from ven_lookup where username like 'VEN%'
   union
select 'NOUSER' from dual
)

12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2590691542

----------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |  4261 |   457K|   153   (4)|
|*  1 |  HASH JOIN            |               |  4261 |   457K|   153   (4)|
|   2 |   VIEW                | VW_NSO_1      |     3 |    51 |     9  (34)|
|   3 |    SORT UNIQUE        |               |     3 |    14 |     9  (78)|
|   4 |     UNION-ALL         |               |       |       |            |
|*  5 |      TABLE ACCESS FULL| VEN_LOOKUP    |     1 |     7 |     2   (0)|
|*  6 |      TABLE ACCESS FULL| VEN_LOOKUP    |     1 |     7 |     2   (0)|
|   7 |      FAST DUAL        |               |     1 |       |     2   (0)|
|   8 |   TABLE ACCESS FULL   | VEN_BIG_TABLE | 52550 |  4772K|   143   (2)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OWNER"="$nso_col_1")
   5 - filter("USERNAME"='VENKAT')
   6 - filter("USERNAME" LIKE 'VEN%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        741  consistent gets <b> 700+ blocks </b>
          0  physical reads
          0  redo size
       1351  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         12  rows processed

<b> same query if forced with Index </b>
<b> --------------------------------</b>         
select /*+ INDEX(b IDX_BIGTAB_OWNER)*/ * from ven_big_table b
where owner in
(
select username from ven_lookup where username = 'VENKAT'
       union
select username from ven_lookup where username like 'VEN%'
       union
select 'NOUSER' from dual
)


--------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |  4261 |   457K|   153  
|   1 |  TABLE ACCESS BY INDEX ROWID| VEN_BIG_TABLE    |  1420 |   128K|    48  
|   2 |   NESTED LOOPS              |                  |  4261 |   457K|   153  
|   3 |    VIEW                     | VW_NSO_1         |     3 |    51 |     9  
|   4 |     SORT UNIQUE             |                  |     3 |    14 |     9  
|   5 |      UNION-ALL              |                  |       |       |        
|*  6 |       TABLE ACCESS FULL     | VEN_LOOKUP       |     1 |     7 |     2  
|*  7 |       TABLE ACCESS FULL     | VEN_LOOKUP       |     1 |     7 |     2  
|   8 |       FAST DUAL             |                  |     1 |       |     2  
|*  9 |    INDEX RANGE SCAN         | IDX_BIGTAB_OWNER |  1420 |       |     4  
--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("USERNAME"='VENKAT')
   7 - filter("USERNAME" LIKE 'VEN%')
   9 - access("OWNER"="$nso_col_1")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         21  consistent gets <b> <-- 20% of above query </b>
          0  physical reads
          0  redo size
       1349  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         12  rows processed

During my prev attempt, I tried moving the subquery into a temporary table and that obviously fixed it..any clue why the subquery with "union" draws a wrong estimation.

Tom Kyte
October 25, 2006 - 9:34 am UTC

but did you notice that the estimated cardinality is still way way off?

you got 12 rows
it thought, well, more than 12.

ignore math mistake

Oraboy, October 24, 2006 - 10:25 pm UTC

>> 21 consistent gets <-- 20% of above query

sorry ,ignore the math mistake.(dont know why I thought it was 100 blocks in the prev query)..

Its 21 vs 741 consistent gets (Index Vs FTS)

Thanks

in clause with subquery & union

Oraboy, October 25, 2006 - 12:37 pm UTC 

>>but did you notice that the estimated cardinality is still way way off?

Yes.. thats why I added this note " subquery has one union  <b>(even if it uses the index,note that estimated Rows is way off)</b>"

I see the estimation figures go way awry ,the moment it tries to join to outer table (VEN_BIGTABLE) irrespective of whether its Nested Loop (Index ) or Hash Join (Full table scan)

but BIG_TABLE's owner has an index and histograms..and after all, if the same conditions were given without the subquery, the estimations are good and it uses the correct plan.

fyi:

SQL>ed  <b> Without subquery </b>
Wrote file afiedt.buf

  1  select * from ven_big_table b
  2   where owner = 'VENKAT'
  3      or owner like 'VEN%'
  4*     or owner = 'NOUSER'
SQL>/

12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2014700340

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |     6 |   558 |     6   (0)|
|   1 |  CONCATENATION                |                  |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID | VEN_BIG_TABLE    |     5 |   465 |     3   (0)|
|*  3 |    INDEX RANGE SCAN           | IDX_BIGTAB_OWNER |     5 |       |     2   (0)|
|   4 |   INLIST ITERATOR             |                  |       |       |            |
|   5 |    TABLE ACCESS BY INDEX ROWID| VEN_BIG_TABLE    |     1 |    93 |     3   (0)|
|*  6 |     INDEX RANGE SCAN          | IDX_BIGTAB_OWNER |     1 |       |     2   (0)|
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OWNER" LIKE 'VEN%')
       filter("OWNER" LIKE 'VEN%')
   6 - access("OWNER"='NOUSER' OR "OWNER"='VENKAT')
       filter(LNNVL("OWNER" LIKE 'VEN%'))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
       1332  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

<b> Subquery in isolation (which transforms into VW_NSO_1)</b>

SQL>SELECT username from ven_lookup where username = 'VENKAT'
  2  union
  3  select username from ven_lookup where username like 'VEN%'
  4  union
  5  select 'NOUSER' from dual
  6  /


Execution Plan
----------------------------------------------------------
Plan hash value: 3248711011

-----------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |     3 |    14 |     9  (78)|
|   1 |  SORT UNIQUE        |            |     3 |    14 |     9  (78)|
|   2 |   UNION-ALL         |            |       |       |            |
|*  3 |    TABLE ACCESS FULL| VEN_LOOKUP |     1 |     7 |     2   (0)|
|*  4 |    TABLE ACCESS FULL| VEN_LOOKUP |     1 |     7 |     2   (0)|
|   5 |    FAST DUAL        |            |     1 |       |     2   (0)|
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("USERNAME"='VENKAT')
   4 - filter("USERNAME" LIKE 'VEN%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        250  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed

<b> Same query rewritten and Union is pushed outside </b>

SQL>ed
Wrote file afiedt.buf

  1  select * from ven_big_table b
  2  where owner in (
  3     SELECT username from ven_lookup where username = 'VENKAT'
  4             )
  5  union
  6  select * from ven_big_table b
  7  where owner in (
  8     select username from ven_lookup where username like 'VEN%'
  9             )
 10  union
 11  select * from ven_big_table b
 12  where owner in (
 13     select 'NOUSER' from dual
 14*            )
SQL>/

12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3073737340

-----------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |    12 |  1193 |    17  (77)|
|   1 |  SORT UNIQUE                    |                  |    12 |  1193 |    17  (77)|
|   2 |   UNION-ALL                     |                  |       |       |            |
|   3 |    MERGE JOIN CARTESIAN         |                  |    10 |  1000 |     4   (0)|
|*  4 |     TABLE ACCESS FULL           | VEN_LOOKUP       |     1 |     7 |     2   (0)|
|   5 |     BUFFER SORT                 |                  |    10 |   930 |     2   (0)|
|   6 |      TABLE ACCESS BY INDEX ROWID| VEN_BIG_TABLE    |    10 |   930 |     2   (0)|
|*  7 |       INDEX RANGE SCAN          | IDX_BIGTAB_OWNER |    10 |       |     1   (0)|
|*  8 |    HASH JOIN                    |                  |     1 |   100 |     6  (17)|
|   9 |     TABLE ACCESS BY INDEX ROWID | VEN_BIG_TABLE    |     5 |   465 |     3   (0)|
|* 10 |      INDEX RANGE SCAN           | IDX_BIGTAB_OWNER |     5 |       |     2   (0)|
|* 11 |     TABLE ACCESS FULL           | VEN_LOOKUP       |     1 |     7 |     2   (0)|
|  12 |    MERGE JOIN CARTESIAN         |                  |     1 |    93 |     4   (0)|
|  13 |     TABLE ACCESS BY INDEX ROWID | VEN_BIG_TABLE    |     1 |    93 |     2   (0)|
|* 14 |      INDEX RANGE SCAN           | IDX_BIGTAB_OWNER |     1 |       |     1   (0)|
|  15 |     BUFFER SORT                 |                  |     1 |       |     2   (0)|
|  16 |      FAST DUAL                  |                  |     1 |       |     2   (0)|
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("USERNAME"='VENKAT')
   7 - access("OWNER"='VENKAT')
   8 - access("OWNER"="USERNAME")
  10 - access("OWNER" LIKE 'VEN%')
       filter("OWNER" LIKE 'VEN%')
  11 - filter("USERNAME" LIKE 'VEN%')
  14 - access("OWNER"='NOUSER')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       1347  bytes sent via SQL*Net to client
        236  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         12  rows processed

So it looks like OWNER column has proper index and is being used ,but it throws wrong estimates when checked against the unionized subquery.

Could you please give me some directions on what to look and where to start?

Thanks

figured out the mystery

Oraboy, October 25, 2006 - 2:31 pm UTC 

I think I figured out why the estimates went overboard when it had to use a NL/hash_join

basically the subquery output is treated as a bind variable input to outer query

SQL>explain plan for
  2  select * from ven_big_table where owner=:b1;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4166374032

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |  1420 |   128K|    48   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| VEN_BIG_TABLE    |  1420 |   128K|    48   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_BIGTAB_OWNER |  <b>1420</b> |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"=:B1)

And since the owner column is highly skewed its selectivity is computed as num_rows/num_distincts ( I am no JLewis,but just guessing ;-) )

num_rows= 52550
num_distinct=37

<b>selectivity=52550/37=1420.27</b>

Math apart, how do you think I can fix this query? (to use the Index)

Difference of create or replace type and package type myTableType is table of

A reader, March 11, 2009 - 12:05 pm UTC

Hi Tom,
I make a package with type myTableType and I try to use like you explain and it doesn't work.
What are the difference between 
create or replace type myTableType as table of number
and 
create or replace package hgmdummy astype myTableType is table of number;
end hgmdummy;
?
Thanks
Tom Kyte
March 12, 2009 - 7:48 am UTC

create or replace type - that creates a new SQL datatype. SQL can see that datatype, SQL can use that datatype.

in the package, you have created a new PLSQL datatype, SQL cannot see PLSQL datatypes. Only plsql can see plsql datatypes.

Think of SQL being 'lower level' then plsql, plsql is a 'higher level language on top of SQL' - everything SQL sees, plsql can see, but since plsql is "above" SQL, things that plsql invents are not necessarily visible to SQL

A reader, December 08, 2009 - 8:26 am UTC

i am getting three input variable all string ver1(1,2,3), ver2(a,b,c) ver3(j,k,l) i use the method you described at http://tkyte.blogspot.com/2006/06/varying-in-lists.html by creating three different view or there is some new option available in 11.2 G.
Tom Kyte
December 10, 2009 - 12:45 pm UTC

you can use inline views
with subquery factoring
or the TABLE() clause

but - in the end, it will be 6 one way and half a dozen the other.

that is - the same amount of work.

8i Solution

A reader, February 17, 2010 - 10:24 am UTC

Is there any performance reason to favor the 9i solution over the 8i solution? The 8i solution looks pretty straightforward.

SELECT *
FROM TABLE(CAST(in_list('1, 2, 3, 4, 5') AS MyTableType));

This is particular true considering that the 9i solution may require using sys_context to simplify the code.
Tom Kyte
February 17, 2010 - 11:03 am UTC

It is straight forward.

It however entails creation of an object type and installation of a stored procedure.

Those are two things some people just don't want to do.

sys_context vs temporary table

A reader, February 17, 2010 - 10:28 am UTC

Can a temporary table be used in place of sys_context for temporary storage of an in list? Is there any disadvantages of using a temporary table?
Tom Kyte
February 17, 2010 - 11:04 am UTC

you could definitely use a global temporary table.

using sys_context would be awkward for an inlist.

in list

A reader, February 17, 2010 - 11:52 am UTC


Naveen, March 18, 2010 - 1:07 am UTC

It's still the best solution available.
Thanks Tom
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library