Partition range - iterator or single 1

[Feodorit]

I have a question about Oracle Database (9i).
There are two simple queries:

select * from t3_main where data <= trunc(sysdate-112) and data >= trunc(sysdate-112);
select * from t3_main where data = trunc(sysdate-112);

First of this queries parsed for nearly 1 second and executes for nearly 1 second
Second query, as you see? provide the same result, but parsed for nearly 13 seconds, and executes for nearly 1 second.
Yes, FIRST query need much less time for parsing, and the second query run after the first.
On more complex queries parsing time of second type query dramatically increased (in real system 1.3 second for first query and 30 minutes for parsing second query).

t3_main - medium size, partitioned by date on daily basis table.

Execution plans for queries respectively:

1)
SELECT STATEMENT Optimizer=CHOOSE (Cost=4746 Card=14480 Bytes=2896000)
FILTER
PARTITION RANGE (ITERATOR)
TABLE ACCESS (FULL) OF T3_MAIN (Cost=4746 Card=14480 Bytes=2896000)

2)
SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=5965 Bytes=512990)
PARTITION RANGE (SINGLE)
TABLE ACCESS (FULL) OF T3_MAIN (Cost=8 Card=5965 Bytes=512990)

Yes Cost optimization is impressible.

Question: &quot;is it bug, feature or...&quot; - why so hard parsing and how can I avoid it, because for hours parsing for complex join queries.


With respect, Arsen Kroptya.

 

[Feodorit]

Allan> In generally - partition do not increase performance - the idea is to minimize contention between concurrent sessions and increase availability.


&quot;Index range scans – Partitioning physically sequences rows in index-order causing a dramatic improvement (over 10x faster) in the speed of partition-key scans.

Full-table scans – Partition pruning only access those data blocks required by the query.

Table joins – Partition-wise joins take the specific sub-set of the query partitions, causing huge speed improvements on nested loop and hash joins.

Updates – Oracle parallel query for partitions improves batch load speed by.

One of the best ways to improve the disk I/O response time is partitioning. Essentially, partitioning allows you to spread your data across all your disks evenly: the desired result being faster access to the data. &quot;

“Creating a Self-tuning Oracle Database” by Rampant TechPress

 

[AOLEURO]

Hi Feodorit

Well I don’t know the book – and I don’t know any self-tuned database around here. I am still looking for GO_FASTER=YES parameter in INIT.ORA – but I can’t find it.

As I see it, you still got a problem in parsing.

But I was more interesting in your table and index objects – not a discussion in partition.


Regards
Allan
Icq: 346225948

 

[Feodorit]

Hi AOLEURO
Thank You for Your posts

> As I see it, you still got a problem in parsing.

Excuse me, I didn't post to forum, that problem was solved by analyzing tables with DBMS_STATS.
Problem appear when partitions were added to the tables and no analyzing statistics on tables - only analyze partition.

But I really not understand Oracle parsing, and why this queries parsing so differently.