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


ADVERTISEMENT

How To Rebuild Huge Indexes Upto 30gb In Size

Sep 7, 2011

i am having some indexes upto size of 40gb, i need to rebuild them having only 15 temp tablespace and without downtime

View 5 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

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

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

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

Enterprise Manager :: Querying Grid Control Tables To Find Size And Name Of Database?

Sep 25, 2012

query to get the name and size of all the databases associated to grid..

I need to find this to do capacity planning in our environment.

View 6 Replies View Related

Convert Indexes Of Non-partitioned Table To Hash Partitioned Indexes?

Sep 10, 2012

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

View 7 Replies View Related

Server Utilities :: Estimate Size Of FlatFile Based On Table Size?

May 8, 2013

We are planning to export the table data to a file pipedelimited. How do i estimate the size of the FlatFile based on the table size? or avg rowlength

View 3 Replies View Related

Server Administration :: How To Reduce Size Of TEMP DBF File Size

Apr 13, 2011

I am using oracle 8.1.5 database and my temp01.dbf file size is increased upto 19.8 GB now i want reduce its size .

View 13 Replies View Related

Indexes In Oracle

May 9, 2011

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

View 1 Replies View Related

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

Update Global Indexes?

Aug 15, 2011

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

SQL & PL/SQL :: Indexes On Materialized View

Mar 16, 2011

Why we can create indexes only on materialized view and not on normal views?

View 8 Replies View Related

SQL & PL/SQL :: Locking And Foreign Key Indexes

Sep 1, 2013

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

View 33 Replies View Related

SQL & PL/SQL :: Using Function-based Indexes In SE?

Mar 29, 2012

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

View 11 Replies View Related

SQL & PL/SQL :: Indexes For Query Performance?

Mar 16, 2010

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

SQL & PL/SQL :: How Many Different Types Of Indexes Have In Oracle

Jul 12, 2012

How many different types of indexes we have in oracle?

View 2 Replies View Related

How Efficient Are Partitioned Indexes

Feb 22, 2013

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

View 5 Replies View Related

PL/SQL :: Indexes Output Format?

Jan 16, 2013

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

View 2 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

Check That Indexes Need Rebuild?

Jul 31, 2013

How can i check that indexes need rebuild?

View 6 Replies View Related

Combining Hash Cluster And Indexes

Oct 28, 2010

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

View 5 Replies View Related







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