How To Find Over Fragmented Tables / Indexes

Jun 19, 2012

i 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


ADVERTISEMENT

Find Ddl For Any Indexes?

Oct 9, 2012

on 11g R2 on Win 2008

How to find ddl for any indexes built on MY_VW ?

View 6 Replies View Related

SQL & PL/SQL :: Find Out Database Name For Indexes

Jan 15, 2013

I 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 Related

Size Of Tables And Corresponding Indexes

Dec 13, 2012

Does 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?

View 4 Replies View Related

Query To Find Current Queries Not Using Indexes?

Mar 29, 2012

hint 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?

View 1 Replies View Related

Performance Tuning :: How To Find All Indexes Scripts In Particular Schema

Aug 16, 2012

getting all indexes script in particular schema.

View 4 Replies View Related

SQL & PL/SQL :: Effect Of Truncate On Associated Indexes Of Tables?

Jul 21, 2012

I 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.

View 1 Replies View Related

Performance Tuning :: How To Find Local And Global Indexes On Particular Table

Mar 19, 2012

getting how many local and global indexes on particular oracle table

View 2 Replies View Related

Performance Tuning :: How To Partition Tables And Indexes

Jan 12, 2011

So our situation is pretty simple. We have 3 tables.

A, B and C

the model is A->>B->>C

Currently A, B and C are range partitioned on a key created_date however it's typical that only C is every qualfied with created date. There is a foreign key from B -> A and C -> Bhave many queries where the data is identified by state that is indexed currently non partitioned on columns in A ... there are also indexes on the foreign keys that get from C -> B -> A. Again these are non partitioned indexes at this time.

It is typical that we qualifier A on either account or user or both. There are indexes (non partitioned on these) We have a problem with now because many of the queries use leading wildcards ie. account like '%ACCOUNT' etc. This often results in large full table scans. Our solution has been to remove the leading wildcard.

We are wondering how we can benefit from partitioning and or sub partitioning table A. since it's partitioned on created_date but rarely qualified by that. We are also wondering where and how we can benefit from either global partitioned index or local partitioned indexes on tables A. We suspect that the index on the foreign key from C to B could be a local partitioned index.

View 3 Replies View Related

SQL & PL/SQL :: Rebuild All Indexes For The Tables In Control Table?

Mar 6, 2013

I have one control table as below.I want to rebuild all indexes for the tables in control table.

The control table is having the following data.

SEQ_IDTABLENAME SCHEMA_NAME
1GEDIS_ORDER_FORM_STATES ALL
2GEDIS_NOTES ALL
3GEDIS_CARD_TYPE_AUDIT APRT
4FAX_HEADER OMS

In the control_table schema_name "ALL" means this is for 30 schemas(The table is existed in 30 schemas).Except for schema_name "ALL" ,the table is existed in the particular schema(The table is existed in the only one schema).

I tried the following code it is executing for all 30 schemas(ALL).But it is not executing for specific schemas.

CREATE OR REPLACE PROCEDURE Rebuilding_index
IS
l_sql VARCHAR2(4000);
CURSOR cur_tab_schema
IS
SELECT tablename,schema_name
FROM control_table3;

[code]....

This contains the 30 schema names.

SELECT owner_name FROM global_bu_mapping;

View 4 Replies View Related

Server Administration :: How To Find Non-indexed Queries And Columns That Require Indexes

Sep 1, 2011

How to find non-indexed queries and columns that require indexes?

View 3 Replies View Related

Performance Tuning :: Couldn't Have Indexes On Tables In Production

Sep 27, 2013

I have tried a lot by alternate solutions like rearranging the order of tables in join and moving where conditions before but no success...Its a bottleneck and I could not have indexes on these tables in production...I want to change the approach in subquery

SELECT
g.COLUMN1,
g.COLUMN2,
e.COLUMN3,
g.COLUMN4,
MIN(e.dat1) KEEP ( DENSE_RANK FIRST ORDER BY date2 Desc) * -1,
min(to_char(date3,'dd-mm-yyyy'))
[code]....

View 5 Replies View Related

New Tables And Indexes Created Not Show In Dba-segments View?

Aug 8, 2012

I have created 3 tables and some indexes, but these objects do not show up in dba_segments view. Is this a normal behaviour? Previously, with dictionary managed tablespace, I can specify the minimum extent to create, when the table/index is created. But I'm not sure how the locally managed tablespace work.

I'm using Oracle 11g R2 (11.2.0.1.0) for Microsoft Windows (x64), running on Windows 7.For the purpose of reproducing this issue, I have created the tablespaces as follow:

CREATE TABLESPACE CUST_DATA
DATAFILE 'd:appasusoradataorcl11gr2CUST_DATA01.DBF' SIZE 512K
AUTOEXTEND ON NEXT 256K MAXSIZE 2000K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO;

[code]...

View 2 Replies View Related

Text :: Advanced Compression And Oracle Tables / Indexes

Sep 11, 2012

I have noticed that Oracle text related objects, particularily the $I tables are some of the largest objects in our database. I have been actively pursuing utilizing Oracle advanced compression in our databases for OLTP table compression and LOB object compression. I have been unable to find any documentation or notes on if it is advisable to implement either table OLTP or LOB compression for Oracle text objects.

View 1 Replies View Related

Enterprise Manager :: List Of Tablespaces And What Tables And Indexes Are In Those Tablsespaces?

Oct 21, 2011

Whats the easiest way I can see a list of tablespaces and what tables and indexes are in those tablsespaces? Can this be done through OEM?

View 3 Replies View Related

Performance Tuning :: Only LOCAL Bitmap Indexes Are Permitted On Partitioned Tables

Feb 4, 2005

16:28:32 SQL> create bitmap index bp_idx_ag_id on transactions(type);

create bitmap index bp_idx_ag_id on transactions(type)
*
ERROR at line 1:ORA-25122: Only LOCAL bitmap indexes are permitted on partitioned tables

how to create bitmap index on partitiioned tables

View 3 Replies View Related

Performance Tuning :: Create Small Functional Indexes For Special Cases In Very Large Tables

Apr 5, 2012

Create small functional indexes for special cases in very large tables.

When there is a column having one values in 99% records and another values that have to be search for, it is possible to create an index using null value. Index will be small and the rebuild fast.

Example

create index vh_tst_decode_ind_if1 on vh_tst_decode_ind
(decode(S,'I','I',null),style)

It is possible to do index more selective when the key is updated and there are many records to create more levels in b-tree.

create index vh_tst_decode_ind_if3 on vh_tst_decode_ind
(decode(S,'I','I',null),
decode(S,'I',style,null)
)

To access the record can by like:

SQL> select --+ index(vh_tst_decode_ind_if3)
2 style ,count(*)
3 from vh_tst_decode_ind
4 where
5 decode(S,'I','I',null)='I'
6 group by style
7 ;

[code]....

View 2 Replies View Related

How To Check Fragmented Space

Jul 14, 2012

huge deletion happened from app side. How to check fragmented space ?

View 8 Replies View Related

Export/Import/SQL Loader :: How To Import Indexes Separately / Skipping Tables And Other Objects

Jul 9, 2013

aix 6.111.2.0.3 I have an expdp dump from prod  to be imported to our test database.I have imported it using impdp, but to my surprise the tables were imported but  lots of indexes were not created? even If I have used TRANSFORM=SEGMENT_ATTRIBUTES:N just to use the default USERS tablespace. How do I import the indexes separately, skipping the tables and other objects?

View 14 Replies View Related

Performance Tuning :: Oracle 10g - Identify Fragmented Table

Jul 18, 2010

Need sql scripts which can identify the fragmented table in oracle 10g.

View 23 Replies View Related

Performance Tuning :: Create Statements On A Highly Fragmented Disk

Nov 24, 2010

On Oracle 10g, I create, delete and drop a lot of tables. Therefore, the disk is highly fragmented.The execution of a very simple create statement takes more than a minute. If I execute the same statement but first truncate the table and insert the data, it takes less than a second!

I think this has to do with the high fragmentation of the disk. Obviously, I can defragment the disk, but I will always have a high fragmentation since I use a lot of create, delete and drops.

how I can improve the performance of create statements on highly fragmented disks?

View 7 Replies View Related

SQL & PL/SQL :: Find All The Tables Present In Database?

Jun 8, 2010

I need to find all the tables present in DB or Schema.

View 3 Replies View Related

SQL & PL/SQL :: Find Locked Tables In Hierarchy

Feb 1, 2012

Is it possible to find the locked objects in hierarchical order. Consider the below example

--Connect to scott schema
create table block_session(a NUMBER, b VARCHAR2(100))
/
insert into block_session select rownum,rownum*10 from dual connect by level<=10
/
COMMIT
/
GRANT ALL ON block_session to HR
/
update scott.block_session
SET a=10
where b=10
[code]......

Is it possible to get all locked table details in hierarchical order?

View 4 Replies View Related

SQL & PL/SQL :: Find Out Tables That Are Frequently Getting Deleted?

Mar 25, 2012

How to find out the tables that are frequently getting deleted?

View 2 Replies View Related

SQL & PL/SQL :: How To Find Tables Which Does Not Have Specific Columns

Jan 11, 2013

I need a query to find list all tables in a schema which does not have 'ADDRESS', 'CITY', 'STATE' columns.

View 8 Replies View Related

PL/SQL :: Find The Duplicate Data In Tables 11g

Feb 19, 2013

i want to find the duplicate data in my tables i have the below structure

SQL> desc x;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
FILE_CODE                                          NUMBER(20)
STREAMNUMBER                                       VARCHAR2(50)
SERVICEKEY                                         VARCHAR2(50)
CALLINGPARTYNUMBER                                 VARCHAR2(50)
CALLEDPARTYNUMBER                                  VARCHAR2(50)
CHARGEPARTYINDICATOR                               VARCHAR2(50)

[Code] ......

i found the below query it's right

SELECT * x
WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM x
GROUP by FILE_CODE,..........................,SEQ_NO ); all the column

View 7 Replies View Related

SQL & PL/SQL :: Joins - Find Missing Records Between Two Tables?

Mar 7, 2010

I have two tables A with columns a.key, a.location_code, a.status and a.first_name and table B with cols b.key, b.location_code, b.status and b.first_name.

I want to find the missing records between the two tables and as well check whether each column is populated correctly. That is if u take a record with id 1 check if loc_code is same in both the tables and if they are different, insert the key and first record column and second record column into a new table. And similarly if there is no record wiht that particular id in the second table, insert the record.

For missing records in the sense for records which are present in A but not in B, am using

Select a.key_no, a.loc_code, b.loc_code
from A,B
where a.key_no=b.key_no(+)
and b.key_no IS NULL

But the problem is I need to put some constraints on the B table like b.status='Married'and b.loc_code='CA'. When am using this condition in the above query, it's throwing me error saying cannot use outer join operator in and or or.And I could not figure out how to check for the columns being populated correctly between the two tables and at the same time check for missing ones

View 5 Replies View Related

PL/SQL :: Find Common Data In 2 Columns In Two Different Tables

Oct 22, 2012

query to find out common data from 2 columns in two different tables??

View 6 Replies View Related

Find List Of Tables Inserted / Updated In Last 6 Month

Oct 7, 2012

I tried to find out recently updated table list. I couldn't find anything.

CODE>> JUST SAMPLE  TRANSACTIONS >>

SQL> conn sam
Enter password:
Connected.

SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$y1TXhcjGApvgQAB/AQBOpA==$0 TABLE
TAB1                           TABLE
[code]........

View 1 Replies View Related

SQL & PL/SQL :: Query To Find List Of Tables Contains Particular Data In Schema?

Feb 3, 2012

I want to list the tables in a schema which contains a particular value or data.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved