Complex Object Type and SQL

SQL Optimizer SQL Optimizer for Oracle
1

Recently I have started using some Oracle Complex Types for Table Functions in SQL. I know that the Quest SQL Optimizer can’t do much with them but I would like to optimize the statements that use the table function, even if all I can do is run my own permutations of SQL to look at resources memory, disk IO, etc…

Is there any chance we can get this in the product. If you need example code please let me know.

I also seem to find that it leads to Access Violations.

2

Hi David,

Thanks for your interest in SQL Optimizer. We are glad to hear from users what they need so we can make the product a better tool.

I would like to see some example code. It would help us understand your requirements and make sure that we are providing what you need.

Regarding the Access Violation, we are aware that there are intermittent occurences. We are looking at this and hope that it will be improved in the next release.

Thanks,
Alex

3

Sorry for the delay. Here is a few snippets of code.

So here is the query that I would like to be able to run in SQL Optimizer to help get the stats back in a way that is much easier to digest.

SELECT /*+ FIRST_ROWS(100) */
vbugs.bug_id,
vbugs.bug_status,
vbugs.def_type,
vbugs.smr_state,
vbugs.reporter_disp_name,
vbugs.assigned_by_disp_name,
vbugs.assigned_disp_name,
vbugs.facility,
vbugs.version,
vbugs.product,
vbugs.peg_total,
vbugs.bus_total,
vbugs.gpeg,
vbugs.short_desc
FROM vbugs,
(SELECT COLUMN_VALUE watchid
FROM TABLE (hotquery.watch_issues (‘DBUSBY3’, 200))) watchs
WHERE vbugs.bug_id = watchs.watchid

So that low level query is the table function. And here is the code for the table function.

FUNCTION watch_issues (myldapid IN userpass.ldapalias%TYPE,
maxrows IN PLS_INTEGER:= hotquery.maxrecords)
RETURN issue_ids
IS
var_useralias userpass.ldapalias%TYPE
:= UPPER (watch_issues.myldapid) ;
datarows issue_ids;

  CURSOR watching_cur (cursor_var_useralias IN userpass.ldapalias%TYPE)
  IS
     SELECT   wi.bug_id
       FROM   watchissues wi
      WHERE   wi.ldapalias = cursor_var_useralias;

BEGIN
DBMS_APPLICATION_INFO.set_module (
module_name => ‘Oracle SWIFT Engine’,
Action_name => ‘HotQuery watch_issues’
);
OPEN watch_issues.watching_cur
(cursor_var_useralias => watch_issues.
var_useralias);

  FETCH watch_issues.watching_cur
     BULK COLLECT INTO   watch_issues.datarows
     LIMIT LEAST (watch_issues.maxrows, HOTQUERY.MAXRECORDS);

  CLOSE watch_issues.watching_cur;

  RETURN watch_issues.datarows;

END watch_issues;

Now if you need anything else please let me know or contact Bert Scalzo if you need to reach me as well.

4

If you need more examples please let me know. I think as more people discover and start using this your going to see more and more of this occur.

5

Hi David,

Thank you for sending the code snippet. I have added your request to our enhancement list. Feel free to contact us if you have any other requests.

Thanks,
Alex