Anyway these queries didn't return any records:
SELECT * FROM problem WHERE solved = '0';
SELECT * FROM problem WHERE solved = 0;
As you can see in the query different predicates are used. One compares the "solved" column to a NUMBER and the other compares it to a CHAR.
Without seeing the table definition a lot of things could be the cause of not being able to see any data. A reproducible test case would be nice.
SQL> CREATE TABLE "PROBLEM"
2 ( "PROBLEM_ID" NUMBER NOT NULL ENABLE,
3 "PROBLEM_DESCRIPTION" VARCHAR2(4000),
4 "ACTION_TAKEN" VARCHAR2(4000),
5 "TRANSACTION_ID" NUMBER NOT NULL ENABLE,
6 "SOLVED" NUMBER NOT NULL ENABLE,
7 CONSTRAINT "PROBLEM_PK" PRIMARY KEY ("PROBLEM_ID") ENABLE,
8 CONSTRAINT "SOLVED_VALUE" CHECK ( "SOLVED" IN ('1', '0')) enable
9 )
10 /
Table created.
SQL>
SQL>
SQL> CREATE INDEX "PROBLEM_IDX1" ON "PROBLEM" ("TRANSACTION_ID")
2 /
Index created.
SQL>
SQL> CREATE SEQUENCE "PROBLEM_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER NOCYCLE
2 /
Sequence created.
SQL>
SQL> CREATE OR REPLACE TRIGGER "BI_PROBLEM"
2 before insert on "PROBLEM"
3 for each row
4 begin
5 select "PROBLEM_SEQ".nextval into :NEW.PROBLEM_ID from dual;
6 end;
7
8 /
Trigger created.
SQL> ALTER TRIGGER "BI_PROBLEM" ENABLE
2 /
Trigger altered.
SQL> insert into problem
2 (problem_id
3 ,problem_description
4 ,transaction_id
5 ,solved
6 )
7 values
8 (22
9 ,'the problem'
10 ,81
11 ,0
12 );
1 row created.
SQL>
SQL> insert into problem
2 (problem_id
3 ,problem_description
4 ,transaction_id
5 ,solved
6 )
7 values
8 (2
9 ,'There are'
10 ,81
11 ,0
12 );
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> SELECT *
2 FROM problem
3 WHERE solved = '0';
no rows selected
SQL> SELECT *
2 FROM problem
3 WHERE solved = 0;
no rows selected
And there you have it, even though we created two records each with a 0 (zero) for the "solved" column, no rows were selected by either of the SELECT statements issued.
How could this be possible?
Let's have a look at the Explain plan for one of the statements:
SQL> select *
2 from problem
3 where solved = 0
4 /
no rows selected
SQL> select *
2 from table (dbms_xplan.display_cursor)
3 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID dz2r64h4m6f2z, child number 0
-------------------------------------
select * from problem where solved = 0
Plan hash value: 4173894327
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| PROBLEM | 2 | 8086 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("SOLVED"=0)
Note
-----
- dynamic sampling used for this statement
The filter with operation id 1 in the Explain Plan shows "NULL IS NOT NULL". As this predicate is always false no data is shown. According to the "Inside the Oracle Optimizer"-blog this effectively removes the table from the query.
The "NULL IS NOT NULL" filter is a false constant predicate, that will prevent the table scan from even taking place.
So because of the filter the data isn't shown, but why is the filter introduced? There must be something wrong here. In the past I've used similar queries on similar tables which all seemed to be working well. Why is this scenario different?
Let's have a closer look at the table definition
CREATE TABLE "PROBLEM"
( "PROBLEM_ID" NUMBER NOT NULL ENABLE,
"PROBLEM_DESCRIPTION" VARCHAR2(4000),
"ACTION_TAKEN" VARCHAR2(4000),
"TRANSACTION_ID" NUMBER NOT NULL ENABLE,
"SOLVED" NUMBER NOT NULL ENABLE,
CONSTRAINT "PROBLEM_PK" PRIMARY KEY ("PROBLEM_ID") ENABLE,
CONSTRAINT "SOLVED_VALUE" CHECK ( "SOLVED" IN ('1', '0')) enable
)
/
Notice anything unusual?
No?...
How about now
...
"SOLVED" NUMBER
...
"SOLVED_VALUE" CHECK ( "SOLVED" IN ('1', '0'))
...
Notice the quotes in the Check Constraint definition? The datatype of the column is NUMBER and the Check in the Check constraint uses CHAR.
Could this be the cause? Let's verify
SQL> CREATE TABLE "PROBLEM"
2 ( "PROBLEM_ID" NUMBER NOT NULL ENABLE,
3 "PROBLEM_DESCRIPTION" VARCHAR2(4000),
4 "ACTION_TAKEN" VARCHAR2(4000),
5 "TRANSACTION_ID" NUMBER NOT NULL ENABLE,
6 "SOLVED" NUMBER NOT NULL ENABLE,
7 CONSTRAINT "PROBLEM_PK" PRIMARY KEY ("PROBLEM_ID") ENABLE,
8 CONSTRAINT "SOLVED_VALUE" CHECK ( "SOLVED" IN (1, 0)) enable
9 )
10 /
Table created.
SQL> insert into problem
2 (problem_id
3 ,problem_description
4 ,transaction_id
5 ,solved
6 )
7 values
8 (22
9 ,'the problem'
10 ,81
11 ,0
12 );
1 row created.
SQL>
SQL> select problem_description
2 from problem
3 /
PROBLEM_DESCRIPTION
----------------------------------------------------------------------------
the problem
And the filter is eliminated from the Explain Plan
SQL> select *
2 from table (dbms_xplan.display_cursor)
3 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID 18wkx6g72p35j, child number 0
-------------------------------------
select problem_description from problem
Plan hash value: 3149918165
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| PROBLEM | 1 | 2002 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Puzzling
Why does these queries give output, even with the original Check Constraint in place? The only difference is using bind variable instead of hard coded values...
SQL> var b number
SQL>
SQL> begin
2 :b := 0;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select problem_description
2 from problem
3 where solved = :b
4 /
PROBLEM_DESCRIPTION
----------------------------------------
the problem
There are
SQL> select problem_description
2 from problem
3 where solved = to_char (:b)
4 /
PROBLEM_DESCRIPTION
----------------------------------------
the problem
There are
Any suggestions?
Oracle 11g Release 2
As always, things change with different versions. Until now this code has been run on Oracle 10g:
SQL> conn alex/alex@orcl
Connected.
SQL> select *
2 from v$version
3 /
BANNER
-------------------------------------------------------------
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
With Oracle 11g Release 2 the original queries (and original table definition) do return records:
SQL> select *
2 from v$version
3 /
BANNER
--------------------------------------------------------------
Personal Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> SELECT problem_description
2 FROM problem
3 WHERE solved = '0';
PROBLEM_DESCRIPTION
--------------------------------------------------------------
the problem
There are
SQL> SELECT problem_description
2 FROM problem
3 WHERE solved = 0;
PROBLEM_DESCRIPTION
--------------------------------------------------------------
the problem
There are
Links
Read the whole thread here.
Inside the Oracle Optimizer
Hi Alex,
ReplyDeleteThis is a great example of (where bugs can be introduced with) "semantic query optimization", which I will talk about next tuesday at the Amis ODTUG-preview mini conference.
Toon