|
|
|
|
Re: How to identify index type for partitioned tables [message #659895 is a reply to message #659884] |
Thu, 02 February 2017 08:46 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Thanks Michel fore your reference link .
I have gone through it
As per the document its giving the following information :
Quote:For partitioned indexes, indicates whether statistics were collected by analyzing the index as a whole (YES) or were estimated from statistics on underlying index partitions and subpartitions (NO)
But some of existing tables in the data base are having the GLOBAL STATUS as yes. With the help create script of that table I had created one more new table with same number of partitions , indexes with different table name . But for the newly created table GLOBAL STATUS is new :
DROP TABLE EXAMPLE;
CREATE TABLE "EXAMPLE"
( "ID" NUMBER(10,0) NOT NULL ENABLE,
"UID" VARCHAR2(40),
"PIX" VARCHAR2(40),
"FNAME" VARCHAR2(100),
"MNAME" VARCHAR2(100),
"LNAME" VARCHAR2(100),
"SFIX" VARCHAR2(40),
"JTITLE" VARCHAR2(100),
"COMNAME" VARCHAR2(40),
"ADD1" VARCHAR2(50),
"ADD2" VARCHAR2(50),
"ADD3" VARCHAR2(50),
"CTY" VARCHAR2(30),
"STAT" VARCHAR2(20),
"POSTCODE" VARCHAR2(10),
"COUNTY" VARCHAR2(40),
"COUNTRY" VARCHAR2(40),
"PH_NUM" VARCHAR2(20),
"FX_NUM" VARCHAR2(15)
)
PARTITION BY RANGE ("ID")
(PARTITION "EXAMPLE_P1" VALUES LESS THAN (50000)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
,
PARTITION "EXAMPLE_P2" VALUES LESS THAN (100000)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
);
CREATE INDEX EXAMPLE_GLO_IDX
on EXAMPLE (id)
GLOBAL PARTITION BY RANGE (ID)
(PARTITION example_Glo_id_p1 VALUES LESS THAN (5000),
PARTITION example_Glo_id_p2 VALUES LESS THAN (10000),
PARTITION example_Glo_id_p3 VALUES LESS THAN (1000000),
PARTITION example_Glo_id_p4 VALUES LESS THAN (2000000),
PARTITION example_Glo_id_max VALUES LESS THAN (MAXVALUE));
select UI.GLOBAL_STATS, UI.PARTITIONED , UI.*
from SYS.USER_INDEXES UI where TABLE_NAME ='EXAMPLE';
Output
NO YES EXAMPLE
Please help to understand the Global Status of the user indexes
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|