Home » SQL & PL/SQL » SQL & PL/SQL » list partition table
list partition table [message #256340] Fri, 03 August 2007 20:03 Go to next message
Messages: 115
Registered: July 2007
Senior Member

Wish you fine.
I am very curious about a sql statment on a list partitined table:
Here is a table T was LIST partitioned on TYPE column.
I issued a statment below:
select distinct type from t;

I guess :If the TYPE column have 2 key: 'Y' and 'N'
Does oracle know this and do not query the table?
But when I test it,I foud oracle does a full scan on the table ,why?

Re: list partition table [message #256341 is a reply to message #256340] Fri, 03 August 2007 22:01 Go to previous messageGo to next message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Good question. Unfortunately, Oracle is just not that clever. It can only get data from scans: table scans and index scans - these must read the blocks of the table or index. I don't know of any case where Oracle satisfies (rather than "optimises") a query using the meta-data (data dictionary) rather than the data itself.

One partial exception (there's always one!) would be if you had a BITMAP index on the TYPE column. In your case, that means two bit strings would be stored. To satisfy the SELECT DISTINCT query (providing there is no WHERE clause), Oracle just needs to read the header of each bit string to find out what value it contains.

SELECT DISTINCT on a bitmap indexed column is blindingly fast - even on enormous tables - because it performs so little IO.

Note that this still doesn't break the "rule" of satisfying queries only from data blocks, because that's where the bitmap values are stored.

It wouldn't surprise me at all if Oracle introduced this type of feature in the future. Of course it would only work with LIST partitions (not RANGE or HASH) - and then only when there is not DEFAULT partition - and then only when there was no joins, no where clause, and no other SELECT columns.

Ross Leishman
Re: list partition table [message #256351 is a reply to message #256341] Sat, 04 August 2007 00:26 Go to previous message
Messages: 115
Registered: July 2007
Senior Member

Hi! Rleishman:
Thanks for you perfect explain!
Previous Topic: Error creating users in Procedure (merged)
Next Topic: Curious if this code is correct.
Goto Forum:

Current Time: Thu Oct 27 04:00:53 CDT 2016

Total time taken to generate the page: 0.20778 seconds