Find Ddl For Any Indexes?
Oct 9, 2012on 11g R2 on Win 2008
How to find ddl for any indexes built on MY_VW ?
ADVERTISEMENT
SQL & PL/SQL :: Find Out Database Name For Indexes
Jan 15, 2013I am trying to find out database name for the unusable indexes thru the query; I am using dba_indexes for the index name but not sure which view I need to join with to find the db name.
View 11 Replies View RelatedHow To Find Over Fragmented Tables / Indexes
Jun 19, 2012i am using oracle 10g. is there a way to find out the over fragmented tables/indexes ,so that reorganizing or rebuilding them will improve sql performajnce?
View 4 Replies View RelatedQuery To Find Current Queries Not Using Indexes?
Mar 29, 2012hint of which DBA views we need to query to find current running queries which are not using indexex?
I suppose v$session is one , what else we need to look to?
Performance Tuning :: How To Find All Indexes Scripts In Particular Schema
Aug 16, 2012getting all indexes script in particular schema.
View 4 Replies View RelatedPerformance Tuning :: How To Find Local And Global Indexes On Particular Table
Mar 19, 2012getting how many local and global indexes on particular oracle table
View 2 Replies View RelatedServer Administration :: How To Find Non-indexed Queries And Columns That Require Indexes
Sep 1, 2011How to find non-indexed queries and columns that require indexes?
View 3 Replies View RelatedConvert Indexes Of Non-partitioned Table To Hash Partitioned Indexes?
Sep 10, 2012RDBMS - 11.1.0.7, I it possible to convert indexes of a non-partitioned table to hash partitioned indexes by retaining table as non-partitioned?
If yes, is this what it is Creating a Hash-Partitioned Global Index - can be created for partitioned and non-partitioned tables?
Indexes In Oracle
May 9, 2011this is just a generic question. If a table has multiple Indexes on it say a table of 20 columns where 8 columns have indexes on it (each columns out of the 8 has an index on it and out of which three columns contain unique indexes on it).
If i run a query on this table where only two columns are specified in the where clause are the remaining 6 indexes on the table scanned during query execution.
Update Global Indexes?
Aug 15, 2011If I have owner, table_name is there a query I can issue that will tell me if I have to add the "update global indexes" clause when dropping a partition from a table?
View 1 Replies View RelatedSQL & PL/SQL :: Indexes On Materialized View
Mar 16, 2011Why we can create indexes only on materialized view and not on normal views?
View 8 Replies View RelatedSQL & PL/SQL :: Locking And Foreign Key Indexes
Sep 1, 2013Say we have an employee(id_emp) table with a primary key on id_emp. We have also some history tables emp_stuff with columns say (id_emp, dat_event, some_stuff) with primary key id_emp, dat_event.
This means that we have a unique index on (id_emp,dat_event). We also have a foreign key id_emp that references employee(id_emp). When we update id_emp on employee, we still have a lock on emp_stuff. According to this (end of the page) :
Quote:So, in short, with releases prior to Oracle Database 11g Release 1, you will want an index on the foreign key of the child table if you do any of the following:
Update the parent table primary key
Delete from the parent table
Merge into the parent table
So is id_emp in emp_stuff considered as indexed (through the unique index of the primary key) or do we have to add an explicit index
like this CREATE INDEX emp_stuff ON emp_stuff(id_emp) to avoide child table locks?
SQL & PL/SQL :: Using Function-based Indexes In SE?
Mar 29, 2012I have noticed a very questionable phrase on an article updated in 2011: "Oracle SE may allow you to create a function-based index, but you must pay for an EE license to use FBI's." [URL] Is this true? I have tested a FBI on my SE and works just fine.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> create table t ( col varchar2(10));
Table created.
SQL> create index t_idx on t(upper(col));
Index created.
SQL> insert into t values('a');
[code]....
SQL & PL/SQL :: Indexes For Query Performance?
Mar 16, 2010Lot of my tables are just for join purpose , a table with just with two columns both FK , I did not add any composite PK to this table, lot my queries uses this table , because it does not have composite PK will the query be slow ? what I have to do to increase performance querying with such tables ?
View 5 Replies View RelatedSQL & PL/SQL :: How Many Different Types Of Indexes Have In Oracle
Jul 12, 2012How many different types of indexes we have in oracle?
View 2 Replies View RelatedHow Efficient Are Partitioned Indexes
Feb 22, 2013Using Oracle 11.2.0.3
We are evaluating partition strategies with view to achieving performance gains in reporting in particular. How efficient are partitioned indexes in this regard e.g.
just partitioned indexes on an un partitioned table.
One large fact table with durrogate keys on which have bitmpa indexes which link to unique key in associated dimensions. Considering partitioning the bitmap index which links to the largest dimension and similarly partition the dimension key on largest dimension.
PL/SQL :: Indexes Output Format?
Jan 16, 2013currently i am using below version
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
{CODE}
here is my query to find out the list of indexes and positions in a single schema.SELECT
user_indexes.TABLE_NAME||CHR(9)||
user_indexes.INDEX_NAME||CHR(9)||
TRIM(user_ind_columns.COLUMN_NAME)||CHR(9)||
user_ind_columns.column_position ||CHR(9)||
[code]...
i am trying to spool the output to .txt file. in textfile the output should be in below format :
Table_name Index_name column_names
---------------- ---------------- ------------------
ACS_TEST ACS_TEST_IDX COL1,COL2
C_COLLEGE INX_COLL STUDENT_ID,FIRST_NAME,LAST_NAME,DEPT_ID
Note : Based on the position value the column name have to be placed.
Check That Indexes Need Rebuild?
Jul 31, 2013How can i check that indexes need rebuild?
View 6 Replies View RelatedSize Of Tables And Corresponding Indexes
Dec 13, 2012Does size (number of rows) of table and corresponding indexes have influence on INSERT operations (bigger table - slower insert)?
I supposed that if select returns the same rows in small and big table, there will be no difference in performance?
Combining Hash Cluster And Indexes
Oct 28, 2010A basic select and group by query I am optimising for my Database course has returned results that indicate it will perform better on a clustered index when returning a smaller number of rows (5% of the largest table) and on a hash clustered index when returning higher volumes (50% and 80%). I understand that it is possible to use more than one index type on a table to improve performance, but I am struggling to understand how I might establish a hash cluster and a cluster on the same table? and then use hints to drive the query down one access path or the other.
Site admin - this site is a very useful resource.
Fastest Way To Move Table With Indexes?
Jan 18, 2011I want drop some old partitions from big table but this will not increase free space on disk. So I want to move table with indexes to anothers tablespaces. What is the fastest way to do that? ALTER TABLE ... MOVE TABLESPACE ...? CTAS ? Or something else?
View 4 Replies View RelatedSQL & PL/SQL :: Which Constraint For Bitmap Join Indexes
Jul 18, 2013I have a few dimension tables with surrogate keys and a fact table with foreign keys on columns referring to the dimension table. I want to create primary keys on the dimension tables and foreign keys on the fact table, but with minimal overhead of constraint validation when loading the data.
Other than bitmap indexes on the FK columns on the fact table, I also want to create a bitmap join index on the fact table joined to the dimension table on a handful of dimension columns.
Which is the best suited constraint definition? Only RELY, or RELY + DISABLE, or RELY + DISABLE + NOVALIDATE, or RELY + NOVALIDATE, or any other?
Replication :: No Indexes On Materialized View?
Jan 9, 2008I have created materiaized view
Create Materialized View table_name
Tablespace FG_Report
Build Immediate Refresh Force
Start with SYSDATE next SYSDATE+5/1440
As Select * from table_name@dblink;
after creating, i don't see indexes on the the materialized view created. where as in actual table we have indexes..
Can we replicate indexes along with Materialized views?
SQL & PL/SQL :: Procedure To Rebuild Partitioned Indexes
Oct 3, 2012I have a task to create a procedure that disables (particular) partitioned indexes and REBUILD after ETL load.
Developers wants 3 parameters for the procedure:
1) TABLE_OWNER2) TABLE_NAME3) TRANSACTION_DATE = 13-JUN-12
And here is what I have done. I have created a procedure that takes 3 input parameters, please see the attached script:
1) TABLE_OWNER 2) TABLE_NAME 3) PARTITION_NAME - requires to query the particular partition to get the partition name
a situation where they will input dates as the partition was on a DATE column, now my challenge is how to incorporate this into the procedure to accept DATE as an input which will require one to query the particular table to get dates.I thought of using (HIGH_VALUE - 1) to get the dates from ALL_IND_PARTITIONS.
SQL & PL/SQL :: Indexes With Wild Card At Both Sides?
Sep 2, 2010optimising the below query ....
SELECT
CBU_CD,
MKTG_SUB_SEGMENT_A_NM,
BILLG_SYSTEM_ID,
BILLG_ACCT_NO,
[code]...
SQL & PL/SQL :: Effect Of Truncate On Associated Indexes Of Tables?
Jul 21, 2012I know that truncate is a ddl operation that removes all the data from the table and set the HWM to very beginning. I am curious to know, is anyhow indexes will be affected on the issuance of truncate command or we need to rebuild the indexes after issuing truncate.
Also is there any way to know that how many rows/blocks a select statement is scanning because of the effect of HWM even the table has 0 rows.
SQL & PL/SQL :: Are NULLs Indexed In BITMAP Indexes
Jul 24, 2012Are NULLs get indexed in bitmap indexes? How can I verify that.
View 3 Replies View RelatedPerformance Tuning :: Practices With Indexes?
Dec 10, 2010So I was reading about indexes here:[URL]...1Is there any reason to NOT use an index? If there aren't, then should you use an index on every column on every table?
What is the general best practice with indexes? After reading the section, it seems that there are only positive impacts of using an index, so why are they not automatically created?
Server Administration :: Indexes To Be Rebuild?
Jan 30, 2010How to find indexes which are eligible to rebuild in the database environment? Is there any query to find it?
View 7 Replies View RelatedWhen Do Global And Local Indexes Becoming UNUSABLE?
Nov 29, 2012RDMS Version : 11.2.0.2
Platfomrm : AIX 6.1
For partitioned tables (RANGE, LIST types ) what are circumstances when a Global or a Local Index become UNUSABLE ? I was told that in some circumstances where the Indexes become UNUSABLE , not even a SELECT query against the table will work. Is this true ? For partitioned tables with Global Index, the global index will be listed in DBA_INDEXES . Right ?