Hi Vlad,

> This is my current scenario.
>
> I have a SP that has the WHERE clause and looks something like(simplified

version):

>
> PROCEDURE GET_DATA(IN_USERID INTEGER)
> RETURNS(
> OUT_NAME varchar(50),
> OUT_SIZE integer)
> AS
> BEGIN
> FOR
> SELECT
> NAME,
> SIZE
> FROM DIRS
> WHERE USER_ID = IN_USERID --USER_ID is a primary key
> INTO OUT_NAME, OUT_SIZE
> END
>
> I also have another SP that does ORDERing:
>
> PROCEDURE GET_DATA_WRAPPER(IN_USERID INTEGER, IN_ORDERBY varchar(32))
>
> RETURNS(
>
> OUT_NAME varchar(50),
>
> OUT_SIZE integer)
>
> AS
>
> BEGIN
>
> FOR
> EXECUTE STATEMENT 'SELECT * FROM GET_DATA(' || :IN_USERID || ') ORDER BY

' || :IN_ORDERBY

> INTO OUT_NAME, OUT_SIZE
>
> END
>
> When I run SELECT * FROM GET_DATA(1) optimizer picks PLAN (DIRS INDEX

(UNQ1_DIRS)).

>
> When I run SELECT * FROM GET_DATA_WRAPPER(1) optimizer picks PLAN

(GET_DATA_WRAPPER NATURAL)

In the first case, it acually reports the table and index used, while
in the second case, it cannot report anything, so it seems, besides
the procedure name. That is a bit weird as well.

> Does the optimizer picks PLAN (DIRS INDEX (UNQ1_DIRS)) inside the

GET_DATA() when I call SELECT * FROM GET_DATA_WRAPPER(1)?

>

I think so, but not reporting it due to EXECUTE STATEMENT.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com