OraFAQ Forum: Performance Tuning » Which index to choose local or global for attached scenario

Home » RDBMS Server » Performance Tuning » Which index to choose local or global for attached scenario (10.2.0.3 db and AIX OS)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Which index to choose local or global for attached scenario [message #658546]

Mon, 19 December 2016 03:14

oratech10
Messages: 26
Registered: September 2011

Junior Member

Hi Guys.. Attached the doc and advice me to choose the best index applicable for the attached scenario. thanks

Attachment: best_index_to_choose.doc
(Size: 612.50KB, Downloaded 2362 times)

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658548 is a reply to message #658546]

Mon, 19 December 2016 03:38

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

I do not think it is possible to advise, because the scenario is incomplete. You need to decide on your objectives for this partitioning. Is it, for example, to improve performance or to improve manageability? In your very old release, these two are often incompatible.You might also want to consider how many table partitions you intend to have and whether they will be range or hash. Or something else.

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658549 is a reply to message #658548]

Mon, 19 December 2016 03:47

oratech10
Messages: 26
Registered: September 2011

Junior Member

If I need to improve both performance and manageability. table got monthly Range partitions, total 26 partitions

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658550 is a reply to message #658549]

Mon, 19 December 2016 03:50

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

You probably can't improve both. As I already said. If you want to, you need to upgrade to release 12 which has some major enhancements aimed at that issue.

However, you still need to provide more information. What problem(s) do you wish to solve?

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658551 is a reply to message #658550]

Mon, 19 December 2016 03:52

oratech10
Messages: 26
Registered: September 2011

Junior Member

Thanks for advice. I want to improve the performance. So which is the best? As per the attached explain plans is it not enough to choose which is best?

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658552 is a reply to message #658551]

Mon, 19 December 2016 04:03

oratech10
Messages: 26
Registered: September 2011

Junior Member

Also please look at this attachment and advice why the explain plan going for SYS owner ? what is this :TQXXXXX OBJECT?

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658553 is a reply to message #658551]

Mon, 19 December 2016 04:04

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Nowhere near enough information.
You need to be absolutely clear on why you want to partition the table(s). If you get partitioning right, the benefits are sometimes huge; if you get it wrong, the results will be disastrous. So your first step has to be to define the problem. Then you need to consider all the possibilities, of which there are many.

I usually say "don't use partitioning just because you can". Use it only if have a defined business problem and can prove, mathematically, that your chosen partitioning strategy will fix that without introducing others. It does look as though you are approaching this backwards: you seem to have already decide that the table should be range partitioned by month perhaps without considering why, and are now considering only the most basic options for the indexes.

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658554 is a reply to message #658553]

Mon, 19 December 2016 04:09

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Not everyone can read attachments, if you want more folks to see this, you need to post it according to the posting guidelines

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658561 is a reply to message #658553]

Mon, 19 December 2016 20:04

oratech10
Messages: 26
Registered: September 2011

Junior Member

My table is big.. around 26 million records..each month around 1 million records. Attached table and index sql creation scripts. Need partitioning so that we can drop oldest partitions and keep only latest 26 months data as business requirement.

-- Create table
create table PC_LINK_ATTACHMENT
(
  PXCREATEDATETIME       DATE,
  PXCREATEOPNAME         VARCHAR2(256 CHAR),
  PXCREATEOPERATOR       VARCHAR2(256 CHAR),
  PXCREATESYSTEMID       VARCHAR2(64 CHAR),
  PXINSNAME              VARCHAR2(256 CHAR),
  PXLINKEDCLASSFROM      VARCHAR2(510 CHAR),
  PXLINKEDCLASSTO        VARCHAR2(510 CHAR),
  PXLINKEDREFFROM        VARCHAR2(510 CHAR),
  PXLINKEDREFTO          VARCHAR2(510 CHAR),
  PXOBJCLASS             VARCHAR2(192 CHAR),
  PXUPDATEDATETIME       DATE,
  PXUPDATEOPNAME         VARCHAR2(256 CHAR),
  PXUPDATEOPERATOR       VARCHAR2(256 CHAR),
  PXUPDATESYSTEMID       VARCHAR2(64 CHAR),
  PYLABEL                VARCHAR2(128 CHAR),
  PZINSKEY               VARCHAR2(510 CHAR) not null,
  PZPVSTREAM             BLOB,
  SICORRATTACHMENTSTATUS VARCHAR2(510 CHAR),
  PYMEMO                 VARCHAR2(255),
  PYCATEGORY             VARCHAR2(30),
  PYFILEORCATEGORY       VARCHAR2(30 CHAR)
)
partition by range (PXCREATEDATETIME)
(
  partition P20101101_20101130 values less than (TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20101201_20101231 values less than (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20110101_20110131 values less than (TO_DATE(' 2011-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20110201_20110229 values less than (TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20110301_20110331 values less than (TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20110401_20110430 values less than (TO_DATE(' 2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20110501_20110531 values less than (TO_DATE(' 2011-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20110601_20110630 values less than (TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20110701_20110731 values less than (TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20110801_20110831 values less than (TO_DATE(' 2011-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20110901_20110930 values less than (TO_DATE(' 2011-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20111001_20111031 values less than (TO_DATE(' 2011-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20111101_20111130 values less than (TO_DATE(' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20111201_20111231 values less than (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20120101_20120131 values less than (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20120201_20120229 values less than (TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20120301_20120331 values less than (TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20120401_20120430 values less than (TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20120501_20120531 values less than (TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20120601_20120630 values less than (TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20120701_20120731 values less than (TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20120801_20120831 values less than (TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20120901_20120930 values less than (TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20121001_20121031 values less than (TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20121101_20121130 values less than (TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20121201_20121231 values less than (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20130101_20130131 values less than (TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20130201_20130228 values less than (TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20130301_20130331 values less than (TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20130401_20130430 values less than (TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20130501_20130531 values less than (TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20130601_20130630 values less than (TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20130701_20130731 values less than (TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20130801_20130831 values less than (TO_DATE(' 2013-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20130901_20130930 values less than (TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20131001_20131031 values less than (TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20131101_20131130 values less than (TO_DATE(' 2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20131201_20131231 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20140101_20140131 values less than (TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20140201_20140228 values less than (TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20140301_20140331 values less than (TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20140401_20140430 values less than (TO_DATE(' 2014-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20140501_20140531 values less than (TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20140601_20140630 values less than (TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20140701_20140731 values less than (TO_DATE(' 2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20140801_20140831 values less than (TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20140901_20140930 values less than (TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20141001_20141031 values less than (TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20141101_20141130 values less than (TO_DATE(' 2014-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20141201_20141231 values less than (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20150101_20150131 values less than (TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20150201_20150228 values less than (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20150301_20150331 values less than (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20150401_20150430 values less than (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20150501_20150531 values less than (TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20150601_20150630 values less than (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20150701_20150731 values less than (TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20150801_20150831 values less than (TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20150901_20150930 values less than (TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20151001_20151031 values less than (TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20151101_20151130 values less than (TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20151201_20151231 values less than (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20160101_20160131 values less than (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20160201_20160229 values less than (TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20160301_20160331 values less than (TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20160401_20160430 values less than (TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20160501_20160531 values less than (TO_DATE(' 2016-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20160601_20160630 values less than (TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20160701_20160731 values less than (TO_DATE(' 2016-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20160801_20160831 values less than (TO_DATE(' 2016-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20160901_20160930 values less than (TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20161001_20161031 values less than (TO_DATE(' 2016-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20161101_20161130 values less than (TO_DATE(' 2016-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20161201_20161231 values less than (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20170101_20170131 values less than (TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20170201_20170228 values less than (TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20170301_20170331 values less than (TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20170401_20170430 values less than (TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20170501_20170531 values less than (TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20170601_20170630 values less than (TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20170701_20170731 values less than (TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20170801_20170831 values less than (TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20170901_20170930 values less than (TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20171001_20171031 values less than (TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20171101_20171130 values less than (TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20171201_20171231 values less than (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20180101_20180131 values less than (TO_DATE(' 2018-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20180201_20180228 values less than (TO_DATE(' 2018-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20180301_20180331 values less than (TO_DATE(' 2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20180401_20180430 values less than (TO_DATE(' 2018-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20180501_20180531 values less than (TO_DATE(' 2018-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20180601_20180630 values less than (TO_DATE(' 2018-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20180701_20180731 values less than (TO_DATE(' 2018-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20180801_20180831 values less than (TO_DATE(' 2018-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20180901_20180930 values less than (TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20181001_20181031 values less than (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20181101_20181130 values less than (TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition P20181201_20181231 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    ),
  partition PFUTURE values less than (MAXVALUE)
    tablespace PC_LINK_DATA_TS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      minextents 1
      maxextents unlimited
    )
)
;
-- Create/Recreate indexes 
create index PC_LINK_ATTACHMENT_IDX1 on PC_LINK_ATTACHMENT (PXOBJCLASS, PXINSNAME, PXLINKEDREFFROM)
  tablespace PC_LINK_DATA_TS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1M
    minextents 1
    maxextents unlimited
  );
create index PC_LINK_ATTACHMENT_IDX2 on PC_LINK_ATTACHMENT (PXLINKEDREFTO)
  tablespace PC_LINK_DATA_TS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1M
    minextents 1
    maxextents unlimited
  );
create index PC_LINK_ATTACHMENT_IDX3 on PC_LINK_ATTACHMENT (PXLINKEDREFFROM)
  tablespace PC_LINK_DATA_TS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1M
    minextents 1
    maxextents unlimited
  );
create unique index PC_LINK_ATTACHMENT_NEW_PK on PC_LINK_ATTACHMENT (PZINSKEY)
  tablespace PC_LINK_DATA_TS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 1M
    minextents 1
    maxextents unlimited
  );

[mod-edit: contents of attachment inserted into message body by bb]

Attachment: table_index_sql.txt
(Size: 31.95KB, Downloaded 1993 times)

[Updated on: Mon, 19 December 2016 20:21] by Moderator

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658562 is a reply to message #658561]

Mon, 19 December 2016 20:07

oratech10
Messages: 26
Registered: September 2011

Junior Member

If someone could not see my select sql in the doc.. can see below one. thanks

select pzPVStream from pc_link_attachment
where pxLinkedRefTo like 'PEGACOMMBANK-CORR-SWIFT XXP110223-000111!20110223T172952.338 GMT%' and pxObjClass ='Link-Attachment'
order by pzInsKey

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658563 is a reply to message #658562]

Mon, 19 December 2016 20:21

oratech10
Messages: 26
Registered: September 2011

Junior Member

also please throw some light on attached explain plan. Why SYS objects are appearing?

select pzPVStream from pc_link_attachment where pxLinkedRefTo like 'PEGACOMMBANK-CORR-SWIFT XXP161115-000029!20161115T063801.679 GMT%' 
and pxObjClass ='Link-Attachment' 
order by pzInsKey

SELECT STATEMENT, GOAL = ALL_ROWS			Cost=87	Cardinality=1	Bytes=248	Optimizer=ALL_ROWS	CPU cost=12347198			Id=0	IO cost=86					Operation=SELECT STATEMENT								Plan id=8270	Position=87					Statement id=TEST_APP		Time=2	Timestamp=12/20/2016 2:15:07 AM
 PX COORDINATOR										Id=1						Operation=PX COORDINATOR				Parent id=0				Plan id=8270	Position=1	Projection="PZPVSTREAM"[LOB,4000]	Qblock name=SEL$1			Statement id=TEST_APP			Timestamp=12/20/2016 2:15:07 AM
  PX SEND QC (ORDER)	Object owner=SYS	Object name=:TQ10001	Cost=87	Cardinality=1	Bytes=248		CPU cost=12347198			Id=2	IO cost=86			Object node=:Q1001		Operation=PX SEND	Options=QC (ORDER)		Other tag=PARALLEL_TO_SERIAL	Parent id=1				Plan id=8270	Position=1	Projection=(#keys=0) "PZPVSTREAM"[LOB,4000]				Statement id=TEST_APP		Time=2	Timestamp=12/20/2016 2:15:07 AM
   SORT ORDER BY			Cost=87	Cardinality=1	Bytes=248		CPU cost=12347198			Id=3	IO cost=86			Object node=:Q1001		Operation=SORT	Options=ORDER BY		Other tag=PARALLEL_COMBINED_WITH_PARENT	Parent id=2				Plan id=8270	Position=1	Projection=(#keys=1) "PZINSKEY"[VARCHAR2,2040], "PZPVSTREAM"[LOB,4000]				Statement id=TEST_APP		Time=2	Timestamp=12/20/2016 2:15:07 AM
    PX RECEIVE			Cost=86	Cardinality=1	Bytes=248		CPU cost=616407			Id=4	IO cost=86			Object node=:Q1001		Operation=PX RECEIVE			Other tag=PARALLEL_COMBINED_WITH_PARENT	Parent id=3				Plan id=8270	Position=1	Projection="PZINSKEY"[VARCHAR2,2040], "PZPVSTREAM"[LOB,4000]				Statement id=TEST_APP		Time=2	Timestamp=12/20/2016 2:15:07 AM
     PX SEND RANGE	Object owner=SYS	Object name=:TQ10000	Cost=86	Cardinality=1	Bytes=248		CPU cost=616407			Id=5	IO cost=86			Object node=:Q1000		Operation=PX SEND	Options=RANGE		Other tag=PARALLEL_TO_PARALLEL	Parent id=4				Plan id=8270	Position=1	Projection=(#keys=1) "PZINSKEY"[VARCHAR2,2040], "PZPVSTREAM"[LOB,4000]				Statement id=TEST_APP		Time=2	Timestamp=12/20/2016 2:15:07 AM
      PX PARTITION RANGE ALL			Cost=86	Cardinality=1	Bytes=248		CPU cost=616407			Id=6	IO cost=86			Object node=:Q1000		Operation=PX PARTITION RANGE	Options=ALL		Other tag=PARALLEL_COMBINED_WITH_CHILD	Parent id=5	Partition id=6	Partition start=1	Partition stop=53	Plan id=8270	Position=1	Projection="PZINSKEY"[VARCHAR2,2040], "PZPVSTREAM"[LOB,4000]				Statement id=TEST_APP		Time=2	Timestamp=12/20/2016 2:15:07 AM
       TABLE ACCESS BY LOCAL INDEX ROWID	Object owner=CERS	Object name=PC_LINK_ATTACHMENT	Cost=86	Cardinality=1	Bytes=248	Optimizer=ANALYZED	CPU cost=616407		Filter predicates="PXOBJCLASS"='Link-Attachment'	Id=7	IO cost=86	Object alias=PC_LINK_ATTACHMENT@SEL$1	Object instance=1	Object node=:Q1000	Object type=TABLE	Operation=TABLE ACCESS	Options=BY LOCAL INDEX ROWID		Other tag=PARALLEL_COMBINED_WITH_PARENT	Parent id=6	Partition id=6	Partition start=1	Partition stop=53	Plan id=8270	Position=1	Projection="PZINSKEY"[VARCHAR2,2040], "PZPVSTREAM"[LOB,4000]	Qblock name=SEL$1			Statement id=TEST_APP		Time=2	Timestamp=12/20/2016 2:15:07 AM
        INDEX RANGE SCAN	Object owner=CERS	Object name=PC_ATTACHMENT_IDX_02	Cost=86	Cardinality=1		Optimizer=ANALYZED	CPU cost=609915	Access predicates="PXLINKEDREFTO" LIKE 'PEGACOMMBANK-CORR-SWIFT XXP161115-000029!20161115T063801.679 GMT%'	Filter predicates="PXLINKEDREFTO" LIKE 'PEGACOMMBANK-CORR-SWIFT XXP161115-000029!20161115T063801.679 GMT%'	Id=8	IO cost=86	Object alias=PC_LINK_ATTACHMENT@SEL$1		Object node=:Q1000	Object type=INDEX	Operation=INDEX	Options=RANGE SCAN		Other tag=PARALLEL_COMBINED_WITH_PARENT	Parent id=7	Partition id=6	Partition start=1	Partition stop=53	Plan id=8270	Position=1	Projection="PC_LINK_ATTACHMENT".ROWID[ROWID,10]	Qblock name=SEL$1		Search columns=1	Statement id=TEST_APP		Time=2	Timestamp=12/20/2016 2:15:07 AM

[mod-edit: contents of attachment inserted into message body by bb]

Attachment: explain_plan.txt
(Size: 3.82KB, Downloaded 2206 times)

[Updated on: Mon, 19 December 2016 20:23] by Moderator

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658564 is a reply to message #658563]

Mon, 19 December 2016 21:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>If I need to improve both performance and manageability. table got monthly Range partitions, total 26 partitions
Then why did you post DDL for close to 96 actual partitions?

Run the following in SQL*Plus
If PLAN_TABLE does not exist, then invoke @$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL
EXPLAIN PLAN FOR <"slow" SQL statement>
SELECT * FROM table(dbms_xplan.display);
Don't forget to format the plan when you post it. The indentation is VERY important.

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658567 is a reply to message #658564]

Tue, 20 December 2016 00:55

oratech10
Messages: 26
Registered: September 2011

Junior Member

Data is only under latest 26 months and you can ignore the rest empty partitions.

I do not have access from putty sqlplus.. only through plsql tool I login. Also for now below sql performance is good. I just need to know the best suitable index when we use queries like below where we are not using the partitioned column in where clause. thanks

select pzPVStream from pc_link_attachment where pxLinkedRefTo like 'PEGACOMMBANK-CORR-SWIFT XXP161115-000029!20161115T063801.679 GMT%'
and pxObjClass ='Link-Attachment'
order by pzInsKey

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658569 is a reply to message #658567]

Tue, 20 December 2016 01:37

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Your execution plan is unreadable.

Your table is rather small, I am surprised that there is any need for partitioning at all. Inserting or deleting a million rows per month is trivial. Databases I run on my laptop do that in a few seconds:

orclz>
orclz> select num_rows,avg_row_len from user_tables where table_name='T1';

  NUM_ROWS AVG_ROW_LEN
---------- -----------
   1436480         115

orclz> set timing on
orclz> insert into t1 select * from t1;

1436480 rows created.

Elapsed: 00:00:17.84
orclz> commit;

Commit complete.

Elapsed: 00:00:00.00
orclz> delete from t1 where rownum < 1000000;

999999 rows deleted.

Elapsed: 00:00:30.93
orclz>

Rather than complicating the environment and causing all sorts of problems by introducing partitioning for no purpose, I would look at restructuring your table to change all those CHAR columns to VARCHAR2. THat would be a project well worth doing.

By the way, I wish you would not say "record" when you mean "row".

--update, sorry, I misread the DDL regarding CHAR/VARCHAR2.

[Updated on: Tue, 20 December 2016 03:09]

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658570 is a reply to message #658567]

Tue, 20 December 2016 01:42

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Quote:

I do not have access from putty sqlplus.. only through plsql tool I login.

You do not need PuTTY to produce a properly formatted execution plan. If this unknown plsql tool you use can't do it, install SQL*Plus on your PC and use that.

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658571 is a reply to message #658570]

Tue, 20 December 2016 02:01

oratech10
Messages: 26
Registered: September 2011

Junior Member

Ok noted. thanks. Let say if i got 26 billion rows. Which index you suggest ? Attached png format images of explain plan. Let me know if you can read the png.

Attachment: local_xplan.png
(Size: 15.93KB, Downloaded 1577 times)

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658572 is a reply to message #658571]

Tue, 20 December 2016 02:01

oratech10
Messages: 26
Registered: September 2011

Junior Member

attached global index explain plan png

Attachment: global_xplan.png
(Size: 13.55KB, Downloaded 1588 times)

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658574 is a reply to message #658572]

Tue, 20 December 2016 02:08

oratech10
Messages: 26
Registered: September 2011

Junior Member

also please take note that table contain BLOB objects.. thus we maitain only 26 months data, removing the oldest data to avoid storage space issues

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658575 is a reply to message #658571]

Tue, 20 December 2016 02:12

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

What is really going on? You have no performance problem (you have already said that) and the data volumes are so low that it is hard to see any case for partitioning.

For example, has someone bought the partitioning option, and now needs to justify the purchase? Or are your consultants short of work, and looking around for "useful" things to do?

If you want to know which index is faster, just time the queries. If you want to know the manageability issues, try dropping a partition.

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658576 is a reply to message #658575]

Tue, 20 December 2016 02:55

oratech10
Messages: 26
Registered: September 2011

Junior Member

The partitions were already created before I joined the company, however since no future partitions were created all the data was getting resided into PFUTURE partition for the latest 26 months of data. So recreated the table with proper partitions.

When I go for global index, timing is bit faster compared to local index however explain plan cost is more compared with global index. So we need to check for timing to choose the best index or the cost of explain plan? Please let me know on this. thanks a lot for your inputs. Smile

global index - timing 0.42 seconds - cost 257 - cpu cost 18855292
local index - timing 0.56 seconds - cost 59 - cpu cost 17347198

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658577 is a reply to message #658569]

Tue, 20 December 2016 03:00

oratech10
Messages: 26
Registered: September 2011

Junior Member

>Rather than complicating the environment and causing all sorts of problems by introducing partitioning for no purpose, I would look at restructuring your table to change all those CHAR columns to VARCHAR2. THat would be a project well worth doing.
-- We already have VARCHAR2 COLUMNS like for example VARCHAR2(256 CHAR)

By the way, I wish you would not say "record" when you mean "row". -- ok noted. thx..yes rows i meant.

Report message to a moderator

Re: Which index to choose local or global for attached scenario [message #658578 is a reply to message #658576]

Tue, 20 December 2016 03:06

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Thank you for explaining the situation. It would seem that your predecessor made some rather poor design decisions, and you now have the opportunity to correct them.

You have proved that the global index is faster. This is to be expected, because you search only one index tree instead of 96 index trees. Now you need to test the effect on the indexes of dropping a partition. Brace yourself.

However, if I were in your position, I would not try to make this work. It would convert the table to non-partitioned. I would also motivate the upgrade to at least release 11.2, preferably 12.1. That will give you secure file LOBs, which may improve management and performance of your BLOB column astronomically.

--update: sorry I misread your DDL regarding CHAR/VARCHAR2

[Updated on: Tue, 20 December 2016 03:07]

Report message to a moderator