Home » SQL & PL/SQL » SQL & PL/SQL » Seeing things (list partition)
Seeing things (list partition) [message #230361] Wed, 11 April 2007 12:04 Go to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I'm guessing that I'm either seeing things, or there is a bug with list partitioning in the database version I'm using. Any ideas with the following? Notice the no rows selected.

MYDBA@orcl > 
MYDBA@orcl > create table test
  2  (
  3  	     id 	     number not null
  4  	     ,data	     varchar2(20) not null
  5  	     ,state_dt	     date
  6  	     ,constraint     test_pk primary key(id)
  7  );

Table created.

MYDBA@orcl > 
MYDBA@orcl > insert into test values (1, 'aaa', sysdate);

1 row created.

MYDBA@orcl > insert into test values (2, 'bbb', null);

1 row created.

MYDBA@orcl > commit;

Commit complete.

MYDBA@orcl > 
MYDBA@orcl > select * from test order by id;

        ID DATA                 STATE_DT
---------- -------------------- --------------------
         1 aaa                  11-APR-2007 12:59:40
         2 bbb

2 rows selected.

MYDBA@orcl > select * from test where state_dt >= '01-JAN-2007' order by id;

        ID DATA                 STATE_DT
---------- -------------------- --------------------
         1 aaa                  11-APR-2007 12:59:40

1 row selected.

MYDBA@orcl > select * from test where state_dt >= to_date('01-JAN-2007','DD-MON-YYYY') order by id;

        ID DATA                 STATE_DT
---------- -------------------- --------------------
         1 aaa                  11-APR-2007 12:59:40

1 row selected.

MYDBA@orcl > 
MYDBA@orcl > drop table test;

Table dropped.

MYDBA@orcl > 
MYDBA@orcl > create table test
  2  (
  3  	     id number not null
  4  	     ,data varchar2(20) not null
  5  	     ,state_dt date
  6  	     ,constraint test_pk primary key(id)
  7  )
  8  partition by list(state_dt)
  9  (
 10  	     partition p1 values (null)
 11  	     ,partition p2 values (default)
 12  );

Table created.

MYDBA@orcl > 
MYDBA@orcl > insert into test values (1, 'aaa', sysdate);

1 row created.

MYDBA@orcl > insert into test values (2, 'bbb', null);

1 row created.

MYDBA@orcl > commit;

Commit complete.

MYDBA@orcl > 
MYDBA@orcl > select * from test order by id;

        ID DATA                 STATE_DT
---------- -------------------- --------------------
         1 aaa                  11-APR-2007 12:59:40
         2 bbb

2 rows selected.

MYDBA@orcl > select * from test where state_dt >= '01-JAN-2007' order by id;

no rows selected

MYDBA@orcl > select * from test where state_dt >= to_date('01-JAN-2007','DD-MON-YYYY') order by id;

no rows selected

MYDBA@orcl > 
MYDBA@orcl > drop table test;

Table dropped.

MYDBA@orcl > 
MYDBA@orcl > select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
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

5 rows selected.

MYDBA@orcl > 
MYDBA@orcl > spool off

Re: Seeing things (list partition) [message #230368 is a reply to message #230361] Wed, 11 April 2007 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it seems to be a bug.
If you display the explain plan you get:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 1509442644

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    11 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST EMPTY|      |     1 |    11 |     2   (0)| 00:00:01 |INVALID|INVALID|
|*  2 |   TABLE ACCESS FULL  | TEST |     1 |    11 |     2   (0)| 00:00:01 |INVALID|INVALID|
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATE_DT">=TO_DATE('01-JAN-2007','DD-MON-YYYY'))

Notice the "PARTITION LIST EMPTY".
You have a test case, now you can raise a SR.

Regards
Michel
Re: Seeing things (list partition) [message #230377 is a reply to message #230361] Wed, 11 April 2007 13:03 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Thanks. Yeah I saw the same thing with explain plan. Actually I was testing two variations and noticed something else:

where state_dt is null
where state_dt is not null

Interestingly, the first one knew to only FTS a single partition, which was the entire reason for me doing this specific partitioning scheme to begin with. But the second one wasn't as smart. It did a FTS across all partitions.

Both of those did at least return correct results. As did:

Then I started playing with the range scan on the date field and ran into the weirdness. Although this did work:
where state_dt between sysdate - 2 and sysdate + 2

I'm much more used to bugs that blow things up, rather than those that actually return incorrect results.

But unfortunately I'm a contractor at the moment at a site where I'm not in a position to raise a TAR or access Metalink.

The above was run on a 32 bit windows 10.2.0.1 platform. I've also run the same thing on 64 bit HP 10.2.0.3 with the same reults.
Re: Seeing things (list partition) [message #230390 is a reply to message #230368] Wed, 11 April 2007 13:56 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Hmmm, also, how is it that you have the same time on SYSDATE when you inserted at two different times.

It's almost as if it is selecting from a synonym pointing to a different table called TEST in one of those SELECTs. Maybe you have a trigger that points to a different TEST when you use a WHERE clause Wink

[Updated on: Wed, 11 April 2007 14:02]

Report message to a moderator

Re: Seeing things (list partition) [message #231788 is a reply to message #230361] Wed, 18 April 2007 07:30 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
If interested, this appears to be an in-progress bug, number 5245038.
Previous Topic: samll query required
Next Topic: Quer to select value of a column as comma seperated string
Goto Forum:
  


Current Time: Sun Dec 04 02:37:27 CST 2016

Total time taken to generate the page: 0.09836 seconds