Home » RDBMS Server » Performance Tuning » How to identify index type for partitioned tables (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
How to identify index type for partitioned tables [message #659860] Wed, 01 February 2017 07:16 Go to next message
saipradyumn
Messages: 303
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All ,

How to identify the weather created index is global or local for a partitioned table? Can we depend on this GLOBAL_STATS on USER_INDEXES

Thanks in advance
Re: How to identify index type for partitioned tables [message #659864 is a reply to message #659860] Wed, 01 February 2017 08:05 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
use USER_PART_INDEXES
Re: How to identify index type for partitioned tables [message #659883 is a reply to message #659864] Thu, 02 February 2017 01:17 Go to previous messageGo to next message
saipradyumn
Messages: 303
Registered: October 2011
Location: Hyderabad
Senior Member

Thanks you very much Black Swan.

What kind of information will be available in GLOBAL_STATS@USER_INDEXES will give?

Even we have the USER_IND_PARTITIONS data dictionary view to get the indexes information of the partitioned table

What is the difference between USER_PART_INDEXES and USER_IND_PARTITIONS

Thanks
Sai Pradyumn
Re: How to identify index type for partitioned tables [message #659884 is a reply to message #659883] Thu, 02 February 2017 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 65199
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Database Reference

Re: How to identify index type for partitioned tables [message #659895 is a reply to message #659884] Thu, 02 February 2017 08:46 Go to previous messageGo to next message
saipradyumn
Messages: 303
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
Re: How to identify index type for partitioned tables [message #659897 is a reply to message #659895] Thu, 02 February 2017 09:01 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
The meaning of the GLOBAL_STATS column is described in the doc MC pointed you to, with an explanation of when it will be YES and when it will be NO. Can you explain what it is that isn't clear?
Re: How to identify index type for partitioned tables [message #659898 is a reply to message #659897] Thu, 02 February 2017 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
>from SYS.USER_INDEXES UI where TABLE_NAME ='EXAMPLE';

you should NOT be using "SYS" to do anything related to application support, maintenance, or testing!
Re: How to identify index type for partitioned tables [message #659906 is a reply to message #659897] Fri, 03 February 2017 03:45 Go to previous messageGo to next message
saipradyumn
Messages: 303
Registered: October 2011
Location: Hyderabad
Senior Member
Hi John/BlackSwan

Thanks for your reply.
I am not understanding when that global status will moved to YES/No

In My example initially it was 'NO' , but after executing they one of the following two statements , it became 'YES'


  BEGIN 
  DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'DBOWNER', TABNAME=> 'EXAMPLE' , CASCADE=>TRUE, degree=>16);
  DBMS_STATS.GATHER_INDEX_STATS (ownname=> 'DBOWNER',  indname =>'EXAMPLE_GLO_IDX');
  END; 


select UI.GLOBAL_STATS, UI.PARTITIONED , Ui.Table_Name
from USER_INDEXES UI where TABLE_NAME ='EXAMPLE';

OUTPUT

YES YES EXAMPLE


Now I had removed SYS. Can we uses these tables BlackSwan. My question is when it will become to N.
(i.e) How to gather statistics on underlying index partitions and sub partitions ?
Re: How to identify index type for partitioned tables [message #659909 is a reply to message #659906] Fri, 03 February 2017 04:08 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
I don't see what your problem is. If you gather global stats, that column will be YES. If you don't gather them, or delete the previously gathered global stats, it will be NO.
Re: How to identify index type for partitioned tables [message #659910 is a reply to message #659909] Fri, 03 February 2017 04:41 Go to previous messageGo to next message
saipradyumn
Messages: 303
Registered: October 2011
Location: Hyderabad
Senior Member
Hi John ,

Here is my confusion how to make it as No after analyzing tables statistics

As per your comments /documentation there are two ways

1:
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)

2:
Quote:


Delete the previously gathered global stats .



How to delete the previously gathered stats and How to gather the statistics for index partitions/sub partitions



Re: How to identify index type for partitioned tables [message #659911 is a reply to message #659910] Fri, 03 February 2017 04:52 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
You gather, or delete, object statistics with procedures in dbms_stats. They are all documented. If you can't understand the descriptions of the procedures, you can ask.
Re: How to identify index type for partitioned tables [message #659913 is a reply to message #659909] Fri, 03 February 2017 05:30 Go to previous messageGo to next message
saipradyumn
Messages: 303
Registered: October 2011
Location: Hyderabad
Senior Member
Thank you very much for your continuous support .

DBMS_STATS.DELETE_TABLE_STATS - This is providing the way to delete the recently gathered statistics.

But how to gather the statics at partition /sub partition level which will keep the GLOBAL STATUS
as N which is defined at the time of creation of the table .

In the Real time world under the which scenario will go for the Deleting the recently gathered statistics?
As the gathering statistics will be used in order to selected optimized execution plan for query
Why we will go for the deleting recently gathered statistics


Thanks in advance
Sai Pradyumn
Re: How to identify index type for partitioned tables [message #659914 is a reply to message #659913] Fri, 03 February 2017 05:36 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
I fail to see the purpose of your questions. Perhaps someone else can understand what you are trying to achieve.
Re: How to identify index type for partitioned tables [message #659915 is a reply to message #659914] Fri, 03 February 2017 06:09 Go to previous messageGo to next message
saipradyumn
Messages: 303
Registered: October 2011
Location: Hyderabad
Senior Member
Nothing John ,

I try to understand the usage the DELETE_TABLE_STATS in the real time production environment
Re: How to identify index type for partitioned tables [message #659916 is a reply to message #659915] Fri, 03 February 2017 07:49 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
What high level problem are you trying to solve?
Why are you obsessed with YES or NO values?
Re: How to identify index type for partitioned tables [message #662293 is a reply to message #659916] Mon, 24 April 2017 01:26 Go to previous messageGo to next message
saipradyumn
Messages: 303
Registered: October 2011
Location: Hyderabad
Senior Member
Hi BlackSwan ,

I am trying to understand the concept behind this. Anyway thanks for your support

Thanks
SaiPradyumn
Re: How to identify index type for partitioned tables [message #662320 is a reply to message #659915] Mon, 24 April 2017 22:16 Go to previous message
trantuananh24hg
Messages: 704
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
saipradyumn wrote on Fri, 03 February 2017 12:09
Nothing John ,

I try to understand the usage the DELETE_TABLE_STATS in the real time production environment
A question to you:

What is Statistics do you understand about object, in your case, Partitioned Table?
Previous Topic: How long lock should be in order to take snapshot of locks
Next Topic: Query became very slow
Goto Forum:
  


Current Time: Mon Nov 20 20:38:46 CST 2017

Total time taken to generate the page: 0.04137 seconds