Index Name in User_Indexes with No Column Name [message #232130] |
Thu, 19 April 2007 10:24  |
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 #232136 is a reply to message #232131] |
Thu, 19 April 2007 10:42   |
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   |
 |
Mahesh Rajendran
Messages: 10708 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   |
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 #232159 is a reply to message #232140] |
Thu, 19 April 2007 12:18   |
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   |
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 #232197 is a reply to message #232190] |
Thu, 19 April 2007 15:08   |
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.
|
|
|
|
|