on delete null and accessing optimiser costs
The poster asked two things: how to obtain an optimiser cost estimate for a dynamically built SELECT without actually running it (so an application can warn users about slow queries), and whether IDS 11 would add an "ON DELETE NULL" option for foreign keys. Replies suggested SET EXPLAIN ON AVOID_EXECUTE, which writes the plan and cost to sqexplain.out; when the poster said he needed the cost returned to the application, Art Kagel advised querying sysmaster:syssqexplain after preparing the statement, filtering by session id (picking the right row if several statements are prepared). The ON DELETE NULL question went unanswered.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, does anyone know: i) Is it possible to access optimiser costs for a select in statement without executing the select. In other words, the result of the select would be a cost rather than the result set. This would be useful where queries are built dynamically by a program, and the user can be given feedback on expected peformance i) Will ids 11 provide a "on delete null" option for foreign key constraints? Thanks in advance.
for the 1st question: set explain on avoid_execute; select mycol from mytab where mycol2 = 'myvalue'; this will return no rows and will generate the optimizer costs in the sqexplain.out file. I do not know about the IDS 11 question ... ________________________________ From: ids-bounces@iiug.org on behalf of ANTHONY PERSIC Sent: Thu 11/29/2007 9:43 PM To: ids@iiug.org Subject: on delete null and accessing optimiser costs [10544] Hi, does anyone know: i) Is it possible to access optimiser costs for a select in statement without executing the select. In other words, the result of the select would be a cost rather than the result set. This would be useful where queries are built dynamically by a program, and the user can be given feedback on expected peformance i) Will ids 11 provide a "on delete null" option for foreign key constraints? Thanks in advance. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
set explain on avoid_execute;
select my_crazy_sql from my_important_table;
----- Original Message -----
From: "ANTHONY PERSIC" <tony.persic@amcor.com.au>
To: <ids@iiug.org>
Sent: Thursday, November 29, 2007 10:43 PM
Subject: on delete null and accessing optimiser costs [10544]
> Hi, does anyone know:
>
> i) Is it possible to access optimiser costs for a select in statement
> without
> executing the select. In other words, the result of the select would be a
> cost
> rather than the result set. This would be useful where queries are built
> dynamically by a program, and the user can be given feedback on expected
> peformance
>
> i) Will ids 11 provide a "on delete null" option for foreign key
> constraints?
>
> Thanks in advance.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Bored tonight? -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Joe Plugge Sent: Thursday, November 29, 2007 10:22 PM To: ids@iiug.org Subject: RE: on delete null and accessing optimiser costs [10545] for the 1st question: set explain on avoid_execute; select mycol from mytab where mycol2 = 'myvalue'; this will return no rows and will generate the optimizer costs in the sqexplain.out file. I do not know about the IDS 11 question ... ________________________________ From: ids-bounces@iiug.org on behalf of ANTHONY PERSIC Sent: Thu 11/29/2007 9:43 PM To: ids@iiug.org Subject: on delete null and accessing optimiser costs [10544] Hi, does anyone know: i) Is it possible to access optimiser costs for a select in statement without executing the select. In other words, the result of the select would be a cost rather than the result set. This would be useful where queries are built dynamically by a program, and the user can be given feedback on expected peformance i) Will ids 11 provide a "on delete null" option for foreign key constraints? Thanks in advance. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.
Sorry didn't make myself clear. I'd like the optimiser cost to be returned by the select statement (not the real result), rather than have to go to the sqexplain output file -- but hard to do that in an application.
ANTHONY PERSIC wrote: > Sorry didn't make myself clear. I'd like the optimiser cost to be returned by > the select statement (not the real result), rather than have to go to the > sqexplain output file -- but hard to do that in an application. > After preparing a statement the application can get it's session number and use it to query the sysmaster:syssqexplain table. The cost will be in there. If there are multiple outstanding (ie prepared but not freed) statements you will get multiple records from that table for your session and will have to select which is relevant. Art S. Kagel > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >