| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: performance problem with partitioned table query.
Thank you Igor. But only 1 of the 14 partitions contains data during all the tests. Why should the extra 13 empty partitions slows down the query? I also tried to drop 12 of the empty partitions. Results didn't change. -Jessica
-----Original Message-----
Sent: Thursday, January 24, 2002 5:37 AM
To: Multiple recipients of list ORACLE-L
Jessica,
It looks like your query has to deal with all 14 partitions, because the
column 'poid_id0', which your table partitioned on, is not in 'where'
clause.
That's why Oracle can not eliminate other (not populated) 13 partitions.
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
> Oracle 8.1.7.0.0
>
> table event_t range partitioned by column poid_id0. only 1 partition
called p_1 out of the 14 contains data. A query on event_t became
significantly slow after rows increase:
>
> select poid_DB, poid_ID0, poid_TYPE, poid_REV, start_t, end_t, sys_descr
> from event_t
> where event_t.end_t >= :1 and event_t.end_t < :2 and
> event_t.poid_TYPE like :3 and (event_t.account_obj_ID0 = :4 and
> event_t.account_obj_DB = 1 ) order by event_t.end_t desc
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 SORT (ORDER BY)
> 0 PARTITION RANGE (ALL) PARTITION: START=1 STOP=14
> 0 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF
> 'EVENT_T' PARTITION: START=1 STOP=14
> 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'I_EVENT__ACCTOBJ_END_T' (NON-UNIQUE) PARTITION: START=1
> STOP=14
>
> Index I_EVENT__ACCTOBJ_END_T was created on event_t ( account_obj_id0,
end_t ) using LOCAL.
> Other 2 columns involved in the where clause have either only one distinct
value or a few. So are not indexed.
> column account_obj_id0 has 1 million unique values in event_t and remain
unchanged during the tests. when rows insert, average rows per
account_obj_id0 value increase as well.
>
> Trace shows always the same execution plan but elapsed time increased
enormously!
> I did 2 rounds of tests, every round I dropped and recreated event_t
empty:
>
> In test round 1:
> 1.) inserted 1 million rows into event_t with same end_t value. Query
returned:
> call count cpu elapsed disk query current
rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: ineyman_at_perceptron.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jessica Mao INET: jmao_at_portal.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Jan 24 2002 - 16:42:59 CST
 |
 |