db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Issue Comment Edited: (DERBY-4136) Allow optimizer overrides for UPDATE and DELETE
Date Sun, 13 Jun 2010 20:23:14 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4136?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12878432#action_12878432
] 

Dag H. Wanvik edited comment on DERBY-4136 at 6/13/10 4:22 PM:
---------------------------------------------------------------

I tried to enable the override for DELETE from table, by just enabling
the usage of DERBY-PROPERTIES override in the parser for the DELETE statement:

In deleteBody:
   [targetProperties = propertyList(false) <CHECK_PROPERTIES>]  ->
   [targetProperties = propertyList(true) <CHECK_PROPERTIES>]

(true means allowed for external usage) and ran this experiment:

connect 'jdbc:derby:wombat;create=true';
create table foo(c varchar(100), i int, j int);
insert into foo values ('aaa',4,5),('bbb',6,7);
create unique index ic on foo(c);

delete from foo --derby-properties index=ic
;

Looking at the execution plan it seems to have picked up this information, cf. the line containing

   "User supplied optimizer overrides on FOO are { index=IC }"

So, I wonder, was there a reason why the overrride feature was not
made available for ordinary users at the time it was added (it seems
to be enabled for internal usage, cf. the final check in propertyList,
although it may not be in use...

sqlgrammar.jj#propertyList:
        :
        // if this property override is supported in internal mode
        // only, then do that verification here.  if
        (!propertiesUseAllowed)
            checkInternalFeature("DERBY-PROPERTIES");


 ******* Delete ResultSet using row locking:
deferred: false
Rows deleted = 2
Indexes updated = 1
Execute Time = 0
        Project-Restrict ResultSet (2):
        Number of opens = 1
        Rows seen = 2
        Rows filtered = 0
        restriction = false
        projection = true
                constructor time (milliseconds) = 0
                open time (milliseconds) = 0
                next time (milliseconds) = 0
                close time (milliseconds) = 0
                restriction time (milliseconds) = 0
                projection time (milliseconds) = 0
                optimizer estimated row count:            7.00
                optimizer estimated cost:           20.17

        Source result set:
                Project-Restrict ResultSet (1):
                Number of opens = 1
                Rows seen = 2
                Rows filtered = 0
                restriction = false
                projection = true
                        constructor time (milliseconds) = 0
                        open time (milliseconds) = 0
                        next time (milliseconds) = 0
                        close time (milliseconds) = 0
                        restriction time (milliseconds) = 0
                        projection time (milliseconds) = 0
                        optimizer estimated row count:            7.00
                        optimizer estimated cost:           20.17

                Source result set:
                        User supplied optimizer overrides on FOO are { index=IC }
                        Index Scan ResultSet for FOO using index IC at read committed isolation
level using exclusive row locking chosen by the optimizer
                        Number of opens = 1
                        Rows seen = 2
                        Rows filtered = 0
                        Fetch Size = 1
                                constructor time (milliseconds) = 0
                                open time (milliseconds) = 0
                                next time (milliseconds) = 0
                                close time (milliseconds) = 0
                                next time in milliseconds/row = 0

                        scan information:
                                Bit set of columns fetched={0, 1}
                                Number of columns fetched=2
                                Number of deleted rows visited=0
                                Number of pages visited=1
                                Number of rows qualified=2
                                Number of rows visited=2
                                Scan type=btree
                                Tree height=1
                                start position:
                                        None
                                stop position:
                                        None
                                qualifiers:
                                        None
                                optimizer estimated row count:            7.00
                                optimizer estimated cost:           20.17

----------------------------------------------------------------


      was (Author: dagw):
    I tried to enable the override for DELETE from table, by just enabling
the usage of DERBY-PROPERTIES override in the parser for the DELETE statement:

In deleteBody:
   [targetProperties = propertyList(false) <CHECK_PROPERTIES>]  ->
   [targetProperties = propertyList(true) <CHECK_PROPERTIES>]

(true means allowed for external usage) and ran this experiment:

connect 'jdbc:derby:wombat;create=true';
create table foo(c varchar(100), i int, j int);
insert into foo values ('aaa',4,5),('bbb',6,7);
create unique index ic on foo(c);

delete from foo --derby-properties index=ic
;

Looking at the execution plan it seems to have picked up this information, cf. the line containing

   "User supplied optimizer overrides on FOO are { index=IC }"

So, I wonder, was there a reason why the overrride feature was not
made available for ordinary users at the time it was added (it seems
to be enabled for internal usage, cf. the final check in propertyList,
although it may not be is use...

sqlgrammar.jj#propertyList:
        :
        // if this property override is supported in internal mode
	// only, then do that verification here.  if
	(!propertiesUseAllowed)
	    checkInternalFeature("DERBY-PROPERTIES");


 ******* Delete ResultSet using row locking:
deferred: false
Rows deleted = 2
Indexes updated = 1
Execute Time = 0
	Project-Restrict ResultSet (2):
	Number of opens = 1
	Rows seen = 2
	Rows filtered = 0
	restriction = false
	projection = true
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		restriction time (milliseconds) = 0
		projection time (milliseconds) = 0
		optimizer estimated row count:            7.00
		optimizer estimated cost:           20.17

	Source result set:
		Project-Restrict ResultSet (1):
		Number of opens = 1
		Rows seen = 2
		Rows filtered = 0
		restriction = false
		projection = true
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			restriction time (milliseconds) = 0
			projection time (milliseconds) = 0
			optimizer estimated row count:            7.00
			optimizer estimated cost:           20.17

		Source result set:
			User supplied optimizer overrides on FOO are { index=IC }
			Index Scan ResultSet for FOO using index IC at read committed isolation level using exclusive
row locking chosen by the optimizer
			Number of opens = 1
			Rows seen = 2
			Rows filtered = 0
			Fetch Size = 1
				constructor time (milliseconds) = 0
				open time (milliseconds) = 0
				next time (milliseconds) = 0
				close time (milliseconds) = 0
				next time in milliseconds/row = 0

			scan information:
				Bit set of columns fetched={0, 1}
				Number of columns fetched=2
				Number of deleted rows visited=0
				Number of pages visited=1
				Number of rows qualified=2
				Number of rows visited=2
				Scan type=btree
				Tree height=1
				start position:
					None
				stop position:
					None
				qualifiers:
					None
				optimizer estimated row count:            7.00
				optimizer estimated cost:           20.17

----------------------------------------------------------------

  
> Allow optimizer overrides for UPDATE and DELETE
> -----------------------------------------------
>
>                 Key: DERBY-4136
>                 URL: https://issues.apache.org/jira/browse/DERBY-4136
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.4.2.0
>            Reporter: Ronald Tschalaer
>            Priority: Minor
>
> Since UPDATE and DELETE statements effectively run a query behind the scenes to find
the rows to update or delete, it would be useful to be able specify optimizer overrides (--
DERBY-PROPERTIES) for these statements too. As a specific use case, I have an issue with locking
where I need to ensure the indexes are always used in order to prevent deadlocks. The current
workaround is to run a SELECT (where I can supply the appropriate --DERBY-PROPERTIES index=...)
with an updatable ResultSet and do the updates/deletes on the ResultSet. While this works,
it is uglier and less clear than the corresponding UPDATE/DELETE statements.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message