Query with NULL - comp.databases.oracle.server

Hi, I have a lot of queries like below: >select * from bxat.no5 >WHERE (no0_session_id = :wp18 OR :wp18 IS NULL) >AND (tbname = :wp19 OR :wp19 IS NULL) so an access full on no5 How can I speed up this query ? Thanks in advance (Oracle 9.2.0.6)

On Dec 29, 12:19=A0pm, "bob123" <bob...@gmail.com> wrote: > Hi, > > I have a lot of queries like below:>select * from bxat.no5 > >WHERE (no0_session_id =3D :wp18 OR :wp18 IS NULL) > >AND (tbname =3D :wp19 OR :wp19 IS NULL) > > so an access full on no5 > > How can I speed up this query ? > Thanks in advance > (Oracle 9.2.0.6) Bob, If the issue is that the optimizer is choking on the ORs you might, barring adding indexes, want to consider changing your query from the present set of ORs to a set of simpler UNIONs, e.g., select * from bxat.no5 WHERE no0_session_id =3D :wp18 UNION select * from bxat.no5 WHERE wp18 IS NULL UNION .. .. .. --Jeff

Let's look at simplified query: select * from bxat.no5 WHERE (no0_session_id = :wp18 OR :wp18 IS NULL) We assume that there is an index on "no0_session_id" and this index has good selectivity. If :wp18 has a value then we want the query to use index. If :wp18 is NULL then all rows in the table satisfy WHERE clause so we want the query to use full table scan. Let's simplify the query even further and look at the plans: SQL> select * from bxat.no5 where no0_session_id = :wp18; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=13) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'NO5' (Cost=1 Card=1 Bytes=13) 2 1 INDEX (UNIQUE SCAN) OF 'NO5_IDX01' (UNIQUE) (Cost=3 Card=1) SQL> select * from bxat.no5 where :wp18 is null; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=84653 Card=42845620 Bytes=85691240) 1 0 FILTER 2 1 TABLE ACCESS (FULL) OF 'NO5' (Cost=84653 Card=42845620 Bytes=85691240) Note FILTER above. You get the same plan if you run query with condition "1=0". While plan contains full table scan Oracle doesn't actually run it if condition is false - it is stopped by FILTER. Now the original simplified query: SQL> select * from bxat.no5 where no0_session_id = :wp18 or :wp18 is null; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=84653 Card=2142282 Bytes=27849666) 1 0 TABLE ACCESS (FULL) OF 'NO5' (Cost=84653 Card=2142282 Bytes=27849666) Here "FILTER" disappeared, so Oracle always runs the full scan. Finally the version with UNION: SQL> select * from bxat.no5 where no0_session_id = :wp18 2 union 3 select * from bxat.no5 where :wp18 is null; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=156184 Card=42845621 Bytes=85691253) 1 0 SORT (UNIQUE) (Cost=156184 Card=42845621 Bytes=85691253) 2 1 UNION-ALL 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'NO5' (Cost=1 Card=1 Bytes=13) 4 3 INDEX (UNIQUE SCAN) OF 'NO5_IDX01' (UNIQUE) (Cost=3 Card=1) 5 2 FILTER 6 5 TABLE ACCESS (FULL) OF 'NO5' (Cost=84653 Card=42845620 Bytes=85691240) So the trick is to keep the FILTER.

On Dec 29, 9:19=A0pm, "bob123" <bob...@gmail.com> wrote: > I have a lot of queries like below: > select * from bxat.no5 > WHERE (no0_session_id =3D :wp18 OR :wp18 IS NULL) > AND (tbname =3D :wp19 OR :wp19 IS NULL) > > so an access full on no5 > > How can I speed up this query ? > Thanks in advance > (Oracle 9.2.0.6) One possible way to achieve this without the explicit usage of UNION suggested is the rewrite with NVL or DECODE as outlined here: http://jonathanlewis.wordpress.com/2007/01/09/conditional-sql/ Which would look something like this for your particular query: select * from bxat.no5 WHERE no0_session_id =3D nvl(:wp18, no0_session_id) AND tbname =3D nvl(:wp19, tbname) The optimizer can then transform this using a concatenation transformation to make potential use of an index. Note however that this optimisation doesn't perform multiple concatenation transformations for multiple similar predicates, as can be seen here: http://jonathanlewis.wordpress.com/2007/02/14/conditional-sql-2/ so it might not work that well in your particular case. and you need to be aware that this re-write is not exactly the same as your statement if the columns are nullable since the re-write filters out any rows where the column is null whereas your original statement does not. Regards, Randolf Oracle related stuff blog: http://oracle-randolf.blogspot.com/ Co-author of the forthcoming "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/14= 30226684

On Dec 29, 4:39=A0pm, jefftyzzer <jefftyz...@sbcglobal.net> wrote: > On Dec 29, 12:19=A0pm, "bob123" <bob...@gmail.com> wrote: > > > Hi, > > > I have a lot of queries like below:>select * from bxat.no5 > > >WHERE (no0_session_id =3D :wp18 OR :wp18 IS NULL) > > >AND (tbname =3D :wp19 OR :wp19 IS NULL) > > > so an access full on no5 > > > How can I speed up this query ? > > Thanks in advance > > (Oracle 9.2.0.6) > > Bob, > > If the issue is that the optimizer is choking on the ORs you might, > barring adding indexes, want to consider changing your query from the > present set of ORs to a set of simpler UNIONs, e.g., > > select * > from bxat.no5 > WHERE no0_session_id =3D :wp18 > UNION > select * > from bxat.no5 > WHERE > wp18 IS NULL > UNION > . > . > . > > --Jeff Being that :wp18 and :wp19 are program bind variables then depending on which variables have a value they are actually 4 different conditions: return all rows when neither variable has a value, return matching rows for wp18 when wp19 is null,, return matching rows for wp19 when wp18 is null, and return only rows that match both wp18 and wp19 when both have a value. While one query could be written to handle this set of requirements, I would consider to make the correct desired result clear for future maintenance programmers just coding a simple SQL statement for each condition within a IF structure within the program. That way, based on which variables have values the correct desired SQL statement is submitted. I think taking the simple approach would likely greatly reduce the chance the CBO chooses anything but the optimal plan for the submitted query and again the simplier SQL will be unlikely to confuse any future maintenance programmer as to what result is desired. Which approach is best is going to depend on the environment: additional complexity in where clause conditions, how static the application is expected to be, etc.... It may be that each approach will prove beneficial depending on the program in question. HTH -- Mark D Powell --

On Dec 30, 7:36=A0am, Mark D Powell <Mark.Powe...@hp.com> wrote: > > While one query could be written to handle this set of requirements, I > would consider to make the correct desired result clear for future > maintenance programmers just coding a simple SQL statement for each > condition within a IF structure within the program. =A0That way, based > on which variables have values the correct desired SQL statement is > submitted. > > I think taking the simple approach would likely greatly reduce the > chance the CBO chooses anything but the optimal plan for the submitted > query and again the simplier SQL will be unlikely to confuse any > future maintenance programmer as to what result is desired. I'd go for a CASE over IF for future maintenance, if it is a tossup. Had one of those yesterday, was so glad it was CASE - took 5 minutes, most of which was source control/comments, would have needed much more work if IF's. The issue is often that over time, the IF's get more complicated - people can't resist nesting and ELSE. This usually arises from changing business rules to account for all the situations not thought of (or admitted) originally. I know, you'd think there would be no difference since they can be logically equivalent... but programmer behavior removes the equivalency. jg -- @home.com is bogus. http://100waystokillapeep.blogspot.com/2009/04/psycho-bunny-killer.html

On 12/31/2009 09:58 AM, bob123 wrote: > OK thanks all ... > The problem is that i can't rewrite the code > it's a third party software ... > any clue ? Difficult... You can't easily use stored outlines because those would be optimal for a single of the four cases. You may get away with outlines in four different categories but then you somehow need to switch between them before the statement is executed. I guess everything you attempt will soon get messy. Is there no chance to get the vendor of the application to change it? Maybe you can file it as bug and get a resolution via your support contract. Good luck! robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/

On Tue, 29 Dec 2009 21:19:22 +0100, bob123 wrote: >>select * from bxat.no5 >>WHERE (no0_session_id = :wp18 OR :wp18 IS NULL) AND (tbname = :wp19 OR >>:wp19 IS NULL) I would advise against checking whether an external variable is NULL in the SQL. I am aware that Hibernate sometimes does that, but that can be reworked. -- http://mgogala.byethost5.com

On Wed, 30 Dec 2009 07:36:19 -0800, Mark D Powell wrote: > Being that :wp18 and :wp19 are program bind variables then depending on > which variables have a value they are actually 4 different conditions: > return all rows when neither variable has a value, return matching rows > for wp18 when wp19 is null,, return matching rows for wp19 when wp18 is > null, and return only rows that match both wp18 and wp19 when both have > a value. Plus the fact that the job checking whether an external variable is null really belongs to the programming interface and not the database. -- http://mgogala.byethost5.com

On Dec 31, 8:33=A0am, Mladen Gogala <gogala.mla...@gmail.com> wrote: > On Wed, 30 Dec 2009 07:36:19 -0800, Mark D Powell wrote: > > Being that :wp18 and :wp19 are program bind variables then depending on > > which variables have a value they are actually 4 different conditions: > > return all rows when neither variable has a value, return matching rows > > for wp18 when wp19 is null,, return matching rows for wp19 when wp18 is > > null, and return only rows that match both wp18 and wp19 when both have > > a value. > > Plus the fact that the job checking whether an external variable is null > really belongs to the programming interface and not the database. > > --http://mgogala.byethost5.com Since the OP has now informed us the SQL is part of a package and cannot be changed I think the OP's options are limited. Stored outlines might be of use but I think it is unlikely since one possible condition in the SQL as posted requires a full table scan that I do not think there is any way around without changing the SQL. I suggest complaining to the vendor about the manner in which the SQL is written and its effect on performance. I have some limited experience with DB2 UDB and a liittle more experience with SQL Server so I believe it is safe to say that having the program issue simplier SQL for each desired result set based on program variables having values or not would be more effiicient in all three systems than having to have that check made in the SQL. I think this last is pretty much in agreement with Mgogla's previous post in that some actions belong in the program and some in the database. HTH -- Mark D Powell --

bob123 (bob123@gmail.com) wrote: : Hi, : I have a lot of queries like below: : >select * from bxat.no5 : >WHERE (no0_session_id = :wp18 OR :wp18 IS NULL) : >AND (tbname = :wp19 OR :wp19 IS NULL) : so an access full on no5 : How can I speed up this query ? : Thanks in advance : (Oracle 9.2.0.6) 1. Try adding the not null explicitly to each section, I seem to recall that helps the optimizer recognize what to do. select * from bxat.no5 WHERE ( :wp18 IS not NULL and no0_session_id = :wp18 OR :wp18 IS NULL) --------------------- AND ( :wp19 IS not NUL and tbname = :wp19 OR :wp19 IS NULL) --------------------- then you could try using union. Someone suggested that though an illustration of it didn't seem to use it correctly select * from bxat.no5 WHERE ( :wp18 IS not NULL and no0_session_id = :wp18 OR :wp18 IS NULL) union select * from bxat.no5 WHERE ( :wp19 IS not NUL and tbname = :wp19 OR :wp19 IS NULL) if the union helps but the main part of the query is complicated then put it in a with clause (my example may have the syntax wrong) with ( select * from bxat.no5 where complicated stuff in common ) as the_query select * from the_query where ( :wp18 IS not NULL and no0_session_id = :wp18 OR :wp18 IS NULL) union select * from the_query where ( :wp19 IS not NUL and tbname = :wp19 OR :wp19 IS NULL)

On Thu, 31 Dec 2009 08:30:45 -0800, Mark D Powell wrote: > Since the OP has now informed us the SQL is part of a package and cannot > be changed I think the OP's options are limited. In other words, the OP is screwed. Happy New Year. -- http://mgogala.byethost5.com

On Thu, 31 Dec 2009 08:30:45 -0800, Mark D Powell wrote: > Since the OP has now informed us the SQL is part of a package and cannot > be changed I think the OP's options are limited. Stored outlines might > be of use but I think it is unlikely since one possible condition in the > SQL as posted requires a full table scan that I do not think there is > any way around without changing the SQL. In 10G there is DBMS_ADVANCED_REWRITE package which can be used to rewrite the bad SQL even if there is no access to the source. The procedure in the package is "DECLARE_REWRITE_EUIVALENCE". A brief example is available here: http://www.blogskies.com/2009/07/dbmsadvancedrewrite.html Somewhat more detailed article is here: http://www.oracle-base.com/articles/10g/dbms_advanced_rewrite.php -- http://mgogala.freehostia.com

On Jan 3, 9:06=A0pm, Mladen Gogala <gogala.mla...@gmail.com> wrote: > In 10G there is DBMS_ADVANCED_REWRITE package which can be used to > rewrite the bad SQL even if there is no access to the source. The > procedure in the package is "DECLARE_REWRITE_EUIVALENCE". A brief > example is available here: I haven't suggested this because in its present form in 10g (haven't tested it yet in newer releases, but I think these restrictions still apply) it has some limitations that don't make it suitable for OLTP like applications. In particular it doesn't support SQLs that contain bind variables and therefore it is probably not applicable to the statements mentioned here. See Metalink (sorry, MOS) Note 392214.1 Dion Cho has also two interesting articles about this package: http://dioncho.wordpress.com/2009/03/06/optimizing-unoptimizeable-sql-dbms_= advanced_rewrite/ http://dioncho.wordpress.com/2009/10/13/dbms_advanced_rewrite-and-dml/ Regards, Randolf Oracle related stuff blog: http://oracle-randolf.blogspot.com/ Co-author of the forthcoming "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/14= 30226684

On Mon, 04 Jan 2010 02:52:53 -0800, Randolf Geist wrote: > See Metalink (sorry, MOS) Note 392214.1 I'm aware of that paper, but that stinking pile of ... stuff ain't working... again. > > Dion Cho has also two interesting articles about this package: > > http://dioncho.wordpress.com/2009/03/06/optimizing-unoptimizeable-sql- dbms_advanced_rewrite/ > > http://dioncho.wordpress.com/2009/10/13/dbms_advanced_rewrite-and-dml/ > > Regards, > Randolf I wasn't aware of these two excellent articles. Thanks again. -- http://mgogala.byethost5.com

On 29 dec 2009, 22:39, jefftyzzer <jefftyz...@sbcglobal.net> wrote: > On Dec 29, 12:19=A0pm, "bob123" <bob...@gmail.com> wrote: > > > Hi, > > > I have a lot of queries like below:>select * from bxat.no5 > > >WHERE (no0_session_id =3D :wp18 OR :wp18 IS NULL) > > >AND (tbname =3D :wp19 OR :wp19 IS NULL) > > > so an access full on no5 > > > How can I speed up this query ? > > Thanks in advance > > (Oracle 9.2.0.6) > > Bob, > > If the issue is that the optimizer is choking on the ORs you might, > barring adding indexes, want to consider changing your query from the > present set of ORs to a set of simpler UNIONs, e.g., > > select * > from bxat.no5 > WHERE no0_session_id =3D :wp18 > UNION > select * > from bxat.no5 > WHERE > wp18 IS NULL > UNION > . > . > . > > --Jeff That should be a "UNION ALL". a. performance (saves an expensive SORT) B. to get all rows, even if there are duplicates

On Jan 5, 4:32=A0am, "Gerard H. Pille" <ghpi...@hotmail.com> wrote: > On 29 dec 2009, 22:39, jefftyzzer <jefftyz...@sbcglobal.net> wrote: > > > > > On Dec 29, 12:19=A0pm, "bob123" <bob...@gmail.com> wrote: > > > > Hi, > > > > I have a lot of queries like below:>select * from bxat.no5 > > > >WHERE (no0_session_id =3D :wp18 OR :wp18 IS NULL) > > > >AND (tbname =3D :wp19 OR :wp19 IS NULL) > > > > so an access full on no5 > > > > How can I speed up this query ? > > > Thanks in advance > > > (Oracle 9.2.0.6) > > > Bob, > > > If the issue is that the optimizer is choking on the ORs you might, > > barring adding indexes, want to consider changing your query from the > > present set of ORs to a set of simpler UNIONs, e.g., > > > select * > > from bxat.no5 > > WHERE no0_session_id =3D :wp18 > > UNION > > select * > > from bxat.no5 > > WHERE > > wp18 IS NULL > > UNION > > . > > . > > . > > > --Jeff > > That should be a "UNION ALL". > > a. performance =A0(saves an expensive SORT) > B. to get all rows, even if there are duplicates With respect, while I readily agree that UNION ALL is likely faster given that it obviates the need for a duplicate-eliminating sort, I think the UNION is necessary precisely because it eliminates duplicates. My goal was to suggest an alternate syntax that was semantically equivalent to the OP's original query, which wouldn't have returned duplicate rows given that it made only a single pass through the "bxat.no5" table (whether the "bxat.no5" table contains duplicates is a different matter altogether). Regards, --Jeff

bob123 wrote: > OK thanks all ... > The problem is that i can't rewrite the code > it's a third party software ... > any clue ? > > written in a far and distant country, where the programmers had no clue about the data model. Speculating, of course, but I've seen these constructs before. Bet ya those columns are NOT NULL. -- Regards, Frank van Bortel

Similiar Articles:

Query with NULL - comp.databases.oracle.server
Hi, I have a lot of queries like below: >select * from bxat.no5 >WHERE (no0_session_id = :wp18 OR :wp18 IS NULL) >AND (tbname = :wp19 OR :wp19 I...

(Null) File: /fmi/xml/fmresultset.xml - comp.databases.filemaker ...
I am trying to run a query http://localhost/fmi/xsl/test.xsl?-grammar=fmresultset&-encoding=UTF-8&-db=TRS2-arc.fp7&-lay=entry&-find&-max=10 and I ge...

Bad query - comp.databases.mysql
Hello, I want to update the nb_mail column with this query but the select query returns empty results in same case while i need 0 value. An idea...

Hibernate305: delete query fails with "must begin with SELECT or ...
Query with NULL - comp.databases.oracle.server... original simplified query: SQL> select * from bxat.no5 where no0_session_id = :wp18 or ... Hibernate305: delete query ...

update field with increment values - comp.databases.mysql ...
... any php loop i use when fields have values update table set col=col+1 where ..... but couldn't find anywhere how to do when values are 0 or null with only mysql query ...

you tried to assign null value to a variable that is not a variant ...
OK, I'm pulling my hair out. I'm trying to append records to a table. No code, just an append query. When the table is in the same database, it wor...

Append Query works in query view but not when run from VBA - comp ...
Query with NULL - comp.databases.oracle.server You get the same plan if you run query ... sql-2/ so it might not work that well ... I'm trying to append records to a table.

Visual Query Builder WHERE Clause - comp.soft-sys.matlab ...
Query with NULL - comp.databases.oracle.server If :wp18 is NULL then all rows in the table satisfy WHERE clause so we want the query to ... sys.matlab ... help with order ...

Syntax Error In Select Where Query - comp.databases.btrieve ...
Query with NULL - comp.databases.oracle.server Hi, I have a lot of queries like below: >select * from bxat.no5 >WHERE (no0_session_id ... Syntax error trying to return ...

SQL0101N for a Select query - comp.databases.ibm-db2
Query with NULL - comp.databases.oracle.server Hi, I have a lot of queries like below: >select * from bxat.no5 >WHERE (no0_session_id = :wp18 OR :wp18 IS NULL) >AND ...

pass through query from access to oracle - date format - comp ...
Query with NULL - comp.databases.oracle.server pass through query from access to oracle - date format - comp ... Query with NULL - comp.databases.oracle.server pass ...

How to query when a text field is too big to convert? - comp ...
My problem with the query below is that field_2 is a text field and is too fat to ... SQL 2005: > > =A0 =A0CREATE TABLE sometable (field_1 text NULL, field_2 text NULL ...

Can't export a pass through query... - comp.databases.ms-access ...
... oracle - date format - comp ... two-table, many to one query - comp.databases.mysql | id | date | title ... Can't export a pass through query... - comp ... Query with NULL ...

Concatenating address line for report - comp.databases.ms-access ...
The query has this: Address: [fldCompanyname] & ", " & [fldAddress1 ... Assuming the missing data is NULL Address: [fldCompanyname ...

How best to detect duplicate values in a column? - comp.databases ...
If resultset is empty there's no problem, else deal with it. - Subquery the ... You didn't even attempt to answer my original question: Which type of SQL query is ...

How to Access a Null Query | eHow.com
Null queries in Access let you find records where some of the fields are empty. This can help you find potential problems in your tables so you can resolve this issue ...

How to Access Query Null Values | eHow.com
Microsoft Access is a database management solution that allows users to edit and deploy databases via an easy-to-use graphics interface. Part of the Microsoft Office ...

7/25/2012 1:20:56 PM