It's local. Since the 2 columns of the index used in the execution
plan are not the range partition key, I understand that global may be
better than local for this particular index. But I can't use global
here due to the application logic.
"Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote in message news:<3C4714C2.D140FA6D_at_exesolutions.com>...
> Are you indexes local or global?
>
> Daniel Morgan
>
>
>
>
> Jessica Mao wrote:
>
> > Table event_t has 14 range partitions, only 1 out of 14 contains data
> > -- about 1 million rows. A workload called CSR has a query on event_t.
> > When event_t increased from 1 million rows to more than 2 million --
> > still inside 1 partition, CSR throughput dropped from 390 ops/sec to
> > 200 ops/sec. Here's the query statement:
> >
> > ********************************************************************************
> >
> > 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
> >
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > Parse 36 0.00 0.04 0 0 0
> > 0
> > Execute 118 0.01 0.01 0 0 0
> > 0
> > Fetch 118 0.61 86.71 1385 5045 0
> > 587
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > total 272 0.62 86.76 1385 5045 0
> > 587
> >
> > Misses in library cache during parse: 1
> > Optimizer goal: CHOOSE
> > Parsing user id: 21 (PIN)
> >
> > 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
> >
> > ********************************************************************************
> >
> > The index 'I_EVENT__ACCTOBJ_END_T' was created as:
> >
> > create index i_event__acctobj_end_t on event_t ( account_obj_id0,
> > end_t )
> > tablespace pinx04
> > initrans 16 storage (initial 1m next 10m maxextents unlimited
> > pctincrease 0 freelists 16)
> > local nologging parallel 15 ;
> >
> > event_t range partitioned by column poid_id0.
> > event_t frequently analyzed with estimate statistics.
> > Oracle 8.1.7.0.0
> >
> > Why did this query become so slow when rows increased? Is there
> > anything I can do to improve it? Is there any related documents I can
> > study on?
> >
> > Thank you very much.
> >
> > -Jessica
Received on Fri Jan 18 2002 - 14:18:10 CST
