Get Index Selectivity?

Dec 28, 2012

I have a table (Order_Table) in which there are 4 indexes. Suppose the column names are col1, col2, col3, col4, col5, col6. The primary key is on col1, col2, col3, col4.

There are three indexes on the table:

index1: col1
index2: col1, col2, col3, col5
index3: col1, col2, col3, col6

Now when I am issuing a select statement with a WHERE clause of col1,col2,col3 then it is using index2. Again when I am issuing a select statement with a WHERE clause of col1,col2 then it is using the primary key. understand the functionality or point to the documentation link.

View 7 Replies


ADVERTISEMENT

Text :: Index For Domain Index With Composite Domain Index (CDI) Very Slow

Jun 27, 2012

I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:

Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....

I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.

View 5 Replies View Related

Performance Tuning :: Local Index Versus Global Index On Partitioned Table

Jun 28, 2011

I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.

View 6 Replies View Related

Server Administration :: Convert Global Index To Local Index

Jun 23, 2011

I have a global index and I want to convert it to local index.Is there a way to recreate local index with out dropping the global index.

I can create a local index first and then drop the global index. But is there a way to create it with out dropping the global index, just convert it.

View 5 Replies View Related

SQL & PL/SQL :: ORA-01502 - Index Or Partition Of Such Index Is In Unusable State?

Nov 29, 2010

I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using
sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.

View 3 Replies View Related

Performance Tuning :: Index With NVL / Query Is No Longer Using Index

Nov 19, 2010

I have a query which had a join:

a.c1=b.c1 and a.c2=@var

where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like

a.c1(+)=b.c1 and nvl(a.c2,@var)=@var

This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')

But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?

View 2 Replies View Related

Performance Tuning :: Force Index If Table Not Using Index?

Aug 9, 2013

How to force an index if the table not using the index?

View 10 Replies View Related

When Index Lock Contention Occurs / How To Know Which Index Is Causing

Aug 27, 2012

We have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.

This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.

Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.

View 5 Replies View Related

SQL & PL/SQL :: Create Non-cluster Index On A Clustered Index?

Jul 11, 2012

Can we create non-cluster index on a clustered index?

View 5 Replies View Related

SQL & PL/SQL :: Difference Between Index And Clustered Index

Aug 3, 2010

Any on give explanation for difference between Index and Clustered Index?

It will be great if i get explanation how memory allocation and Execution takes place.?

View 4 Replies View Related

Performance Tuning :: Difference Between Index Rebuild And Index Rebuild Online?

Jan 10, 2011

What is the difference between index rebuild and index rebuild online.

View 3 Replies View Related

PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full

Oct 5, 2013

Let's consider such table that all rows fit into single block:

SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin

[code].....

why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2

View 8 Replies View Related

Last Used Time Of Index

Apr 7, 2011

I'm using Oracle 11g and I have a bunch of indexes and I want to check if they are being used. I just ran my workloads and now I want to see when each one was last used so I can see if it was during my timeframe or not.

After I ran my test, I found the below, but since I did not enable this, plus I have many indexes.

--Monitor an index to see if it's used
alter index SAMPLE_INDEX monitoring usage;
select * from v$object_usage where index_name = 'SAMPLE_INDEX';
alter index SAMPLE_INDEX nomonitoring usage;

View 4 Replies View Related

Index With Degree More Than 1

Jul 29, 2010

There is an index with degree 16 on a rac env. The base table has no degree. The table and index are not partitioned. Does the degree of index only affect index DDL (alter, rebuild etc)? Any effects on query (PQ)?

View 4 Replies View Related

Index In Cache

Apr 21, 2013

i have a application which uses 32 tables for retrieval in this 4 tables are important and have a size more than 100 mb can i move the index of these 4 tables cache memory to improve the applications retrieval performance if i done so ,then that will affect any other applications performance

View 1 Replies View Related

SQL & PL/SQL :: Index And Group By

Sep 5, 2012

I have a query that extracts the row with a max(record_date) within a group.

select ssn, fund, type_indicator, annuitant, cur_year, record_date, guarantee
from LC_MORTALITY
where (ssn, fund, type_indicator, annuitant, cur_year, record_date) in
(select ssn, fund, type_indicator, annuitant, cur_year, max(record_date)
from LC_MORTALITY
group by ssn, fund, type_indicator, annuitant, cur_year);

the table has index that matches the group by clause exactly.

create index IDX_LC_MORTALITY_sftayd on LC_MORTALITY
(SSN,
FUND,
TYPE_INDICATOR,
ANNUITANT,
cur_year,
record_date

However, the plan ignores the index

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1963K| 241M| | 1375K (4)| 05:21:04 |
|* 1 | HASH JOIN RIGHT SEMI| | 1963K| 241M| 9701M| 1375K (4)| 05:21:04 |
| 2 | VIEW | VW_NSO_1 | 145M| 8038M| | 241K (12)| 00:56:22 |
| 3 | HASH GROUP BY | | 145M| 8038M| | 241K (12)| 00:56:22 |
| 4 | TABLE ACCESS FULL| LC_MORTALITY | 145M| 8038M| | 219K (3)| 00:51:13 |
| 5 | TABLE ACCESS FULL | LC_MORTALITY | 145M| 9840M| | 222K (5)| 00:51:49 |
---------------------------------------------------------------------------------------------
[code]...

It uses the index but still shows "table access full" in one place. Why is there still a full access?Can I do anything to optimize further?

View 9 Replies View Related

SQL & PL/SQL :: Use Of Table Index?

Feb 10, 2012

Am pasting the sample code here, which i got from some site..

DECLARE
TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population population_type;
continent_population population_type;
howmany NUMBER;

[code]...

Here we are fetching indexes (like Antartica/Australia) from these two statements continent_population.FIRST or continent_population.LAST. If there are three or more indexes in the same table, how to fetch all of them?

I have tried using this, but doesnt work because loop variables are by default integers:

for i in continent_population.FIRST .. continent_population.LAST loop
dbms_output.put_line('i:'||i);
end loop;

View 2 Replies View Related

SQL & PL/SQL :: Explicit Name For Index Of IOT

Feb 18, 2011

Need some Clarification on the below query:

Can we specify explicit name for the index of the IOT.

View 2 Replies View Related

SQL & PL/SQL :: How To Rename A Index

Aug 8, 2011

How can i rename a index? is there a way except for drop and create.

View 2 Replies View Related

When To Use A Reverse Key Index

Jul 3, 2012

I've read some documentation about the reverse key indexes, but I haven't understood when really it can be useful use them. when it's smart to use them according to your experiences?

View 4 Replies View Related

SQL & PL/SQL :: Functional Index

May 19, 2011

How can i turn this into functional index.

WHERE (IPC_DISP_ID IS NOT NULL AND DISP_EXIST > 0)
AND (IPC_UPU_BCD IS NOT NULL AND RECPT_EXIST > 0)
AND ( (IPC_ITM_ID IS NOT NULL AND ITM_EXIST > 0) OR IPC_ITM_ID IS NULL )

View 7 Replies View Related

SQL & PL/SQL :: Delete An Index

Jun 27, 2010

I have a very huge table. There are many indexes.My focus is on the following indexes:

APT_DEDUCTIBLE A_PT_ORDREGBUS_UNIT
APT_DEDUCTIBLE A_PT_ORDREGMD_CODE
APT_DEDUCTIBLE A_PT_ORDREGPT_CODE

[Code]...

My Question is, since this 3 indexes has two columns "Bus_unit and Md_code", which index can i delete. Will they effect on database performance, if i delete one of them?

View 4 Replies View Related

PL/SQL :: Index Not Being Used In Group By

Sep 14, 2012

Here is the scenario with examples. Big table 333 to 500 million rows in the table. Statistics are gathered. Histograms are there. Index is not being used though. Why?

  CREATE TABLE "XXFOCUS"."some_huge_data_table"
   (  "ORG_ID" NUMBER NOT NULL ENABLE,
  "PARTNERID" VARCHAR2(30) NOT NULL ENABLE,
  "EDI_END_DATE" DATE NOT NULL ENABLE,
  "CUSTOMER_ITEM_NUMBER" VARCHAR2(50) NOT NULL ENABLE,
  "STORE_NUMBER" VARCHAR2(10) NOT NULL ENABLE,
  "EDI_START_DATE" DATE,

[Code]...

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT num_rows FROM user_tables s WHERE s.table_name = 'some_huge_data_table';

  NUM_ROWS                                                                     
----------                                                                     
333338434                                                                     

SQL> SELECT MAX(edi_end_date)
  2    FROM some_huge_data_table p
  3   WHERE p.org_id = some_number
  4     AND p.partnerid = 'some_string';

MAX(EDI_E                                                                      
---------                                                                      
13-MAY-12                                                                      

Elapsed: 00:00:00.00

SQL> explain plan for

  2  SELECT MAX(edi_end_date)
  3    FROM some_huge_data_table p
  4   WHERE p.org_id = some_number
  5     AND p.partnerid = 'some_string';

Explained.

SQL> /

PLAN_TABLE_OUTPUT                                                                                  
----------------------------------------------------------------------------------------------------
Plan hash value: 2104157595                                                                                                                                                                           
--------------------------------------------------------------------------------------------       
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |       
--------------------------------------------------------------------------------------------       
|   0 | SELECT STATEMENT             |             |     1 |    22 |     4   (0)| 00:00:01 |       
|   1 |  SORT AGGREGATE              |             |     1 |    22 |            |          |       
|   2 |   FIRST ROW                  |             |     1 |    22 |     4   (0)| 00:00:01 |       
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| some_huge_data_table_PK |     1 |    22 |     4   (0)| 00:00:01 |       
--------------------------------------------------------------------------------------------       

SQL> explain plan for

  2  SELECT MAX(edi_end_date),
  3         org_id,
  4         partnerid
  5    FROM some_huge_data_table
  6   GROUP BY org_id,
  7            partnerid;

Explained.

PLAN_TABLE_OUTPUT                                                                                  
----------------------------------------------------------------------------------------------------
Plan hash value: 3950336305                                                                                                                                                                            
-------------------------------------------------------------------------------                    
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                    
-------------------------------------------------------------------------------                    
|   0 | SELECT STATEMENT   |          |     2 |    44 |  1605K  (1)| 05:21:03 |                    
|   1 |  HASH GROUP BY     |          |     2 |    44 |  1605K  (1)| 05:21:03 |                    
|   2 |   TABLE ACCESS FULL| some_huge_data_table |   333M|  6993M|  1592K  (1)| 05:18:33 |                    
-------------------------------------------------------------------------------      

Why wouldn't it use the index in the group by? If I write a loop to query for different partnerid (there are only three), the whole things takes less than a second.

btw, I gave the index hint too. Didn't work. Version mentioned in the example.

View 18 Replies View Related

Index On Partitioning Key

Mar 13, 2012

There is a very large fact table that is range partitioned by a column DATE_KEY of type NUMBER(38), such that every hour is a different partition. There is a bitmap index BX$FACT#DATE_KEY on field DATE_KEY, which also is a foreign key referencing DATE_DIM (DATE_KEY). There is a different DATE_KEY for every hour, generated as YYYYMMDDHH24.

When I run

"SELECT * FROM FACT WHERE DATE_KEY >= 2012031207 AND DATE_KEY < 2012031208"

to get all the records for 7 am on March 12th, partition pruning kicks in and sees that only one partition is used. The CBO then decides to do a full scan of the partition. This behavior is correct/desired.

If, however, I run

"SELECT * FROM FACT WHERE DATE_KEY = 2012031207 AND DATE_KEY < 2012031209"

to get all the records for 7 and 8 am, Oracle knows that it will have to scan two partitions. The CBO then decides that using the BX$FACT#DATE_KEY must be a good idea and, instead of doing a full scan of the partitions, does access by local index rowid, which is many times slower.

I think I understand the cause - when more than a single partition is involved, Oracle has to use the global index stats (instead of the local ones, like in the first scenario) and the CBO decides to use it because that the selectivity for the global index is great, when in fact the query will return all the rows for that particular partition (no selectivity).

How to get the CBO to choose a full scan in the second scenario as well? I need to support ad-hoc queries generated by a BI tool, so I cannot add hints to the queries. I also can't get rid of the index on DATE_KEY, because in real life the predicates are on fields of the date dimension, not directly on the key, so I need to join on it.

View 1 Replies View Related

Bitmap Index Not Getting Used

Oct 20, 2012

I am using ORACLE 11gR1.

I was running a test on a huge table with a bitmap index on one of the columns. But bitmap index is not getting used. Below are the test details.

create table test (col1 number, col2 number);

begin
for i in 1..1000000
loop
if mod(i,2) = 0 then
insert into test values(i, 'Y');
else
insert into test values(i, 'N');
end if;
end loop;
end;
COMMIT;

The intention here is to have only two distinct values in the entire table. Based on these values I will not build BITMAP index on col2.

CREATE BITMAP INDEX BITMAP_TEST on TEST(col2) COMPUTE STATISTICS;

Now when I run the below query, it doesn't uses BITMAP index. Instead the explain plan shows a full table scan.

select * from test where col2 = 'Y';

Now when I force ORACLE to use index through hint, the cost is too high while using the bitmap index(probably why the ORACLE chose not to use the index at the first stage).

I have read somewhere, that BITMAP index is useful when we have more than 1 or 2 bitmap indexes on other columns of the same table as well And the query should be like

select * from table where col1 = 'Y' and sex = 'F';

In this case oracle will use BITMAP but not in the case where there is only one column that has BITMAP index.

Considering all the factors stated above, is there any way I can fine tune my original query?

select * from test where col2 = 'Y';

View 7 Replies View Related

PL/SQL :: What Is Non Clustered Index

Sep 25, 2013

What is non-clustered index, How to create non-clustered index.

View 10 Replies View Related

Query Not Using Index?

Jul 18, 2012

Query
SELECT case.case_objid FROM clrods.case@clrods.equant.com case, table_x_cwp_tickect_details_vw t WHERE CASE.case_condition_cd IN ('OPEN', 'OPEN-DISPATCH', 'OPEN-REJECT', 'OPEN-RETURNED') AND case.case_type_cd in ('CUSTOMER FAULT', 'CHRONIC','SCHEDULED ACTIVITY','PROBLEM') AND ROWNUM <= 500 AND case.case_objid = t.ticket_objid AND ( ( case.account_id = '672286' ) ) ORDER BY case.case_id DESC

From PROD

Plan
SELECT STATEMENT HINT: FIRST_ROWS Cost: 2,629 Bytes: 221,500 Cardinality: 500
     37 SORT ORDER BY Cost: 2,629 Bytes: 221,500 Cardinality: 500
          36 COUNT STOPKEY
            
[code]...

From DEV platform

Plan
SELECT STATEMENT HINT: FIRST_ROWS Cost: 591 Bytes: 61,134 Cardinality: 138
     37 SORT ORDER BY Cost: 591 Bytes: 61,134 Cardinality: 138
          36 COUNT STOPKEY
        
[code]...

1. Index is rebuild
2. stats are up to date
3. Redef on table is done.

View 7 Replies View Related

SQL & PL/SQL :: Index Became Unusable

May 16, 2011

why i encountered the error that the index became unusable.

View 2 Replies View Related

PL/SQL :: Foreign Key Index

Mar 13, 2013

You should almost always index foreign keys because they are frequently used in joins. In addition, if you intend to delete or update unique or primary keys on the parent table, you should index the foreign keys to improve the locking of child records.what I don't understand are, I should create index on foreign key column in parent table or child table or both ?

View 1 Replies View Related

Index Creation Estimates

May 13, 2013

I have a quiet large table (around 140 GB) and i want to create an index on it. I was wondering, before i fire my my create index statement, i know in advance how long will it take to complete? I know after the create index is executed, we can monitor and gets some estimates from longops view. but is there any way we estimate before hand?

Also my database is in archive log mode and what would be the fastest way to create an online index?

View 5 Replies View Related







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