Toad World® Forums

Optimize context dependent queries


#1

Let’s say we have a view depending on a package variable like this:

CREATE OR REPLACE PACKAGE ptest
AS
ps_login VARCHAR2(100 CHAR);

FUNCTION get_login
RETURN VARCHAR2;

PROCEDURE set_login(is_login IN VARCHAR2);
END ptest;
/

CREATE OR REPLACE PACKAGE BODY ptest
AS
PROCEDURE set_login(is_login IN VARCHAR2)
AS
BEGIN
ps_login := is_login;
END set_login;

FUNCTION get_login
RETURN VARCHAR2
AS
BEGIN
RETURN ps_login;
END get_login;
END ptest;
/

CREATE OR REPLACE VIEW v$test
AS
SELECT ptest.get_login FROM dual;

Now I want to optimize a query to v$test like

SELECT * FROM v$test;

Of course this is a very simple example.

The basic question is how to optimize context dependent queries with sql optimizer?


#2

Sorry for late reply,

We have a feature call view to inline view, which is extract the view into calling SQL statement. For your example, we may generate a SQL

SELECT * FROM (SELECT ptest.get_login FROM dual)

However, it doesn’t apply to package as it may include program logic. We can’t design what we should extract unless we do a parser for PL/SQL.

Tony Ng


#3

Hi Tony,

thx for the reply. Maybe my question was not clear/precise. Or I’m missing something here. I don’t want to optimize the PL/SQL code. It’s just that I want to run the statement in sql optimizer for oracle but before doing so I have to run some PL/SQL for initialization, here it would be ptest.set_login(‘foo’).

Thx in advance, Hans


#4

got it - that’s what I was searching for

Otions > General > Options > Execute Login Script


#5

First, I am sorry to misunderstand your question.

The login script is designed for executing just after making connection. Normally, user may want to change the session configuration such as alter session … It should fit for your need in this case. However, if your script take really long time to run, you may wait for it until finished. There is no about function during connection.

Please let me know if you have problem to use it.

Thanks,

Tony Ng


#6

Hi Tony, no problem, we are humans :wink:

It helps for now. I will add a feature request.


#7

Hi Tony,

no problem, we are humans.

I added

http://www.toadworld.com/products/toad-for-oracle/i/other/sql_optimizer_for_oracle_-_run_script_in_addition_to_exec_login_script

to the idea pond. Of course it would be great not to have to restart sqlopt for changing a context parameter.

But at least there is a way.

Kind regards

Hans

Von: TonyNg [mailto:bounce-TonyNg@toadworld.com]

Gesendet: Mittwoch, 22. November 2017 02:47

An: sqloptoracle@toadworld.com

Betreff: RE: [SQL Optimizer for Oracle - Discussion Forum] Optimize context dependent queries

RE: Optimize context dependent queries

Reply by TonyNg

First, I am sorry to misunderstand your question.

The login script is designed for executing just after making connection. Normally, user may want to change the session configuration such as alter session … It should fit for your need in this case. However, if your script take really long time to run, you may wait for it until finished. There is no about function during connection.

Please let me know if you have problem to use it.

Thanks,

Tony Ng

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from SQL Optimizer for Oracle Forum
notifications altogether.

SQL Optimizer for Oracle - Discussion Forum

Flag
this post as spam/abuse.