Home » SQL & PL/SQL » SQL & PL/SQL » Index Name in User_Indexes with No Column Name
Index Name in User_Indexes with No Column Name [message #232130] Thu, 19 April 2007 10:24 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,
I am confused, Please have a look, User_indexes shows some indexes and no column name and it's Unique,
User_ind_columns doesn't show these indexes, and seems like names are also System generated, from where and how it came

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL> select table_name,index_name from user_indexes where table_name='XMIT_ADDR';

TABLE_NAME            INDEX_NAME                  INDEX_TYPE
------------------------------ ------------------------------
XMIT_ADDR          SYS_IL0000023429C00010$$          LOB
XMIT_ADDR          SYS_IL0000023429C00011$$          LOB
XMIT_ADDR          SYS_IL0000023429C00012$$          LOB
XMIT_ADDR          SYS_IL0000023429C00019$$          LOB
XMIT_ADDR          SYS_IL0000023429C00021$$          LOB
XMIT_ADDR          XIF_XMTADD_XMTSRVID               NORMAL
XMIT_ADDR          XPK_XMTADD                        NORMAL

7 rows selected.

SQL> select a.index_name,a.index_type,b.column_name from user_indexes a,user_ind_columns b where
  2      a.index_name=b.index_name and a.table_name='XMIT_ADDR'
  3  /

INDEX_NAME            INDEX_TYPE     COLUMN_NAME
-----------------------------------------------------------------XIF_XMTADD_XMTSRVID   NORMAL        XMIT_SRV_ID

XPK_XMTADD           NORMAL         XMIT_ADDR_ID


Thanks

[Updated on: Thu, 19 April 2007 10:26]

Report message to a moderator

Re: Index Name in User_Indexes with No Column Name [message #232131 is a reply to message #232130] Thu, 19 April 2007 10:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Those are lobindexes.

[Updated on: Thu, 19 April 2007 10:30]

Report message to a moderator

Re: Index Name in User_Indexes with No Column Name [message #232136 is a reply to message #232131] Thu, 19 April 2007 10:42 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Mahesh for quick response
This is the table Structure
SQL> desc XMIT_ADDR
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 XMIT_ADDR_ID                              NOT NULL NUMBER(10)
 XMIT_ADDR_EMAIL_NM                                 VARCHAR2(100)
 RMIT_ADDR_FAX_TN                                   NUMBER
 XMIT_ADDR_MODE_CD                                  CHAR(10)
 RMT_PATH_NM                                        VARCHAR2(50)
 XMIT_ADDR_USR_NM                                   VARCHAR2(50)
 XMIT_ADDR_PSWD_TX                                  VARCHAR2(10)
 XMIT_ADDR_TIMEOUT_DU                               NUMBER(10)
 RMT_FILE_NM                                        VARCHAR2(50)
 XMIT_ADDR_CMD1_IM                                  BLOB
 XMIT_ADDR_CMD2_IM                                  BLOB
 XMIT_ADDR_CMD_JCL_IM                               BLOB
 XMIT_ADDR_FAX_ATTN_NM                              VARCHAR2(50)
 XMIT_ADDR_EMAIL_ATTN_NM                            VARCHAR2(50)
 LAST_UPDT_USR_ID                                   NUMBER(10)
 LAST_UPDT_TS                                       DATE
 XMIT_SRV_ID                                        NUMBER(10)
 XMIT_HDR_CMD_IN                                    NUMBER(1)
 XMIT_HDR_CMD_CL                                    CLOB
 XMIT_FTR_CMD_IN                                    NUMBER(1)
 XMIT_FTR_CMD_CL                                    CLOB



This means columns with BLOB and CLOB datatypes has these indexes,so you mean system generates them, can we get rid of them , The problem is all the indexes on the tables are on differennt tablespace (Index_tablespace) and these LOB indexes are on Data tablespace, so should this be moved, or if we not using and if these are not helping us should we dropped this LOB indexes.


Thanks

[Updated on: Thu, 19 April 2007 10:43]

Report message to a moderator

Re: Index Name in User_Indexes with No Column Name [message #232137 is a reply to message #232136] Thu, 19 April 2007 10:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>these LOB indexes are on Data tablespace, so should this be moved
LOB indexes should not moved. Oracle recommends (at least used to, not sure about the latest texts), to have Lobindex in same tablespace as lobdata (Even in case of regular index, keeping it in a separate tablespace helps nothing but maintenance).

Look into docs.
When you create a lob object, a lobindex is automatically created. Do not attempt to drop it.
scott@9i > create table myclob
  2  (c1 clob);

Table created.

scott@9i > select table_name,index_name from user_indexes;

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
MYCLOB                         SYS_IL0000026921C00001$$

[Updated on: Thu, 19 April 2007 10:48]

Report message to a moderator

Re: Index Name in User_Indexes with No Column Name [message #232140 is a reply to message #232137] Thu, 19 April 2007 10:58 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Again Mahesh,

Well we had 1 Oracle Consultant from Oracle Corp and here is a cut from his long list of recommendations
10.1	Separation of Tables and Indexes into Different Tablespaces
If a table is physically separated from its indexes, contention to the same hardware resources is reduced as Oracle can access the table and the indexes at the same time.
Recommendations
•If possible separate index and tables for the users listed above.

And that's why i started looking at indexes on Data Tablespaces,
Thanks for info on LOB indexes,



Mahesh if you get a chance please comment on this,
http://www.orafaq.com/forum/t/80111/82337/

because i need to revisit on this, this is also from one of the recommendations from Oracle regarding Pinning of Frequently used Packages/Code.

Thanks Again
Re: Index Name in User_Indexes with No Column Name [message #232143 is a reply to message #232140] Thu, 19 April 2007 11:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> reduced as Oracle can access the table and the indexes at the same time
Just plain wrong. Myth.
Well known fact is,
Oracle will not access in parallel.
First index is accesed. Then the table data.
This topic is so dead Smile. Just google/search this forum about it and you get to know the facts.
>>•If possible separate index and tables
Only for ease of administration. It has nothing to do with performance.
Re: Index Name in User_Indexes with No Column Name [message #232144 is a reply to message #232143] Thu, 19 April 2007 11:03 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> http://www.orafaq.com/forum/t/80111/82337/
Already in good hands. Got nothing new to say Smile
Re: Index Name in User_Indexes with No Column Name [message #232150 is a reply to message #232144] Thu, 19 April 2007 11:25 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Just to back up Mahesh's reply:

http://www.dbforums.com/showthread.php?t=356696
Re: Index Name in User_Indexes with No Column Name [message #232159 is a reply to message #232140] Thu, 19 April 2007 12:18 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
faiz_hyd wrote on Thu, 19 April 2007 11:58
Thanks Again Mahesh,

Well we had 1 Oracle Consultant from Oracle Corp and here is a cut from his long list of recommendations
[code]
10.1 Separation of Tables and Indexes into Different Tablespaces
If a table is physically separated from its indexes, contention to the same hardware resources is reduced as Oracle can access the table and the indexes at the same time.
Recommendations



How long ago was this? Really, the advgice provided by Mahesh and link by Andrew are the relevant ones. Search google for Oracle Myths I'd be scared if an Oracle Consultant spewed this out. Either their standard have really gone down the dumper, they are just regurgitating decades old info. I don't know if I would trust anything else this person said.

[Updated on: Thu, 19 April 2007 12:20]

Report message to a moderator

Re: Index Name in User_Indexes with No Column Name [message #232176 is a reply to message #232159] Thu, 19 April 2007 13:43 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks All for their valuable inputs,
Just and FYI, i am pasting some more recommendations assigned by them to our team to complete them.
Best Practices

•	Oracle processes constantly access data files and redo log files. 
If these files are on common disks, there is potential for disk contention. 
Redo log files are written by the Log Writer process (LGWR) when a transaction is committed. 
Information in a redo log file is written sequentially. This sequential writing can take place much faster if there is no concurrent activity on the same disk. 
So separate database data files from redo log files by putting them on separate disks.
•	Most log switches should be more than 15 minutes apart.
•	Ensure that the online redo logs are the same size.

 Recommendations

•	Recreate online logfiles on system with a larger size, and perhaps add one or more redo log files. Nearly all the log switches on system should be less than the recommended minimum of 15 minutes apart for most log switches. 
•	In alert.log we have observed “Can not allocate a new log” messages, so you need increase the redo log groups.



Best Practices
•	Each production table should have a primary key.



Best Practices
•	Indexes should be checked regularly if they have more leaf blocks than the table.
•	Reorganize fragmented indexes.
•	Rebuild indexes online. Rebuilding indexes online does not affect to table base. The following advantages will be provide using alter index <name> rebuild online:
o	DMLs are allowed on the base table
o	Although slower compared with coalesce, coalesce cannot be done “online”
o	Base table is only locked in shared mode (as opposed to exclusive mode for offline rebuilds) 
o	Intermediate data is stored changes are recorded in a temporary journal table during the index rebuild, which will be used to update the new index at the end of the ONLINE processing.


Best Practices
•	Repair the tables that are commonly subjected to full table scans and have an excessive amount of wasted space. Resetting the HWM on these tables will result in shorter response times during full table scans and increased throughput for the system.
•	Reorganize sparse tables


Best Practices
•	Large objects should be pinned via DBMS_SHARED_POOL.KEEP in the shared pool to improve performance and to avoid fragmentation.
 Recommendations
•	Large objects should be pinned via DBMS_SHARED_POOL.KEEP in the shared pool to improve performance and to avoid fragmentation.


Any recommendation or documentation on DBMS_SHARED_POOL.KEEP ,


Thanks

[admin: Trimmed line lengths]

[Updated on: Thu, 03 May 2007 04:02] by Moderator

Report message to a moderator

Re: Index Name in User_Indexes with No Column Name [message #232185 is a reply to message #232176] Thu, 19 April 2007 14:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Rebuild indexes online.....
What was not said is,
after rebuilding the indexes on-line, you might notice the tablespace has actually grown in size
(to hold the temporary journal). So it is not always the best option.

>>Reorganize fragmented indexes.
Fragmentation is another dead topic and a widely propogated myth.
Indexes need to be rebuilt only on very special occasions and definately not ona regular basis. .
With LMT use uniform extent size and your fragmentation is ALMOST GONE.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2667245192483
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7037830724934

< quoting TomKyte from several of his replies>
do not pin anything.  The shared pool does the work for you.  pinning was a short term  solution to a problem which the large_pool solved entirely.  pinning is a waste of your time.
pinning was used before we had a large pool.  It is not something I recommmend doing in 8.x and up, its just not necessary in most all cases) .....
Pinning was necessary in 7.x especially  .....
pinning was "useful" for big packages before the 4k pagesize stuff. after that, not very useful .....

< /endquote>

PS:
As Joy already stated, this information/document of yours is just outdated and you are still following it.
It would not be fair to pick on everything Smile
Just look up into to the latest documentation. All the rest of information are pretty generic.
Re: Index Name in User_Indexes with No Column Name [message #232190 is a reply to message #232185] Thu, 19 April 2007 14:39 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Everybody for their Responses,

Mahesh / Joy that information is outdated, but will you beleive we have this information provided to us as a list of recommendations just 1 week back from a consultant from Oracle Corp who has been on our site for 2 weeks analysis and these are from his list of Action Items.

Well you know when things are handed to top manager's and they think all the changes as recommended should be completed ASAP, but just as you said regarding Pinning and etc.....

If possible i will send that DOC on your personel email ID's some time to have a look on all of them, so that atleast you can get an idea what to expect when a consultant comes from Oracle Razz


Thanks all,
Re: Index Name in User_Indexes with No Column Name [message #232197 is a reply to message #232190] Thu, 19 April 2007 15:08 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
If the document is copyrighted, I would not want to have any part of it. I would be interested though what company provided it. Was it actually Oracle corporation or a consulting firm approved by Oracle. I have to tell you, the "best practices" looks suspiciously familiar and I have a bad feeling this is just one of those people who has no common sense or logic, but just tows the company line from 10-15 years ago and does not adapt to the evolution of the Oracle.
Re: Index Name in User_Indexes with No Column Name [message #234324 is a reply to message #232130] Mon, 30 April 2007 16:56 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Just my 2 cents... It has been my experience that the people in oracle consulting that are good, leaves to make real money as a consultant. Some very sharp people work for oracle, but I have been disappointed in the past by Oracle Consulting.
Re: Index Name in User_Indexes with No Column Name [message #234406 is a reply to message #234324] Tue, 01 May 2007 09:03 Go to previous message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
thanks a lot guys..very informative
Previous Topic: Regular Expression In Query Help Needed...
Next Topic: SQL How do I return all records between two times
Goto Forum:
  


Current Time: Thu Mar 28 12:32:13 CDT 2024